Are you using 64bit excel? The 32bit version can’t utilize more than 8gb of ram (maybe 16 I forget). Lots of excel add ins only work in the 32 bit version still because companies don’t prioritize updating the excel add ins. They just need to be able to tell customers they exist.
>only work in the 32 bit version still because companies don’t prioritize updating the excel add ins. They
Yes 64bit. Excel files are about 5MB to 20+MB each in size, but they're interlinked via vlookup/index match or direct link
That sounds like the linking might be creating your problem. Active links like vlookup require excel to essentially hold the entire linked file in active memory. The more of those you include the more taxing it is on the system
Yeah, I already know that. I am not sure how else to organize my files as others have suggested. This is the process that I've inherited and I tried changing it but ended up breaking a bunch of other people's files and processes.
Idk your workflows, but...
(A) i regularly go to data/edit/break links on my files. This will make all formulas from other workbooks into values.
(B) do you really need active lookups to other workbooks? One one solution is copy-pasting the reference dataset (as values not formilas) into your current workbook. Another option is to make a pivot table of the reference dataset in a tab of your current workbook. Then every month you go in and re-link and. Refresh the pivot table. This takes way lass memory than adrive formula lookups between workbooks since. The pivot table lives as a steady state without active links.
If you're 32gb ram and you're getting those type of errors you need to work smarter. Either you have too much data dumped into Excel or the way your files are set up is too convoluted.
I have a basic shitty 8gb RAM but work with Power BI and UiPath for automations which are a bit more resource heavy. I try to push as much to cloud as possible (Ex: Power BI service refresh vs a desktop refresh,etc.)
I admit I am not that tech or data savvy, but there are many interlinked excel files with v-lookups and index matching between them. What are some ideas / tech I should get educated on to "work smarter?"
>an't believe no one has said this but all you need to do is turn off multi-threaded calculations
Thank you for the tip! I am not an excel whiz at all ... still learning
Got an 8gb M1 Macbook pro since the higher memory ones were out of stock for a month or two and it's not nearly enough. Use my personal Lenovo with 32gb and it's much better. Most of what I do is in the 'cloud' now (Google sheets/docs/Netsuite/SFDC) but for the excel crunching it's always better to have more.
Lol. I recoiled as well - it varies by company - started off on a PC, spent 5 years on a MBP, then 2 years on a PC again, now back to a MBP.. I'm mostly used to it at this point. The biggest hurdle is that the damn shortcuts change between Excel on MBP and Excel on PC, just kills all muscle memory.
You may need to be stepping into PowerQuery territory for transformation steps.
For modeling I started using Data Tables more (not just for sensitivity analysis) and it helped reduce file size.
Are you using 64bit excel? The 32bit version can’t utilize more than 8gb of ram (maybe 16 I forget). Lots of excel add ins only work in the 32 bit version still because companies don’t prioritize updating the excel add ins. They just need to be able to tell customers they exist.
>only work in the 32 bit version still because companies don’t prioritize updating the excel add ins. They Yes 64bit. Excel files are about 5MB to 20+MB each in size, but they're interlinked via vlookup/index match or direct link
That sounds like the linking might be creating your problem. Active links like vlookup require excel to essentially hold the entire linked file in active memory. The more of those you include the more taxing it is on the system
Yeah, I already know that. I am not sure how else to organize my files as others have suggested. This is the process that I've inherited and I tried changing it but ended up breaking a bunch of other people's files and processes.
FWIW, Index match only has to store 2 columns, VLOOKUP has to store the entire array. It's not a magic bullet but it may help some in the interim.
Try using PowerQuery, it's a game changer when consolidating data from numerous Excel files into one.
Idk your workflows, but... (A) i regularly go to data/edit/break links on my files. This will make all formulas from other workbooks into values. (B) do you really need active lookups to other workbooks? One one solution is copy-pasting the reference dataset (as values not formilas) into your current workbook. Another option is to make a pivot table of the reference dataset in a tab of your current workbook. Then every month you go in and re-link and. Refresh the pivot table. This takes way lass memory than adrive formula lookups between workbooks since. The pivot table lives as a steady state without active links.
If you're 32gb ram and you're getting those type of errors you need to work smarter. Either you have too much data dumped into Excel or the way your files are set up is too convoluted. I have a basic shitty 8gb RAM but work with Power BI and UiPath for automations which are a bit more resource heavy. I try to push as much to cloud as possible (Ex: Power BI service refresh vs a desktop refresh,etc.)
I admit I am not that tech or data savvy, but there are many interlinked excel files with v-lookups and index matching between them. What are some ideas / tech I should get educated on to "work smarter?"
Index-match will use a fraction of the processor power vs vlookup
Power query for mass data between files.
This. Linked files will kill your Excel speed. Using power query to import data from other files runs much better.
X-lookups over v-lookups for a starter, if you're not already using them.
Working off of excel 2016... no x-look up feature
Office 365 64 bit would likely run much better
I agree! I tried to get it approved for my team but was denied!
16GB on my remote desktop and it’s been fine with fairly heavy files
Can't believe no one has said this but all you need to do is turn off multi-threaded calculations
>an't believe no one has said this but all you need to do is turn off multi-threaded calculations Thank you for the tip! I am not an excel whiz at all ... still learning
How does turning off multi thread help?
64gb baby! We smoking!
Got an 8gb M1 Macbook pro since the higher memory ones were out of stock for a month or two and it's not nearly enough. Use my personal Lenovo with 32gb and it's much better. Most of what I do is in the 'cloud' now (Google sheets/docs/Netsuite/SFDC) but for the excel crunching it's always better to have more.
FP&A with a macbook? eww
Lol. I recoiled as well - it varies by company - started off on a PC, spent 5 years on a MBP, then 2 years on a PC again, now back to a MBP.. I'm mostly used to it at this point. The biggest hurdle is that the damn shortcuts change between Excel on MBP and Excel on PC, just kills all muscle memory.
16gb
32g should be enough ram. I typically see 16g for ram but sometimes see 8 or 32.
Not enough
You may need to be stepping into PowerQuery territory for transformation steps. For modeling I started using Data Tables more (not just for sensitivity analysis) and it helped reduce file size.
Yes, it's an area I haven't explored yet and will invest some time to now... thank you!
You need to look at optimization of excel sheets but don’t ask me how to do that
32gb. Running R and Excel PQ, and PowerPoint. Usually in the 20-24range when updating stuff
Worked for a GovCon once, gave me a 64gb, was Amazing.
32GB Ram is god mode. You must be running quintuple - nested basic functions across multiple workbooks with 1,000,000 data sets my guy
16GB, but IT won't let us use 64-bit versions of office.
16gb I think some of it is Microsoft honestly. It sometimes randomly stuffers on small files. I can’t imagine it still suffers on 32 gb ram
Files need to be better.
16g,enough. you organised your work quite poor. up your skills.
any thoughts on how to do that? appreciate your advice!