7 useful Excel functions & shortcuts for accountants

Microsoft Excel remains an important tool for accountants, but many accounts payable (AP) professionals have a love/hate relationship with spreadsheets. Data entry remains a time-consuming, labor-intensive task, and any lapse in concentration can result in costly errors that take a long time to put right. 

If your Excel relationship remains in ‘it’s complicated’ territory, try these seven Excel functions and shortcuts for accountants to streamline your AP data entry tasks in future. 

XLOOKUP

XLOOKUP is almost like a Google search function for your spreadsheets. Use it to find a single item in a range or table without having to manually search through the data. It’s a more powerful tool than its predecessor, VLOOKUP — which requires you to specify column_number or true/false. By contrast, XLOOKUP can search anywhere in the data, delivering the information you need in an instant.

Syntax:
=XLOOKUP

Concatenate

The concatenate function automatically combines two text strings into one. If column B lists a series of employees’ first names, and column C lists their second names, concatenate will combine the two into FIRSTNAME:LASTNAME in column D, or wherever you want the data to populate. It’s an enormous time-saver if you have multiple columns of related text data, and dramatically reduces manual data entry time.

Syntax:

CONCATENATE(text1, [text2], ...)

Pivot tables

Pivot tables are a great way to compare, trend and summarize large volumes of data. Accountants find them particularly useful for demonstrating the significance of data to non-accountancy or other laymen audiences. You can also sort and filter information in a pivot table to present the data in an even more digestible way. Go to insert > tables > pivot table to try them for yourself.

SUMIF/SUMIFS

The SUMIF function is one of the more basic tools in an Excel users’ toolbox, but no less useful for all that. This function sums all the cells in a range, provided they meet a certain condition, allowing you to layer in a great degree of specificity — for example, a specific vendor, date range, or value. SUMIF only allows you to specify one condition, but its close cousin, SUMIFS, can be used to sum multiple criteria at once. 

Syntax:

SUMIF(range, criteria, [sum_range])

=SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")

View > new worksheet

The new worksheet function allows you to view two windows side-by-side in the same worksheet. Why is that important? Excel users often have to flip back and forth between sheets to compare or copy data, duplicate formulas, etc. It’s annoying and cumbersome, and you can easily lose your place if you have multiple tabs open at the bottom of your Excel window. Instead, hit ALT WN to duplicate your worksheet in a new window. You can view these two worksheets in split screen, and easily navigate between them.

UNIQUE

The UNIQUE function helps you instantly find any unique values in a list or range. Enter =UNIQUE and you can quickly uncover any duplicates or erroneous pieces of data in a data range, allowing you to spot errors before they can do any damage. It’s a game-changer for anyone working with large data sets, and an effective way to wheedle out duplicate invoices and payments.

Syntax:

=UNIQUE(array,[by_col],[exactly_once])

CTRL S

It might seem dumb, but if you’ve ever forgotten to save your work and had Excel crash on you, you’ll know the importance of the ‘save’ shortcut. It takes a fraction of a second to hit CTRL S and frequently save your work, but it can take hours to manually do that work all over again.

Mastering some of the more essential Excel functions, formulas and shortcuts for accountants can make your life easier, and save hours in your workday.