Modern accountancy practices spend much more time analysing accounts using cloud based software such as Xero, but there is still an important role for Excel spreadsheets. Raw data export files often form the inputs for accounting data. These files are often in CSV format and need to be cleaned up before the data can be converted into useful information. Spreadsheet software has improved significantly in recent years, and when used effectively it can significantly reduce the time taken to complete routine tasks.
Excel tables are a powerful tool for converting a raw data file e.g. a CSV export into a formatted table. The easiest way to create a table is to select a cell anywhere inside the range of data and then Ctrl + T (Cmd + T for a Mac). Excel will automatically detect the table headers and format the data. If you add rows on the bottom of the table, the table will automatically resize to include the new data.
One of the great features of a table in Excel is that the process assigns a name to the table, and to each column header in the table. Tables automatically create dynamic named ranges so to you don’t need to use over-complicated formulas to analyse data. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. This makes formulas much easier to understand.
My tip for tables is make sure you give them a relevant name. By default it will end up with a name like Table2. To name a table:
Select any cell in the table to show the Table Tools > Design tab on the ribbon.
Remember that table names need to be unique in a spreadsheet and names can’t use spaces so try Bank_Transactions rather than Bank Transactions.
A step up from an Excel table is a PivotTable. A PivotTable can be created from any data range, but it is probably a good idea to convert the data to a table first following the steps above.
When you are ready to create a PivotTable select Insert and then PivotTable.
Your new PivotTable will be a powerful tool to calculate, summarise and analyse data. It is really flexible and allows you to exclude data and remove surplus information that your reader doesn’t need.
Any advanced user of Excel will need to know the following functions:
Use VLOOKUP when you need to return a value from a table from a unique reference in the first column. An example of this a customer email address from a table with their ID in the first column of data.
SUMIFS can be used to summarise data in a table. This formula is a really quick way to provide a total based on multiple criteria e.g. total sales income where region is UK and colour is red.
LEFT returns the leftmost characters of a string. RIGHT does the same for the rightmost characters. This is really helpful when you want to extract a code from a longer text, e.g. FL from Tampa FL
The MID function will return text in the middle of a string. This is often used with FIND or SEARCH to find the starting point for the fragment of text you are looking for.
TEXTJOIN is really helpful for combining data in two columns or rows, e.g. First name + Last name. The power of TEXTJOIN is that it will ignore empty cells and you can specify how to separate the text being joined (e.g. with a space, comma or dash)
EDATE will return the same day of the month a number of months earlier or later. Many financial reports work on a monthly cycle so this shortcut save you typing the month for each new row in a table.
EOMONTH is a cousin of EDATE. This will give you the last day of the month a number of months earlier or later.
The following functions are newer and won’t be available in all versions of Excel. If you are sending them to someone who uses other spreadsheet software, they may not work at all.
XLOOKUP, SORT, UNIQUE, FILTER, SEQUENCE, AGGREGATE
For more articles on Excel functions check out the Financial Management articles Top 5 Excel functions you might not know and Transform data with Excel Power Query.
Alterledger Ltd is registered with the Information Commissioners Office.
© 2023 Alterledger Ltd
Website by Haiwyre