Excel remains popular because it is continually being updated but it can be difficult to stay on stop of all the updates and how they might work best for you. There are some Excel functions that I believe all accountants should know and the following updates will be ones they want to use:
Formatted tables in Excel
In Excel 2007, the Format As Table icon appeared in the Home ribbon. Unfortunately, because it also involves formatting, people thought that this is just a formatting tool. This is a data tool not a formatting tool. There are many advantages to using Format As Table on your tables. These include:
- the table automatically expands when new rows or columns are added
- table names (technical name: Structured References) are created that work in a similar way to range names. They are more flexible than range names as they automatically expand to include new data
- formulas in columns are automatically copied down when created, edited, or when new rows are added
- formatted tables can be included in the Data Model (see PivotTables)
If you use a formatted table as the data source for a PivotTable, then when you refresh the PivotTable any new data in the formatted table is automatically included in the PivotTable.
The keyboard shortcut to apply Format As Table is Ctrl + T. When used correctly, tables improve structure and can add flexibility to your spreadsheets.
Excel shortcuts for accountants. Listen now.
PivotTables in Excel
PivotTables allow you to summarise data with reports that are created with a simple drag-and-drop interface.
Excel 2013 introduced the Data Model into Excel. Prior to that a PivotTable could only report on a single table of data. The Data Model lets you create relationships between multiple formatted tables, much like a relational database. This almost eliminates the need to use the VLOOKUP function to add extra reporting fields to data tables.
The Data Model allows you to create Date, Mapping and Grouping tables that can simplify otherwise complex reporting requirements. (I cover examples of these techniques in “Easy steps to make data relationships work in Excel”, from October 2015, and “Use Excel relationships to enable PivotTables to report by financial year”, from December 2016, on intheblack.com.)
This filter feature was added in Excel 2010 without much fanfare. I covered these in my “Advanced Excel tips: Slicer to the rescue” article in November 2016. Slicers are a graphic filtering option that are more intuitive and user-friendly than Excel’s standard filtering interface. In Excel 2010, they only work on PivotTables and are in the Options Ribbon tab when the PivotTable is selected.
In Excel 2013 and later versions, they also work with formatted tables. Excel 2013 added a Timeline Slicer which works with date-based fields (one of my clients upgraded to Excel 2013 just to get Timeline Slicers). In Excel 2013 and later versions, Slicers are in the Design Ribbon tab for Formatted tables and the Analyze tab for PivotTables.
Improve your powers of Excel at this year's World Congress of Accountants, 5-8 November 2018 in Sydney. Learn more about the world's leading accounting and finance conference.
Power Query was first added to Excel as an add-in to Excel 2010. In the latest version of Excel, it is called Get & Transform and is in the Data ribbon tab. This is probably one of the most powerful features added to Excel in the last 15 years. It automates importing and cleansing of data from multiple sources. Data cleansing refers to correcting dates, layouts, number formats and inserting missing records. In the past this was typically a time-consuming, manual process or possibly done via a macro.
Power Query allows you to select specific tasks that are recorded in sequence; those tasks are repeated in the same sequence each time you refresh the query.
You can save the results of the query to a sheet or into the Data Model where PivotTables can report on it. I covered Power Query in “An easier way to use CSV files in Excel” in June 2016 and “Master the Power Query in Excel” in August 2017.
Excel functions you need to know
Below (table 1) are the functions that, in my opinion, most accountants should know. In table 2 you will find articles relating to each of these functions.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]