75 tips and tricks for your favourite spreadsheet. Microsoft Excel

1.Convert rows to columns
You can convert rows to columns (and columns to rows) by highlighting the cells you want to switch around, clicking on Edit, Copy, selecting a new cell and then going to Edit, Paste Special… Finally, place a tick in the Transpose box on the dialog box and click on OK.
2.Calculate time between dates
Enter in a cell the formula =A2-A1, where A1 is the earlier date, and A2 the later one. Don’t forget to convert the target cell to number format – do this by highlighting the cell, clicking on Format, Cells…, picking on the Number tab and selecting Number from the Category: list.




1.Convert rows to columns
You can convert rows to columns (and columns to rows) by highlighting the cells you want to switch around, clicking on Edit, Copy, selecting a new cell and then going to Edit, Paste Special… Finally, place a tick in the Transpose box on the dialog box and click on OK.
2.Calculate time between dates
Enter in a cell the formula =A2-A1, where A1 is the earlier date, and A2 the later one. Don’t forget to convert the target cell to number format – do this by highlighting the cell, clicking on Format, Cells…, picking on the Number tab and selecting Number from the Category: list.
3.Enter URLs as text,
not hyperlinks To prevent Excel from converting written Internet addresses into hyperlinks, add an apostrophe to the beginning of the address, for example ‘www.futurenet.com.
4Calculate running totals Enter the numbers to be added in column A, say A1 to A5, then enter =SUM($A$1:A1) into column B. Highlight the cells beside the ones with
numbers in (in our example, B1 to B5)and go to Edit, Fill, Down. This places
the running total of the figures in A1 to A5 in the adjacent column.
5.Remove hyperlinks
from your work If Excel has already converted your written URL into a hyperlink, you can cancel it by right-clicking on the offending address and selecting Hyperlink, Remove Hyperlink from the menu that pops up.
6.Fit wide tables to the page width
To make your tables fit neatly on the page, click on File, Page Setup…,select the Page tab, click on the Fit to: radio button and pick 1 page wide.
Click on the tall box and press [Delete], leaving the box empty.
7.Hide your data from prying eyes
If you want to hide from view any sensitive data, highlight the relevant
cell and click on Format, Cells… Click on the Numbers tab, select Custom
from the Category: list, double-click on the Type: input box and enter ;;;.
Undo the operation to make your data visible again.
8.Use template worksheets
Templates can save you considerable time when you’re setting up a new
worksheet. Click on File, New…, select the Spreadsheet Solutions tab and
choose a template from the list.
9.Access help for Lotus users
If you’ve converted from Lotus 1-2-3 and find Excel confusing, you can
access help specific to your situation by clicking on Help, Lotus 1-2-3 Help…
10.Use the formula browser Select a cell and click on the Paste Function button on the main toolbar. Pick the function you require from the
list box and click on OK. Now highlight the cells on which you want
the target to perform the function and click on OK. Excel converts Web site addresses to hyperlinks automatically. Right-click on them to remove the link.
Filling Down – the easy way to perform dynamic calculations.
11.Customise your AutoFills
If you use the same list over and over in different worksheets, you might want to add it to your AutoFill list – this will save you heaps of time in future.
Highlight your list, click on Tools, Options… and select the Custom Lists tab. Click on Import, then OK.
12.Use the AutoCalculator
If you need to calculate a sum based on a row or a column of figures and you can’t be bothered typing in a function, just select your figures and glance down at the status bar – you’ll find the sum of the selected cells there. The Paste Function button enables you to browse formulas and is the easiest way to enter Functions.
13A quick way to enter the time
To enter the current date or time click on a cell and type =today() or =now().
Excel updates the result every time you open the sheet, so it’s always current.
14Enter a fixed time into Excel
If you want Excel to enter the current date or time and fix it at that point – for example, to show the last date the sheet was modified – click on a cell and press
[Ctrl] + [;] for the date and [Ctrl] + [:] for the time.
15Find the currently active cell
If you’ve been scrolling around your spreadsheet and you lose your place,
you can jump back to the currently active cell by pressing the [Ctrl] + [Backspace] keys.
16.See the big picture
width or a row’s height is to place your mouse pointer close to the join between
two columns or rows, then click and hold down the mouse button, and drag
the column or row to the desired size.
17Fit text automatically
Make your work look neater by selecting the relevant column, and clicking on Format, Column, AutoFit Selection.
18.Fast copy, no clipboard
Pressing [Ctrl] + [‘] is a speedy way to duplicate the formula or figure in the cell above the one you’re in.
19.Fast and easy multiple entry
If you need to enter a formula in several cells at once, simply select all the target
cells, type your formula as normal and press [Ctrl] + [Enter].
20.Pick cells for AutoCalculator
You can select unconnected cells by holding down [Ctrl] while you click on
the individual cells.
21.Hide comments to reduce clutter
You can hide notes and other comments by using the Insert Comment function. Highlight a cell, click on Insert, Comment and type your text. Click away from the input box when you’ve finished. The comment will appear whenever you pass your mouse over the relevant cell.
22.Re-colouring the grid lines
You can change the colour of the grid by clicking on Tools, Options…, selecting the View tab, clicking on the Color: list box and choosing a new colour from the palette. Choosing white effectively removes the grid completely.
23.Angle your entries…
To make Excel display the text in cells at an angle of 45 degrees, right-click on
the toolbar, select Chart, and pick either of the ab icons on the new toolbar.
24…to any degree you like
If you want custom angles right-click on the cell and select Format Cells…
From the pop-up menu, click on the Alignment tab, and then drag the Text pointer in the Orientation window.
25.Zoom in to your selection
You can have Excel display just the area you’re working in by highlighting the
relevant cells, clicking on the arrow on the Zoom box on the toolbar and
selecting Selection from the list.
26.Another default entry direction
When editing cells the cursor moves down when you press [Return]. To
change the direction click on Tools, Options… and select Edit. Click on the
Move selection after Enter list box and choose another direction from the list.
27.Launch Excel, open your file Launch Excel, open the file you use
most often, and click on File, Save As… In the Save As dialog, navigate to
the Xlstart folder in your Office folder and click Save. Now the file will open
automatically when you launch Excel.
28.Set decimal points Click on Tools,
Options… and select the Edit tab. Place a tick in the Fixed decimal tick box and use the arrows in the Places: input box to set the number of decimal places.
29.Calculate the median value
To calculate the median value of a number of figures, say A1 to A10, enter =median(A1:A10) into the target cell.
30#NAME! and #NUM! errors
Excel returns a #NAME! or #NUM! error whenever a formula refers to nonexistent
names or numbers. To sort it out, re-enter the formula correctly.
31.Quick sort your columns
The quickest way to sort a column into a hierarchy is to select it and click on
the Sort Ascending or Sort Descending buttons (the ones with the AZ arrows).
32.Quickly change cell dimensions
The quickest way to change a column’s What’s more, if you right-click on the
sum a pop-up menu will appear offering additional quick calculation functions.
33.Erasing entries while typing
If you mistype your formula, press [Esc] to erase the cell’s content.
34Enter number sequences
Enter the first number in a cell, press the [Ctrl] key, and then click, hold and
drag the cell’s handle until you’ve reached the desired number of cells.
Release the mouse button first, and then the [Ctrl] key.
35.See formulas at a glance
You can view all of your formulas at once by clicking on Tools, Options…,
selecting the View tab, and placing a tick in the Formulas tick box.
36.Highlight cells of a kind
You can select all the cells of a certain type by using the Special functions.
Click on Edit, Go To…, click on the Special… button, choose the cell type
from the dialog box and click on OK.
37.Switch off the zeroes
You can prevent zeroes from cluttering your sheets by clicking on Tools,
Options…, selecting the View tab, and unticking the Zero values tick box.
38.Protect your work
Open the sheet you wish to protect and click on File, Save As… Click on the
General Options… item in the Tools menu (in the top right corner of the
Save As dialog) and enter a password in the Password to open: input box.
Finally, click on the Save button. You might want to make a note of the
password before continuing.
39Customise your dates
You can customise the format of a date in a cell using the Custom cell formatter.
To do this, enter a date in a cell, click on Format, Cells…, click on Custom
in the Category: pane, click on the Type: input box and press [M] repeatedly until the desired format is displayed in the Sample area above.
40.Quick column copy
You can fill out the cells in a column by double-clicking on a cell’s handle.
Excel will place copies of the clicked cell in each cell below, stopping when
it reaches a cell with blanks either side. (Confused? Try this: enter a figure in
cell A1, then enter figures in cells B1 to B5, then double-click on A1’s handle.)
41.Merge cell contents
To merge the contents of cells A1 and B1, click on cell C1 and enter =A1&B1.
The result is not a sum but a text string, so merging 10 and 7 will return
107, rather than 17.
42.Use dynamic formatting
You can set Excel to alert you to critical figures in cells by setting it up so the
colour or size of text in a given cell changes whenever a certain condition is
reached. Select a cell (say, the cell containing the total of your bank
balance) and click on Format, Conditional Formatting… Choose the
conditions in the pop-up dialog (say ‘Cell value is less than 0’) and click on
the Format… button. Now choose a new colour in the Color: list box and
click on OK. Click on OK again to finish. From now on Excel will display
your chosen figure in the new colour whenever the figure falls below zero.
43.Border connected cells
You can place a border around a group of related cells – all the totals, for
example – by selecting them and clicking on Format, Cells…, then selecting the Border tab and clicking on the Outline button.
44.Shrink entries to fit their cells
You can force Excel to display the full text of a cell by using the Shrink to fit
option. Click on Format, Cells…, click on the Alignment tab and place a tick
in the Shrink to fit tick box. Obviously, the more text you have, the smaller it’ll
be, so this isn’t practical for small cells with lots of content.
45.Create hyperlinks to support files
To place links in cells that enable you to load other documents with one click,
enter a name for the link in a cell and press [Ctrl] + [K]. Click on the File…
button. Navigate to the file you wish to link to. Double-click on it and click
OK. From now on, whenever you click in that cell, Excel will jump to the file.
Handy keyboard tips
30 shortcuts to speed up your calculations.
Select the current column [Ctrl] + [Space]
Select the current row [Shift] + [Space]
Edit the active cell [F2]
Move to the beginning of the worksheet [Ctrl] + [Home]
Move to the last cell on the worksheet [Ctrl] + [End]
Paste a name into a formula [F3]
Paste a function into a formula [Shift] + [F3]
Alternate value/formula view [Ctrl] + [`] (on key [1])
Calculate all sheets in all open workbooks [F9]
Display the Go To dialog box [F5]
Display the Find dialog box [Shift] + [F5]
Display the Format Cells dialog box [Ctrl] + [1]
Create a chart [F11]
Insert a new sheet [Alt] + [Shift] + [F1]
Repeat the last action [F4]
Repeat Find [Shift] + [F4]
Open [Ctrl] + [F12]
Exit [Ctrl] + [F4]
Check spelling of current cell [F7]
Activate the menu bar [F10]
Display the Macro dialog box [Alt] + [F8]
Apply outline to active cell [Ctrl] + [Shift] + [&]
Convert to a percentage [Ctrl] + [Shift] + [%]
Select all filled cells around active cell [Ctrl] + [Shift] + [*]
Move to next sheet [Ctrl] + [Page Down]
Move to previous sheet [Ctrl] + [Page Up]
Complete a cell entry and move up [Shift] + [Enter]
Complete a cell entry and move right [Tab]
Complete a cell entry and move left [Shift] + [Tab]
Edit a cell comment [Shift] + [F2]