An open community 
of Macintosh users,
for Macintosh users.

FineTunedMac Dashboard widget now available! Download Here

Page 1 of 3 1 2 3 >
Topic Options
#2978 - 09/05/09 09:07 AM When is an Excel file getting too big?
JoBoy Offline


Registered: 08/05/09
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

Top
#2980 - 09/05/09 09:16 AM Re: When is an Excel file getting too big? [Re: JoBoy]
joemikeb Offline
Moderator

Registered: 08/04/09
Loc: Fort Worth, Texas
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.
_________________________
joemikeb • moderator

Top
#2982 - 09/05/09 09:34 AM Re: When is an Excel file getting too big? [Re: joemikeb]
JoBoy Offline


Registered: 08/05/09
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

Top
#2984 - 09/05/09 09:43 AM Re: When is an Excel file getting too big? [Re: JoBoy]
Ira L Offline


Registered: 08/13/09
Loc: California
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: 27" iMacs, Macbook Air, macOS 10.14.x,; iPhones, iPods and iPads galore!

Top
#2994 - 09/05/09 11:59 AM Re: When is an Excel file getting too big? [Re: Ira L]
JoBoy Offline


Registered: 08/05/09
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

Top
#3075 - 09/06/09 10:17 AM Re: When is an Excel file getting too big? [Re: JoBoy]
Ira L Offline


Registered: 08/13/09
Loc: California
OK, good to know. Better safe than sorry.
_________________________
On a Mac since 1984.
Currently: 27" iMacs, Macbook Air, macOS 10.14.x,; iPhones, iPods and iPads galore!

Top
#3223 - 09/07/09 12:36 PM Re: When is an Excel file getting too big? [Re: JoBoy]
GrayShades Offline


Registered: 08/04/09
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

Top
#3232 - 09/07/09 01:37 PM Re: When is an Excel file getting too big? [Re: JoBoy]
oldMacMan Offline


Registered: 09/02/09
Loc: Auckland, New Zealand
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

Top
#3249 - 09/07/09 02:54 PM Re: When is an Excel file getting too big? [Re: GrayShades]
JoBoy Offline


Registered: 08/05/09
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

Top
#3250 - 09/07/09 02:59 PM Re: When is an Excel file getting too big? [Re: oldMacMan]
JoBoy Offline


Registered: 08/05/09
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.


Edited by JoBoy (09/07/09 03:02 PM)
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#3346 - 09/08/09 03:49 PM Re: When is an Excel file getting too big? [Re: JoBoy]
Virtual1 Offline


Registered: 08/04/09
Loc: Iowa
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.




Edited by dkmarsh (09/08/09 04:32 PM)
Edit Reason: inserted hard return to avoid stretched browser window
_________________________
I work for the Department of Redundancy Department

Top
#3428 - 09/09/09 03:39 PM Re: When is an Excel file getting too big? [Re: Virtual1]
JoBoy Offline


Registered: 08/05/09
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

Top
#4254 - 09/23/09 12:02 PM Re: When is an Excel file getting too big? [Re: Virtual1]
JoBoy Offline


Registered: 08/05/09
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

Top
#4256 - 09/23/09 01:12 PM Re: When is an Excel file getting too big? [Re: JoBoy]
alternaut Offline

Moderator

Registered: 08/04/09
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

Top
#4262 - 09/23/09 03:31 PM Re: When is an Excel file getting too big? [Re: JoBoy]
Ira L Offline


Registered: 08/13/09
Loc: California
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: 27" iMacs, Macbook Air, macOS 10.14.x,; iPhones, iPods and iPads galore!

Top
#4265 - 09/23/09 06:18 PM Re: When is an Excel file getting too big? [Re: Ira L]
JoBoy Offline


Registered: 08/05/09
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

Top
#4268 - 09/23/09 07:02 PM Re: When is an Excel file getting too big? [Re: JoBoy]
joemikeb Offline
Moderator

Registered: 08/04/09
Loc: Fort Worth, Texas
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.
_________________________
joemikeb • moderator

Top
#4270 - 09/23/09 09:26 PM Re: When is an Excel file getting too big? [Re: joemikeb]
JoBoy Offline


Registered: 08/05/09
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!)


Edited by JoBoy (09/23/09 09:39 PM)
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#4272 - 09/23/09 11:25 PM Re: When is an Excel file getting too big? [Re: JoBoy]
artie505 Online


Registered: 08/04/09
> 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.)


Edited by artie505 (09/23/09 11:31 PM)
Edit 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

Top
#4273 - 09/24/09 06:20 AM Re: When is an Excel file getting too big? [Re: JoBoy]
joemikeb Offline
Moderator

Registered: 08/04/09
Loc: Fort Worth, Texas
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.
_________________________
joemikeb • moderator

Top
#4281 - 09/24/09 10:28 AM Re: When is an Excel file getting too big? [Re: joemikeb]
JoBoy Offline


Registered: 08/05/09
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

Top
#4282 - 09/24/09 10:35 AM Re: When is an Excel file getting too big? [Re: joemikeb]
JoBoy Offline


Registered: 08/05/09
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

Top
#4283 - 09/24/09 11:02 AM Re: When is an Excel file getting too big? [Re: JoBoy]
alternaut Offline

Moderator

Registered: 08/04/09
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

Top
#4286 - 09/24/09 11:48 AM Re: When is an Excel file getting too big? [Re: JoBoy]
Ira L Offline


Registered: 08/13/09
Loc: California
So with the amazing savings on RAM, did you buy the iPhone? grin

(oops, subject change alert!)
_________________________
On a Mac since 1984.
Currently: 27" iMacs, Macbook Air, macOS 10.14.x,; iPhones, iPods and iPads galore!

Top
#4288 - 09/24/09 12:12 PM Re: When is an Excel file getting too big? [Re: Ira L]
JoBoy Offline


Registered: 08/05/09
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

Top
Page 1 of 3 1 2 3 >

Moderator:  alternaut, dianne, dkmarsh