Understanding and using spreadsheets is essential for every accountant – but their functionality is so vast that even experienced users often don’t know the benefits of some of their most useful tools, and their potential pitfalls. Here’s a quick summary of some of our favourite functions.
Data validation
Data validation allows you to set cells so that they only accept specific information. It works for numerical values and text, making it a very powerful tool. Applying data validation to cells can help prevent incorrect entries being made into a spreadsheet that is being used by several people.
You can even write your own error message to explain why a cell does not accept data that goes against the validation criteria.
You can also use data validation to create dropdown lists in cells, which may help cut down on typing during data entry and give consistent spelling to entries, helping later searching or counting of results.
Count functions
There are various useful count commands in Excel. COUNT tells you the number of cells in a range that contain numbers; COUNTA counts the number of cells that are not empty (this can be text or numerical data), and COUNTIF allows you to count the number of cells that meet a specific requirement – eg how many cells contain the word ‘overtime’.
VLOOKUP and HLOOKUP
Excel’s lookup functions use a formula to find information in large spreadsheets – saving you from manually searching through the data. VLOOKUP searches columns, and HLOOKUP searches rows.
Find or remove duplicates
When working with data, you may find that you end up with duplicates, particularly if data comes from multiple sources. Excel’s Remove Duplicate tool quickly searches for – and removes – replicated data.
When using this function, make sure you ‘select all’ the columns in your data before you remove duplicates as there may be different information attached to different instances of replicated data (for example, a phone number against one entry for Joe Bloggs, and an email address for a second). To identify duplicated data without removing it, use conditional formatting, highlight cells rules, duplicate values.
Pivot tables
Many people find pivot tables daunting, but these interactive tables are very simple to create. They’re a powerful tool – particularly when working with large datasets – allowing you to analyse and summarise information, and create reports, with just a few clicks.
If you’re already working in an accounting role, you will have lots of opportunities to use your spreadsheet skills outside of your studies, creating new spreadsheets and sharing them, and your skills, with other members of your teams.
If you’re not yet working in a finance role, don’t worry – spreadsheets are useful in many jobs, and away from work too (such as creating a budget for household expenditure). Whatever you use them for, try to practise spreadsheet work frequently and you will find that they will soon become second nature.
• Cath Littler is the Head of L&D (Accounting) at Mindful Education
