Microsoft Excel is a vastly powerful program, capable of working with large amounts of data and performing complex functions. You might only use it to manage your holiday allowance or keep track of the staffing rota, but everyone could benefit from a few useful shortcuts. Whatever your level of expertise, these tips and tricks will see you well on your way to excellence.
Conditional formatting
Conditional formatting is a way of telling excel to highlight certain cells if they meet a specific requirement – and it’s fairly easy to do. Let’s say you’ve got a list of random numbers, and you want to highlight all numbers over 100 by adding a blue fill. Maybe you have a range of deadlines and you want to know which ones are approaching or which ones have passed. You can do all sorts of tricks with conditional formatting. The best way to learn is to experiment – try creating a range of data on a new sheet and then click the conditional formatting button on the home tab. The different options are fairly self-explanatory.
The status bar
Want to know the average value of a set of data without typing in a formula? It’s easy. The status bar in the bottom of the window can show you the average, maximum value, minimum value, and the sum of a group of data as soon as you select it. If it’s not showing what you need, try right-clicking on the status bar and ticking the options you want it to display.
Showing and hiding
If you’re working with a large amount of data, sometimes there are fields you just don’t need to see. Maybe you have a list of employees with hours worked, rate of pay and total pay. All we really need to see is the employee name and the total pay, so we can hide the other two columns. Select a few cells in the column you want to hide and press CTRL and SPACE to select the entire column. With the entire column selected, press CTRL and 0. The data is still there, but the column will now be hidden.
If you want to show a hidden column again, select both of the columns either side of the hidden data, right-click your selection and then choose ‘unhide’.
Quick navigation with lots of data
Imagine you’ve got a spreadsheet with over a million names and email addresses and you want to move down to the end of the list. Scrolling isn’t an option here – just think how much time you would waste! Simply hit CTRL and ↓ to jump straight to the last piece of data. This works in any direction.
Plus, if you want to select everything between the selected cell and the last populated cell on the list, use CTRL, SHIFT and ↓.
Lock the top row
More often than not, the top row of your spreadsheet is going to contain the field names of your table – name, email address, date of birth, etc – so it would be nice if we could see it all the time. You can lock the top row using the option in the view pane, or you can remember a keyboard shortcut. With the top row selected, press ALT and W followed by F and then R. To unfreeze the pane again, press ALT and W followed by F and then F again.
Having a locked top row is especially useful if you are using the quick filtering tools…
Easy filtering
With the first row selected, press CTRL, SHIFT and L. Each cell will now have a little arrow next to it. Clicking on the arrow will give you access to a bunch of useful filtering options, allowing you to search for a specific record, group of records, or sort your records from A-Z or largest to smallest.
Finally, here are a few more simple shortcuts to speed things up a bit:
New worksheet: SHIFT and F11
Switch between worksheets: CTRL and PG UP or PG DOWN
New workbook: CTRL and N
Insert current date: CTRL and ;
Insert current time: CTRL, SHIFT and ;
Quick autosum (sum of data above current cell): ALT and =
Generate a quick chart with data selected: F11
7 top tips for using Excel spreadsheets