Using Excel for Accounting

Posted by: Patricia Barlow Post Date: 4th December 2014

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.

Highlighting data

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.

Spreadsheets 1

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.

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.

Data validation box

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.

circled data

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:

Excel stage 1

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.

Excel stage 2    Excel stage 2 -2

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:

Excel stage 3

We now have a pivot table that looks like this:

Excel stage 4

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.

Linking cells

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:

excel stage 5

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.

Want to become a qualified accountant?

Get in touch below to find out how, or call us on
01332 613 688

Share this post