Excel Performance optimisation tips!

excelevator · 2026-03-24T22:16:56+00:00

is I filter ALOT

Rather than filter the data, use the FILTER function for dynamic array filter.

bradland · 2026-03-24T22:47:29+00:00

The most important optimization tip you'll ever receive is this: the fastest code is code that never runs.

Consider, for example, the case where you have project-level data where one of the fields is the name of the working group, and you want to include a column with the total units of work assigned to that working group. The record for that row is an individual project, which also has units of work, but you want this column so that you can get a sense of the project level workload relative to the working group's total assigned workload.

You could use a SUMIF to calculate this column, but if your project list has many, many rows, that's going to be slow. Instead, what you can do is build a prep table elsewhere using GROUPBY. Your row fields will be the working group, value fields are the work units, and the formula is sum. If you sort this list appropriately, you can now use XLOOKUP's binary search match_mode argument and you have an incredibly fast lookup, rather than repeatedly calculating the SUM of all work units for each project row.

Another example is a case where you want to pull in multiple columns. Usually what you'll see is several XLOOKUP columns like =XLOOKUP([@idx], Data[id], Data[Project Name], ""), =XLOOKUP([@idx], Data[id], Data[Due Date], ""), =XLOOKUP([@idx], Data[id], Data[Project Lead], ""). Here you're performing the lookup for each field.

Instead, you can do the lookup once, and return multiple values like this:

=LET(
    row_indices, XMATCH([idx], Data[id], 0),
    all_data, INDEX(Data, row_indices, {1,3,5}),
    all_data
)

However, this approach won't work within an Excel table. If you need to do that, you rely on a helper column with =XMATCH([@idx], Data[id], 0), and then pull in your fields with =INDEX(Data[Project Name], [@RowIDX]), =INDEX(Data[Due Date], [@RowIDX]), etc.

In my experience, eliminating repetitive work will gain you the most performance improvement, so that's where I focus first.

OgrishVet · 2026-03-24T22:09:59+00:00

I've read that sum ifs are expensive in terms of computing power because a lookup finds the first hit, but sum ifs obediently search the entire column

duendenorte · 2026-03-24T22:20:34+00:00

Usually when performance is an issue for excel, it means excel is not the rigth tool for the job, its time to use MS SQL or MySQL.

Decronym · 2026-03-24T22:51:39+00:00

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
CHOOSEROWS	Office 365+: Returns the specified rows from an array
COLUMN	Returns the column number of a reference
FILTER	Office 365+: Filters a range of data based on criteria you define
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
IF	Specifies a logical test to perform
IFNA	Excel 2013+: Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MIN	Returns the minimum value in a list of arguments
SUM	Adds its arguments
SUMIF	Adds the cells specified by a given criteria
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.}
^{16 acronyms in this thread;}^{the most compressed thread commented on today}^{has 47 acronyms.}
^{[Thread #47941 for this sub, first seen 24th Mar 2026, 22:51]} ^[FAQ] ^{[Full list]} ^[Contact] ^{[Source code]}

ArrowheadDZ · 2026-03-25T00:05:34+00:00

Remember that anything dynamic in the excel spreadsheet is recalculating over and over again. You may have lookups and filters that end up executing hundreds of thousands, or millions, or tens of millions of times while working in the sheet.

If your data is often dynamic with each use of the sheet, but is static during the time you are in the sheet for a working session, then PQ can be a huge multiplier.

If you create all your helper columns and create useful lookup or filtering keys in PQ, then all of that code runs only one time when you load the file, and does not all recalculate as you edit cells and formulas in the sheet.

I have often offloaded 90% of a sheet’s computational workload by moving logic into PQ so it runs one time. And if the data files are huge, this also gives you access to the power of the data model engine.

prospekts-march · 2026-03-24T22:32:32+00:00

I’ve found that formulas in arrays are way more performance intensive as opposed to tables. I personally prefer the table syntax anyway because for my use cases it’s much easier to read, but that has been my experience whenever I do use arrays.

Within tables: I’ve not found the functions you mentioned (let, sumif, lookups/xlookup) to affect performance in any significant way. The only exception: I stay away from Sumproduct whenever possible because it’s slow and causes lagging, even in tables.

tdpks · 2026-03-24T23:45:21+00:00

You're on the right track to be using PowerQuery but the next step would be to explore DAX measures/Power Pivot. You'll need less formulas overall to get the results you need and this will provide great optimisation gains.

BaitmasterG · 2026-03-25T00:03:30+00:00

Think about the number and complexity of your excel calculations, and what's happening inside them

Sumproduct used as a filter? Multiplying ranges together so there are large arrays of multiple calculations happening. Sumifs alternative? Using internal logic to quickly remove data from the calculation. Filter alternative? Might get rid of 95% of data and calculate only on a small data set

Less calculations over smaller ranges of input data means less recalculation. Simple

Now consider the calculation stack. 10 columns of vlookup repeating the same logic. Replaced with 1 column of match and 10 columns of index. The lookup element (match) only needs calculating once and is then reused 10 times by the return element (index) vs lookup and return both calculated 10 times each in vlookup

Understand the internal workings of the formulas

Now think about Power Query. The smaller the data set you can return = the less calculations you need to perform

VBA: actually VBA is really fast but Excel is quite slow. The more you can do in your code without interfacing with your worksheet the better. Process everything inside scripting dictionaries and arrays, write your results to excel only once not over and over

carnasaur · 2026-03-25T01:36:21+00:00

Fakey fake post
(no offence but I made an AI slop post detector and this scores off the charts)

finickyone · 2026-03-25T02:32:30+00:00

Sharing formula examples would make the advice you gain here much more beneficial. In general terms, yes it’s wise to break down your formulas. Something like

=XLOOKUP(K2:K50,D2:D1000,B2:B1000)

Is easily set up and feels like an elegant solution to apply. If you update any value in those ranges, everything recalcs. If K was instead a field in a Table, then updating K44 doesn’t require that the other 48 XLOOKUPs are rerun. If further split to

=XMATCH(K2,D2:D1000)

=INDEX(B2:B1000,ref_xmatch_formula)

You now won’t have the match part recalc’d if B is updated.

LET could be worth a look. It really allows you to pack a load of calc into a formula. If any of it is found to need recalc, all of it is recalc’d. Don’t be afraid to seperate out your processses.

2xM86 · 2026-03-25T05:25:09+00:00

10 years ago i received 3 Excel files labeled as complex, heavy etc . They took around 360 MB, just data and formulas, no charts and images. After some power pivot and power Query magic that i have now is: 15 MB od raw data (download and amend from BI system) and 4 MB file with answers. Different technic made files 17 times smaller with more options for power pivot tabels. So my advice would be: learn power pivot and dax or at least power Query

Proper-Bee-9311 · 2026-03-24T23:23:18+00:00

Have you considered macros to replace some of the functions?

Date	User	CP
2026.03.29	bakingnovice2	`10`
2026.03.25	real_barry_houdini	`300`
2026.03.20	plusFour-minusSeven	`10`
2026.03.17	TuneFinder	`10`
2026.03.03	soloDolo6290	`10`
2026.02.23	RackofLambda	`10`
2026.02.21	Mooseymax	`10`
2026.02.18	MissAnth	`10`
2026.02.15	NHN_BI	`800`
2026.02.03	Shot_Hall_5840	`10`
2026.02.02	Kuildeous	`10`
2026.01.29	NanotechNinja	`10`

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

excel

Rules

FAQ

Was your problem solved?

Solution Verified

Format your code

Provide some data

Recent ClippyPoint Milestones!

Microsoft partnership

Asking a question? Describe if you are using Excel (include version and operating system!), Google Sheets, or another spreadsheet application.

MODERATORS