In any accounting role, you can benefit from incorporating spreadsheets into everyday tasks, making your job streamlined and easier to manage.

If you’re looking to progress onto AAT Level 3 Advanced Diploma, it’s good to see how spreadsheets can support you.

While small business owners and those new to accounting can find it difficult to track payments, spreadsheets offer a simple solution.

Spreadsheets are commonly used to analyse money that has been spent and income that has been received. They allow you to split the amount of money you have spent and received by time period and source. For example, you can separate out different types of expenditure such as rent or insurance, and track exactly how much can been spent on these in each month, by using a simple table.

1. Easy payment tracking

Another benefit of using spreadsheets is that you can link worksheets together. This is useful for things like cash flow, where you need to link the actual balance in the bank at the end of last month to the opening balance for this month. To do this, just press = in the cell that you want to link from, and put the cursor in the cell you want to link to.

This works well for budgets too, especially if you have completed a production budget in one worksheet, and this needs to link to the master budget. Then, if the production budget changes, it automatically updates the master budget.

2. Linking related data

When working with lots of financial data, you’ll want to make it as easy to navigate as possible. Thankfully, most spreadsheet software offers a variety of tools to help you make your spreadsheets visually appealing and easy to understand.

For example, you can use Ctrl + B when a cell is selected to make all of the text in that cell bold, which is useful for quickly helping titles and labels to stand out.

You might also choose to colour code cells, and then sort your data by colour.

3. Useful formatting

When working with lots of financial data, you’ll want to make it as easy to navigate as possible. Thankfully, most spreadsheet software offers a variety of tools to help you make your spreadsheets visually appealing and easy to understand.

For example, you can use Ctrl + B when a cell is selected to make all of the text in that cell bold, which is useful for quickly helping titles and labels to stand out.

You might also choose to colour code cells, and then sort your data by colour.

4. Spreadsheet functions

IF statements

The use of functionality such as IF statements is very useful in accounting. Imagine you have to analyse the amount of money spent on wages for a month. You could use an IF statement to indicate whether or not a member of staff has been paid overtime. This could be something as simple as:

= if(A1>37, Yes, No)

This means that if the number of hours indicated in cell A1 is greater than 37 hours, the answer ‘Yes’ will be generated; otherwise, ‘No’ will be selected.

IF statements can also be used in calculations. So if we are looking at the sales staff for an organisation, it may be that if they make sales greater than £5,000, in a week we give them 0.5% of the total sale value. This can be achieved by the following function:

=if(A1>5000, cell A3*0.05, 0)

The SUM function

This is a very quick and easy function that saves an awful lot of time. Rather than adding up huge amounts of data on your calculator, you can reach a total just by selecting the SUM function, and highlighting everything to be included.

LOOKUP functions

A tool in Excel that is used often by accountants is the HLOOKUP or VLOOKUP. This function searches for information associated with specified information, and can be extremely useful in a stock control environment.

For example, if you want to know if a particular part is in stock, you can use a HLOOKUP to find out the number of items associated with a particular item number. The makes the process of searching for values related to other values much quicker.

Take a look at some more tips specifically relating to Excel here.

Fluency with spreadsheets and data analysis is essential in any accounting role, and is covered indepth in our AAT Level 3 Advanced Diploma. To find out more about becoming a qualified accountant, get in touch.