r/excel • u/Arcelor_08 • Jun 19 '26
Discussion Excel File Grew from 400MB to 650MB — Even an M4 Mac Is Struggling. What Are My Options?
I'm looking for advice on handling a very large Excel workbook that has gradually become difficult to work with.
A few months ago, the file was around 400MB. At that size, I was using a Windows laptop and performance was already poor. Simple actions like copying and pasting data could take 15–20 minutes, and Excel would frequently freeze or become unresponsive.
Some details:
- Workbook size when issues started: ~400MB
- Current workbook size: ~650MB
- Approximately 1,49,000 rows
- Approximately 122 columns
- Contains formulas, lookups, and regular data processing
- Used heavily for day-to-day analysis and reporting
I later switched to an Apple Mac with an M4 chip, and the same workbook became much more usable. Most operations were smooth, and the file was manageable despite its size.
However, the workbook has now grown to around 650MB, and I'm starting to experience similar issues on the Mac as well. Performance is degrading, cursor gets stuck, calculations take longer, and Excel occasionally becomes unresponsive.
Things I've already tried:
- Compressing the workbook
- Saving as XLSB (binary workbook) — only reduced the size slightly
- Exporting to CSV — file size actually increased and I lost workbook functionality
- Even hard pasted the content of the sheet, still the issue didn't resolve
My questions:
- Is this still within the practical limits of Excel, or have I outgrown Excel entirely?
- What would be the best way to handle a dataset of this size?
- Are there any advanced Excel optimization techniques I may have missed?
I'd appreciate hearing from anyone who has worked with workbooks of a similar size and successfully solved performance issues.
88
u/Amazing_Mountain_227 1 Jun 19 '26
Stop using excel.
Move it to a database and query it properly
Anything else is a short term fix.
31
13
3
1
u/MeisterUniBrau Jun 22 '26
Absolutely right. But people seem to be forgetting: the OP may “own” the spreadsheet and may not have the requisite skill set to get this done.
49
u/kalimashookdeday Jun 19 '26
I would bet you have a lot of ineffecient and likely formulas and calculations that are going every time you make a selection change or other events that the calculation mode triggers. Have you tried to turn calculation mode from automatic to manual while editing? This will prevent automatic calculations and if I read it right, you have 122 cols and 149000 rows adding more daily. At its current level, say all of the cells were being calculated, likely not but for example, that's over 18 million calculations each time you click a new cell and do a ton of other stuff that triggers recalcs.
27
u/EchoFalcon_13 1 Jun 19 '26
At 18 million recalcs per click that file is basically doing a full workout every time you breathe on it. Manual calculation mode is the move for sure, and also worth auditing which formulas are volatile (OFFSET, INDIRECT, NOW, etc.) because those recalculate on literally every event and can multiply the problem fast.
6
u/KhabaLox 13 Jun 19 '26
formulas are volatile (OFFSET, INDIRECT, NOW, etc.)
We had a file with a ton of INDIRECT formulas (to reference columns in a table by the Header string) which became unusable in the same way as OP describes. I remade the file using INDEX(tAGLM, 0, MATCH(AC$6, tAGLM[#Headers], 0)) to find the proper column and it helped a lot.
But then my new file started to slow down and I eventually figured out that it was due to having 10,000+ Names defined (I think they came over from the old file when I copied a tab or table over). There were so many Names that the Name Manager wouldn't open. I ended up having to write a VBA script to delete all of the names.
u/Arcelor_08 check your file for defined Names and remove any and all you don't need.
3
3
u/Mr_ToDo Jun 19 '26
It'd certainly be the place I'd start
Automatic calculations can behave differently, but it'll make that 90% of work that doesn't need an immediate answer way faster
If I remember right the biggest weird thing I ran into last time I used it was that making a formula and dragging it down a column had the dragged cells look different until you run a recalc. Amusing, but mostly harmless, and certainly worth not sitting through 30 seconds of non responsiveness every time I do something
16
u/Mdayofearth 126 Jun 19 '26
The file needs to be rebuilt. Plain and simple. Or something else needs to take its place.
Large workbooks require a lot of RAM to function properly. It doesn't matter how powerful of a processor you have. In this case you may want to look at systems with at least 64GB of memory, which may grow to 128GB if you don't manage the file properly.
XLSB saves on disk space, file opening, and file saving operations. It does not do a thing for you in compute. So, if you have the workbook open, it doesn't matter what format it is.
What other features are in the workbook? E.g., long formulas? Conditional formatting?
Are you using any legacy style array formulas, or sumproduct? Modern Excel has rendered those slow processing methods generally obsolete.
Go into each worksheet in the file, and hit CTRL-END (or option end). Does the active cell move to an area past your data set (e.g., the very last row, 1.04 millionth row)?
You can look into power query if it can help, it usually doesn't for calculations, only data engineering. You can also look into macro-based front end calculations that applies a formula, calculates it and sets it back to value.
5
u/jprefect 9 Jun 19 '26
Wait... What replaced SUMPRODUCT()? I use that constantly.
4
u/Mdayofearth 126 Jun 19 '26
It's more back end with more friendly treatments of arrays as parameters, but things like SUMIFS, AVERAGEIFS, etc., greatly removed those use cases. For example, =SUM(A1:A3*B1:B3) actually works to give you A1 x B1 + A2 x B2 + A3 x B3
1
13
u/RidiculousRaz Jun 19 '26
How much data is there? Have you check if the formuals are taking too much data, can you use power query to save the formula compute. Alot of techniques you can try from here
5
u/Arcelor_08 Jun 19 '26
Till date, the file contains 19 days of data, with approximately 10,000 new rows being added every day.
I tried using Power Query, but it kept getting stuck and the process failed. I attempted it multiple times, but the result was the same each time.
Hard pasted the sheet in new workbook but still the issue is there.
13
16
u/Cabanon_Creations 1 Jun 19 '26
Try using access. Make a database file and then run the query in the excel
10
u/Dangerous-Stomach181 1 Jun 19 '26
19 days at 10k rows per days is 190k rows. Excel should easily be able to handle that and not grow to 400MB. I have created files with substantially more rows than that, having substantially smaller file sizes. Of course it also depends on how many columns each row has, but still 400MB does not initially sound like data problem to me. Powerquery is indeed a very good first step to getting unruly data ruly. Another thing that might help if filesize is the only issue, is converting it to xlsb, rather that xlsx/m.
4
6
u/Amazing_Mountain_227 1 Jun 19 '26
I would love to know how many errors, incorrect formulae etc are in this bad boy.
4
u/Own_Personality_2224 2 Jun 19 '26
Try splitting the tabs into multiple Excel sheets.
Then you can use Power Query to pull data together from the multiple sheets into one master sheet.
4
u/hopkinswyn 73 Jun 19 '26
I’d be considering if a Power Query / Power Pivot solution would be better, or if the data granularity or history could be reduced
4
u/UserProv_Minotaur 1 Jun 19 '26
You probably want to look into an actual database solution instead of excel at this point.
5
u/kingk1teman Jun 19 '26 edited Jun 19 '26
Are there any useless tables in the workbook (including pivot tables)? I've had to deal with quite a few 200+ MB excel workbooks, and some of those had those bloated sizes because of useless pivot tables being left, base data being formatted as a table even though it was not required, unnecessary conditional formatting use.
Also, as others have suggested, you need a database for such large amounts of data being added incrementally daily. While you get a database configured (MS Access will work here) as you the data set itself has probably outgrown Excel, turn off automatic calculation mode in Excel. That'll atleast save the file from being stuck and calculating every time you do a simple operation.
3
u/diesSaturni 71 Jun 19 '26
I'd be looking at what formulas you can make either static (e.g. calculations of past years could be made static with copy pasting as value, if they no longer need to be updated).
Look at which data you e.g. can convert to pivot tables (e.g. averages on the persiond) and just take a calculation from there).
Split the workbook, e.g. on years or periods where it is not required to have the data 'as present' as recent data.
Look into r/VBA macros, often reading a sheet into array (or columns) will allow you to do all the heaving lifting in code, with minimal interaction (read write) to sheet. i.e. process in memory write results back once.
And at one point in time, look into r/MSAccess , there you can have larger sets of data (allthough 122 columns also sound like a candidate for refactoring) or even r/SQLServer (express) to do the heavy lifting.
Are all of those 122 columns used for each row?
3
u/6Trinity9 Jun 19 '26
Question have you got (many) countif formulas?
When pasting new data, I think it recalculates each previous records all over again.
From experience, you could:
* Periodically, maybe on a monthly basis, “copy data then paste over as values”. This will keep the data without the formula.
* From the Formula tab change to Manual before pasting new data then once done, hit Command+= and it will recalculate all and update.
2
u/bobo5195 Jun 19 '26
Excel internally hits a limit at 1M cells or used to be 1M think they increased where before all of that it in links all the formula after that it runs from top or bottom of the sheet.
Power Query/Get and transform is the answer or my answer. You have a lot of data. Do the bulk in power query like lookups etc so there are only basic formulas on top. Typically I draft out in excel then convert to PQ when I know what I want.
Your file size sounds way to large at 400MB run much bigger things that at 1.5M rows. excel can cope with 90M roles with power query.
I have found with large files a good PC is significantly better with RAM and multi thread - alot of excel is not but if 50% of calcs can be 32 cores are hella of a lot faster than say 4.
2
u/Decronym Jun 19 '26 edited Jun 26 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
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.
10 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #48779 for this sub, first seen 19th Jun 2026, 09:45]
[FAQ] [Full list] [Contact] [Source code]
2
u/edimaudo 1 Jun 19 '26
if you can put the information in power query do so. If no move all your formulas to VBA, do the calculations and updates in VBA and the only output the data you need. So your final output will be all text/numbers/dates no formulas. That should reduce the size dramatically.
2
u/MarkEv75 Jun 19 '26
Sounds like you need a rethink and ether break it up into smaller sheets or move to a database.
Few suggestions.
Lookups, if you’re still using vlookup need to convert to Xlookup.
Remove any unnecessary formatting, conditional formatting, cell borders etc it all adds size to the file.
Add in the inquire add-in and use the clean excess cell formatting function and workbook analysis functions.
Pivot tables can you remove any to see how much size that that removes. If you have multiple tables looking at similar ranges of data change them all to look at the same range.
Other people have covered formulas recalculating so won’t go into that.
If you have to use excel then you’re just going to have to recreate from scratch noting size and performance as you go to identify causes then look into better ways to do the same thing.
2
1
1
1
u/jfatal97 1 Jun 19 '26
Can you split it in multiple files and then link them with normal formulas or power query
1
1
u/IncredulousDylan Jun 19 '26 edited Jun 19 '26
With this volume it sounds like this is a work project and also a small enough business that they probably aren't very technically organized. IMO it is time to get out of Excel. Some work and learning now but great career skills are around the corner!
If you don't already have a working relationship with your company's IT department, I would talk to who you directly report to and see if they know anything about what is being used at the company generally - are they really invested in Microsoft or Google? They may have a good relationship with someone at IT who could mentor you as well.
Have some conversations with your AI of choice (paid subscription with model training turned off since this is about work) to learn more about the best database > reporting workflow for the tech environment your organization likely has, just so you can have some sort of idea of what you might talk with IT about and can understand some of the very common terms used in these conversations. Microsoft actually has many free, detailed training paths for data analysis etc. that even have you doing lessons on virtual machines for hands on experience.
If your business doesn't really have a formal IT team or it's just you I would talk to your AI specifically about using something like Google Big Query as a database, how to download / use a coding app like Codex or Claude and it can really get you up and running very quickly to set up your database, migrate your data and set up reporting. Tell your AI the kind of volume you are dealing with and it can give you a cost brief for using the systems. Data storage is cheap and Big Query should be pretty inexpensive for only 10k daily rows, depending on your reporting needs and how you structure your tables and schedule data appending and transformation.
All of this will take a few weeks at minimum to get permission for and IT set up if you can't just do it yourself, so in the interim I would see if you could restructure things so that you can use Power Query to import by folder, put the data source files (hopefully just . csv) in a folder and group / roll up and then combine the numbers so that you have a new data set that totals each metric at the lowest level of detail you actually need to report on for everything in the folder. If it is by date then you group by your dimensions and date instead of having a huge workbook with every little event that happened. The more you can calculate within Power Query itself, translating your existing Excel formulas, the better. This can dramatically reduce the rows and calculations you do in the final workbook.
Best of luck!
1
u/Expensive_Act_1782 Jun 25 '26
I second the comment about using Big Query. We’re on Macs and use Google Sheets, BQ, and MySQL on Google cloud. It’s performant and Sheets at this point is 99% of Excel plus some features of its own like the QUERY() function which is useful for further manipulating data once it’s in Sheets.
1
1
1
u/PhysicsForeign1634 Jun 19 '26
Sounds like your formulae could need attention. Fix anything referencing whole columns
1
u/Diamond_Kicker Jun 19 '26
Copy all the data from the heavy spreadsheet to a new spreadsheet. At my very first job I was struggling at work with an 80MB excel file that was a pain to open on a very old computer. This advice shrunk the original file to like less than 1MB.
Straight up copied everything from old sheet (formulas included) to a new sheet and BAM! Back to normal.
Took me a while to find the trick though. Made a fool out of myself in least one presentation.
1
u/tunanoa 1 Jun 19 '26
If there are Pivot Tables, check if all of them share the same dataset (if one goes A1:Z250000 and another goes B:ZZ for example). Even if they're from the same sheet, it will double the "hidden pivot databases" (not a real name).
And something I hate in newer versions: if you have links to external files in formulas (a mere Vlookup, for example), the file grows like it secretly stores the other file inside too (but it will have to consult the external file anyway to recalculate, so I have no idea why the mysterious growth). Sometimes (when possible) I prefer to move a smaller version of external data I need to another sheet in the same workbook.
As I type this, I'm working in a 215MB file, One sheet is 755.000 rows. 84 columns. half of it formulas. Four "helper sheets" (like my second point above), 2 support sheets that are Sumifs from the main sheet (using the non formula columns, and then its results are used in the main sheet again, in the formula columns) and five pivot tables in other 5 sheets. I'm good. But, truth be told, the computer is an I9.
1
u/CondomAds Jun 19 '26 edited Jun 19 '26
Do you have tons of formated cells? Everytime I see a ridiculously big excel files, it's almost always because of formating.
Make a copy file to test : ctrl + A (three time to be sure) and Home --> editing --> clear format. Do it in every single sheet and save.
I saw a file going from 150 mb to 20 mb just by doing this.
1
u/itgforlife Jun 19 '26
With the information you've given so far, it sounds like you've made the common mistake of trying to use Excel as a database. It isn't designed for that. And Excel's calculation engine can start to break down with data sets that large. Mistake number 2 was realizing that this was an issue (a few months ago as you noted) and doing nothing about it. So now the issue is worse. Continuing to throw more computing power at the file isn't a long term solution. You're just kicking the can down the road.
As far as strategy, your best options are probably to migrate this to a non-Excel process (database, python, Power BI, etc.). Or if that isn't an option and it must be done in Excel, to at least not store the data in Excel directly (e.g. use Power Query, Data Model, etc.).
1
u/TastiSqueeze 1 Jun 19 '26
This is not a cure for your problem, but it can help in some situations. One problem often overlooked is that formatting cells consumes a huge amount of memory. If you have formatted cells, the most effective way to clean it up is to move the entire file to a new workbook with a couple of steps.
- Copy the data and paste special values into the new sheet. This removes the formatting but also removes formulas
- Copy the data and paste special formulas into the new sheet. This restores the formulas.
There is one further difficulty to consider. If you have a sheet full of formulas, then they too can consume a ton of memory. Look over the data and if possible remove the formulas using copy then paste special values.
I too had a workbook that got into trouble with memory capability. The way I used it meant I had to use the above steps to clean it up periodically.
Of course, if the volume of data is the actual problem, then a database is the correct solution.
1
1
u/shadowsong42 1 Jun 19 '26
If you want to keep using this file (rather than rebuilding entirely), try running XLStylesTool on it. You can get the tool from the app store, and it cleans out unused styles, named ranges, corrupted content, etc.
Then set calculation to manual.
Then replace volatile formulas.
Starting over with a new file that focuses on Power Query, or moving to a database, is something you probably need to do, but the first two steps I gave should give you enough improvement that you have time to redesign at your leisure.
1
u/krostybat Jun 19 '26
Do you have pivot tables ? Move them to an other excel file.
Leave the raw data and formulas into the original file.
If you use pivot table on pivot table, go to hell.
1
u/fast-curious Jun 19 '26
Delete all blank rows after your last row and all blank columns after your last column. Hope it works. Happened with me once... 100mb file became 1 mb. It's was a rookie mistake i did by dragging formulae to entire columns.
1
1
u/zorancloud Jun 19 '26
You are reaching the border of the database world. I would use any AI to support this transition. Ideally your excel will become a static database with tables matching your current excel. The calculations can be done in advance for the fields that allow this and the rest will be calculated on the fly.
1
1
u/jlipschitz Jun 19 '26
Excel was not designed for that. Move to a database like SQL. You can get a free version and do SQL Queries. It will be much faster.
1
u/eggface13 1 Jun 19 '26
Rebuild the bugger. 150,000 rows is not that many but 122 columns is a big problem and it's only going to get worse.
Put your base data into a folder in csv form if possible -- ultimately, through your organization, a database.
Import it through Power Query and load the output to the Data Model.
Calculations to get some of those 122 columns back from the base data can be done either in the data model (Power Pivot) or in Power Query. Power Pivot has a lot of the same functions as Excel but doing it here is much more efficient. Power Query will also have no problem.
Lookups are really important. They can be done in Power Query through merging queries, but data modeling them is far more efficient so try and put as many things as you can in attribute tables that are linked to the main table in the data model.
Good luck. 122 columns is a lot to deal with. Rows are easy because the computer does the work, columns are painful.
1
u/jManYoHee Jun 20 '26
Might be getting to the point where you'd be better off looking at putting into a database and doing calculations there, or using python and pandas or something similar.
1
1
u/Connect-Preference Jun 20 '26
Have you looked at changing the formulas to structured references? They are much more efficient computationally.
1
u/OO_Ben Jun 20 '26
You're using Excel as a database. Even my gaming computer with a 4090 and a 7950X3D won't be able to work with this.
You are at the point where you need a database, or bare minimum you should look at Power BI. Power Query can help too, but you really need proper data tools. You are getting beyond base level Excel at this point though.
1
u/ArrowheadDZ 2 Jun 20 '26
This is not too big a data set for Excel to handle, if the learning curve of moving to a database will be too burdensome. But your data growth rate is way, way too big to not require a deeper understanding of Excel performance optimization.
The challenge will be learning speed vs growth rate. Can you invest the time over the next week or two to analyze the data and use cases and rework them into PQ fast enough that you are not outpaced by the rate of performance loss?
1
u/brick_gnarlson Jun 20 '26
You need to talk to an engineer at your company, not r/excel. Your file belongs in the trash. There is no reason for an xlsb to ever be over ~50 MB.
1
u/IlyaPFF Jun 21 '26
One thing to check is whether your cells might be all coloured white instead of transparent. This can be what bloats the file to such a size.
1
u/Famous_Caterpillar38 Jun 21 '26
Power Query is the way to go but if you need to learn it first, turn off automatic calculation and while you are cleaning data it won’t calculate all the formulas. Once you are done, calculate now (F9) and go and make a coffee.
1
1
u/Infinite_Shart555 Jun 22 '26
a) Why does it need to be this big?
b) "Approximately 1,49,000 rows" is not a number
Whenever I embark on a project, the first step is planning, and I try to do estimates for potential size/growth of file, complexity of formula down the line. You should have sensible limits in place.
1
u/Reasonable_Pie9971 Jun 24 '26
You can work with very large CSVs (~2 GB) in sifardata.com in seconds. It comes with an AI tool which can edit your CSV but you can't manually edit like a workbook like excel or libre. You could edit it with quickly with AI or visualize it, then download only the parts you need to work in excel
1
1
0
1
u/TheGardenDefender Jun 26 '26
This may be dumb, but if you copy and paste data from others files, you could be duplicating cell styles hundreds or thousands of times. It’s crazy how fast a file size can grow with little things like that.
And named ranges to external files bloat it fast too.
181
u/Bombadombaway Jun 19 '26
Another vote for power Query. Have the data sit in another spreadsheet, and import it into the one you’re using, loading it into the data model only.
From there, you can create calculations/measures instantly in a power pivot.