How powerful should my MacBook Pro be for complex Excels?

Hi Grant, once again many thanks. I see what you mean that my screenshots told a story that at least once, all memory was used up, an 92.8mb was swapped. But I think it's fair to say we don't know if it was Excel related.

I quit Dropbox and re-entered the Excel and no change.

I then restarted Dropbox, and interestingly, it now takes 780Mb instead of the 4.28Gb of memory. So that's 3.5Gb returned "for free". I can only guess it's something accumulative as Dropbox has been open a long time, and there are 50,000+ files "behind it", so it's perhaps caching the indices or whatever. There's certainly a lesson learned here to look at memory and if Dropbox is using excessive memory, quit it and reopen it. I can't just open it when needed as others use it and we all need to have latest files. This Mac Pro M2 is still high end, so can't stop multi tasking here as wouldn't be workable.

I've been trying all sorts of things, and the only improvement on opening the Excel is when I deleted 1 "long" "IFS" formula that's not really needed. That knocked 11% off opening the Excel (as the same formula is in 200,000 rows). So there's a red flag there, just about the formulae. It might be some Excel functions are particularly CPU or memory intensive, and could be replaced by more efficient ones that do similar things . Different ways to skin a cat, etc.

Sorry, but I didn't understand what you wrote as per the below ... I tried System SETTINGS / Users & Groups / .. but just saw my "Account (Admin) and the Guest one, but nothing to do with anything I can disable with a "-"

System Preferences > Users&Groups > your Userid > Login Items. ...

... NOT the Checkbox, that indicates invisible.

Select Dropbox and delete [-] out of the list, (and everything else while you are up).

Grant Bennet-Alder wrote:

Swap used: 9.28MB

All memory was completely used up and at least at one time, as much as 92.8 MB simulated (in slow motion) on disk. that indicator is a smoothed high-water mark indicator, so you may reclaim some of that swap space over time.

DropBox using 4.28 GB (one sixth of total RAM) to do nothing for you in this moment is completely unacceptable. You should quit Dropbox for now and make certain that you DO NOT launch it at login, but only when needed to sync a file, and quit immediately afterward.

System Preferences > Users&Groups > your Userid > Login Items. ...

... NOT the Checkbox, that indicates invisible.

Select Dropbox and delete [-] out of the list, (and everything else while you are up).

The absolute best way to speed up this spreadsheet is to have more RAM memory available.

I suggest you Excel FIRST after a Restart, and keep memory hogs like Chrome off until you are done spread-sheeting.

Hi Grant, many thanks for your quick reply. Of the 30+ formulae columns, I've given below some examples ....

A. Some of the formulae are simple. For example

=A*B+C/100

=SUM(Table2[@[XXXX EU Fee]:[MM Non-EU Fee]])

=IF([@CCode]="L000","L000",LEFT([@CCode],6))

=AND([@Date]>=(TODAY()-'By last ''n'' days'!$B$6),[@Date]<TODAY())

B. There are a lot with "IFS". For example ...

=IFS([@[XXXX-EU Amount]]<>0,"XXXX",[@[XXXX-NonEU]]<>0,"XXXX",[@[MM-EU]]<>0,"MM",[@[MM-NonEU]]<>0,"MM",TRUE,"Not sure")

=IFS([@Date]>'By PCN'!$B$2,0,[@[PDate]]="",[@[MPayout]],[@[PDate]]>'By PCN'!$B$2,[@[MPayout]],TRUE,0)

Does that help answer your question?

Hi Grant, thanks. Very helpful. Yes, I normally have Chrome, Outlook, Word, Excel, Preview and some other Apps open all the time. I just quit them all and did a Force Quit to all the apps I saw open, leaving me only with Finder and Preview. I then reentered the Excel and it was the exact same time to open it. So it doesn't seem to be related to the other apps.

The Excel is 105Mb, and the memory is 24Gb, so would expect memory isn't a limitation here.

I've added 2 screenshots from Activity Monitor, below. I'm no expert on understanding it, but it doesn't look like all memory is being used, and it doesn't look like the memory needed by Excel is excessive...

Hi Grant, you have been extremely helpful and have really helped the thought process.

I agree with you that there might be an opportunity to optimise the Excel performance and I'll try in the forum you suggested.

For now, I will hold off buying the M3 Pro, 36Gb, as even though I'm sure there will be SOME improvement, I should focus on the Excel first.

As for moving to a database, I've thought about it, but it is not a small project, with a huge learning curve, so as they say, "the devil you know ... ".

My sincere thanks for your time and help

Hi Grant, thanks. files in my Dropbox will change many times a day; sometimes because of changes I make and sometimes because of changes another team member will make. We both always need to have immediate access to the latest files and versions of files. So it's simply not practical to keep opening and quitting DropBox.

This MacBook Pro M2, 24Gb is a high end spec, relatively speaking, and should be able to handle such multi tasking.

I think using Activity Monitor, maybe once a day for now, I should check how much memory it uses and quit/restart it it's excessive, ie was 4.5Gb last night, and now 850Mb following quite/restart.

I'm going to take your advice and see what the Excel forums can tell me. I tried the "Performance" option you mentioned in a preview message .. it's not available on Mac's Excel. So I tried online and had a limit of 100Mb upload for an Excel and mine was 108Mb. So went back to an earlier version, 85Mb, ran the performance module online, it said it changed 11,000 cells, I filed it, and then reopened it from my Mac and there was no change to the opening time.

I must say though that whilst it was open online, it was much faster than the desktop Mac version of Excel. Hmmmm !.