As an accountant, you will spend much of your time using Excel spreadsheets. They can be used for analysing a variety of financial data, from expenditure and income, to wages. Here are three handy Excel tools you can use to make working with any account a smooth process.
A really useful yet under used tool within Excel is the ability to circle invalid data.
This tool allows you to circle data in a spreadsheet that should not be there.
For example, if I have a spreadsheet that analyses staff wages, I need to make sure that no one is being paid below the minimum wage. I want any wages below £6.50 per hour to be flagged up in my spreadsheet.
To circle any data that is below the minimum wage of £6.50, highlight the numerical data and, from the tabs at the top, go to: Data > Data validation.
From here choose Data validation again. You now need to set criteria that you will allow, so in the Settings tab, in the Allow field choose Decimal. In the Data field, choose “greater than”, and in the Minimum field, enter 6.50.
Then go to the Data validation menu item again, and select Circle Invalid Data from the drop down. This should circle any cells where the value is less than £6.50.
Using pivot tables
As an accountant, you will usually be dealing with large lists of data. Creating a pivot table allows you to zero in on the information you need, and compare specific groups of data.
Let’s say you are analysing data about customer orders to find out what has been a company’s best selling product over a year, depending on the month and weekday. You’re starting with raw data that tells you about the month, weekday, product and number of orders associated with each sale:
To sort the data into an easy-to-analyse pivot table, take the following three steps:
1. Select a cell anywhere within the data
2. Go to the Insert tab, and select PivotTable. All the data in adjoining cells will automatically be selected in the table range, so click OK in the box that follows.
3. You now need to choose the layout of your pivot table, deciding what fields should appear as rows and columns, what values should be shown against these, and how the data should be filtered. For this example, we want to display months as columns, products as rows, sales as the value, and have the ability to filter by weekday:
We now have a pivot table that looks like this:
We can now compare the sales for each product by month and weekday. For example, I can take the following conclusions from the table:
- In April, the Red hat sold best, but in December, the Blue hat had the most sales.
- On Mondays, the Yellow hat was the best seller, but on Fridays, the Blue hat and Red hat were neck and neck.
- The most successful product throughout the year was the Blue hat, and the month with the highest sales was February.
Excel allows you to link cells, meaning that if you change a piece of information in one spreadsheet, it can automatically update the same information in another spreadsheet.
Let’s imagine we have an analysis of the bank account in one document, and we want to link the totals to those in the bank control account document, so they are updated automatically. From the bank control account, we then want to link the balance c/d figure to the statement of financial position document. Then, if a change is made to the bank analysis, it will automatically update the bank control account, which in turn will update the statement of financial position.
Let’s say I want to link the total from speadsheet A to spreadsheet B. To do this, I should press equals in the cell that I want to be automatically updated in spreadsheet B. I then need to choose the cell where the data is kept in spreadsheet A. When selected, it will look like this, telling me the name of the cell from which I am taking my data:
This linking also works well with analysis of wages and expenses.
If you would like to develop your ability to use Excel for financial data analysis, AAT is an ideal qualification. The basics are covered at Level 2 and, at Level 3, the Spreadsheets and Software unit takes you into more detail.