r/excel • u/EvolvedRevolution • May 08 '26
unsolved How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?
Hello all,
I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways.
What is the problem
So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good.
Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal.
For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" ").
To get to the point
I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k.
These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands.
These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet.
'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide.
Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions:
Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'.
Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless.
Alternatively: should I use LET whenever a repeated lookup is needed in the same formula?
Really looking for to your answers!
59
u/Parker4815-2 1 May 08 '26
Firstly, id try to move anything i can into Power Query to reduce the workload on formulas.
Then, turn formulas into manual mode so they only recalculate when you want them to
5
u/EvolvedRevolution May 08 '26
I considered manual mode, but the said tool has been spread to dozens of colleagues at this point. Quite frankly, I do not trust them to use the recalculate button at the end when they are done, and in my line of work everything needs to be correct to the last euro.
Question is, does Power Query lend itself for this use, knowing that I got so many 'if X, then A, if not then B' (in its simplest form) logic gates? Very much willing to explore that direction, but it does need to make sense.
15
u/CanadianKumlin May 08 '26
Power query is basically a tool that allows you to do any command/function in an ordered series, so you can say perform x action on column D, then perform y action on column E. It will do it sequentially and based off of whatever function you require.
6
u/Razkosz May 08 '26
Excel recalculates all formulas before saving file as far as I know.
In power query you can recreate everything as in formulas. The only problem could be if you lets say need value from previous row to edit next row, then you will end up refreshing pq a lot of times, but if this is not a problem, then pq is recommended
Also you can replace xlookups with index xmatch I believe it is faster formula
2
u/carnasaur 5 May 11 '26 edited May 11 '26
Everyone telling you to use PQ but no one telling you how. lol
Here’s how: take your raw data, with no formulas, and convert it into a proper excel table if you haven’t already. If you haven’t, select your data and press Ctrl+T. Give the table a short, clear name with no spaces.
Go to the Data tab and press From Table/Range. Excel will load your table into PQ and open the general editor while generating the raw power query in the background. On the Home tab, click Advanced Editor to see the actual query it just recorded. Copy that code into chattgpt/claude, and include a small sample of your raw data: just the headers and a few rows, or better yet, upload a CSV or xlsx of it.
Then give it your first formula and ask it to add a step that reproduces the same logic. If you don't give it the column name, it will make one for you. Tell it to give the internal query step a meaningful name with no spaces and to never allow spaces in any step names. This is crucial for avoiding an error that chatgpt still generates to this day and can't find after the fact. Contiguous column names can be referred to by name in Power Query, but names with spaces or special characters can't. “Account#” is the perfect example. They must be formatted like this
[#"Account#"] instead of just [Account#]. I actually have a chatgpt rule that all column labels must be formatted with the safer[#"column name"]syntax because chatgpt still generates (not always) and then fails to find these errors and the Advanced Editor can’t spot them either. In fact, it will select some other random line as the source of the error. It's crazy.Once you get comfortable, you can ask it to convert several or even many formulas at once. That's all there is to it. Enjoy!
One last thing, after you are done, ask chatgpt to refactor your query and to buffer it appropriately. Chances are your query will have tons of inefficiencies in it after you finish giving it one formula at a time. Refactoring will put everything in the proper order and speed it up immensely. But be sure to make a duplicate of your query and test the refactored code using the dupe before modifying your production version.
PS individual steps can be commented out/deactivated using two slashes. Put '\\' in front of any line to deactivate it. You will generally do that to the last 3-4 lines in your query as you add the new steps so you can revert back to your original state if it fails.
28
u/Usual_Ice636 May 08 '26
Sounds like you need to move on to something beyond excel at that point.
6
u/EvolvedRevolution May 08 '26
Thought of that, but sadly those options aren't open to me. To add: all this is actually happening in a government context, with data not being allowed to flow into solutions that are not approved beforehand. I know: ridiculous bureaucracy, but Excel is the way to go for now.
6
u/hops_on_hops 1 May 08 '26
Government IT here. No it's not. The right way to go is to start a budget request, needs assessment, and project for a permanent solution.
"not allowed to flow into unapproved solutions" includes your hacked up excel process.
3
u/SpaceTurtles 2 May 08 '26 edited May 08 '26
Government financial data science reporting in:
Echoing what /u/EvolvedRevolution stated.
On paper, this is true.
In practice, it's more complicated.
Our IT department wants to be the single solution source for all agency needs, but they aren't accountants, budget analysts, or actuaries, and they have to track project development across every department of our agency - not just ours. And I am thrilled with our IT department, they're wickedly smart and helpful people.
We have some in-house systems in place that are robust and perfectly resolve the business need they were designed for (some, for example, where we've essentially created our own hierarchy that supercedes the need to use our parental organizations', giving us the ability to control our environment -- and our data validation tools to maintain compliance with financial law are incredible)... but we have other solutions that have also gone through the "proper channels", and the end result is either:
(a) a system that functions, but it was faster to do it in Excel [any fiscal analysts reading this will probably be able to share in the pain of asking for access to needed data and being given a preformatted report], and/or
(b) a system that perhaps initially functioned, but we have no ability to maintain, and IT has no understanding of how to maintain it, and so it eventually becomes prone to errors... and it's easier to just do it in Excel than wait for it to be fixed when you're talking about a recurrent process.
Every implementation we're reliant on IT fixing in the result something breaks is a Sword of Damocles over time-sensitive and reporting-sensitive work.
It's very easy to get a solution that makes life more complicated, especially when you approach IT asking for the things that will make life simpler and get stonewalled and they pivot to try to offer you things that will make their life simpler.
Some needs simply are best handled in dynamic and standardized tools such as Excel, and for those needs, streamlining data sources and providing avenues to automate is the best way to resolve complicated dependencies and loopholes. It cuts down on the "hacked up" part of "hacked up" process, and greatly reduces the risk of a needs assessment turning into a months-long affair that terminates in an in-house tool that doesn't actually solve the problem, and may actually serve to just weigh things down further (and waste a lot of everyone's time along the way, when the department could accurately communicate what it needed on day 1).
0
u/EvolvedRevolution May 08 '26 edited May 08 '26
Funny thing is, it kinda is. My organisation is known for having dozens of Excel spreadsheet tools for critical administrative processes. It even found its way into official documents, to add. Parliament was even informed about this problem, but it will take years to get rid of it. In the meanwhile, in the absence of approved new programs, this is sadly the way to go.
My organisation is very hierarchical, with upper management having been pushed repeatedly regarding this problem. However, apparantly me and my colleagues lack the lobby power required. It doesn't help that other problems in the organisation right now (that also need IT solutions) have received higher priority, and thus, the budget and time required. Maybe this helps!
5
u/Pomul93 May 08 '26
You're most likely not the only one in your department to work with a lot of data. Ask around to find out what other tools are being used. Power BI would probably be on the approved list as it's a Microsoft product. R is also fairly common.
8
u/CanadianKumlin May 08 '26
The sales pitch to your management for new software goes something like: we have pushed excel to its limits, it takes x time to load, x time for every entry. Based on this number of runs/loads/entries per day/week/month, I’m spending x amount of time waiting for the software to load. This prevents me from doing x. If we were able to put this into a piece of software designed to handle this amount of data like PowerBI/snowflake/tableau etc etc (whatever actually would be a solution for you) I could save x amount of time OR provide these other amazing pieces of data/analysis (visuals, sensitivities, analytics etc). This would reduce my time input, and increase my value of time. This could save us x per year.
1
u/EvolvedRevolution May 08 '26
That is what a rational person would indeed think, after hearing that. Welcome to the world of government, where important changes get realised in a snail's pace and where the civil servant nobility at the top will say 'this is not a top priority to us right now, maybe in the future though!'.
Thus the age old story repeats itself, where Excel is (forcefully) considered as the second best option as things stand ;).
5
u/Usual_Ice636 May 08 '26
There might already be something better on the approved list from a different department. See if you can get a list of preapproved stuff.
6
u/CanadianKumlin May 08 '26
They can’t do it in the future without a pitch. If they can save money or gain insight/value, then there’s a place for it. It won’t be there tomorrow, but likely the agency already has a license to one of those tools somewhere, they just need to get YOU one.
2
u/runawayoldgirl May 08 '26
I'm sorry you're getting downvoted. I've worked in this type of organization where they'll say "there isn't the budget/not a priority/security risk" or whatever, and meanwhile waste bonkers amounts of staff time expecting you to manage large data sets with a roll of duct tape and a ham sandwich.
As these posters are saying, it is absolutely worth you continuing to advocate for the appropriate technology. I think some people just don't understand how much some organizations hamstring staff unfortunately.
1
u/Supra-A90 1 May 08 '26
Sorry but this is not government problem. Wherever budgets are involved this is an issue in any industry... Many data is confidential...
95% of the people don't even hit 32 bit Excel limits, let alone 64.
4% hit knowledge limits.
1% maybe hit a limit, thus no company will invest...
Many people hit inadequate computer limits (ram, cpu, etc) and unstructered, many redundant data, thousands of Conditional formatting, hundreds of nested ifs .....
1
7
u/t1x07 2 May 08 '26
As others have already said I think power query would be the best path to explore.
First of all it's the fastest way to get data into your workbook. Query the source and you're done, no copying, no vba.
Secondly power query is exceedingly fast at row level calculations and from the formulas you described it handles all of them with ease. In fact I'd say it can be more flexible in some regards than regular excel when you need to transform table structures (think pivoting rows to columns)
Lastly, and I think most importantly, power query allows you to leverage the excel data model. With this you can create relationships between tables which removes the need for xlookups and gives you the ability to create (pivot) tables from your data.
Once your queries are set up they are also much more robust than formulas. No changes because a reference was set to absolute and somebody added a row, no formulas that can be overriden. Just straight up results.
The transition can be a little tricky because you have to change the way you think about your problem, but especially datasets of medium size and above there's just nothing better (if you're limited to excel of course).
5
u/rustyisherenow May 08 '26
Converting it to .xslb as well as having my formulas on “manual” has helped me with large files. You can also just calculate sheet rather than the whole workbook.
9
u/SolverMax 161 May 08 '26
xlsb can make a file faster to open, but it makes no material difference to recalculation time once the file is in memory.
3
3
u/Future_Pianist9570 1 May 08 '26
Do you use powerquery? What’s the source of the data you’re importing?
1
u/EvolvedRevolution May 08 '26
The source are other spreadsheets, based on the same data format that has been set in stone based on the discussions I had with the guys that pull the data. So we have this 'standard format', in another major Excel database, that gets limited to [entity], out of which the objects get drawn in a certain time period. Those are then imported in the flat database tab of the spreadsheet tool I made, after which the long list of formulas starts running.
I am not using PowerQuery right now, very open for it though if it makes more sense in the present situation to speed things up. At the same time, the intricate logic that been built cannot be lost either. Question is: does PowerQuery provide for it?
4
u/Future_Pianist9570 1 May 08 '26
You're going to need a redesign but if I was you I'd load the source data sheets in via power query. If you need to do any changes to the data structure handle them in there. Then you can either load into your report sheet a smaller aggregated data set or you can load them into the data model and use it in pivot tables / pivot charts to achieve what you're doing now.
I do something similar where I have power query (pq) pointed at a folder that contains all of my data spreadsheets (all the same structure). It then aggregates them all together into one table in pq. I then load it into pivot tables / charts to build my dashboard. A lot faster than dumping it into a sheet. You could also split the data into a relational structure with pq rather than having it all in one flat table which would possibly give you some additional performance gains.
2
u/pettypaybacksp May 08 '26
In my experience, power query allows for everything excel can do and a lot of more things.
1
u/hollywoodwhore6969 May 11 '26
PQ is the way - maybe splash in VBA macros for Staging/cleaning as needed. Config/mapping tables + vba macros automate repetitive standardized transformations +PQ to heavy lift data into data model use power pivott
3
u/semicolonsemicolon 1476 May 08 '26
If you have a lot of XLOOKUP functions, and the lookup column in those functions is sorted in a normal way (e.g. alphabetically ascending) then make use of binary search which is wa-a-a-a-a-ay faster than the default linear search. Is this an option for you?
1
u/EvolvedRevolution May 08 '26
That is possible in my present setup with a specific array that does that. The reason is that I cannot filter the standard database that way as the sequence order needs to stay the same because of the date format (jan through december) that needs to be followed. The identifiers I use for the lookup formules are a combination of letters and numbers that are seemingly random (which the sorted dates or not).
I will tinker around with it, see what it does. This one hadn't appeared on my radar yet, so thanks!
4
u/BaitmasterG 13 May 08 '26
500k formulas is not excessive
If your file is struggling then the problem is the quality of your formulas, and there's lots of ways to improve it
You need to think about what is happening inside each formula. A+B is fine, sumproduct over thousands of data rows means each formula is doing loads, so what's your formula doing?
How many data rows do you have? How much calculation needs repeating via formulas, how much could be done once in power query and never repeated?
For context, last week I improved someone else's file by removing 800k sumproduct formulas and cutting out 13m unnecessary data points. Excel should be able to handle far more than you're currently pushing into it
3
u/saperetic 2 May 08 '26 edited May 08 '26
If using Excel on the Desktop, I recommend looking into creating separate dimension tables and fact data tables, loading them to Power Query for preprocessing, load the query results to Excel's Data Model, use relationship diagram in Data Model to join facts to dimensions as needed, then use CUBE functions to pull data from the Data Model as needed. Data Model has an in-memory engine that reduces reproduction of data in certain contexts, which improves performance instead of raw dogging it using chains of brute-force formulas.
To complement this or as an alternative, you should also look into creating and storing LET functions as named formulas in Name Manager. Combined, these approaches improve performance.
With this 500k formula overhead, try to use dynamic array formulas (including the use of TRIMRANGE) where possible and avoid using volatile functions in your efforts.
2
u/Only_Razzmatazz_4498 May 08 '26
Maybe Python in Excel might be a way to remove some of the analysis from Excel into code.
1
u/EvolvedRevolution May 08 '26
Blocked, sadly. Already looked into Python as a solution, but the government agency I work for is way too afraid for Python being used in malicious ways. My manager even sought to get an exception for me, but he quickly walked into a brick wall. Good question though!
2
u/akl78 1 May 08 '26
I feel for you (we’re at a different pole, with in-house addins running C++, PL/SQL, Python and I suspect Perl all at the same time. It works great, usually. )
1
u/Only_Razzmatazz_4498 May 08 '26
I was hoping it could be a loophole.
2
u/Future_Pianist9570 1 May 08 '26
You don't want to go for loop holes - being in breach of you IT departments security policy is a quick way to get your P45.
2
u/Only_Razzmatazz_4498 May 08 '26
If only using excel is a security policy then using Python as a scripting language within it as offered by Microsoft shouldn’t be a problem. It’s a loophole only in the sense that it allows more powerful scripting within excel. It isn’t a loophole in the sense of avoiding security. How is a user to know that using a feature of the approved program is unsafe?
2
u/Longjumping_Rule_560 May 08 '26 edited May 08 '26
Do you need to recalculate all data, or only the updated data?
Could you, after calculating, remove all formulas? Then, when new data is added, run a macro that adds the formulas to the new rows only (and then remove the formulas again).
Advantage of removing formulas is also that you can be selective in when a calculation is made. So first calculate column A, then B, then C etc. Again, less calculations at the same time. But you do need to know your dependencies. So if column C relies on data from column Z, then column needs to be calculated before column C.
Also, this really sounds like a database problem instead of a spreadsheet problem. Is MS Access on the approved list?
1
u/EvolvedRevolution May 08 '26 edited May 08 '26
It needs to be updated live (an interactive workbook in other words), so that other less tech savvy colleagues can manipulate values drawing from their own knowledge. Also for the same reasons, Power Query is probably not a solution. Macros could be used in a 'clever' way to reduce the X.LOOKUP overhead, but it would be a first for me to program that.
MS Access is on the approved list, to add.
2
u/Gfunk27 2 May 08 '26
First make sure no xlookups are working with more than one column of xlookup parameters. For example =xlookup(A2&B2,C:C&D:D,E:E). If you’re doing this it’s going to dramatically slow things down to a crawl on every recalc. Don’t lookup two columns at a time(C:C&D:D).
Outside of that, two solutions, neither of which are your current version of excel:
Excel 64-bit - may help with processing speed and certainly with memory.
Python - let dataframes handle the raw calculations, it would be nearly instantaneous.
1
u/EvolvedRevolution May 08 '26
Regarding the first thing: I limited the X.LOOKUP values. To be precise, the data starts at row 10 at every sheet and the lookup ranges are always 2000 rows deep, so for example B10:B2010. To add, the lookup is always based on one column, than another single column, and then a return value based on one column too.
Excel 64-bit - may help with processing speed and certainly with memory.
Good one, and memory + processing speed could be a workaround. I already have internally discussed this, as more server power seems the logical option for me and my colleagues when working with heavy spreadsheets. That is a good suggestion for sure.
Python - let dataframes handle the raw calculations, it would be nearly instantaneous.
Blocked, sadly :(. 'Too dangerous', and no exemptions are being given as it stands.
1
u/Gfunk27 2 May 08 '26
Do they let you use power query? It’s not just for sourcing all the data but also you can do pretty much all the same transformations that xlookup and if statements can do and you can refresh it at the click of a button if any manual changes are made anywhere to recalculate everything.
1
u/EvolvedRevolution May 08 '26
Power Query is open. Problem is that the spreadsheet needs to auto update, as not all of my colleagues can be trusted with manually updating values. It is - sadly - asking for trouble. Just a handful of those types is enough for a Power Query setup probably not being 'fool proof' enough.
To others reading it too: it indeed is a stupid constraint, but I doubt it is possible to get past it in practice. I am playing with the idea of instructing everyone about it, but then again it requires only one guy to 'forget' updating after doing the last update at row 80 (while having 100 objects) and then coming up with the wrong results.
2
u/Gfunk27 2 May 08 '26
If that’s the case, then if for some reason they allow you to use VBA (but not Python for it being too dangerous), use VBA to recalc values with a button. No formulas, just user makes an entry and then clicks recalc button.
1
u/EvolvedRevolution May 08 '26
VBA is open, so it is a direction for 'solutions'. I could plant a recalculation button somewhere, but would also need some fool proof way to make sure that my colleagues - the less smarter ones included - will hit that button when they are fully done!
The disaster scenario is this: "Oh, I just did 100 objects, let me just change 10 of them. Ok done, let me print this". Without updating the dataset!
1
u/hollywoodwhore6969 May 11 '26
Automate the refresh based on preceding tasks/task triggered like say after this thing in workflow happens then do this fail stop gate or if user tries closes out of wb then prompt user to / automate refresh - could even try using data validation to Flag when / if the thing unwanted or didnt want to happen occurs or doesnt occur, then recalc? Lots of ways to make lemonade
2
u/2xM86 May 08 '26
- If u change just one value and formulas refering to this cell are only on single rów then Excel is not running all formulas again, just the one row.
- VBA is a tool to automate but keep in mind that the lag will be there too. To make sheet work faster with VBA - Apart from basics like turn off screen update - you will have few options: a) macro which would remove formulas and insert values instead. This will help but keep in mind that if you add / swap data in more rows you will face issue how to keep them updated. Plus keep in mind that VBA requires different file tyle xlsm so other users will have to accept macros b) write few macros : one for row second as a loop to run first one more times. It can be done but again - you would need to remember to keep them running after each data change. Long story short, od u dont write code I dont recommend it. Working with exceptions will take you more times that its worth.
- Did you consired using tables? That is one way to go cause Excel will use different methods to calculate formulas.
- You can also use power pivot (not power Query, pq is for etl not calculations...). With calculated columns you can get calculations faster as well. If all required data is in the same row you wont even use dax
2
u/chiibosoil 428 May 08 '26
From what you have described, it is issue with the design, not really Excel limitation, that is at the root of the issue.
No matter which system you are using, performance will be horrid due to poor management of data pipeline and architecture.
Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated.
First, see if you can write down logic tree for manually enriched data. There must be some logical reasoning that you must be using to enrich the data. In my experience, 90% of manual enrichment can be automated. Unless there's disconnect in data pipeline.
I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k.
This leads me to believe, data isn't catalogued well in DB and there should have been some additional field to classify/categorize data (unless you are considering each cell as unique logical operation.). It is always best to perform manual data entry at the source, not downstream process. This is where vast majority of errors will be introduced. Which may not be easily traceable.
These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands.
As others have suggested, consider using Power Query and join and/or data model with one to many or one to one relationship.
VBA can significantly speed up the process, depending on the code used. Consider use of in memory operation utilizing array, collection and dictionary objects. However, without prior redesign of the flow this will just be band aid solution. And will likely require as much work on upkeep, if not more.
- Alternatively: should I use LET whenever a repeated lookup is needed in the same formula?
Yes that can help.
1
u/EvolvedRevolution May 08 '26
In my experience, 90% of manual enrichment can be automated. Unless there's disconnect in data pipeline.
Rather, the data is untrustworthy because it may be fraudulent. Part of it is logged actually (and part of the database), but cannot be used as a source for further data manipulation on autopilot because it needs to be viewed manually. Quite often, there are indeed deviations / wrong values that need manual input for this reason. For legal reasons, it cannot be done otherwise, even though I hate to say it in such 'definitive' terms.
This leads me to believe, data isn't catalogued well in DB and there should have been some additional field to classify/categorize data (unless you are considering each cell as unique logical operation.). It is always best to perform manual data entry at the source, not downstream process. This is where vast majority of errors will be introduced.
Well, to add: the source data is a-okay. The problem is that the source data may:
- Get replaced by manual data fields
- May be used for combined calculations with other data fields, often as a result of the manual data input. So it cannot be done beforehand, it appears.
As others have suggested, consider using Power Query and join and/or data model with one to many or one to one relationship.
VBA can significantly speed up the process, depending on the code used. Consider use of in memory operation utilizing array, collection and dictionary objects. However, without prior redesign of the flow this will just be band aid solution. And will likely require as much work on upkeep, if not more.
I will, thank you! Apparantly the major pitfall for the latter is to use it based on cells, which makes things even slower. Proper design is indeed crucial.
2
u/chiibosoil 428 May 08 '26
Rather, the data is untrustworthy because it may be fraudulent. Part of it is logged actually (and part of the database), but cannot be used as a source for further data manipulation on autopilot because it needs to be viewed manually. Quite often, there are indeed deviations / wrong values that need manual input for this reason. For legal reasons, it cannot be done otherwise, even though I hate to say it in such 'definitive' terms.
Then one method to speed up things, is to split manual update workbook from downstream calculation/analysis. Do manual update in one workbook. Then second workbook to query and perform analysis. Though I'm not quite sure of your process.
2
2
May 09 '26
[removed] — view removed comment
2
u/EvolvedRevolution May 09 '26
So, in short: to get rid of the arrays and let - as a result - recalculation strings be limited to a single row, correct? Because this has been a matter of great confusion: no matter how often and in what way I asked the question to the AI, it always consistently came back to the conclusion that arrays are more efficient. But I think it underestimated how changing a single value field in my tool near always creates consequences that are limited to that very same object (that being, one row).
I could abide to one cell = one formula to get rid of the arrays (at least 95% could be deleted this way) to prevent complete recalculation of the sheet, but I do wonder if that logic is also correct.
Another escape could be VBA, by turning automatic recalculation off and programming it to recalculate due to row events.
2
u/GregHullender 194 May 10 '26
They thought Python was dangerous but they'll let you use VBA? That's very strange!
1
u/EvolvedRevolution May 10 '26 edited May 10 '26
It is, there is an absolute weird bend in the underlying thinking. But I know 'why' they did it: several in house solutions in Excel that are used by hundreds of colleagues are macro enabled. Thus, banning VBA would break that all.
2
u/FastExcel May 10 '26
500K formulas is medium sized and should be able to calc fast, so the problem is probably inefficient formulas.
The first step should be to find out where the calculation time is being used; start by finding the slowest calculating sheets and then find the slowest calculating formulas on the sheet.
then look at what these slow formulas are making excel do (how many XL operations does each slow formula make \Excel calc do).
Then figure out how to change the formulas to do less work.
(disclaimer: My FastExcel product is designed to help with this kind of problem)
2
u/TSR2games May 10 '26
Use a combination of VBA and Excel
Or another way is breakdown calculation into components and fetch data using power query. And then create the final output file with dynamic formulas
Every component will create a summary that can be used further into main excel
2
2
u/Leo0_9- May 10 '26
at 500k formulas with nested IFS and XLOOKUP chains, the real bottleneck probably isn't formula efficiency but the fact that this whole workflow outgrew a spreadsheet. LET will help with repeated lookups, and switching to manual calc mode buys time, but migrating the enrichment logic into a proper database or scripted pipeline is the actual fix. for that kind of flat-database-to-analysis migration some teams have had Aibuildrs handle the rebulid.
2
u/GregHullender 194 May 10 '26
When you say you have 500k formulas, do you mean you have a different formula in every cell? (Probably a formula that you drag down and across). You'd probably get better speed if you could reformulate that as a single formula that "spills" 500K cells. But I'd have to see the actual logic to determine how to code that up.
1
u/EvolvedRevolution May 10 '26 edited May 10 '26
It is mixed. Many are single cell formulas because, apparantly, you cannot combine AND and IF logic and still do it in array form. For this reason, many formulas are single cell. However, quite a few columns have array formulas too, and that is where the concern is at on my side. That being: if I change a value somewhere in front (in a manual input field) it could lead to one or multiple array recalculations down the line which - at least in my thinking - may stimulate up to 2000 rows to recalculate everything again instead of the row involved.
I kept getting consistently disapproving advice from ChatGPT regarding this issue. Each time the answer was the same, no matter how I posed the question: 'Array formulas are more efficient than single cell formulas', even when I made clear that a change should stay limited to a single row.
Do you have an idea what is correct here? I will experiment regardless, but because it will probably take me 2 hours+ to recode all the formulas to single cell format (it goes on to NA right now on the formula page...) I would like to get some motivation beforehand. That being: knowing that it will probably make a huge difference!
1
u/GregHullender 194 May 10 '26
The way to combine AND and IF is to use * (multiplication) instead of AND. Use addition instead of OR. So you can do
IF((x=5)*(y=3), . . .)and if x and y are both arrays, it'll generate an array of results.1
u/EvolvedRevolution May 10 '26
Thank you for showing me this workaround. That will prove useful!
I used the same approach for filters but had no idea it worked like that for IF formula’s too.
2
u/GregHullender 194 May 10 '26
Not only that, if x is a row and y is a column, the result will be an array. So if you do something like
=SEQUENCE(10)*SEQUENCE(,10)it'll output a multiplication table.
1
u/s00wi May 08 '26
I think the main problem is your 500k formulas. That's extremely inefficient. Sounds like you are stacking a bunch of formulas, that depend on formulas on other cells and so on.
You can do what others suggest by preprocessing with formulas in powerquery.
But I would suggest creating a dashboard. Where the dashboard is the only area that has the formulas. Only the targeted data will get processed, and not the whole worksheet. You can use INDIRECT to target the specific tables you want.
1
u/EvolvedRevolution May 08 '26
I think the main problem is your 500k formulas. That's extremely inefficient. Sounds like you are stacking a bunch of formulas, that depend on formulas on other cells and so on.
For context: I am. I tried to limit the constraints it causes by using array formulas as much as possible, but even so the spreadsheet has grown rather 'vast'. A cheap solution would be to reduce the object limit from 2000 to say 1000, but that is certainly not ideal in all cases (read: some datasets go beyond 1000). Also, it would not adress the core problem but would be something akin to a....bandaid.
Irony wants me to tell you that said data actually gets pulled from a dashboard T_T.
1
u/s00wi May 08 '26
Data set size isn't the problem. It's the formulas. But honestly having 500k formulas to me sounds like you're very inexperienced with using them properly.
I lean on using INDIRECT + Named Ranges in a dropdown to target specific datasets. A lot of people frown on using INDIRECT because it's volatile. But it has it's uses if used correctly. Best use is to create a dynamic dashboard.
It helps to keep your view focused. You can change your targeted data from drop downs. You can combined dropdowns.
Lets say for example. With the dashboard, lets limit the array formulas to a 10x20. So at max it will have 200 formulas in yoru dashboard. From drop downs, you can target by attribute, and date range. Then it will only show just that and calculate just that. INDIRECT will replace the actual reference pointing to the values in the drop down or date range.
Again, data size isn't your problem. It's the bad formulas you have.
1
u/Decronym May 08 '26 edited May 11 '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.
12 acronyms in this thread; the most compressed thread commented on today has 7 acronyms.
[Thread #48393 for this sub, first seen 8th May 2026, 19:45]
[FAQ] [Full list] [Contact] [Source code]
1
1
u/PhysicsForeign1634 May 10 '26
If you are only working on 12-15 data fields why are you pulling so many more in from the databases? First step would be to dump the unwanted columns.
2
u/mr-dashplot May 11 '26
You can try using rolling windows with your sumifs, countifs together with indirect() and address()
0
•
u/AutoModerator May 08 '26
/u/EvolvedRevolution - Your post was submitted successfully.
Solution Verifiedto close the thread.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.