An essential part of every accountant’s toolbox, Excel can be used for everything from simple calculations to full-on models. ICAEW Technical Manager and Excel expert David Lyford-Tilley shares his top tips on making the most of what the program has to offer
Webinar
David hosted the Five of the best Excel tips for students webinar which is available on demand. You can download the workbook that he used during the webinar to demonstrate all the tips and tricks.
1. Understand how functions work
No doubt everyone will have seen a SUM function, but understanding the different elements can help you learn new formulas more easily:
- = tells Excel you want to write a formula, not just type normally
- SUM is the name of the function you want to use. There are hundreds of these – one for almost every situation imaginable – which can be found in collections on the Formulas tab
- () marks where the inputs for the formula start and end
- D2 specifies a cell address – this will be changed if the formula is pasted elsewhere, but a reference like $D$2 wouldn’t be
- : joins two cell references together into a range, shortcutting identifying a large list of cells
Practise typing functions rather than using the wizard. And remember these key principles for good spreadsheet practice (find the full list of 20 principles here):
- Be consistent in the use of formulas
- Keep formulas short and simple
- Never embed in a formula anything that might change or need to be changed
2. Experiment with PivotTables for easy data analysis
PivotTables are automatically generated summaries of data. If you have any kind of accounting or other data and need to summarise it by label, a PivotTable is quicker and easier than a formula.
To make one easily, use Insert => PivotTable, and then experiment by dragging different column headings to the different areas of the menu to design your own summary. The original data won’t be affected, so you don’t have to worry about damaging it as you try things out.
One important note: you will need to use Data => Refresh if you want to update for any changes made to the source data.
3. Learn how to lookup
If you need to get a value from a table based on a label, you need to do a lookup. There are various ways to do it, from the hoary classics LOOKUP and VLOOKUP to the more modern INDEX MATCH and XLOOKUP. I’d recommend learning INDEX MATCH as a flexible option that’s available in all current versions of Excel.
The formula is a little more complicated, as it combines two functions one inside the other, but it’s highly flexible and reliable. Don’t forget that final ,0 to indicate to Excel that you want to return only the exact matching item!
4. Sample quickly with RAND
You can randomise a list of items by putting an =RAND() function next to each (generating random numbers), and then using Data => Sort on that column. This is excellent for picking a random sample.
5. Stay curious
Perhaps the best advice I can give is to keep looking for opportunities to learn. I have been writing a weekly blog on Excel for over eight years now, and I am still learning new things all the time! I usually recommend that, if it’s taking you 15 minutes or more to complete a task manually, you should look to learn a better method instead.
As a student you can join the Excel Community using ITEXSTUDENT discount code. I particularly recommend checking out the Spreadsheet Competency Framework for an idea of your current level, and what to learn next in order to advance.