10 key areas for boosting your AAT Excel skills

Are you studying accounting, but don’t know your pivot tables from your absolute references? Then you’ve come to the right place. Whether you’re looking to refresh your knowledge or simply start from scratch, we’re here to guide you on the key AAT Excel skills you need to know.

Key AAT Excel criteria

1. Conditional formatting

What is it?

Chances are you’ve probably seen this in action somewhere, and there’s a good reason for that. Conditional formatting allows you to format any cells in your spreadsheet with a specific identifier. You can highlight information with colours, icons, or data bars. It’s a great way of seeing key trends and information at a glance, without having to sift through rows and rows of data.

How can it be used for accounting?

As an accountant, you could be using this tool to automatically flag any staff salaries greater than a certain amount. You might also apply it to highlight any bills that are higher than usual.

See the AAT’s conditional formatting guide here.

2. Pivot tables

What is it?

Arguably one of the most useful, yet misunderstood functions, a pivot table is a table that summarises a large data set. It allows you to get an overview of things like totals and averages by pulling information from another database. You can also sort, group, count, and reorganise data from numerous places in one table. Pivot tables help accountants spot valuable insights more easily by compiling data from multiple sheets.

How can it be used for accounting?

Say you had an employee sickness spreadsheet, an annual leave spreadsheet, and a lateness spreadsheet. Using a pivot table would allow you to look at all of these figures together in one place. A lifesaver for many accountants.

See the AAT’s pivot table guide here.

3. Paste special

What is it?

This simple feature allows you to keep existing formats when copying and pasting from elsewhere. It’s a handy way of pasting formulas without getting errors. So, if you’ve ever experienced a ‘#VALUE’ error, try using this first to solve the problem. It can also be used for something as simple as text, so you don’t need to spend time adding in fonts and sizes for each sheet you work with. It’s a huge timesaver for simple formatting jobs, but it’s also a great tool for pasting any regular formulas you need.

How can it be used for accounting?

If you’re setting up a new expenses sheet for the month, you can copy any existing formulas from your previous one. This saves time and minimises the chance of errors.

See the AAT’s cut, copy, and paste guide here.

4. Add multiple rows

What is it?

This is another quick fix and a staple for any Excel user. Instead of clicking the same button multiple times over, you can add in as many multiple new rows as you need. It’s ideal for formatting without having to edit the nearby rows, and also for adding in any forgotten or new data.

How can it be used for accounting?

If you have 3 new vendors or 5 new employees that need recording, you can add these anywhere in your sheets with ease.

5. Absolute references

What is it?

Not to be confused with a relative reference, an absolute reference allows you to keep rows and columns locked. This means they remain the same regardless of whether they’re copied or filled. So even if you change the data in a cell or move a formula elsewhere, the reference will still pull from your chosen cell.

How can it be used for accounting?

If you were carrying out some financial analysis, you could use an absolute reference. This would let you refer to a fixed figure, like a budget, to be repeatedly used within different formulas.

See the AAT’s absolute references guide here.

6. Print optimisation

What is it?

A series of tricks to make sure your printouts look the best they can be. Adding headers, gridlines, and repeating row titles across multiple pages to name a few. Printing spreadsheets isn’t always as straightforward as it would seem. Getting to grips with these skills is sure to impress your colleagues and also let your data speak for itself.

How can it be used for accounting?

If any spreadsheets need printing and presenting or filing, you’ll be the go-to person. You can show off your newly acquired skills and ensure that everyone has the right information in a format that’s easy-to-digest.

See the AAT’s Excel formatting guide here.

7. Extend formula across/down

What is it?

Instead of individually copying and pasting formulas, you can quickly extend across multiple rows or columns. It’s particularly useful for big spreadsheets and saves a lot of time, in addition to reducing errors.

How can it be used for accounting?

Copying more than 5 rows of data in your tax spreadsheet? Using this tool means you can even carry over formulas more efficiently, without having to manually enter them everytime.

8. Flash fill

What is it?

Flash fill identifies trends or patterns in your data and completes fields for you. It’s another time-saving technique that can take the monotony out of manually updating spreadsheets.

How can it be used for accounting?

Say you have a sheet for your employees which contains their personal details, like first and last names. If the first name is in column A and the last name is in column B, flash fill can automatically fill out column C with both names combined.

See the AAT’s flash fill guide here.

9. Index Match

What is it?

Index Match is a combination of the functions Index and Match. Index gives you the value of a cell in a table, based on the row and column number. Whereas Match tells you the location of a cell in a row or column. Together, these formulas can look up and return the value of a cell in a table, both vertically and horizontally. It’s essentially an advanced Vlookup.

How can it be used for accounting?

If you’re looking at a budget sheet, and want to know the value and location of a certain category, you can apply Index Match. This replaces having to do a CTRL + F command, especially if you have more than one of the same identifier in your sheet.

See the AAT’s index match guide here.

10. Filters

What is it?

Our final area is another one you’re likely to have come across before. For large spreadsheets, you’re likely to need specific information from time to time. With this function you can, you guessed it, filter out the irrelevant details from any column and just focus on what you need to see.

How can it be used for accounting?

In a tax spreadsheet, you might only want to look at the cells within a certain time-frame. Applying a filter means you can separate dates and get straight to the data that you need.

See the AAT’s filtering guide here.

Other useful tips

Even the most experienced Excel users can slip up when it comes to AAT exams. Practice is key. The more you work with spreadsheets, the more you’ll get used to them.

Remember to read the questions more than once to fully understand what they’re asking for. Does some text need to be in a certain colour? Make sure it’s in that colour. When it comes to the more technical side of Excel, it can be easy to miss these details and focus all your energy on completing those pesky pivot tables.

You can also use the = tool in Excel if you’re really stuck. Simply type = and then any letter to bring up a possible list of functions. It won’t guide you as to which you need, but it does give a summary of what it does. It’s particularly useful for jogging your memory.

Study AAT with Eagle

In short, there are multiple ways in which Excel provides a helping hand to accountants. Why not take your newfound knowledge and apply it to an AAT subscription with Eagle? Try AAT free for 7 days.