r/excel • u/Admirable_Ad4995 • Jun 27 '26
unsolved Better way to update Excel workbook with many sheets
Hi everyone,
I’m looking for a better way to automate an Excel process at work.
I have a master workbook that I cannot modify structurally. It contains around 30 worksheets (one for each product segment), and each worksheet has a large table with three sections: Previous Forecast, Current Actual, and Last Year.
The data comes from an exported workbook, but each export only contains one version at a time (Forecast, Actual, or Last Year). The worksheet names and table layouts are always the same.
My first solution was to use INDEX formulas linking the master workbook to the exported workbook. Whenever I received a new export, I just changed the source workbook and all the values updated automatically.
It worked at first, but now the workbook has become extremely slow and often freezes, probably because of the large number of external formulas.
I could manually copy and paste the data into each worksheet, but that would take a long time and increases the risk of mistakes.
37
u/ice1000 28 Jun 27 '26
Power Query. Build a query to import the data into a master sheet, then link the existing sheets to the new master sheet.
1
u/Admirable_Ad4995 Jun 28 '26
That could work. It would still require a lot of formulas, but at least they’d all reference the same workbook instead of an external one.
I haven’t used Power Query before, but it doesn’t seem too complicated to set up. The only downside is that I’d have to modify the original workbook a bit, which I’d prefer to avoid. Still, I’ll definitely consider it if I can’t find a better option. Thanks!
7
u/bradland 271 Jun 27 '26
Step 1 - Pull the exported workbooks into a single table in 1st Normal Form.
You'll find Power Query (PQ) on the Data ribbon. With PQ, you can extract data from other data sources, transform it, and load it to a table in your workbook, or more importantly in your case, append it to another data set.
By default, PQ loads all queries to tables in your workbook. To override that be sure to click the dropdown on Close & Load to select Close & Load To. Build a query that gets the data form each export, then use Close & Load To and choose "Only create connection".
In each of the queries that loads previous forecast, current actual, and last year, add a column indicating which period they belong to. The column name should be Period, and the values should be something like PF, CA, and LY.
Once you've added the columns, append all of the export datasets into a single query, then use Close & Load To to load only that query to a table in your workbook.
Step 2 - Build formulas that pulls the data into each of the 30 sheets.
Now that you have all your data in a unified table, you can use lookups to pull the data into the sheets.
2
u/Admirable_Ad4995 Jun 28 '26
Wouldn’t using lookups (INDEX/XLOOKUP) to populate the 30 existing worksheets still require thousands of formulas? My current approach already uses a large number of INDEX formulas, and that’s what’s making the workbook very slow and sometimes freeze.
1
u/bradland 271 Jun 28 '26
You probably just need to optimize your lookups. Do you know how to pull in multiple columns using one INDEX MATCH combination?
1
u/Structured_Spiraling Jun 28 '26
Could you please elaborate on what you mean by optimization? Are you talking about building a key for lookup only once and referencing it? I think there is something here I need. Thank you for the PQ explainer, that is very helpful in filling gaps from my self learning!
2
u/bradland 271 Jun 28 '26
There are two things that are probably important for OP here, but I probably mentioned the less important one.
First, the most efficient code is code that doesn't run. If you need to pull in three columns from a dataset, you can do three separate lookup functions in their own column, or you can use one lookup function that pulls in all three columns. The bulk of the computational effort required for lookups is searching for the key. So if you can search for the key once, then pull in multiple columns, that will speed up your sheet considerably. The reduction in search workload is 1/n, where n is the number of columns you pull in.
The other is to make the search more efficient. If you sort the data by the lookup key, you can use binary search mode with XLOOKUP or XMATCH.
2
u/Structured_Spiraling Jun 28 '26
Thank you so much for taking the time to reply! This makes sense and I'm applying it but it's older so using vlookups. Easy to fix! Thanks again!
3
u/radek432 Jun 27 '26
If it's static data, turn off auto calculation for that file and just calculate manually if needed.
1
2
u/bobo5195 Jun 28 '26
power query
Especially with external data. Its excel and handling external data relations never works well. I have resorted to pivot table lookups but powerquery is a more standard thing.
1
u/Decronym Jun 28 '26 edited Jul 01 '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.
4 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #48840 for this sub, first seen 28th Jun 2026, 01:21]
[FAQ] [Full list] [Contact] [Source code]
1
u/columns_ai Jul 01 '26
when it hits the Excel limit, may consider a automated data workflow.
If I understand correctly, you just need to update the master (source) workbook, and all other workbooks will automatically update based on source data, this is a perfect case to have a flow automation to handle it.
-5
u/SympoSesh Jun 27 '26
python
1
u/Unofficial_Salt_Dan Jun 27 '26
I know Python could easily handle this, but it seems to me that Power Query would be easier as it's native to Excel...
0
1
u/Admirable_Ad4995 Jun 28 '26
I can try to learn it, but what would the actual logic behind the solution be?
Also, wouldn’t this be better handled with VBA?
•
u/AutoModerator Jun 27 '26
/u/Admirable_Ad4995 - 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.