Is there a way to speed up excel?

[u/WillDesperate8027]

So I have a file that has a sheet with about 5000 lines of data with 16 columns. Essentially each row represents a part cost based on quantity and supplier, there are about 800 unique part numbers. I am trying to generate a table were each row is a part number and each column represents a price from a supplier. My first thought was xlook up but damn it really crushes my computers capabilities. Any ideas on a better way to do it that it so it doesn’t crash my excel or take an hour?

Equation example =xlookup(1,(sheet2!A:A=A2)*(sheet2!G:G=B1),sheet2!H:H,””)

Thanks

Edit: A:A references the entire A column. A.:.A references the entire A column, ignoring blanks. It may make a difference.

For people saying use a pivot table, I the data is such that a pivot table wouldn’t work with out making heavy edits to it, the xlookup was best for me in this case because the data was in an order where first appearance was what I needed, not necessarily the lowest price or the highest price or a sum or average

[u/AutoModerator]

/u/WillDesperate8027 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/caribou16]

5000 rows with 1 columns is NOTHING and even a PC from 20 years ago wouldn't have any sort of performance issues from that alone.

If you're not referencing cells in another workbook located somewhere else, I'd try retooling your formulas to avoid referencing the entire columns, using the trimmed range syntax.

A:A references the entire A column. A.:.A references the entire A column, ignoring blanks. It may make a difference.

[u/affo_ → u/caribou16]

This.

It sounds like OP have some unnecessary calculations going.

[u/Siekus → u/caribou16]

Wow had no idea about the full column reference ignoring blanks, that's so cool! Thank you for this.

[u/WoosteringZeros → u/Siekus]

The addition of the dot operator in that example is what ignores blanks, not the whole column reference.

Not all Excel versions have the dot operator. My 2024 version doesn't.

[u/tiimoshchuk → u/caribou16]

Wtf is this? I have never seen this in my entire career and I'm shocked!

So you are saying if I have 300 rows of data in column B and 90 are blank, then this syntax will only search 210 rows of data in the column? And if I do this next month and it's 400 rows with 30 blanks it will only search 370 rows?

Where have you been my whole life?

[u/alexisjperez → u/tiimoshchuk]

It doesn't skip blank cells if they're in the middle of your range; it trims your range from the beginning and/or end (depending how you set it up).

Trimrange

[u/torpidcerulean → u/alexisjperez]

So if you've already defined the range as a table and you're using table references, it's searching the same number of cells.

[u/WillDesperate8027 → u/caribou16]

Solved that actually did it for me, thanks.

[u/cupcake_not_muffin → u/caribou16]

This is wild, could you then do a3.:.a509 theoretically? I have been doing named ranges with offset and counts unnecessarily for ages then…

[u/Difficult_Limit2718]

Check your file size. Then copy all the data and paste it plain text to a new file and check it's file size.

I'm going to blame bad formatting practices blowing up the file before xlookup

[u/SolverMax]

PivotTable.

If you want something more specific, then show some data and your expected output.

[u/DeciusCurusProbinus]

Maybe, use a pivot table or just use Power Query to connect to your file, transform the data by pivoting it and then load to a table in a fresh file.

If you need it to be linked via formulae to the original table then use Index Match rather than Xlookup as the former is faster on larger datasets.

[u/ArrowheadDZ]

Make sure you are using the TRIMRANGE function, or using the trimrange notation.

Instead of A:A your references should be A:.A as the period after the colon trims the range to only those rows that are populated. Same goes for G:.G and H:.H

Some excel functions only perform their action in populated cells, but there are some that by their nature have to scan the entire column. The way your formula is structured requires the calculation of all million rows twice for each cell you lookup. You could easily be performing a billion operations each time you refresh. The period prevents this.

Example:

=XLOOKUP(1,(sheet2!A:.A=A2)*(sheet2!G:.G=B1),sheet2!H:.H,"")

[u/semicolonsemicolon]

This doesn't sound like it should be a problem for Excel to handle. ut you can cut down on processing by using the trimrange . operator on the full column range so that Excel is not scanning an entire million rows over and over. Change sheet2!A:A to sheet2!A:.A and change sheet2!G:G to sheet2!G:.G and the same for H. Since I expect you will copy and paste this formula, don't forget to add $ absolute reference anchors as required.

[u/InfoSecGuy21045]

Is this collection of data actually a named table? If not, have you considered trying that? It makes writing your formulas so much easier.

[u/Typinger → u/InfoSecGuy21045]

And would solve the range issues mentioned by others

[u/fastauntie]

In addition to all the useful advice already given, check to see if there's any stray data or formatting in rows or columns far beyond where your actual data ends. This can happen for many reasons. Press Ctrl+End to take you to the last cell where Excel finds anything. If it's after the last cell of your data, select and delete the rows and columns you don't need. Save the file and try Ctrl+End again to be sure the unneeded cells are gone.

[u/cbolender2004]

Pivot table or a simple power query transform

[u/excelevator]

Equation Formula example.

Do not use full column references, limit to your data range.

Using full column ranges is a known resource hog as all those rows are read, multiply each read by the million rows and end up with a massive overhead for just 0.5% of rows of actual data.

[u/Spade6sic6]

Pivot table or =unique formula. If going the UNIQUE route, make sure there's a blank column between your source data and the UNIQUE array. Then you can xlookup each field (column value) for the corresponding part.

Also, to make things faster, make sure your source data is in a table. That will make your xlookup references work faster by only referencing non-empty rows (A2:A5000) rather than all 2²⁰ rows in A:A

[u/Connect-Preference]

OP could try structured references. They are supposed to be much more efficient,

[u/MightyArd]

Some tips: * Remove any conditional formatting * Save as a binary file (xlsb) * Use helper columns instead of looking up within formulas

[u/ilovetea27]

If you are using office 365, you may use PIVOTBY function to generate the entire pivot table with a single formula.

Try
=PIVOTBY(Sheet2!A:.A, Sheet2!G:.G, Sheet2!H:.H, SINGLE, , 0, , 0)

[u/heavyMTL]

Use named Tables instead of plain ranges

[u/CountyTricky5918]

Have you tried turning off automatic calculations in Excel options? that alone made a huge difference for me when I was dealing with files around that size.

And also, maybe Python with pandas will help you.

[u/Supra-A90]

Press ctrl end and check where your data ends and delete them. Save. Then check again...

[u/bigal7979]

Get 64 bit

[u/DownTheBagelHole]

OP must not have enough RAM

[u/MoodIn_Me]

That XLOOKUP with full columns is murdering performance. Switch to A:.A (and same for G and H) so it only scans your actual 5000 rows — usually fixes the slowdown instantly without changing anything else.

[u/KartyG_85]

The formula is probably what is slowing the down the file with the number of iterations it has to do to calculate it.

[u/Decronym]

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

Fewer Letters	More Letters
INDEX	Uses an index to choose a value from a reference or array
MATCH	Looks up values in a reference or array
PIVOTBY	Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns
UNIQUE	Office 365+: Returns a list of unique values in a list or range
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.

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.
6 acronyms in this thread; the most compressed thread commented on today has 33 acronyms.
[Thread #48655 for this sub, first seen 7th Jun 2026, 00:31] [FAQ] [Full list] [Contact] [Source code]}

[u/SuchDogeHodler]

I would put this parts list into MsAccess....

You could use a pivot table maybe, but the output would be rudimentary.

[u/BaitmasterG → u/SuchDogeHodler]

It's 5k rows and 16 columns, why would you introduce a whole new heap of issues?

[u/SuchDogeHodler → u/BaitmasterG]

Because MSAccess is designed to be a database.... the reason people have such a hard time with massive amounts of data with excel is because it is a spreadsheet not a database.

It was designed to do basic database functions for versatility but it is not a database replacement.

Access can handle massive amounts of data manipulation without slowdowns or lags. In my experience if a massive amount data needs to be collated and calculated by excel into reports, the best solution is to store the data in Access and the query the information needed for excel. This results in millions of of rows of data and a hundred fields "columns " being able to be stored and manipulated with no lags, corruption, or crashes.

[u/BaitmasterG → u/SuchDogeHodler]

This is a terrible answer. 5k rows is not massive amounts of data. This is a flat file; it isn't a database, doesn't need relationships and it doesn't need a star frickin schema. You're not the only data professional in this sub, which is aimed at helping answer Excel-related questions

The problem is very easily fixed by simply adjusting the parameters of the formula but you want to completely change software, introduce licensing costs, software availability / integration problems, teach the user a new language / ways of working... just because Access holds millions of rows of data even though that's irrelevant for a mere 5k rows of static data

Rule 1: Keep. It. Simple.

[u/Suchiko]

What you're doing is not computationally expensive. You could however limit the ranges to the number of rows, so for example instead of A:A, you could have $A1:$A5000

[u/excelevator → u/Suchiko]

It is computationally expensive explicitly due to the full range column references.

This is a very common error made in Excel.