You set up an Excel spreadsheet for someone else to use and then they add some rows to the data and the SUM() function no longer work! Sound familiar?

Try this tip.

Set a name called cell_above or similar title. With this name we will create a relative reference to the cell above. In my example, I start in cell B11 and create a range called cell_above with the Refers to: =Sheet1!B10 Notice that the reference is relative, that is there are no $ signs in the reference. Normally range names will have absolute references.

Now I can write my sum formula like this =SUM(B5:cell_above). If rows are inserted above Row 11 the sum formula still work. Try it for yourself. Download cell above example and have a play.

How often do you format numbers on your worksheets to currency format?  Here’s an Excel tip that’s easy to use and easy to remember.

Pressing Ctrl+Shift+$ will apply the Currency format with two decimal places and negative numbers in parentheses to the cells you have selected. This works in the UK to format £. Remember is it Ctrl+Shift+4 ($ not £). Excel format to you default  or local currency.

I was asked the other day “How do I change the text in an Excel spreadsheet so they’re all uppercase or all lowercase?” I guessed that text had already been entered so I gave this response.

Personally I don’t like upper case text it looks the writer is shouting. To convert cells that have upper case text to lower case use the =LOWER() function.

If you need to shout and use upper case then =UPPER() will convert text to upper case

If you are looking for titles try =PROPER(). This will convert to text that has the first letter of each word capitalised.

If I have the following text in a cell: Here is my Text

• UPPER() returns HERE IS MY TEXT
• LOWER() returns here is my text
• PROPER() returns Here Is My Text

I don’t believe that there is an option in Excel to enter data and have it formatted as Upper or Lower case.