Excel skills wannabe accountants need to know

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, an accountancy student or are a business owner who wants a better handle on their finances, here are some of our top Excel tips from the team at Alexander and Co.

These tips can’t provide all the answers though, and if you need help with any accounting issues, our team of experts provide a range of services. You can learn more about our professional team of accountants here and discover more about their excellent skills and knowledge of the industry.

If you are looking for accounting services, please get in touch.

VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are two of the most useful Excel functions for accountants. 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 Excel formulas.

VLOOKUP sceeenshot from Office.com

 

HLOOKUP

Another important excel formula for accountants and finance professionals is HLOOKUP. Taken directly from the Office site, here’s what the HLOOKUP formula does:

Hlookup from Office.com

Reduce mistakes with 0’s

Have you ever found yourself counting the zeros when encountered with a figure in billions and millions? Our experienced accountants certainly have! Don’t worry, there’s a simple tip that could save you lots of time: just type e6 after the number!

Using the 1e6 shorthand for 1000000

 

Paste options that could prevent errors

This is a useful excel skill that everyone needs to know! Have you ever hit ‘paste’ and been left with a sheet full of errors? Try Paste > Special instead! 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, or paste as special when you right click.

Learn keyboard shortcuts

Keyboard shortcuts are essential for anyone who uses Excel regularly. They improve efficiency by turning multiple-click processes into one easy button press (including the one above).

Here are some of our must-learn Excel shortcuts for both Mac and PC.

 

Function

PC shortcutMac shortcut

Paste special

Ctrl+Alt+VAlt+E+S
Save asF12

Opt + shift + cmd + S

Select row

Shift + space

Shift + space

Select column

Ctrl + space

Cmd + shift + space

Format

Ctrl + 1Ctrl + 1 followed by cmd + 1

Open pivot table wizard

ALT + D + P

Cmd + alt + p

Insert today’s date

Ctrl + ;

Ctrl + shift + ;

Add hyperlinkCtrl + K

Cmd + K

Repeat last action

F4

Cmd + Y

Display find & replaceCtrl F

Cmd + F

Move to first cell in worksheetCtrl + Home

Ctrl + left arrow then up arrow

 

If you need more than just these tips to help with your accounting, our team of dedicated experts can help. Learn more about our bookkeeping services if you need assistance with keeping your excel sheets up to date. 

Pivot Tables

Excel for accountants can be challenging, particularly when you’re dealing with huge spreadsheets with many sheets. 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. If you are still struggling with organising your finances, an accountant at Alexander & Co can help.

 

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.

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. Instead, test your formulae by going to Formula and then Evaluate Formula, or by using the shortcut Alt + M + V (Cmd + M + V on Mac).

 

Useful Excel macros for accountants

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 practice 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.

Our services

If you are looking for further help with your accounting and finances, Alexander and Co offer a range of accounting services. Our dedicated and experienced team can help with services for business start ups as well as offering independent advice and online accounting for established businesses needing assistance with their finances.

Get in touch with Alexander & Co

At Alexander & Co, our mission is to provide the highest quality accounting services across the UK. We have a variety of services, from bookkeeping to forensic accounting, and any company looking for business support should get in touch with our team, to discuss how we can assist. To speak with us, fill out the form below.

Contact us

Further Reading

Contact a professional now

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

Next Article

accountants in Manchester

What do you do when you discover fraud in your business?

Contact a professional now