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.
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.
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.
Alterledger Limited is licensed by the Chartered Institute of Management Accountants and registered with the Information Commissioners Office.
© 2020 Alterledger Limited
Website by Haiwyre