Answering AAT spreadsheet questions – everything you should know
We are going to take a look at the spreadsheet functions you need to know and use in order to succeed at AAT Level 3. You will also have a chance to practice using spreadsheet functions with our free practice questions available to download.
Spreadsheets can be an indispensable tool in accounting. They can be essential for carrying out tasks such as budgeting, bank reconciliation and financial forecasting. When used at their best, they can streamline complex processes, reduce errors and improve financial decision making. However, for many of us, they can be a thing of terror and confusion. When faced with a screen full of numbers and a vast array of functions, we can become at a loss of what to do. Often our biggest problem is that we don’t even know that a spreadsheet is capable of doing many of the things it can, so most of the time we only use spreadsheets as a glorified calculator or address book!
In order to pass AAT Level 3, you will need to become comfortable with using a spreadsheet in a number of different ways. This article will explore some of the functions you are required to know in order to pass your exams.
Required Spreadsheet functions for Level 3
The spreadsheet functions you may be asked to used in AAT Level 3 are:
- SUM
- AVERAGE
- MINIMUM
- MAXIMUM
- ROUND
- ROUNDUP
- ROUNDDOWN
- SUMIF
- COUNT
- COUNTA
- COUNTIF
- IF
- VLOOKUP
- HLOOKUP
- DAYS
DAYS
Starting with possibly the most important question that may be asked at any time – how many days until Christmas? You can now find out from our free practice question spreadhseet! (link below)
The DAYS function will tell you the number of days between two dates. The formula is entered into a cell as follows:
=DAYS(later date,earlier date)
So, if you wanted to know how many days are between 14 June 2022 and 8 August 2025, you would enter:
..and you would get the answer 1,151 days. Note that if you are entering dates directly into the formula bar, you need to put them in quotation marks for the spreadsheet to know what to do with them. If the dates are already in the spreadsheet, it is usually quicker to use the cell refrences as follows:
SUM
For a simple calculation of adding up a few cells, then a straightforward formula referencing individual cells can be used:
This kind of formula is impractical where you have dozens of number to add up. Instead, you can use the SUM command. This is entered in the format:
=SUM([start of range]:[end of range])
In the example above, the column of figures from B3 to B7 needs to be added up, so the formula is
=SUM(B3:B7)
Using Autofill to save time in entering formulas
Often you will have worked out a formula to use, but then will have to apply the same calculation to many rows:
We know that the formula in C4 should read “=SUM(A4+B4)”, and C5 should read “=SUM(A5+B5)” and so on, but when there are lots of rows, this would be very time consuming to write each formula in manually. Once you have filled the first formula in, it is easy to get the spreadsheet to automatically fill in the formula for subsequent rows. Simply select the cell containing the formula you want to reproduce and hover your mouse pointer over the bottom right corner of the cell until the mouse pointer turns into a small cross. Then hold down the left mouse button and drag the window down all of the rows you wish to calculate.
How to automatically fill formulas in a spreadsheet – a video demonstration
AVERAGE, MINIMUM, MAXIMUM
If your spreadsheet has a large array of numbers, these commands will find the average, maximum and minimum values. They are entered in the format:
=AVERAGE([start of range]:[end of range])
=MAX([startof range]:[end of range])
=MIN([startof range]:[end of range])
Free practice spreadsheet questions
Next time we will explore the other formulas you will need to master AAT Level 3 questions.
Our Management Accounting Techniques AAT Level 3 Revision questions pack contains more exam style spreadsheet tasks for you to practice, together with over 120 questions on the module.
If you are studying for AAT qualifications, we have many practice questions available for you to supplement your learning and perfect your exam techniques. Find your questions on your current module in our store
