After years of wrestling with messy spreadsheets, I’ve discovered four Excel functions that save me hours every week by automating tedious tasks most people do manually.
4
1. XLOOKUP
Makes VLOOKUP obsolete
I stopped using VLOOKUP years ago when I discovered XLOOKUP. While VLOOKUP only searches to the right and breaks when you move columns, XLOOKUP works in any direction and stays flexible. XLOOKUP is one of the Excel functions that can save you time finding specific data on your spreadsheets.
In my computer component pricing data, I need to find specific GPU prices based on product models. With VLOOKUP, I’d need to restructure my entire table. But with XLOOKUP, I just write:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP searches the entire product column, finds my GPU, and returns the corresponding price. It doesn’t care where the price column sits, and it won’t break if I add more columns later. I use this constantly to cross-reference product information across different sheets without reformatting anything.
The basic syntax for XLOOKUP is:
=XLOOKUP(lookup_value, lookup_array, return_array)
- lookup_value: The value you want to find.
- lookup_array: Where to look for the value.
- return_array: The column or row that has the value you want back.
So, in my case, the value I wanted to find was “GIGABYTE GeForce RTX 3060 12GB Gaming OC”. I wanted to look for that value in column C:C, and return the corresponding value from D:D in the same row where the match was found.
Another thing I like about XLOOKUP is if I add “, -1” at the end of the formula, it searches from bottom to top, letting me find the most recent price entry automatically. This saves me from having to manually sort data every time I update my spreadsheets.
3
2. SUMIFS and COUNTIFS
Handle multiple criteria like a pro
Basic SUM and COUNT functions are fine for simple stuff, but they fall short when you need real analysis. When I need to analyze my pricing data under multiple conditions, SUMIFS and COUNTIFS are the functions I typically use. These functions let me slice through hundreds of rows easily.
Let’s say I want to count how many AMD processors are available on Amazon US. Instead of filtering manually, I write:
=COUNTIFS(F:F, "Amazon US", K:K, "AMD")
This instantly tells me there are 14 AMD processors listed on Amazon in my dataset. The beauty here is that I can stack as many criteria as I need.
For price analysis, SUMIFS works the same way. To calculate the total value of all Intel processors that are currently in stock, I use:
=SUMIFS(D:D, K:K, "Intel", G:G, "In Stock")
This sums all prices in column D where the brand equals “Intel” AND the stock status equals “In Stock”.
The syntax for SUMIFS is:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)
- sum_range: The column you want to add up.
- criteria_range1: The first column to check conditions against.
- criteria1: The condition for the first range.
- criteria_range2, criteria2: Additional ranges and conditions (optional).
COUNTIFS works identically, except it counts matching rows instead of summing values:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)
I prefer SUMIFS and COUNTIFS for quick reports because they update instantly with new data, fit neatly into my existing formulas, and let me keep everything inline without setting up a separate pivot table.
2
3. TRIM and CLEAN
Save you from data hell
Nothing ruins a spreadsheet faster than messy data with extra spaces and invisible characters. I learned this the hard way when my lookups kept failing because of trailing spaces in model names.
TRIM removes extra spaces from the beginning and end of text, plus any extra spaces between words. When I import data from different sources, product names often come with inconsistent spacing. Instead of manually cleaning each cell, I create a helper column and use:
=TRIM(C2)
I then hover my mouse on the edge of the cell until it turns into the plus (+) icon, then drag it down to all the rows I want the Trim function to work.
This turns ” Kingston Fury DDR4 RGB 32GB ” into “Kingston Fury DDR4 RGB 32GB” instantly. TRIM keeps one space between words but removes everything else.
CLEAN goes deeper by removing non-printable characters that you can’t even see. These invisible gremlins often sneak in during data exports and cause mysterious formula failures. I usually combine both functions:
=TRIM(CLEAN(C2))
The syntax is straightforward:
=TRIM(text): Removes extra spaces
=CLEAN(text): Removes non-printable characters
After cleaning my data this way, my XLOOKUP functions work perfectly every time. While I like using Power Query to clean and prepare my workbooks, I often find that TRIM and CLEAN functions work well for simple spreadsheets that require minimal cleanup. With these tools, I’ve saved hours of debugging by making data cleaning a standard first step in any new spreadsheet project.
1
4. TEXTBEFORE and TEXTAFTER
TEXTBEFORE and TEXTAFTER are a few more of my favorite Excel functions to clean messy spreadsheets. Excel’s newer text functions excel at pulling specific information from messy strings. My price column had entries like “$177.52”, “178.33 USD”, “₱9055 “, and “9645.50 PHP” all mixed together.
TEXTBEFORE extracts everything before a specific delimiter:
=TEXTBEFORE(D2, " USD")
This pulled “178.33” from “178.33 USD” instantly.
TEXTAFTER works in reverse, extracting everything after a delimiter:
=TEXTAFTER(C2, "AMD ")
This extracted “Ryzen 5 5700X 8-Core AM4 Processor” from “AMD Ryzen 5 5700X 8-Core AM4 Processor”.
For complex extractions, I combine both functions. To get just the numeric price from “$177.52 USD”:
=TEXTBEFORE(TEXTAFTER(D8, "$"), " USD")
The syntax for TEXTBEFORE and TEXTAFTER are:
=TEXTBEFORE(text, delimiter) and =TEXTAFTER(text, delimiter)
What makes these functions revolutionary is their precision. Instead of using complex combinations of MID, FIND, and LEN functions, I get clean extractions with simple, readable formulas. I use these constantly to separate model numbers, extract product specifications, and pull clean data from imported text that would otherwise require hours of manual editing.
These four functions solve some of the biggest time-wasters in Excel. Finding data with flexible lookups, analyzing by multiple criteria, cleaning messy imported text, and extracting specific information from complex strings. Most people handle these tasks manually, spending hours on work that takes minutes with the right formulas.
I’ve used these functions for everything from component pricing analysis to inventory management reporting. They work regardless of your industry because messy data and complex lookup requirements are universal. Once you master them, you’ll wonder how you ever managed spreadsheets without them.
