Excel skills for current and future accountants

The topics I cover in this month’s column are, in my opinion, essential knowledge for most accountants.

Excel is not going away, even though there are regular predictions of its demise. Broadening your knowledge of Excel’s capabilities doesn’t necessarily mean mastering them.

Here’s an old story that dates back to the mid-2000s.

Before Microsoft started the updates that became Excel 2007, it ran a comprehensive survey of Excel users and asked them what they wanted Excel to be able to do. The survey found that 80 per cent of what people wanted was already in Excel.

People didn’t know where it was, or what it was called. That was why the Ribbon Interface was created in Excel 2007. It placed lots of features in front of users, instead of hiding them away in menus, such as pointing to an icon bringing up a brief description.

My point is that learning what Excel can do broadens your horizons. Someone else may provide the solution, but as least you have the knowledge to ask the right question.

If you're still using Excel like you were 10 or 15 years ago, then you have a lot of opportunity to become much more productive.

Power BI is the dashboard and reporting solution of the future.

Power BI does not have, and is unlikely to ever have, Excel’s flexibility. Power BI and Excel work seamlessly together. They are not mutually exclusive.

I have included Power BI in my list of recommended Excel skills to master, but note it is a separate application to Excel.

Power Query

This is the number one Excel skill that accountants should have. Power Query has been built in since Excel 2016. The same Power Query engine in Excel is in Power BI.

Power Query can capture and automate the data cleansing and importation process. I have written many articles with companion videos explaining its many features and advantages.

Power Query allows you to

• extract raw data from virtually any data source (even PDF files)
• fix issues with the data like bad dates; missing codes or extra spaces
• create a data table that can be used by a Pivot Table
• combine data from multiple files into a single data table
• convert report layouts into data tables 

The process is created once and then the steps are re-run whenever you refresh the data. Little or no coding is required for most tasks. There is a programming language (called M) that allows you to handle more complex data cleansing processes.

Power Pivot

My previous article introduced this topic.

The Data Model was added in Excel 2016. It allows you to treat tables in Excel like a relational database and create relationships between tables. This adds another dimension to Excel’s reporting possibilities.

Prior to the Data Model, Excel only handled two-dimensional tables. A pivot table could only report on a single table. The addition of the Data Model is like adding a third dimension to your tables.

Pivot tables can now extract data from multiple tables in one report.

Dynamic arrays

I did a series of three articles from May to July in 2020 that featured this new calculation technique.

This technique will change the way spreadsheets are developed in the future. Formulas automatically spill down and across as far as they need to, from a single formula. You can work with a range of values in the same way you used to work with a single cell.

It offers so many opportunities to simplify reports and dashboards while making them more flexible.

Data visualisation

This isn’t exactly an Excel skill, but is part of producing effective charts (graphs) in Excel.

With the rise of Power BI for dashboards, more focus has been aimed at producing better charts that convey a message with a minimum of elements.

Data visualisation means using the right chart to get your message across. It also usually involves removing unnecessary chart components to reduce visual “noise”.

Formatted tables

This is a feature that has been around for many versions but has slipped under the radar of many accountants. I think because the word “format” is on the icon it has been ignored as a formatting feature.

The Format As Table button on the Home ribbon instructs Excel to treat a table like a little database. It is a data feature, not a format feature. It adds a lot of extra data functionality to the table.

I covered this feature in detail in the August 2018 article and video.

Many of Excel’s data features require the use of a formatted table.

Functions

These are the 20 functions I believe accountants should learn. If 365 appears after the name, it means you need Microsoft 365 (subscription version) or Excel 2021 to use the function.

SUM – you probably think you know the SUM function. Do you know what a 3-D SUM is? Can you do one? In the latest Excel version, the SUM function can perform all the calculations that SUMPRODUCT can perform. It is now more flexible than SUMIFS.

SUMIFS – this multi-conditional SUM can include wildcards characters in its conditions.

IF – make decisions with a function.

AND – review multiple conditions and return a single TRUE or FALSE.

OR – review multiple conditions and return a single TRUE or FALSE.

XLOOKUP – 365 - a flexible more robust replacement for VLOOKUP and even INDEXMATCH.

INDEX – the all-round flexible function that works well with tables and dynamic arrays.

MATCH – the function that adds flexibility to INDEX and other functions.

TEXT – the function that allows you to format dates and numbers to use with text.

IFERROR – handles all formula errors except the new #SPILL! error.

INDIRECT – the function of last resort - it can provide solutions that no other function can even attempt.

COUNTIFS – conditional counting can be useful in many situations.

EOMONTH – easily find the last day of a month.

EDATE – adds months to dates.

TEXTJOIN – join ranges of text together easily.

UNIQUE – 365 – list all the unique entries from a list – great for creating drop-down lists.

SEQUENCE – 365 – create a sequential list of numbers that can drive budgets and financial models from a single input cell.

FILTER – 365 – a formula that returns a filtered list based on a data table.

SORT – 365 – a formula that returns a sorted list based on a data table or another function.

LET – 365 – allows the use of variables within formulas. Can reduce the length of complex formulas.

Data validation

This feature on the Data Ribbon tab is an often-overlooked one that can make your spreadsheets more useable and reliable.

You can use Date Validation to limit the dates, numbers and text that can be entered in a cell. It is commonly used for in-cell drop lists.

Power BI

Power BI takes Excel’s Power Query and Power Pivot and adds a powerful Dashboard/Reporting engine overlaid with robust security and easy sharing capabilities.

Power BI is the likely future of many existing Excel reports.

The Dashboard features are amazing and can take reports to another level of interaction, plus they can drill down, something else Excel doesn’t have.

If you have not tried Power BI, I highly recommend you download the free desktop version and start playing with it.

The companion video will go into more detail to demonstrate these techniques.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]