5 Excel Functions You Probably Didn't Know About

Excel is an excellent tool for data analysis and manipulation. It allows you to create charts, graphs, pivot tables, and other valuable tools. And for most people, that's as far as it goes. They only use the functions they are familiar with or understand.

However, familiarizing yourself with more unknown functions can help you manipulate data with the click of a button, saving you time and money. Here are the top five Excel functions you probably didn't know about!

1. XLOOKUP

Released in 2019 by Microsoft, XLOOKUP is an upgrade to the VLOOKUP AND HLOOKUP functions. It uses less computer memory and is easier to understand.

XLOOKUP allows you to search a range of data with specific parameters and return a value if there's a match. For example, you could use XLOOKUP to check the price of a product if you know the product's stock-keeping unit. It also enables you to search one-dimensional or two-dimensional lookup tables.

2. SUMPRODUCT

SUMPRODUCT is one of the least-used and underrated functions in Excel. It multiplies two matching ranges or arrays together and returns the sum. It is similar to SUMIF, but the difference is that SUMIF multiplies the range by the criteria. In contrast, SUMPRODUCT multiplies each cell by the corresponding cell in the criteria range.

3. EOMONTH

EOMONTH calculates the last day of a month, specified by you, or based on your input data. This valuable function can be used when you need to know the end date of a billing cycle or the due date for some other event in the future. While Excel automatically fills in end dates if you insert a start date, EOMONTH allows you to generate any future dates without scrolling through your calendar month-by-month. 

It's of significant help for long-term projects that span several months. The most basic use of EOMONTH looks like this:

=EOMONTH(start date, months)

If you have a starting point, such as January 1st, 2022, and you want to calculate the end of May 2022, type =EOMONTH(A1,5). If A1 were your cell containing January 1st and "5" represented May (the 5th month), this formula would return May 31st and fill it into another cell. This formula is helpful if you only want one end date returned after putting in multiple start dates: =EOMONTH(A1:A100,-3), where A1:A100 were cells containing all your start dates and -3 represented three months before those starting points (so it would return March 31st).

4. TEXT

This function converts a value you've selected to a specific number format. It is most useful when you need to display numbers in a more readable format. Applying this function is vital when you're using spreadsheet software that doesn't support the formatting options for the numbers you want.

For example, you can utilize the TEXT function to display a number in currency format without applying the currency number format to the cell. You can also use this function to force Excel to display leading zeros if you have numbers such as zip codes in cells.

If you're supplying arguments for `value` and `format text,` they must be given as text strings and should be enclosed in quotation marks ("). When entering a date argument into either of these parameters, it's critical to remember that dates in Excel are stored as sequential serial numbers to be used in calculations.

They are given as dates because they have been formatted by default, but they are typical numeric values attached with no special meaning. Therefore, if you try entering a date into the `value` parameter (or any other numeric value), it will automatically be converted into the serial number equivalent and won't match any of the formatting criteria within `format text`.

5. TRIM

TRIM removes extra or unnecessary spaces from an entry in a cell. What makes it different from other functions such as LEN and SUBSTITUTE is that you can use it to clean up the data before performing an analysis on it. This is because you don't have to change the formula itself if your data changes.

TRIM is used with other string functions such as FIND and MID or lookup functions like VLOOKUP or INDEX. For example, if you want to find the central character of a string, you might use =MID(A1, ROUND(LEN(TRIM(A1))/2), 1). There would be an error if you had spaces at the beginning or end of your string because LEN would return a large number.

Are You Leveraging the Above Excel Functions?

When you add the above functions to your toolkit, they make Excel use much easier. If you have any questions, please contact us or listen to our excel training recording today!