Best 10 Excel Functions for Accountants (2024)

AGGREGATE

The AGGREGATE function is a versatile tool in spreadsheets that lets you perform calculations like summing, averaging, and finding maximum or minimum values in a range of cells. It’s especially useful because it can ignore errors and hidden cells in the data.

Take the following as an example:

Suppose you try to sum a column of “Amount” values but one of the cells has a “#DIV/0” error. Normally, this error would stop the sum and return an error for the entire calculation.

The AGGREGATE function will handle this without issue.

AGGREGATE is like a Swiss Army knife—it combines 19 different functions into one.

When you use it, you choose a function by its code number, just as if you were using that function by itself.

Selecting one of the numbers listed is no different than using the selected function on its own.

The real advantage of AGGREGATE comes with its options to handle errors and hidden rows.

Selecting the appropriate option value will alter the way AGGREGATE behaves when encountering errors, hidden rows, or even other AGGREGATE functions.

For example, when you’re creating subtotals and grand totals, AGGREGATE can ignore the subtotals to prevent double counting.

It’s also helpful when you filter tables and don’t want hidden rows included in your results.

If you filter data and errors are present in the remaining visible rows, AGGREGATE will ignore both the errors and the filtered-out rows.

In contrast, a standard SUM function would still include all values in the filtered range, whether visible or not.

ROUND

TheROUNDfunction allows you to round your results to a set number of decimal places.

When we perform calculations, often the result is displayed to a level of precision beyond our needs.

To ensure that a result is only ever displayed to a set level of decimal precision, we can enclose the calculation in a ROUND function and define the number of decimal places, such as 2 decimal places…

…or rounded to a whole number.

A lesser-known ability of the ROUND function is that you can round UP in a “left of the decimal” fashion. This is useful when you are representing very large numbers, but you only need precision to a certain level.

EMONTH

TheEOMONTH(End of Month) function accepts a date or a reference to a cell holding a date and produces a new date that is the last day of the month for a set number of months forward or backward in time.

For example: If we supply the date “1/15/2021” and ask for the end of the month 3 months from that date, we will produce “4/30/2021” as a result. We move 3 months into the future then push to the end of the resultant month.

If we want the end of the current month, we inform EOMONTH to move0(zero) months forward.

If we want to move backward in time, we provide a negative value to the EOMONTH function.

BONUS FUNCTION

Unfortunately, Excel does not possess aBOMONTH(Beginning of Month) function, but with a bit of creativity, we can create one.

If you wanted to get the first date of the following month, you can write an EOMONTH function to calculate the end of the current month… then add a day.

EDATE

TheEDATEfunction allows you to move a set number of months forward or backward in time based on a specified date.

Suppose we need to calculate warranty expirations based on the 14 months from the date of purchase.

The formula(s) would appear as follows:

=EDATE(A3, 14)

If you need to calculate N-number of months in the past, define the “Months” argument as a negative value.

=EDATE(A3, -14)

If you wanted to be generous with your warranties and allow a grace period to the end of the 14^thmonth, you could nest theEDATEfunction within inEOMONTHfunction.

=EOMONTH(EDATE(A3, 14), 0)

WORKDAY

TheWORKDAYfunction is ideal for calculating a set number of days forward (or backward) in time but skip the weekends and possibly holidays. There are two versions of the WORKDAY function:

• WORKDAY– Older version that defines Saturday and Sunday as weekends.
• INTL– Updated version that allows for the definition of 1 or 2-day weekends and what day they occur.

As the WORKDAY.INTL is more feature-rich, we’ll demonstrate that version. We define the arguments as follows:

1. Day to calculate from
2. Number of days to count forward or backward
3. The desired weekend combination
4. The list of holidays (if needed)

If we have a date in cellA3and we wish to count21days into the future, avoiding weekends (SaturdayandSunday) and any holidays that listed (rangeF3:F15), the formula would be written as:

=WORKDAY.INTL(A3, 21, 1, $F$3:$F$15)

The codes for the weekends are as follows:

To learn more about theWORKDAYandNETWORKDAYfunctions, check out the link below.

3D Formulas

3D Functionsaren’t functions but rather shortcuts to writing functions. Suppose you have several sheets of values that you need to sum together and place as a total on a separate sheet. The long way to write a formula like the following that references each sheet separately.

=SUM(COGS!B4:B15) + SUM(ERExp!B4:B15) + SUM(Material!B4:B15) + SUM(NonOpExp!B4:B15)

Just like you can define a range of cells (ex:A1:A10) you can define a range of sheets (ex:COGS:NonOpExp).

We could rewrite the formula using a sheet range definition as follows:

=SUM(COGS:NonOpExp!B4:B15)

When you create a range reference in a spreadsheet, adding new sheets between the starting and ending sheets will automatically include these in your existing formula.

For more examples and detailed explanations of 3D formulas, check out the following link:

XelPlus – Excel 3D Formulas

SUMIFS / AVERAGEIFS / COUNTIFS

Think of theSUMIFS,AVERAGEIFS, andCOUNTIFSfunctions asSUM,AVERAGE, andCOUNTwith built-in filters.

We can point to a range of cells and then define criteria by which to include specific items in the range.

For example:

• Sum amounts where the “Account” is equal to “Services”.
• Average “Profit” for discounted items.
• Count “Sales” that are more than “10,000”.

You can even define multiple filter criteria, such as sum the “Sales” over “100” for “Domestic” orders.

In the below example, we want to total all “Amount” cells (D2:D25) that are associated with the “Services” account (C2:C25). The formula would appear as follows:

=SUMIFS(D2:D25, C2:C25, "Services")

If the “Services” account were listed in a cell (ex:G3), the formula could be written more dynamically like so.

=SUMIFS(D2:D25, C2:C25, G3)

If we want to add another condition to the filter where the “Account” (C2:C25) must be “Employee Related Expenses” (G4) and the “Date” (A2:A25) must be after “1/15/2021” (H4).

We can write the formulas as follows:

To see more examples with detailed explanations of these functions, take a look behind these links:

XelPlus –How to SUM Different Columns based on Header?

XelPlus – Do you know the difference between the SUMIF and SUMIFS function?

IF

TheIFfunction allows you to ask a question then act in one of two ways based on the answer.

The question you ask must be answerable as “True” or “False” and nothing else.

The formula looks like so: ask a question, then perform action “A” if true or perform action “B” if it’s false.

In the below example, we want to audit any “Amount” over “20,000”. We could say, “If the value in cellD2is greater than20000then display the word “Check”. Otherwise, display nothing.”

=IF(D2 > 20000, "Check", "")

We can see that any amount more than 20,000 displays the “Check” message, while other cells display nothing.

NOTE: The use of the 2 double quotes is Excel’s way of saying “display nothing” (empty text).

PRO TIP: It’s best to place the 20,000 value in a separate cell and reference the cell as opposed to hard-coding the 20,000 into the IF formula. This way, you can more easily see what the “Amount” threshold is and if you need to change the threshold, you can change the single-cell value and update all the IF formulas in a single update.

=IF(D2 > H1, "Check", "")

You can check for multiple conditions by either nesting anIFwithin anotherIF(you can do this up to 64 times) or you can use anIFSfunction that allows up to 127 logical declarations, although this is not recommended. Once you get past needing 10 or more decisions, there are usually alternate strategies that will perform better with less logical construction.

To see an ingenious use of wildcards for performing partial text matches with IF, check out the link below:

XelPlus – Excel IF Function with PARTIAL Text Match (IF with Wildcards)

VLOOKUP

The VLOOKUP allows you to take a value in one list, locate that value in another list, then return an associated piece of information.

It would be like ordering lunch where you locate an item in the menu, then return the price to see if you can afford the item.

In our example below, we have a list of “Account #” (columnA) and need the associated “Description” in columnB.

A separate table has the “Account #” (columnG) and “Descriptions” in columnH.

We can write a VLOOKUP formula that says, “Take the “Account #” in cellA3and locate it in “Master Data” table (columnsG:H). When found, move over to the 2^ndcolumn in the “Master Data” table and return the item on the same row.”

=VLOOKUP(A3, G3:H14, 2, False)

The “False” argument at the end tells VLOOKUP to locate the “Account #”exactly, otherwise, return an error message. If you are looking for items that are “close” to the object being searched for, you can write “True” here or leave it empty. This equates to what is known as an “Approximate Match” compared to an “Exact match”.

NOTE: When constructing the table to be searched (G3:H14) ensure that the leftmost (i.e., “first” column) contains the data to be searched. This is a requirement of the VLOOKUP function. The column of associated data being returned does not need to be directly next to the “first” column, but it does need to be to the right of the “first” column.

To see more examples of VLOOKUP in action, click this link for some great practical examples.

AMAZING NEWS!!!!

If you are using Microsoft 365 or Office 2021, you have access to a new lookup function calledXLOOKUP.

💡 XLOOKUPis much more flexible, feature-rich, and easier to use.Check out this article to learn more about this amazing new function.

TRIM

The TRIM function will remove any extra spaces either before or after the cell text as well as remove and redundant spaces within the text.

For example, if we had the following in cellA1:

“ This is a test ! “

We could write the following formula:

=TRIM(A1)

… and get returned “This is a test!”

This is a function to use when you are using VLOOKUP because sometimes the data being searched for has “invisible” spaces at the end that cause havoc with VLOOKUPs ability to perform an accurate match.

If we used the previous VLOOKUP example, we could make the formula more robust by nesting a TRIM function in the first argument like so.

=VLOOKUP(TRIM(A3), G3:H14, 2, False)

Examples of creative uses of theTRIMfunction can be seen in the following post.

Announcement for Office 365 Users (Time saver for Accountants)

All the above-mentioned functions are available in ALL versions of Excel.If you are an Office 365 subscriber, you have access to an assortment of new functions.

💡 Check out this article that showcases many of these new functions.They will change your Excel life.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master these important Excel Functions for Accounting with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.