Tip 1 – Use the keyboard a little more
Keyboard shortcuts are not just for the office guru. With a little practice anyone can use them, see below a few really useful Microsoft Excel Keyboard shortcuts:
- F2 – Click on any cell that contains text and press F2 on your keyboard. This will put your blinker (insertion point) into the end of the cell you have selected and will allow you to type without the need to use the formula bar at the top of the screen.
- CTRL and DOWN (cursor keys) – When you are clicked in to a chunk of data in your Excel spreadsheet, and you want to move to the end, you can press CTRL and DOWN to move down to the bottom of this data. You can also use UP, LEFT and RIGHT and you will be moved to the end in that direction.
- CTRL and P – This will take you to Print and Preview. (This also works in other parts of the office suite)
- CTRL and S – This will save the file you are within. (This also works in other parts of the office suite)
BONUS TIP – If you hover over a button in any part of the Office suite it will show you a post it note style caption which if there is a short for that button it will be detailed here.
TIP 2 – Consider putting totals at the top of lists rather than the bottom
Most of the time when people put together a list of information within Excel they will need to total part of the data. E.G. total all the sales for a list of orders. The tendency here is for you to add a total to the bottom of the list as this is how it has always been done. The issue here is that when the list grows you will have to move this total down to account for the additional data that has been input. But, if you placed the total above the list and select further than the end of the list your total will always be in the same place and you will have less work in the long run. Also if you use Freeze Panes you will always be able to see your total as your total will be near the headings.
TIP 3 – Make a graph quickly
Drag over the data you want to create a graph for and press F11 on your keyboard. You will now have a graph on a new tab. Keep in mind when Excel creates the graph for you it will use the data in to top row and left column to label the axis. So the more preparation you put in the better the result you will get from this.
TIP 4 – Reveal formulas to find potential problems
A very common issue in Excel spreadsheets is it can be easy to overtype formulas with static values. To the naked eye, a typed value will look the same as the answer to a formula. To make matters worse you would have to click on all cells that you think should have a formula in to check for this problem. Well not any more… If you do any of the following Excel will reveal all the formulas to you for the whole Worksheet you are in:
- PRESS CTRL and ¬ on your keyboard. (The ¬ is usually below the Esc key)
- Click FORMULAS tab -> Click SHOW FORMULAS button.
Then if you want to bring back the answers to the formulas again rather than showing the formulas just use the keyboard shortcut or the menu option mentioned above. Think of them as both the on and off switch to this option.
Tip 5 – Add a comment to a cell using a shortcut
If you press shift and F2 on your keyboard when you have a cell selected in Excel this will add a comment to the cell. A comment is like a post-it note about that cell and it can be useful to help you keep track of things about your spreadsheet.
Article by Ben Wordthorpe www.woodthorpeitsolutions.co.uk