Excel Skills Wannabe Accountants Need To Know

 

Pixabay

Excel Skills for Accountants

 

Excel is an incredibly powerful tool for accountants, and it is a key skill that any budding accountant should try to become familiar with. If you’re a newly qualified accountant, accountancy student or just a small business owner who wants a better handle on their finances, there are a few key excel tools you will need to know.

Take a look below to learn more about the most useful Excel functions for accountants.

 

VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are the most useful Excel formulas for accounting and finance. These functions let you search a table of data, and give all the appropriate information for an individual set of data based on only one part of it. For example, if you have details about clients stored in an Excel sheet, you’ll be able to find all of their information in the data by just using one identifier, such as surname or client number. Take a look at the video below to get a better understanding of these useful formulas.

 

VLOOKUP sceeenshot from Office.com

HLOOKUP

Pinched directly from the Office site, here’s what Hlookup does:

Hlookup from Office.com

If that means absolutely nothing to you, then try this YouTube video for an explanation of how Hlookup can help you.

Reduce Mistakes With Zeros

Using the 1e6 shorthand for 1000000

Have you ever found yourself counting the zeroes when encountered with a figure in billions and millions? Never? Are you sure? We certainly have in our time. Well there’s a simple tip: just type e6 after the number!

 

Paste Options

If you’ve hit ‘paste’ and been left with a sheet full of errors, check out the special paste options. You’ll be able to transpose data, copy data validation and more with paste options. Click the drop-down menu next to the paste button to see what options are available.

 

Learn Keyboard Shortcuts

Keyboard shortcuts are an essential for anyone who uses Excel regularly. They improve efficiency by turning multiple-click processes into one easy button press. Give some of the shortcuts below a go!

The best list of keyboard shortcuts we’ve found is this one from ExcelJet.

 

Function Shortcut
Save as F12
Select Row Shift + Space
Select Column Ctrl + Space
Format Ctrl + 1
Open pivot table wizard Alt + D + P
Insert today’s date Ctrl + ;
Add hyperlink Ctrl + K
Repeat last action F4
Display find and replace Ctrl F
Move to first cell in worksheet Ctrl + Home

 

For even more shortcuts, take a look at the definitive list here.

 

Pivot Tables

Excel for accountants can be challenging, particularly when you’re dealing with huge spreadsheets with many pages. Pivot tables can help you to make sense of huge sets of data by organising and summarising selected rows and columns of data to obtain a report on the data you’re interested in.

This might sound complicated, but the guide below provides a great introduction to this useful tool.

 

Pivot table dialogue box in Excel

If you’ve ever felt overwhelmed looking at row upon row of numbers, wondering where to even begin, pivot tables are a quick way of getting some much-needed clarity. Pivot tables are invaluable for extracting important information from large data sets.

For a step-by-step guide on how to use a pivot table, see this post by Excel Easy.

Test Formulae

Excel evaluate formula feature

There’s nothing worse than copying a formula to multiple cells, only to discover it doesn’t work or do what you want it to. Luckily, you’re able to test your formulae by going to Formula and then Evaluate Formula, or by using the shortcut Alt + M + V.

 

Macros

Where to find excel macro button

Macros are a way of automating repetitive tasks, making it a great excel shortcut for accountants. Although they can seem a little intimidating at first, with some practise you’ll master this skill in no time at all. You’ll need to be able to recognise which tasks are suitable for automation, and have a good grasp of formulae to be able to make the most of macros.

Check out the video below to discover more about using macros.

 

These tips and tricks will have you mastering your accounts in no time. If you need further help with your business finances, take a look at our accounting services page to see what we can help you with, or get in touch with our accountants in Salford and Manchester to find out more.

 

Contact Form

We would love to hear from you! Please fill out this form and we will get in touch with you shortly.