Sharpen your tools: Excel tips and tricks
The Intelligent Investor has published a bunch of useful spreadsheets for members recently, including two that help you decide whether to participate in the off-market share buybacks offered by resources giant BHP Billiton and discount electronics retailer JB Hi-Fi.
Investors often ignore Excel as an investment tool due to its complexity. But you don't need to be an expert in order to find it useful. To prove it, The Intelligent Investor's analysts have shared their favourite Excel tips below (Note: the following options are for PC users. Mac users might need to use 'Command' instead of 'Control', and Google 'Mac excel shortcuts' for a list of websites showing shortcuts, like this one).
- Avoid using the mouse for common tasks, as keyboard shortcuts are faster. It's better for your wrist, too.
- Hold down Control while pressing an arrow key to jump to the edge of a block of cells, or – if you're already at the edge of a block – to the next block. (This is also incredibly handy in Word – use it to jump between words.)
- To change a formula without selecting the formula bar, select that cell and press F2.
- To change a cell reference in a formula to absolute, relative or some combination of the two, put the cursor on that cell reference and hit F4.
- If you want to sum rows or columns quickly, select the cell adjacent to the row or column you want to sum and press Alt =.
- Change between sheets quickly by holding down Control and pressing Page Up (left) or Page Down (right).
- For quick cell formatting, press Control Shift <number key>. Here's a quick guide:
- ~: General
- 1: Number, using two decimal places
- 2: Time
- 3: Date
- 4: Currency, using two decimal places
- 5: Percentage
- 7: Put a border around the selection
A final tip for users of Excel 2007 or later: next time you find yourself reaching for the mouse, press Alt. It'll make labels appear over every clickable item in the ribbon at the top of the window, and you can press the corresponding button to use that function. And if you're looking for even more shortcuts, Microsoft's list should whet your appetite.
http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx
What are your favourite Excel tips and tricks?