The Race Data
Starting with aggregation formulas, I raced them against one another to sum over 110k rows of data:
And filtered based on multiple conditions:
Race rules:
1. All formulas return the same result
2. The speed is
recorded in milliseconds, (1 millisecond = 0.001 second)
3. Results for both the initial calculation, which happens when formulas are first entered, and the recalculation which occurs when there's a change that affects a formula's inputs are measured. Only the initial calculation for the lookup functions was measured.
Race 1:
Conditional Aggregation Functions
One of the most common Excel tasks is to sum values based on conditions. Popular options include SUMIFS, DSUM, SUM(FILTER, and SUMPRODUCT. Let's break down their performance differences.
Race Insights
- The first 3 formulas return very close times. That said, all formulas calculate in well under a second, which is impressive.
- I covered the first function, DSUM in a video recently and quite a few people asked how fast it was compared to SUMIFS. You can see here that it's virtually the same. Even when using whole column references.
- SUMIFS is typically one of the fastest ways to perform conditional summing in Excel. It applies each condition sequentially, which efficiently narrows down rows as the formula moves through each criteria range.
In the first SUMIFS above the first criterion is where the type = Level 1. There are 9 levels in the data and level 1 accounts for 29% of the records, so we've reduced the data being passed to the next criteria range by 71%.
- Tip for Speeding Up SUMIFS:
Reorder Conditions, placing the most restrictive condition first, as SUMIFS funnels data down row-by-row. For example, if the condition in column A results in the fewest matches, place it first for better speed.
- SUMPRODUCT, on the other hand, is often slower because it creates virtual arrays in memory, which are more computationally intensive.
- Tip for Speeding Up SUMPRODUCT: Avoid full-column references (like A:A), as SUMPRODUCT will process all rows even if the data range is smaller. Limiting the range to your actual data, like
A1:A100000 can speed things up significantly.
- When I posted the video on DSUM recently, quite a few people said they now use SUM(FILTER(…, but you can see this is almost
as slow as SUMPRODUCT.
Key Takeaway: If you need a lot of conditional sum formulas, use SUMIFS where possible. Reserve SUMPRODUCT for scenarios requiring more advanced operations but always restrict the range.
Race 2: Conditional Functions with AND and OR Criteria
DSUM has another advantage over SUMIFS: it easily handles AND and OR criteria like this:
For instance, you could sum amounts for Level 1 data within a specific date range or Level 2 data within another range — all within one succinct DSUM formula. SUMIFS requires "daisy chaining" multiple formulas to achieve the same result, which can make formulas complex and less readable.
Race Insights
Interestingly, the SUMIFS and DSUM formulas are on par in terms of speed, but I think you'll agree, the DSUM formula is neater.
Optimization Tip: For ease and speed when handling complex conditions, DSUM is often superior to SUMIFS.
Race 3: Dynamic Ranges and Whole Column References
Excel's dynamic ranges simplify formulas, but performance varies depending on the function
used:
Race Insights
- Using SUM with dynamic ranges like TRIMRANGE and the new trim ref dot operator shows similar speeds, as does referencing whole columns.
- A suggestion I got a couple of times when I revealed the new TRIMRANGE and trim ref dot operator was to use TOCOL to trim empty rows, but you can see above its considerably slower. However, these are milliseconds, so it wouldn't be noticeable unless you used a lot of these formulas.
- SUMPRODUCT with
whole columns are slower because they don't efficiently skip empty rows. In particular, SUMPRODUCT with whole column references is roughly six times slower than when it references only specific table cells.
- The traditional ways to reference dynamic ranges using OFFSET and INDEX both return similar calc times, albeit slightly slower than whole column references and TRIMRANGE.
Optimization Tip: Avoid whole column references when using SUMPRODUCT. For faster dynamic ranges, consider using TRIMRANGE or other structured references.
Race 4: Exact Range Lookup Functions: VLOOKUP, INDEX & MATCH, and XLOOKUP
Excel lookup functions have a reputation for slowing down large workbooks. I compared the classic VLOOKUP and INDEX & MATCH with the newer XLOOKUP on a fixed range:
Formula 1:
=VLOOKUP(B4,$P$4:$Q$28,2,0)
Formula 2:
=INDEX($Q$4:$Q$28,MATCH(B4,$P$4:$P$28,0))
Formula
3:
=XLOOKUP(B4,$P$4:$P$28,$Q$4:$Q$28)
Formula 4:
=XLOOKUP(B4:B107522,P4:P28,Q4:Q28)
Race Insights
- For simple lookups on 107,000 rows, VLOOKUP, INDEX & MATCH, and XLOOKUP perform similarly, each calculating in well under a second.
- XLOOKUP used as a spilled array is approximately 30% faster than individual cell-by-cell lookups.
Race 5: Dynamic Range Lookup Functions: VLOOKUP, INDEX &
MATCH, and XLOOKUP
I then compared XLOOKUP using various methods to lookup a dynamic range, allowing the table to grow and have the XLOOKUP formula automatically copy down to the new data:
Formula 1: =XLOOKUP(TRIMRANGE(B4:B1048576,2),P4:P28,Q4:Q28)
Formula 2:
=XLOOKUP(B4:.B1048576,P4:P28,Q4:Q28)
Formula 3: =XLOOKUP(OFFSET(B4,,,COUNTA(B4:B1048576)),P4:P28,Q4:Q28)
Formula 4:
=XLOOKUP(B4:INDEX(B4:B1048576,COUNTA(B4:B1048576)),P4:P28,Q4:Q28)
Formula 5: =XLOOKUP(TOCOL(B4:B1048576,1),P4:P28,Q4:Q28)
Race Insights
Dynamic range XLOOKUPs, which automatically detect the last row, are only slightly slower and remain
very efficient for large datasets.
Optimization Tip: XLOOKUP as a spilled array is the fastest choice when working with large datasets, especially when paired with dynamic ranges.
Tools to Optimize Performance
For Excel power
users, the FastExcel tool by Microsoft MVP Charles Williams can help pinpoint bottlenecks in your workbooks. This add-in includes features like the Calc Range button that I used to measure calculation time in the races above.
Note: You'll notice the calc time above differs slightly from the original test of 121.504. The calc time is affected by other processes running on your PC at the time of the test, but the difference is negligible.
The drill-down profiler can also analyze your workbook, identify the slowest worksheets, and highlight specific formulas that consume the most calculation time. This tool provides a focused approach to streamline large, complex workbooks.
Get the FastExcel tool here* and fix slow workbooks once and for all.
*This is an affiliate link. If you choose to purchase the tool, we earn a small commission. We only recommend tools that are relevant to Excel, useful, and offer exceptional value.
Final Thoughts
Excel's recent updates have improved the performance of many functions, making it
easier than ever to work with large datasets.
Still, selecting the right functions and optimizing their structure remains essential. With the insights from these function speed tests and optimization techniques, you can boost your workbook's performance and streamline your workflow.
For even more efficient formula writing and debugging, check out Excel's new Advanced Formula Editor — a free tool with real-time error detection and formatting options that make creating complex formulas a breeze.
Learn With Us
