I've been doing a fair amount of lecturing on Excel recently with the next IT Faculty Excel roadshow also coming up towards the end of April. Here are just a few Excel shortcuts that I've come across, or which my lecture victims seem to have found particularly useful.
First use of function key 4 – F4
When you are working with cell references, F4 will cycle through the four absolute/relative alternatives e.g.
=A1
=$A$1
=A$1
=$A1
If you select multiple references and then press F4, the absolute/relative pattern of all the selected references is set to the same as the first one. Hopefully an example will make this a little clearer:
Second use of F4
In several Office applications, F4 can often be used to repeat the last action. So as an example in Excel, if you need to insert half a dozen worksheets into a workbook, rather than repeatedly clicking on Insert, Worksheet (Excel 2007 – click on New sheet tab) you can do it once, then just press F4 to repeat the action as many times as you like. Alternatively, if you can remember it, you could just use the Shift+F11 keyboard shortcut to insert worksheets. Either way is probably quicker than selecting a group of worksheets before using Insert, Worksheet to insert a group at a time.
Allen key key
This key probably has a proper name, but I'm not sure what it is. It should be at the top left corner of the main bit of the keyboard, under the Esc key and features a backward apostrophe, a vertical line and an allen key. Control+Allen key in Excel toggles the display of cells containing formulae between the resulting values and the original formulae, which can be useful in checking for inconsistent formulae, or formulae overwritten with a value:
AltGr
Finally and for no particular reason other than it's the only use I've ever found for it, this key – to the right of the space bar, allows you to enter accented vowels and some other characters (Try AltGr+T for example). So AltGr+e gives you é.
Please feel free to share your own favourite Excel shortcut.