IT Know-How Tip: Who knew Excel could do that?

10 Apr 2013

This article forms part of a series of ongoing IT Know How Tips produced by The Directorate of IT Services. Keep an eye out for our tips every other week to find out relevant hints and facts.

Microsoft Excel allows you to use a large variety of functions and comprises so many features, that sometimes it’s hard to know where to start. You may be missing a trick without even realising!

Here are our top five hints (for helpful illustrations on how to follow these hints, see our Knowledge Base article):

1. Display the date and time

A useful feature in Excel which allows you to reference the date and time in your worksheet: type TODAY() into the formula bar to return today’s date and NOW() to return the current time. As these are based on your system clock, they will update every time you open the document. Want to include a static time to document your work? Just click on the cell and hold down CTRL and ; (semicolon). Hold down CTRL SHIFT and ; to get a static time.

2. Show formulas

Toggle between two types of display modes: formula results (Excel’s normal display) and formulas. This makes it easy for you to find cells containing formulas and quickly read through all formulas to check for errors. Using your keyboard, hold down CTRL and ‘ (the grave accent is found top left of your keyboard, next to 1).

3. Select all with one click

Need to select the entire worksheet? Just click the little grey box in the top left corner of your worksheet. It’s the box to the left of the column letters and above the row numbers.

4. Change sheet names and colours

Personalise your worksheets instead of sticking to ‘sheet 1’ and ‘sheet 2’. Simply right click on the worksheet tab (bottom left of worksheet) and select ‘Rename’, or double click on the tab and start typing a new name.

You can even colour code your sheets by right clicking on the worksheet tab and selecting ‘Tab Color’ to choose a new colour!

5. Copy formulas with the fill handle

There is no need to keep recreating a function or formula each time you need it, simply use the fill handle to copy formulas from one cell to another. All you need to do is type out the formula once, press enter, hover your mouse over the bottom right of the cell until you see a black cross and drag down. If you need to fix a cell reference within your formula (so it stays the same as you copy the formula), use the $ sign as follows: A1 to $A$1. You can also do this by highlighting the cell and pressing F4 on your keyboard.

Find out how to do more in Excel:

For more hints and tips, just search ‘excel function’ in the online Knowledge Base.