An open community 
of Macintosh users,
for Macintosh users.

FineTunedMac Dashboard widget now available! Download Here

Previous Thread
Next Thread
Print Thread
Page 1 of 3 1 2 3
When is an Excel file getting too big?
#2978 09/05/09 04:07 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
I have MSExcel 2004. One file now is 2.5Mb in size. It has 22 tabs. One tab has 5,211 rows containing data. The other tabs are not nearly this large. There are columns A through AG containing data. How large can I go without creating a problem?


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #2980 09/05/09 04:16 PM
Joined: Aug 2009
Likes: 16
Moderator
Online
Moderator

Joined: Aug 2009
Likes: 16
I have no idea what the extreme limits are, and they are going to vary based on the content of the spreadsheet and are unlikely to be an absolute do not exceed value. In any case with a spreadsheet that big, be sure and keep good backups of the last few working versions. (A situation tailor made for Time Machine.) When that spreadsheet fails it will most likely be sudden and complete with no recovery possible.


If we knew what it was we were doing, it wouldn't be called research, would it?

— Albert Einstein
Re: When is an Excel file getting too big?
joemikeb #2982 09/05/09 04:34 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Thank you. I was afraid you might say that. smirk
I have Time Machine running to a Time Capsule and SuperDuper making two scheduled, daily copies every night. One SD copy is to another internal drive and the other is a sparsebundle to the Time Capsule.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #2984 09/05/09 04:43 PM
Joined: Aug 2009
Likes: 8
Online

Joined: Aug 2009
Likes: 8
It might also be of interest to look at your RAM memory usage when the spreadsheet is open. Depending on what formulas, etc. are involved, Excel may start to hog your memory and, independent of size, that could cause issues.


On a Mac since 1984.
Currently: 24" M1 iMac, M2 Pro Mac mini with 27" BenQ monitor, M2 Macbook Air, MacOS 14.x; iPhones, iPods (yes, still) and iPads.
Re: When is an Excel file getting too big?
Ira L #2994 09/05/09 06:59 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Originally Posted By: Ira L
It might also be of interest to look at your RAM memory usage when the spreadsheet is open. Depending on what formulas, etc. are involved, Excel may start to hog your memory and, independent of size, that could cause issues.


I tried it just now and no problem. That's probably due to the fact that a major portion is text that requires no calculations.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #3075 09/06/09 05:17 PM
Joined: Aug 2009
Likes: 8
Online

Joined: Aug 2009
Likes: 8
OK, good to know. Better safe than sorry.


On a Mac since 1984.
Currently: 24" M1 iMac, M2 Pro Mac mini with 27" BenQ monitor, M2 Macbook Air, MacOS 14.x; iPhones, iPods (yes, still) and iPads.
Re: When is an Excel file getting too big?
JoBoy #3223 09/07/09 07:36 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
Howdy. In my eight years working with Excel on the Windows side, it was never the size of the file that was a problem (unless over 80 MB). I had some that were 100+ worksheets, some with several thousands of rows of data. Rather what caused real problems were the number of formulas (many thousands), specifically those formulas that contained volatile functions (i.e. that have to recalculate after every change on the spreadsheet). In those cases, it helped to turn off auto calculation, then when done working, turn it back on and wait 15-20 minutes as it re-calculated.

The key was to examine carefully the design of the workbook. I took one that a co-worker had developed that was 25 MB and very slow. In just a matter of two days I redesigned it, had reduced the size to less than 9 MB, and it re-calculated almost instantly.


MacBook Pro 15” Matte, OS X 10.6.6
Scrivener 2, Mellel 2.7.3, DevonThinkPro 2, Accordance 9.1,
OpenOffice.Org 3.3, Notebook 3, Reunion 9, Tinderbox 5.6.1
Re: When is an Excel file getting too big?
JoBoy #3232 09/07/09 08:37 PM
Joined: Sep 2009
Offline

Joined: Sep 2009
Also, may people use a spreadsheet where a database (eg FileMaker) is a more appropriate tool.


Mac since 1984, Apple since 1978
MacBook Pro, iPhone 5, MacBook (in living room for iTunes and searching for recipes), iPad
Re: When is an Excel file getting too big?
GrayShades #3249 09/07/09 09:54 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Thank you. That is extremely helpful.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
oldMacMan #3250 09/07/09 09:59 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
I've used FileMaker quite a bit in the past. This particular project is most efficient in Excel because it allows me to scroll through data at a rapid rate and no reports are produced in electronic form or in print form. Ordinarily, I would agree that a database might be appropriate, but this project is far more convenient in its present form. Thank you for the comment. I'm looking forward to getting better acquainted with you in the forum. I, too, go back to the pre-Mac Apple days with the Apple II and the Lisa.

Last edited by JoBoy; 09/07/09 10:02 PM.

Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #3346 09/08/09 10:49 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
I have my checkbook from 1998 up in xls. also 2.5mb. I started running into problems with excel when it hit about 2.3mb. It would complain about insufficient memory and other things. I have a highly complex spreadsheet with a dozen tabs and tons of very nasty conditional sums. Here's one of the worst: (with curly braces around it of course)
=IF(L45="","",SUM(IF(JDCCU!$A$3:$A$9926="N",0,IF(IF(JDCCU!$C$3:$C$9926>=$M$3,IF
(JDCCU!$C$3:$C$9926<=$M$4,JDCCU!$B$3:$B$9926=L45,0),0),JDCCU!$E$3:$E$9926,0))))

I had to turn off automatic recalculate because it takes 3-5 sec to calculate the spreadsheet.

The other problem I ran into is autofilter stopped working at a certain point. Had to turn off autofilter for the columns that didn't need it, which appeared to help. also I have a note here:

control g
JDCCU!_filterdatabase

apparently that clears/resets something in the auto filter database for the spreadsheet. (JDCCU)

I suppose at some point it's going to just fall apart though. I tried opening it no a newer version of excel and it didn't help.



Last edited by dkmarsh; 09/08/09 11:32 PM. Reason: inserted hard return to avoid stretched browser window

I work for the Department of Redundancy Department
Re: When is an Excel file getting too big?
Virtual1 #3428 09/09/09 10:39 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
I don't have any formulae that complex, but I'm getting the idea that one of these days I'll hit the wall and I need to keep my file copies up to date.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
Virtual1 #4254 09/23/09 07:02 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Since my last post on this thread, I've had two Excel 2004 workbook files crash. One was 1.3Mb in size and the other 2.6Mb. The 1:2 size ratio may be only coincidental. These workbooks are constructed to perform different functions in support of different tasks. Both have multiple worksheets containing complex calculations with a lot of deliberate circular references that are extremely useful to what I'm doing. Both have forms that produce recipes for jobs to be performed in a laboratory environment over and over again with changed parameters for each job. When a new job recipe is made, a new form is generated while preserving all of the previous jobs that have been calculated. Comparing the workbooks that crashed, the 1.3Mb file has many more calculations than the 2.6Mb file, but I really can't estimate a ratio.

On the other hand, a third workbook file, 2.5Mb in size with over 5,200 rows of data, that caused me to start this thread has NOT crashed. It contains far fewer calculations than the other two and no reports or other printed versions are routinely made. I'll keep using it until it crashes just to see how big it can get while remaining functional. [Yes, I use both Time Machine and SuperDuper to make frequent backups to a TimeCapsule and two internal hard drives that are dedicated to backups.]

From the very helpful comments on this thread plus my own subsequent experiments, the answer to the question, "When is an Excel file getting too big?" seems to be, "It depends on how many calculations are involved."

In scrolling through this thread, it is apparent from my experimental results that every one of you who has contributed was correct and very helpful. I did the experiments so that I will know when to make a new copy of each workbook. The previous workbooks provide an archival record that helps to prevent "reinventing the wheel" on projects. Thanks again for your help. I love this forum. I am reporting back to you my results to confirm the facts that you gave good advice and that it was very helpful. Best regards.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #4256 09/23/09 08:12 PM
Joined: Aug 2009
Likes: 1
Moderator
Offline
Moderator

Joined: Aug 2009
Likes: 1
Thanks for the update. You're certainly correct in that recording your actions here can help getting to the bottom of your question. Who knows, given enough record entries there might even be an answer smirk , but if nothing else others with similar questions may be helped or at least urged to post their experience on the topic here. I'll be looking forward to such posts and any new developments. tongue


alternaut moderator
Re: When is an Excel file getting too big?
JoBoy #4262 09/23/09 10:31 PM
Joined: Aug 2009
Likes: 8
Online

Joined: Aug 2009
Likes: 8
I suspect there is not unique answer for "too big". As you have said it depends on…, and the "on…" can vary with each spreadsheet and amount of installed RAM. If someone is looking for a definitive number, like "when you hit x MB you will crash", that number just won't happen.

In your case, as previously suggested by many, the simplest solution may be to add more RAM and extend the life of your spreadsheets, regardless of size.


On a Mac since 1984.
Currently: 24" M1 iMac, M2 Pro Mac mini with 27" BenQ monitor, M2 Macbook Air, MacOS 14.x; iPhones, iPods (yes, still) and iPads.
Re: When is an Excel file getting too big?
Ira L #4265 09/24/09 01:18 AM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Originally Posted By: Ira L
In your case, as previously suggested by many, the simplest solution may be to add more RAM and extend the life of your spreadsheets, regardless of size.


How much is enough? I opened Activity Monitor and watched the little pie chart. At no time during the operation was all of the RAM used. It nearly always showed at least a combined 30% Inactive (blue wedge) and Free (green wedge), yet I felt that something happened to the computer more extensive than an application crashing. The Finder acted like it had problems, too, so I restarted and everything went back to normal. How much IS enough?


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #4268 09/24/09 02:02 AM
Joined: Aug 2009
Likes: 16
Moderator
Online
Moderator

Joined: Aug 2009
Likes: 16
You might take a look at my page on Understanding Virtual Memory and Activity Monitor. A much better indicator of whether or not additional RAM is needed is found in the number of page outs. A page out indicates an 8KB page of memory has been removed from RAM and written to the Swapfile(s).

Based on my experience with Microsoft I am inclined to believe internal pointers within the application are more likely to cause a file to crash or become suddenly unusable than the amount of RAM in your system. However, insufficient RAM, can force premature paging out of data to the swapfile(s) and corresponding paging in of the same data when it is again needed will very definitely cause system performance issues and sluggish application functioning.


If we knew what it was we were doing, it wouldn't be called research, would it?

— Albert Einstein
Re: When is an Excel file getting too big?
joemikeb #4270 09/24/09 04:26 AM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
I checked your very informative page and also the Activity Monitor. While Activity Monitor was open, I ran one of the formerly crashed spreadsheets through its paces. Page outs and Swap used remained at zero throughout the exercise. With all the calculations and circular references, I can see how the internal pointers would get a real workout. Thanks a lot for the insight. I really appreciate it.

I can buy an additional two Gb RAM from Apple for $499.00 or I can buy an iPhone with a whole lot of RAM and I can chuck my Palm Centro that is really weak at surfing the web. Hmmm. (ooops! Subject Change Alert!)

Last edited by JoBoy; 09/24/09 04:39 AM.

Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #4272 09/24/09 06:25 AM
Joined: Aug 2009
Likes: 15
Online

Joined: Aug 2009
Likes: 15
> I can buy an additional two Gb RAM from Apple for $499.00 [....]

As joemikeb has mentioned in the past, "Apple is very proud of their RAM," which translates to "They charge too much!"

I've purchased RAM from Crucial five times and have been totally pleased with their product's performance, as well as with their customer support and prices. (They offer a "Limited Lifetime Warranty" and free shipping.)

(I just took a look at their website, and I believe this page is applicable to your Mac.)

Last edited by artie505; 09/24/09 06:31 AM. Reason: Add second link

The new Great Equalizer is the SEND button.

In Memory of Harv: Those who can make you believe absurdities can make you commit atrocities. ~Voltaire
Re: When is an Excel file getting too big?
JoBoy #4273 09/24/09 01:20 PM
Joined: Aug 2009
Likes: 16
Moderator
Online
Moderator

Joined: Aug 2009
Likes: 16
All of my RAM has been purchased from Other World Computing (OWC). They sell the same DIMMs Apple uses at a small fraction of what Apple charges.


If we knew what it was we were doing, it wouldn't be called research, would it?

— Albert Einstein
Re: When is an Excel file getting too big?
joemikeb #4281 09/24/09 05:28 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Originally Posted By: joemikeb
All of my RAM has been purchased from Other World Computing (OWC). They sell the same DIMMs Apple uses at a small fraction of what Apple charges.


You weren't kidding when you said "small fraction." I bought a matched pair of 1Gb modules for a total of two Gb for $77.99 plus shipping. The package should arrive tomorrow. The price difference was breathtaking. It almost seemed too good to be true, but OWC has a good reputation and I value your experience. Thank you for the suggestion. Best regards.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
joemikeb #4282 09/24/09 05:35 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
My current RAM modules (1Gb each) reside in DIMM Riser B/DIMM 1 and DIMM RIser A/DIMM 1.

Do I install the new pair in DIMM Riser B/DIMM 2 and DIMM RIser A/DIMM 2?


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: When is an Excel file getting too big?
JoBoy #4283 09/24/09 06:02 PM
Joined: Aug 2009
Likes: 1
Moderator
Offline
Moderator

Joined: Aug 2009
Likes: 1
Originally Posted By: JoBoy
Do I install the new pair in DIMM Riser B/DIMM 2 and DIMM RIser A/DIMM 2?

No. According to Apple*, you’ll have to pull one current DIMM from one riser to install both current DIMMs in the same riser, and the new pair in the (now empty) riser.

*) For those official DIMM installation instructions, check out Apple’s KB article Memory (FB-DIMM) Replacement Instructions for the Mac Pro (Early 2008). Specifically, it recommends that ‘DIMMs must be installed as pairs of identical size and type, from the same vendor. [In the illustration below], like-colored DIMMs must match.’.

PS, this KB can be found in Apple’s Mac Pro Support section.


alternaut moderator
Re: When is an Excel file getting too big?
JoBoy #4286 09/24/09 06:48 PM
Joined: Aug 2009
Likes: 8
Online

Joined: Aug 2009
Likes: 8
So with the amazing savings on RAM, did you buy the iPhone? grin

(oops, subject change alert!)


On a Mac since 1984.
Currently: 24" M1 iMac, M2 Pro Mac mini with 27" BenQ monitor, M2 Macbook Air, MacOS 14.x; iPhones, iPods (yes, still) and iPads.
Re: When is an Excel file getting too big?
Ira L #4288 09/24/09 07:12 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Originally Posted By: Ira L
So with the amazing savings on RAM, did you buy the iPhone? grin

(oops, subject change alert!)


I like your style. I'm not buying any more trouble sources until I get this dang Mac Pro back on its feet.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Page 1 of 3 1 2 3

Moderated by  alternaut, dianne, dkmarsh 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.4.33 Page Time: 0.050s Queries: 65 (0.037s) Memory: 0.7079 MB (Peak: 0.8877 MB) Data Comp: Zlib Server Time: 2024-03-28 17:56:35 UTC
Valid HTML 5 and Valid CSS