9 Excel formulas everyone in Accounts Payable should know

At Medius, we’re firm believers in the overwhelming advantages of accounts payable automation. From the time that it saves your team on previously cumbersome tasks (like coding and capture) to the data insights it provides and the speed with which it processes invoices, implementing an automated AP solution for your business is increasingly becoming less of a nice-to-have and more of a necessary tool for successful businesses.

That being said, many within the accounts payable department who have yet to automate their processes still depend on Excel from time to time - and most Excel users only take advantage of a tiny proportion of what the software can offer, missing out on time-saving formulas and functions (ready-made formulas) that can save hassle and make calculations more reliable.

So if Excel is part of your day-to-day workflow, our list of handy formulas and definitions has something for everyone, from basic arithmetic to conditional statements and cleaning up text. While this overview can help you speed up your Excel work, there’s no equivalent to true automation that works as a part of your AP ecosystem through seamless integrations to your ERP and other business systems. Excel works well for the basics, but it’s important to note that it’s impossible to ensure you have a completely error-free sheet, so when it comes to making business-ready decisions, make sure you’re relying on a data-driven analytics viewpoint from a proven AP solution like Medius.

Beginners’ notes:

• Start by selecting the cell where you want the answer to show. Then type = into the formula bar across the top of the screen or choose a function (more on that later). To tell Excel which cells to use for the formula, click and drag with the mouse to select a series of cells and put the range that appears in parentheses, or click on individual cells one at a time.

The answer will pop up when you hit Enter.

• To copy a formula into all the cells in a row or column, select the cell containing the formula, right-click or control/command C to copy, click and drag with the mouse to select all the cells where you want the formula to go, Enter.

1 - Basic arithmetic

To add, subtract, multiply or divide, use the four basic ‘operators’.

Add:                  +

Subtract:          –

Multiply:          *

Divide:              /

The formulas are the same as normal sums, but with the = sign first.

For example:

=A1+A2

=B5*C63

2 - Totaling rows and columns using the AutoSum function

The formula for adding up all the numbers in a row or column is

=SUM (first cell address in row or column:last cell address in row or column)

For example:

= SUM (E14:E222)

But there’s no need to type out the formula or even select the cells. From either the Home or Formulas tab, choose the cell at the end of the row or column that you want to add up, and click AutoSum. You’ll see the formula appear in the formula bar. Click Enter.

3 - Adding up numbers in individual cells

To use SUM to add up numbers right-hand in a single row or column, you’ll need to type the formula into the formula bar. For example, =SUM(A2,A9,C13). Rather than typing the cell addresses, though, just click on each cell one at a time to insert the address, adding a comma between each.

4 - Using the AVERAGE, MODE and MEDIAN functions

Remember the terms and definitions you learned in math class about the three types of average?

Mean: all the numbers added together, divided by how many numbers there are.

Mode: the most frequently-occurring value.

Median: the value in the middle of the data set.

The Excel formula for calculating the mean average of a series of numbers is

=AVERAGE (first cell address in row or column:last cell address in row or column).

No need to type any terms in or select all the cells. In the Formulas tab, click the cell at the end of the row or column you want to average, then click on the dropdown arrow by the AutoSum button and choose AVERAGE.

The mode and median functions aren’t in the AutoSum dropdown. The easiest way to use them is to type =MODE or =MEDIAN into the formula bar and select the group of numbers you want to average (adding parentheses at the start and end).

5 - Identifying where a certain condition is met – the IF function.

Say you want to know which values in column B are greater than 100. Select cell C1, and type =IF (B1>100,”yes”,”no”). You’ll see a Yes appear in C1 if the value in B1 is higher than 100, a No if it’s lower. Copy the formula into all the cells where you need it.

Even better, it’s easy to add a calculation to find out the total number of instances where the condition is met.

Choose a cell. For the example above (for a range of cells C1 to C20), type in the formula =COUNTIFS(C1:C20,”true”). You can add multiple ranges and criteria; just separate each with a comma and put quote marks around text.

6 - Calculating percentages

To find out what percentage a value in one cell is of another is easy with this helpful tool.

Say the value in cell A1 is 20 and the value in cell B1 is 15 and you want to know what 15 is as a percentage of 20. Select cell C1, and type the formula =B1/A1. Then go to the home tab and click the % button in the number section.

7 - Cleaning up text

Text that’s cut and pasted into Excel can often have messy extra spaces. To fix this, simply click in a new cell alongside, type the formula =TRIM, and add the relevant cell selection in parentheses. You’ll get a clean and shiny new list.

8 - Joining text from different cells

A common spreadsheet requirement is to join text in different cells – for example, bringing first name and last name into a single full name column. Fortunately, there’s a formula for this.  

To combine a first name in cell A1 (Anna) and a last name in cell B1 (Mason), the formula =B1&”,”&A1 will give you Mason, Anna. Alternatively, the formula A1&” “&B1 will return Anna Mason.

9 - Creating a series of dates

If you spend far too long typing out date series, help is available.

To autofill a column with consecutive daily dates, type in the first date of the series. Click on the little green square on the bottom right hand corner and drag it down the column. The dates will appear.

For a weekly, monthly or annual series, type in the first two dates, and click and drag the green square.

Excel is a useful tool, and it’s worthwhile to know your way around a spreadsheet. But when it comes to accurately and efficiently managing your company’s accounts payable process, Excel is quickly becoming a thing of the past. Automating your accounts payable process streamlines cost, reduces manual error and provides far better visibility into your data. AP automation also frees your team to focus on more important tasks, thus enhancing overall productivity.