When it comes to accounting, having the appropriate tools is essential to ensure accuracy and efficiency. For this reason, Excel has proven a vital accounting tool. Due to its wide range of functions, Excel allows accountants to complete necessary calculations, from the most basic to the more advanced, with just a few options and clicks. And while many functions used in accounting today have been around for some time, Microsoft is constantly improving Excel and developing lots of new functions that are not only essential but that make accounting easier and more accurate. Read below to find ten of the most useful Excel functions for accounting.

SUMPRODUCT

The SUMPRODUCT function is one of the most used in Excel. It is also considered the most flexible as it allows for multi-criteria summing and counting. SUMPRODUCT multiplies ranges or arrays and returns the sum of products. Another reason it is useful and essential is that it can be used in any version of Excel without any special handling, making the transfer of data from an older Excel file to newer ones easy and accurate.

This function is written =SUMPRODUCT(array1, [array2], …).

TRIM

The TRIM function is used to normalize spacing in the text of an Excel spreadsheet, making it easier to navigate. It automatically strips any space at the start or end of a given string except for the single space between all words within that string. This produces cleaner Excel files, especially when transferring text from another file into the Excel document.

This function is written=TRIM(text).

IF

Another important function for accounting is the IF function. The purpose of IF is to test for a specific condition and identify whether your data meets the criteria (TRUE) or not (FALSE). You can test more than one condition with the IF function and it can be combined with logical functions like OR and AND to further extend the logical test you are running.

This function is written =IF(logical_test, [value_if_true], [value_if_false]).

SEQUENCE

The SEQUENCE function allows you to generate a list of sequential numbers in an array, which can be either one- or two-dimensional based on row and column arguments. It can be used on its own with the array being introduced directly into the worksheet or to create an array inside another more complex formula.

This function is written=SEQUENCE(rows, [columns], [start], [step]).

SUMIFS/AVERAGEIFS/COUNTIFS

You’re probably familiar with the counterparts of these functions that sum, average or count based on a single condition. The value of SUMIFS, AVERAGEIFS and COUNTIFS, however, is their ability to perform specific operations on cells that match multiple criteria in more than a single range. Each function can be used with criteria based on dates, numbers, text and other conditions, depending on your desired return value. SUMIFS adds values, AVERAGEIFS calculates the mean value and COUNTIFS counts the cells that meet one or more criteria.

These functions are written

=SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], …)

=AVERAGEIFS(avg_rng, range1, criteria 1, [range2], [criteria2], …)

=COUNTIFS(range 1, criteria 1, [range2], [criteria2], …)

EDATE

EDATE is an extremely useful function for calculating expiration dates, maturity dates and any other due dates. It allows you to shift dates and months in the past or future. EDATE returns a new date by simply adding or subtracting months from the given date using either a positive or negative number to move forward or backward in time.

This function is written=EDATE(start_date, months).

VLOOKUP/ XLOOKUP

VLOOKUP looks up data in a vertically organized table. Its advantage also comes from the fact that it supports approximate and exact matching, as well as wildcards for partial matches. The XLOOKUP function is a newer version of the VLOOKUP that searches horizontal ranges as well as vertical ones. And, unlike VLOOKUP, it can search data from either the first or the last value of a range or array.

These functions are written

=VLOOKUP(value, table, col_index, [range_lookup])

=XLOOKUP(lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

INDIRECT

INDIRECT is used to return a valid cell reference from a given text string. In other words, it returns a reference to a range. This function is useful when you want to build a text value to use as a valid reference. INDIRECT is a great function to use when you have a formula that needs a variable sheet name, a fixed reference that will not change when rows or columns are deleted and when you need to create numeric arrays using the ROW function in complex formulas. It does not evaluate logical tests or conditions and it can’t be used to perform calculations. 

This function is written=INDIRECT(ref_text, [a1]).

AGGREGATE

The AGGREGATE function returns an aggregate calculation, such as AVERAGE, COUNT, MAX and others, while optionally ignoring any null values, like hidden rows and errors. This function is important to know and understand as it can run a total of 19 different functions, each calculating the specific return value you are looking for.

This function is written=AGGREGATE(function_num, options, ref1, ref2).

LET

With the LET function, you can improve clarity by eliminating redundant calculations and making complex formulas easier to read and write within Excel. It essentially lets you define a named variable within a formula. Once you have used this function to name a variable, you can then easily assign it a value based on a calculation.

This function is written=LET(name1, value1, [name2/value2], …, result).

Excel, and its myriad functions, has proven to be a valuable resource that eases the burden of accounting and tracking a company’s finances. However, there are many functions that may be challenging to understand, which is why you should consider checking out some online courses, many of them free, to increase your familiarity with these Excel functions, or check out the full list of functions and help sources on Microsoft’s website. However, there are more resources available today that can make an accountant’s job even easier and more efficient. Lease accounting software, like the one offered by Yardi Corom, provides a cloud-based solution that ensures standards compliance and increases accuracy in the entire accounting portfolio, automatically.

Yardi Corom is a simple and comprehensive accounting, lease and workplace management solution for CRE tenants. Our cloud-based software solution increases efficiency and accuracy across your entire lease portfolio: manage leases and subleases, track key lease data, centralize transactions and become FASB/GASB/IFRS compliant. To learn more, you can visit our website or schedule a meeting with our team.