Do you often stare at a spreadsheet, deal with endless amounts of data, and feel like there has to be a better way to do things? Well, there is. Over the years working with Excel, I’ve come across a collection of features that have transformed my workflow. These are not your basic SUM and AVERAGE formulas; These are the hidden gems that can save you hours of tedious work.
I’m sharing six of those essential Excel functions that I wish I had learned much sooner. I’m sure they will make a big difference in your work too.
TEXT BEFORE, TEXT AFTER and SPLIT TEXT
Manipulate your text like a pro
In May 2022, Microsoft introduced several features to manipulate text like a pro. While there are already functions like SEARCH, FIND, LEFT, RIGHT, MIDDLE and SEQUENCE, these (relatively) new functions allow you to return everything before or after selecting the delimiter. They are more effective in various scenarios.
Let’s say you have a list of customer addresses in a single column, formatted as in the screenshot below. Now you want to extract the address, city, state and zip code into separate columns. you can add =TEXTBEFORE(A1, “,””)and will tell Excel to look in cell A1 and return everything before the first comma (123 Main St.). Similarly, TEXTAFTER extracts text that appears after a specified delimiter.
You can even use the combination of TEXTBEFORE and TEXTAFTER. For example, =TEXTBEFORE(TEXTAFTER(A1, “,”), “,””) will return any city. TEXTSPLIT splits text into multiple columns or rows using a delimiter. if you are complete =SPLITTEXT(A1, “, “)will split cell A1 into three separate columns.
Overall, these features are invaluable for cleaning up messy data imported from other sources. You can quickly restructure data for reporting and analysis.
DATE and EOMES
Manipulate dates in Excel
DATE and MONTH They are two powerful tools to modify dates in Excel. EDATE returns the date, which is a specific number of months before or after a given start date. Suppose you have a list of subscription start dates in column A and you want to calculate the expiration date, which is 12 months later.
You can enter EDATE(A1, 12) and drag the formula down to apply the same to all cells. It is also very useful when you want to calculate deadlines during project management.
Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1 and January 1, 2026 is serial number 46023 because it is 46,023 days after January 1, 1900.
EMONTH returns the last day of the month, either before or after a specified number of months from a starting date. For example, if you want to calculate contract end dates, you can enter =EOMES(A1, 11) and it should return the last day of the month, 11 months after the start date in cell A1.
VSTACK and HSTACK
Create tables from different columns.
VSTACK and HSTACK combine multiple arrays or ranges vertically and horizontally into a single array. Let’s say you have sales data for two different regions in separate tables. You can combine these two tables into a single vertical list using the VSTACK function.
Similarly, you can join two columns in a table using the HSTACK function. If you have customer names in one column and your company name in another, use = STACK H(A1:A4, C1:C4) to combine them on a single table.
xLOOKUP
A big step forward from VLOOKUP
XLOOKUP can easily replace VLOOKUP and INDEX/MACTH for you. Unlike VLOOKUP, XLOOKUP can search for values in any column, from left to right or right to left, and provides more control over match types. Suppose you have a table with three columns: Product ID (A), Product Name (B), and Price (C).
Let’s say you want to find the price of the product with ID 1003. You can add =XLOOKUP(E1, A2:A6, C2:C6) to show the correct price. XLOOKUP is useful during inventory management, sales analysis, HR databases, or any situation where you need to search for data based on a key value.
COMPENSATE
Forget the SUM function
The OFFSET function allows you to create dynamic ranges in Excel. Returns a range of cells that is a specific number of rows and columns in an initial cell or range. Suppose you have a list of daily sales figures in column A (A1:A30) and you want to calculate the sum of sales for the last 7 days.
Instead of using SUM, you can run =SUM(OFFSET(A30, -6, 0, 7, 1)) and calculate the sum of the page returned. As you add more sales data to column A, the OFFSET function (unlike SUM) will automatically adjust the range and the sum in cell B1 will update.
ADD
A major productivity booster for power users
AGGREGATE is a versatile function that I wish I had known about sooner. Performs calculations such as AVERAGE, COUNT, MAX, MIN, SUM and more and offers options to ignore specific types of values.
Suppose you have a list of sales figures in column A, but some cells contain errors like #DIV/0!. Now you want to calculate the average sales ignoring these errors. You can run the =AGGREGATE(1, 6, A1:A10) formula where 1 specifies the AVERAGE function, 6 specifies that errors should be ignored, and A1:A10 is the range of sales figures. Can learn the function number and options behavior on the official page.
Beyond SUM and AVERAGE
Now that you know these essential Excel functions, put them into practice. Start applying them in your own spreadsheets and see the difference they make. Whether you are analyzing data, automate reportsor simply organizing information, these tools will save you time and increase your efficiency.
If you’re looking to unlock new levels of efficiency, use built-in tricks to automate your workbook. Check out our separate guide for learn more about Excel automation.
