Excel can feel like magic when you truly get a grasp of its many capabilities.
Sure, everyone talks about VLOOKUP and PivotTables, but there are other, often overlooked, formulas that can be absolute game-changers in finance.
Here’s a personal take on my top 10 underrated Excel formulas—ones that I’ve found invaluable time and again in my career.
Let’s dive in and explore what makes each one special.
Table of Contents
Top 10 Underrated Excel Formulas You Must Know
Here are the 10 underrated Excel formulas that have saved me countless hours and helped me advance in my career:
#1: MATCH
During one of my budgeting exercises, I had to quickly identify which of our cost centers were causing variances. Using MATCH, I could instantly figure out which row contained each cost center—saving me hours of scrolling through long lists. By locating the position of data points, MATCH makes subsequent analyses easy. Imagine finding a specific product’s revenue for a particular month—MATCH can feed into INDEX to help pinpoint that value. In my experience, it’s like having a compass pointing you exactly where you need to look.
Formula: =MATCH(“Product A”, A2:A10, 0)
Why It’s Underrated: MATCH gets less attention compared to its famous sibling VLOOKUP, but it’s a more flexible way to locate items within a range.
Finance Example: Suppose you need to find the position of a certain revenue stream within a list of monthly categories. MATCH is perfect for this.
#2: INDEX
When I had to reconcile revenue reports and needed detailed numbers across different sheets, INDEX became my savior. It felt like a treasure hunt where I could finally say, “Aha, that’s the value I need!” INDEX can return any value from a range when combined with MATCH. This combo is far more versatile than VLOOKUP because it works left, right, up, or down—making it incredibly adaptable in financial analysis. It’s saved me countless times when I needed to extract data that just didn’t fit in VLOOKUP’s rigid structure.
Formula: =INDEX(B2:G10, 4, 3)
Why It’s Underrated: INDEX often flies under the radar, yet it’s the powerful partner to MATCH that allows you to pull data from anywhere in your table. Additionally, using INDEX with MATCH—often called the INDEX-MATCH formula—is more powerful than VLOOKUP. Unlike VLOOKUP, INDEX-MATCH can look left as well as right, and it doesn’t require the lookup column to be the first column in the range, providing greater flexibility in financial analysis.
Formula for INDEX-MATCH: =INDEX(B2:B10, MATCH(“Product123”, A2:A10, 0))
Finance Example: Let’s say you want to return the actual revenue value for a certain product in a particular month.
#3: XLOOKUP
A couple of months ago, I had to do a spending analysis by vendor. With XLOOKUP, I felt like I had finally upgraded from a flip phone to a smartphone – everything just clicked. XLOOKUP is simple yet powerful—it can look in any direction, doesn’t require sorting, and can handle errors gracefully. Once I started using it, I realized how much time I’d been wasting with workarounds. Now, my lookup needs are far more seamless.
Formula: =XLOOKUP(“Vendor123”, A2:A10, B2:B10)
Why It’s Underrated: XLOOKUP is newer and often overshadowed by older lookup formulas. However, it’s like VLOOKUP on steroids.
Finance Example: Looking for the expense amount associated with a vendor code? XLOOKUP can do it—no matter where that column sits.
#4: IFERROR
When preparing management reports, the last thing I wanted was for an executive to see a #DIV/0! error. IFERROR made my sheets “executive-ready”—flawless and smooth. Instead of ugly error messages in your spreadsheets, IFERROR allows you to present clean results. I love it for creating presentable outputs without needing manual intervention.
Formula: =IFERROR(A2/B2, 0)
Why It’s Underrated: Handling errors without complex nesting is underrated for clarity and peace of mind.
Finance Example: When performing division to calculate ratios, you might get #DIV/0! if the denominator is zero. Use IFERROR to avoid this.
#5: CONCATENATE
During one project, I needed a way to differentiate between similar product lines across multiple fiscal years. CONCATENATE made my work feel organized and clear. CONCATENATE helps build context into reports by combining relevant information. I often use it to create unique keys or descriptive labels.
Formula: =CONCATENATE(A2, ” – FY”, B2)
Why It’s Underrated: Combining text strings doesn’t get much attention—but for reports and summaries, it’s so handy.
Finance Example: Joining a cost center name with a fiscal year to make unique identifiers.
#6: SUMIF
Whenever our team had to segment revenue into regions for presentations, SUMIF made the work almost too easy—just a quick entry and the numbers were right there. Instead of manually filtering data, SUMIF adds precision. It’s a go-to for me when summarizing sales or expenses by categories.
Formula: =SUMIF(A2:A10, “North”, B2:B10)
Why It’s Underrated: SUMIF is the quiet achiever when it comes to conditional sums—especially powerful in finance.
Finance Example: Summing sales only for a particular region or sales rep.
#7: COUNTIF
While analyzing overdue accounts, COUNTIF helped me identify just how many customers were falling behind—highlighting trends that needed immediate action. Knowing the frequency of specific events is critical—COUNTIF has given me insights into data trends that were otherwise easy to miss.
Formula: =COUNTIF(B2:B10, “>1000”)
Why It’s Underrated: COUNTIF is often overshadowed by SUMIF, but it’s incredibly useful for counting occurrences based on conditions.
Finance Example: Counting how many times a budget variance exceeded a certain threshold.
#8: ISBLANK
During an end-of-quarter close, I relied on ISBLANK to find missing entries in our reconciliation. It’s one of those tools that prevent costly errors from slipping through. This formula is a handy checkpoint. When I’m validating data, ISBLANK helps me ensure that all necessary information is entered before I run complex analyses.
Formula: =ISBLANK(A2)
Why It’s Underrated: Identifying gaps in data is crucial, but ISBLANK is often overlooked.
Finance Example: Checking if certain cells have been filled out before proceeding with calculations.
#9: SUBTOTAL
One time, during a management review meeting, we filtered out a department’s expenses to see what remained. SUBTOTAL saved the day—and saved me from having to redo calculations. Unlike SUM, SUBTOTAL changes dynamically as you filter—making it perfect for ad hoc reporting. It’s my preferred choice when I need clarity on filtered financial data.
Formula: =SUBTOTAL(109, B2:B10)
Why It’s Underrated: SUBTOTAL is more flexible than SUM, especially when dealing with filtered lists. One of the advantages is that if you have in-between other SUMs—such as in a big list with a lot of SUMs in between—SUBTOTAL allows you to avoid counting the subtotals. Additionally, you can do different types of subtotals, either totaling all of the visible lines or the invisible ones.
Finance Example: Calculating total expenses only for visible rows after filtering.
#10: GETPIVOTDATA
In preparing a dashboard for executives, I needed numbers to dynamically update from PivotTables. GETPIVOTDATA became my go-to—once I understood it, it was like my PivotTables were feeding me answers directly. GETPIVOTDATA allows you to extract specific values without manually navigating the PivotTable. It has made many of my dashboarding tasks faster and more accurate.
Formula: =GETPIVOTDATA(“Revenue”, $A$3, “Segment”, “East”)
Why It’s Underrated: People often avoid GETPIVOTDATA because it seems complex, but it’s incredibly efficient for pulling key data out of PivotTables. One of the common problems with PivotTables is that when they feed into a report, any movement in the PivotTable can result in errors in the reporting. With GETPIVOTDATA, it doesn’t matter if the PivotTable changes in terms of format, colors, or lines—it will always bring back the correct value.
Finance Example: Extracting the total revenue figure for a particular segment from a Pivot Table.
Last Words
These formulas might not be the first ones you learn, but they’re definitely worth mastering.
They can make a huge difference in how efficiently you work and how much insight you can gain from your data.
Whether it’s saving hours of manual work or adding layers of precision to your analysis, these underrated Excel formulas have been game-changers for me, and I hope they will be for you, too.
Give them a try next time you’re working in Excel—you might just find they’re the key to unlocking the full power of your spreadsheets.
FAQ
Q: Why should I use INDEX-MATCH instead of VLOOKUP?
A: INDEX-MATCH is more flexible because it allows you to look up values in any direction, not just to the right. It’s also more robust if your data layout changes, unlike VLOOKUP which can break if columns are moved.
Q: What makes XLOOKUP better than VLOOKUP?
A: XLOOKUP is like an upgraded version of VLOOKUP. It can search both horizontally and vertically, doesn’t require sorting, and can handle errors more gracefully, making it a more versatile choice.
Q: How can IFERROR improve my reports?
A: IFERROR helps you avoid ugly error messages like #DIV/0! by replacing them with a custom value. This makes your reports cleaner and more professional, especially when sharing with others.
Q: When should I use CONCATENATE?
A: CONCATENATE (or its modern counterpart, TEXTJOIN) is useful when you need to combine text from different cells. It’s great for creating unique identifiers or adding context to data in your reports.
Q: What’s the advantage of using SUBTOTAL over SUM?
A: SUBTOTAL is more flexible because it only sums the visible rows when you filter your data. This makes it perfect for ad hoc analysis, as you don’t have to adjust your formulas when filtering data.
