Using Excel with really big workbooks

[TomCon]

I have 3 workbooks open, 2 are 150MB and one is about 50MB. Have Calculation set to Manual. My machine has 32GB RAM and is less than a year old, not sure which CPU chip but i'm sure its "new and modern".

Its a painful experience to use Excel. [Most] times i do something as innocuous as use the arrow key to move a cell up/down/left/right or the mouse to select a new cell, Excel goes into "not responding" for about 15-30 seconds. Its a really painful experience to get much done. Quitting and restarting Excel helps...for a little while...until the behavior returns.

Yes, i do need all of these to be open at once. Is this a usual experience with big workbooks? Any suggestions?

Thanks!

 

[Jeffrey Mahoney]

150mb is very large. You might want to find a way to summarize some of your data prior to final product, then import the summary data. Yes, the WB will bog down.

How many cells do you have that have data in them? How many cells are "Used" (formatted or non-empty): To find the approximate Used cells, put your cursor in cell A1 and press Ctrl-Shift-End for every sheet; multiply the number of columns by the number of rows. How much Conditional Formatting (CF) do you use? What percentage of the used cells are formulas?

Some people have a habit of formatting entire rows and columns. This can increase a file size quickly.

I have a workbook that has over 250,000 used cells. CF usage is moderate, 80% percent of those cells are formulas. File size 3.7mb

When I had large workbooks with tables of calculations, I would temporarily value the data. What I did was insert a row at the top of of the table and copy one row of formulas into that row to save the formulas. I then value the full data set. When I need to update the data, I simply copy the formula row down and value them again. Macros can do this automatically.

If you have supporting data that doesn't need to look pretty, I suggest removing all the special formatting.

 

[Joe4]

Are all these workbooks interacting with each other?
Do they have outside links?
Do they contain any VBA code?

If you have some large workbooks all interacting with each other, I wonder if you really are working with some sort of Relational Database. Excel is not a very good tool for that sort of thing. Programs like Microsoft Access, SQL, or Oracle are designed for that sort of thing and handle large amounts of related data much more gracefully.

 

[TomCon]

150mb is very large. You might want to find a way to summarize some of your data prior to final product, then import the summary data. Yes, the WB will bog down.

How many cells do you have that have data in them? How many cells are "Used" (formatted or non-empty): To find the approximate Used cells, put your cursor in cell A1 and press Ctrl-Shift-End for every sheet; multiply the number of columns by the number of rows. How much Conditional Formatting (CF) do you use? What percentage of the used cells are formulas?

Some people have a habit of formatting entire rows and columns. This can increase a file size quickly.

I have a workbook that has over 250,000 used cells. CF usage is moderate, 80% percent of those cells are formulas. File size 3.7mb

When I had large workbooks with tables of calculations, I would temporarily value the data. What I did was insert a row at the top of of the table and copy one row of formulas into that row to save the formulas. I then value the full data set. When I need to update the data, I simply copy the formula row down and value them again. Macros can do this automatically.

If you have supporting data that doesn't need to look pretty, I suggest removing all the special formatting.

The main sheet has 96,000 rows X 141 columns = 13,536,000 cells, probably 90% with content (defined as a table; most formulas use table notation). It has a total of 11 sheets most of the rest not more than a few hundred rows. It has pivot tables (and therefore Pivot Caches). It has UDFs that are called. So, yes, its big. I don't think there is extraneous formatting off of the ends of the occupied cells. I could understand some delay when a calculation is initiated (as i said, calculation set to Manual). But just arrowing between cells...not sure why that has to cause Excel to enter "not responding" so frequently, especially on a machine with so much memory (32GB).

Any other thoughts out there? Thanks!

 

[Joe4]

I don't think it is your computer, I think it is probably the size of what you are trying to do in Excel, especially if you have a lot of formulas.
Excel actually has a limit on how many dependent calculations it can track (it used to be something like a million).
Once you exceed that number, it becomes painfully slow to do anything at all in the workbook.

It is a sign that you are probably using the wrong tool for the job. Excel was not designed to be a relational database or handle very large amounts of data.

 

[Jeffrey Mahoney]

Most of my "very large" workbooks were no bigger than 50mb. After that the drop in speed wasn't tolerable. I needed to use other applications for managing databases. Some sort of SQL database tool. Preprocessing the data it was essential.

 

[TomCon]

Thanks for the thoughts. Yes a big project indeed. For me, I am good with Excel...SQL pretty rudimentary, would be a huge learning curve to try to do this in SQL. I thought since Excel allowed for 1,000,000+ rows and hundreds of columns, it would not necessarily bog down with something that only filled about 1/10 of the rows and maybe 1/3 of the columns. SO, i guess it is not so. What is really bogging me down tho is this behavior of "not responding" with selecting a different cell. Again, on manual calc, i am willing to wait 5 even 10 minutes when i press "Calculate now". I expect that to take some time. In fact, it often takes < 1 minute to calculate...not much more than what selecting a new cell sometimes takes! I did not expect such bogging when merely trying to arrow around the spreadsheet. I can't exactly imagine the internal behavior that would make selecting a different cell take so long. Maybe occasionally when you cross some memory boundary...but not nearly so often as it does!! Any last thoughts besides "go to SQL"?

 

[rollis13]

Hi to all.
@TomCon, you keep saying that you have a new PC with 32 GB of ram, but have you checked in Task Manager (Process) how much Excel really uses? If Excel has thousands/millions of iterations to do (not only calculations) the used ram doesn't grow, so if it slows down it's a limit of Excel (and of CPU if old).
I can't even believe that your file is (slightly) corrupted so moving everything to a new clean file probably wouldn't be of any help.

 

[Joe4]

I thought since Excel allowed for 1,000,000+ rows and hundreds of columns, it would not necessarily bog down with something that only filled about 1/10 of the rows and maybe 1/3 of the columns.

Yeah, be careful not to get caught in that trap. Just because Excel allows a lot of rows and columns doesn't mean that it will handle it all gracefully and efficiently.
If you had 1,000,000 rows of data in only a single column and they were all data (and no formulas), then it might do OK.
But when you start adding formulas, VBA code, Pivot Tables, etc, you are multiplying the effect.

I am curious, do you have any event procedure code (especially "Worksheet_SelectionChange" procedures) or Conditional Formatting?
"Worksheet_SelectionChange" event procedure codes fires any time any cell is selected. So that would REALLY have a deterimental effect on a file with so much data.

 

[Jeffrey Mahoney]

As I said before, if you have any data sets that don't need to be calculated every time something changes, save a row of formulas and value those out.

UDF's are very slow. You should use them sparingly. If you can, change them to lambda formulas.