Excel etc.

Excel (and other) tips

[exceletc-blog]

How to remove trailing spaces from text within a cell

When typing names or words, you might hit the space bar after the last word resulting in a trailing space. This can cause errors when trying to perform look up functions to match data or look up data in adjacent cells also related to the record.

For example: to a look up function in excel,

Mrs(SPACE)Alice(SPACE)Smith

is not the same as

Mrs(SPACE)Alice(SPACE)Smith(SPACE)

and can return an error or not found value. This can be corrected so that the two instances of the name above match exactly by removing the trailing space.

To do this:

Use =trim()

If text is in cell A1, use =trim(A1)

[exceletc-blog]

How to change text from all CAPITAL LETTERS or capitalise the first letter of each word in a cell

Useful for text that is all CAPITALS or text that has lower case for first letter in each word. To change sentence case of a text string with a cell from this:

mr james smith

or this:

MR JAMES SMITH

to this:

Mr James Smith

To do this:

Use =proper()

If text is in cell A1, use =proper(A1)

[exceletc-blog]

How to select an entire row or column (keyboard shortcut)

Select an entire row.

To do this:

shift + space

Select an entire column.

To do this:

ctrl + space

[exceletc-blog]

How to edit a cell without using the mouse (keyboard shortcut)

Edit the contents of a cell (a formula, text, or number) using keyboard shortcut.

To do this:

F2

[exceletc-blog]

How to add a carriage return within a cell

If you have text within a cell and want to add a carriage return (or line break) within the cell (without moving to the cell below).

To do this:

alt + enter

[exceletc-blog]

How to show all formulae on a worksheet at once

To do this:

ctrl + `

(The accent key is shared with the tilde ~ key, next to the 1 key)

Click a cell containing a formula to see the cells the formula relates to. Excel will highlight these cells with colours.

Press ctrl + ` to switch back to regular view.