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?

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.

Thank you. I was afraid you might say that.
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.

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.

OK, good to know. Better safe than sorry.

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.

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

Thank you. That is extremely helpful.

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.

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.

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.

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.

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 , 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.

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.

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?

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.

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!)

> 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.)

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.

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?

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.

So with the amazing savings on RAM, did you buy the iPhone?

(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.

Thanks. See my next post.

This problem is getting more complex. I created a new workbook with one worksheet. The file is 45Kb in size. I did a search and replace that found three locations where I replaced existing text. The search and replace window started acting strange. When I clicked Find Next, it continued to "find" the same three location even after they had been replaced. I should have received a notice that no more search items were found. I then pressed Cmd-S to Save and Excel disappeared from the screen. Also, all icons on the Desktop and the Dock disappeared leaving the custom photo that is my normal Desktop all alone. Then, the machine automatically restarted and the Desktop became normal. I restarted Excel and a version of the worksheet that crashed appeared with (recovered) added to the filename. It was current except the Find and Replace changes were not there. I then opened Activity Monitor and watched its readings while I repeated exactly what I had done before, but when I got to the Save part, the file saved normally and remained OK. During the failed attempt to recreate the crash, Activity Monitor showed VM size: 113.09Gb; Page ins: 436.1Mb; Page outs: 756Kb, and Swap Used: 0 bytes. Does anyone have an idea of what is going on?

The only suspicion I still harbor is that, when my Mac Pro really went under, the Genius folks at the Apple Store said that it was probably a failed graphics card. They replaced it under Apple Care. They also thought there might be a problem with the logic board, but the graphics card seemed to resolve all problems at that time. Since then, I've had miscellaneous issues including the one mentioned on this thread. I always worry wondering if somehow the graphics card has a flaw. I just ordered 2Gb of new RAM for delivery tomorrow, but maybe I should buy a total of 4Gb and remove and replace the existing RAM. I'm lost on this. Help would be greatly appreciated.

Where do I find the Apple Hardware tester? I've used it in the past and thought it was in Utilities on the system installation disk, but I don't find it there.

Update: I found the Hardware Test. Two quick tests revealed no problem. I know that the extended testing for several passes is better, but no time today for that.

The squirrely behavior seems to be limited to Excel. I reinstalled MS Office a couple of weeks ago. No time to do that today.

Thanks for reminding me that "When all else fails, follow the directions." Yesterday was a clash between workload and trouble with Excel. I apologize for letting all my anxieties hang out and for asking for help with things that are in the directions. It was a bad day. Today is much different. I received my extra 2 Gb of RAM from OWC and installed it. Since the installation, I've not had any trouble at all with Excel, but I'll have to have a few days' work to decide that all problems are solved. For now, it looks good. Again, sorry for projecting a bad day onto the forum. I'll try not to do that again.

I hate to bring brand X into the discussion, but there is a brief article there that describes the exact behavior I was experiencing with Excel. The article blames it on an alleged bug in Snow Leopard that occurs when an application is being used while Rosetta is installed. It would also explain why I was having the problem regardless of whether the file was large or small. I don't know if memory size has anything to do with it, but I have yet to have my first problem since installing the extra 2Gb of RAM. What do you forum regulars think about this?

See: http://reviews.cnet.com/8301-13727_7-10361888-263.html?tag=mncol;txt

I'm starting this new thread because my other one on this subject now occupies two pages and may be skipped for being too long. This information is worth seeing if you're having the problem. I've been struggling with Excel 2004 crashing while running on 10.6.0 and 10.6.1. The other thread (When is an Excel file getting too big?) chronicles the discussion.

Tonight, I went to Apple.com > Support > Discussions > Mac OS X v10.6 Snow Leopard > Using Mac OS X 10.6 Snow Leopard Topic : Excel 2004 continually crashes OS 10.6.

I scrolled down to the end of page 1 of the 3 page thread to messages left by David Hoerl. He says that several PPC programs are having the trouble, not just Excel 2004. He offers a diagnosis and repeats a message he received from Apple. In essence, it is that this is a known issue and they’re working on it.

For reasons he explains, he has concluded that it is related to Carbon and not Rosetta. I had left a message on my other thread about a blurb on CNet that connected it to Rosetta. At least I now know enough about it to stop trying to fix it and endure until Apple provides a solution. If it becomes unbearable, I can possibly solve it by upgrading to MS Office/Mac 2008.

On a happier note, I installed an extra 2Gb RAM today (bringing the total RAM to 4Gb) and haven't had a crash since then. I have no idea whether that is coincidence or a solution.

Edited by JoBoy (09/25/09 09:56 PM CST)

I've seen and experienced only PPT 2004 crash, not Excel. Actually, it did not even launch the first time. The remedy is to reinstall Office (sometimes twice). Then it works. It would be great if Apple would be able to remedy that for good.

How do you determine that?

The PPC DIMMs I've purchased from Crucial have had OEM stickers on them (Samsung, once, but that was returned...packaging, rather than performance, problems), but the DIMMs I purchased for my MacBook have Crucial stickers.

I somehow think that Apple does not use OEM any more, but I've no idea what they use in its stead.

Did you remedy it by re-installing Office 2004 or 2008? I see your signature shows 2008. I did reinstall Office 2004 once without results. Maybe I should try again?

My home machine has 2008 (no problems so far with SL), but two at work (one wifey's) have 2004. A reinstall should work fine but you need to remove bits and pieces, not just the 2004 MS Office folder. You need to remove the prefs and the Microsoft folder in the user folder>library>preferences. I would leave the identity folder so that your Entourage prefs would be preserved.
Since I am also not happy with the outcome (had to reinstall twice), I am looking at a bargain deal for Office 2008. The best deal for Special media edition (it has Exchange support) is on Amazon marketplace - under $100.

Thanks for the hints. When reinstalling 2004 from the original DVD, do I have to subsequently install all updates to each app or can I just install the most current one? A Mac "combo" update reinstalls every update that went before it for that version. Does MIcrosoft also do that?

Dianne:

How did you create that hyperlink? I would have done that if I had known how. Also, how can I send a message to an individual on the forum without having to do this public announcement in hopes that the person will see it? (Obviously, I'm doing just that in this message ) I've looked around the forum, but I didn't see anything on either subject.

Microsoft usually does not do that. You will have to install all consecutive updates. Chances are that Microsoft Autoupdate app will guide you through the process. Please do not open Office apps before you install everything. The latest version is 11.5.5.
P.S. DK is correct.

Actually, it looks like you can jump in with the Microsoft Office 2004 for Mac 11.5.0 Update; unlike the updates immediately before and after it, 11.5.0 makes no mention of any previous updates being required.

Actually, it looks like you can jump in with the Microsoft Office 2004 for Mac 11.5.5 Update itself; no previous update requirements listed there, either (compare for instance 11.5.4).

Right! I just used Microsoft AutoUpdate to find the Office 2004 update. It produced 11.5.5. The lead page for this update said, "This update includes all of the improvements that were released in all previous Office 2004 updates." Then it listed them. I thought that was a pretty good assurance that I could install 11.5.5 and forget about the rest. Thanks to you and MacNerd10 for getting me this far. Hopefully, it will do some good.

JoBoy,
I used the information found in FAQ –> What UBBCode can I use in my posts? –> Links.

On first reading, I had difficulty in figuring out the information, so if the
bracket
url=address
bracket
title
bracket
/url
bracket
is not clear, let us know.
It is best if all troubleshooting questions and messages are posted in a public thread so that current and future readers and those using the Search function will have access to all information related to a topic. The primary purpose of FineTunedMac is to serve as a research library documenting a series of troubleshooting questions and replies.

A member can contact another member by clicking on his/her name in the left column, selecting View Profile, and seeing if s/he has entered an email address.
The number of pages spanned by a topic will vary from member to member based on a person's individual settings entered in My Stuff –> Edit Preferences –>
   • Total topics to show per page: (default is 25) . . . mine is 90
        and
   • Total posts to show on one page when viewing a post in flat mode: (default is 25) . . . mine is 90.

Thank you for a treasure of information. My only defense to the hyperlink question is that there is no way I would know that a hyperlink was associated with that FAQ Title. Sorry that my knowledge is spotty. It's deep in some areas and non-existent in others. Thanks again. I really appreciate your taking the time to help me out.

JoBoy,

I am glad to help.

As "Crashes of PPC programs on Intel machines" continues the troubleshooting process begun in "When is an Excel file getting too big?," I have merged the two threads to keep all information in one location.

FINALLLY! Resolution of the WindowServer crashing problem while using Excel 2004 and lessons learned along the way:

I owe this forum community a huge thank you for your various suggestions that led to a resolution of my problems discussed at length in this thread. I'm declaring at least a temporary victory based upon two days of blissful computing without even a hiccup from my previously troubled Mac Pro. Although I do not understand the why, the following are what I finally did to overcome the problem:

1. Add 2Gb more RAM (new total 4Gb). Price $72 plus shipping from OWC.
2. Use both Disk Utility and DiskWarrior 4.2 by booting from their original installation disks instead of using the applications installed on my hard drives.
3. Reinstall MSOffice 2004 from original installation disks and then update Office before opening any Office applications. Update from Office Update 11.5.5. I finally realized that Microsoft has a practice of issuing a few updates that do not include all previous updates and then, periodically, issuing an update that has them all. 11.5.5 has them all. This is a tremendous time saver over installing a large number of updates one at a time in the proper order.

Along the way, I encountered two issues that need mentioning here. First, I experienced an Apple Error number 1407 that prevents deletion of some files. The cure is to run Disk Utility from the Snow Leopard installation disk and use both Repair Disk Permissions and Repair Disk. The error goes away. Second, messages popped up in both Disk Utility and DiskWarrior 4.2 that read as follows:

Warning: SUID file "System/Library/CoreServices/RemoteManagement/ARDAgent.app/Contents/MacOS/ARDAgent" has been modified and will not be repaired.

Warning: SUID file"System/Library/PrivateFrameworks/DesktopServicesPriv.framework/Versions/A/Resources/Locum" has been modified and will not be repaired.

A little research revealed that these two messages should be disregarded. They give a frightening impression of imminent danger. If they really are harmless, I hope Apple fixes the bug asap.

Again, thanks for all the input. This is a great place to troubleshoot. Sorry I got so desperate late last week and started venting frustration and anxiety. It was a very unfortunate confluence of computer trouble and very important work deadlines that were negatively affected by my troubled Mac Pro.

Great! Enjoy the new cat!

Admittedly those "warnings", they are not "errors", give uninformed users pause, but if years of history is any guide, if and when Apple "fixes" those "bugs" they will simply be replaced by different "warnings" in the next update. My guess is the only long term "fix" will be a switch in Disk Utility to set the level of error reporting with the default being to report only uncorrected errors and not warnings or information messages.

By-the-way, you are correct about running Disk Repair from the install disc, or from another boot volume, but permission repair should be run from the normal boot drive, not from the install disc.

Why is that? I vaguely remember hearing it before. Also, should you be booted from the disk where permissions are being repaired if it happens to be a clone of the main drive?

Thanks for checking Crucial for me. I clicked your link and read their stuff. It was impressive. The scale tipper for me was that I have had prior, very good experience with OWC and decided to stick with them for that reason. Brand loyalty is why companies use brands to get and retain customers. I don't know whether it's a personal strength or weakness for people to pay attention to brands. I appreciate your interest in this troubleshoot. It looks like I'm finally in the clear and that more RAM played a major, if not the major part. It's a mystery to me why it did. I watched Activity Monitor carefully and there was always a very large percentage of Free RAM available when I only had 2Gb RAM, but I can't deny the great results that began the moment the extra 2Gb RAM was added.

Rather than my going into a long discussion on the subject I'm going to send you to the horse's mouth. Take a look at this Apple Knowledge Base article. In particular the last paragraph which is titled Should I start up from a Mac OS X install disc to repair disk permissions?

That article was spot on! By running repair permissions from Snow Leopard startup disk (10.6.0), I lost any permissions updates that might have been contained in 10.6.1. I have since run repair permissions from a Disk Utility app that is from 10.6.1 to cure the potential problem. Again, thanks a lot!

Here's an update on the Snow Leopard crashes while using Excel 2004. I continued to follow the suggestions in this forum and things went well for a few days. Then, I started getting frequent crashes while working with relatively small files as well as the large ones.

At that point, I purchased MS Office 2008 Business Edition from Amazon. I got that edition to obtain the access to Exchange that I need for email to the home office. Since installing Office 2008 two days ago, I have had no crashes at all. If I do have them, I'll report them here, but consider no news to be good news. Again, thanks for all the help on this struggle. It appears that I finally have my "fine tuned Mac" back after a couple of months of exhausting hassle. By upgrading my RAM to 4Gb and purchasing Office 2008, I seem to have eliminated my sources of trouble. Otherwise, Snow Leopard has been a real delight. I love its speed. Things just seem to work. I think Apple accomplished its goals of a slimmer, quicker, less problematic OS X. Except for the defective graphics card that Apple replaced, my problems were inadvertently self inflicted by not enough RAM and an old application.

Good luck! MS Office 2008 is definitely less problematic under SL. However, 2004 Excel crashes are very rare, AFAIK. If you want to communicate with other Office users, may I suggest that you save the files in the old version (compatibility mode, without the X in the end). This way, they can be read directly by Office 2004 that is still widespread.

Yes. I intend to do just that. Thanks.

It has been a month since I upgraded to MS Office 2008 and 4Gb RAM instead of 2Gb. Since that time, I have not had a single crash of Excel or any other Office component. To all of you who helped with this problem, I owe a great big thank you! It's a privilege for me to be a member of this forum.