Excel PAINFULLY slow - Why?

[MikeSD]

I created a budget spreadsheet many moons ago.  It was originally created with Excel 97.  It ran great.  It has a tab for each month, and a worksheet for updating the schedule.

It has only 3 macros (trivial) that unprotect, protect, and copies sheets.  There are no STYLES created by me.  It does NOT use OneDrive or any cloud storage.

When I updated and switched to Windows 10 and 11, on two different machines, it now takes over 2 minutes to open, and another 2 minutes to close.  The macros used to complete in seconds.  Not they take minutes.

I have also tried creating a NEW spreadsheet, using more current Excel version.  I copied the spreadsheets from the original workbook, to the new.  But it's still taking forever to open.

What on earth is causing Excel to run so slowly?  There is no indication of any problems, other than the fact it runs slow.

When I ran this on a 10 year old computer, it runs 20 times faster.  With my Surface PRO 7, it runs like molasses.  I also have an i7 Studio plus and it runs just as slow.  I sure would like to get this to run like it does on my 10 year old computer.

Suggestions?

[#2]

@MikeSD 

Just out of curiosity, how many moons is the "many moons" you refer to? Assuming by "moon" you mean something like month, and since you've created monthly sheets (a major design mistake, from my perspective), that could be a factor if those "many moons" add up to, say, 25 years (which would be a workbook with a minimum of 300 sheets).

I have a budget workbook with a single sheet containing ALL of the transactions for my "many moons" worth of income and expense. I let Excel do the "heavy lifting" of summarizing monthly data (using Pivot Table). And the Pivot Table has the feature of enabling easy drill-down to all the backup for any given cell if I want the details on what I spent in, say, "Entertainment" in March of 2020. The "Filter" function on Pivot Tables also enables easy selection of a single year of multiple years to summarize from that consolidated, many moons worth of data, database. 

So if I were you, I'd take all those individual monthly sheets and consolidate them into a single sheet containing a single database of all your income/expense history, remove the macros--really unnecessary.

I've attached a very simple sample of how Pivot Tables serve as but one example of Excel's ability to work with a single consolidated database. There are other ways--especially with the new dynamic array functions (UNIQUE, FILTER) to use Excel very effectively to sort through and summarize data such as you're dealing with, so long as the data is all in one table.

[#3]

@MikeSD 

Some items to check that may be hindering performance:

• Custom styles count.  A blank workbook starts with 47 (The limit being 65,490).  Go to Home | Styles | Cell Styles to view them.  If the workbook is loaded with custom styles, press Alt+F11 to open the Visual Basic Editor.  Press Ctrl+G to show the Immediate window at the bottom (If it's not already showing).  Enter the following in the Immediate window then press Enter to get a styles count:

             ? activeworkbook.Styles.Count

• Used range.  Go sheet-by-sheet and press Ctrl+End to go to the last used cell in a given sheet.  If it's jumping down well below your data, then you'll want to tighten things up.  If you're running an Enterprise version of M365, you have access to Inquire and Clean Excess Cell Formatting:

Clean excess cell formatting on a worksheet (microsoft.com)

• Referencing entire columns or rows in formulas. For example, =XLOOKUP(A2,X:X,Y:Y) or MATCH(B2,25:25,0).  Make the references specific and spill whenever possible and practical.

Dynamic array formulas and spilled array behavior (microsoft.com)

• Use of volatile functions like INDIRECT and OFFSET
• An exorbitant amount of objects on a sheet.  Go to Home | Find and Select | Selection Pane to view the objects on a given sheet.  You never know what might be hiding on a sheet. Especially if data is added via copy/paste from web sources.  I've seen sheets with 1,000+ objects (mostly ones invisible to the eye) that grind Excel to a halt because the app can't render them.
• PivotTables using very large data sources.  Pivoting entire columns in anticipation of new data (e.g.  A:W or even A1:W100000 when the actual data set is much smaller).  Use Tables as Pivot sources when possible.
• You might also consider running Workbook Analysis (Another feature that comes with Inquire).

Analyze a workbook with Spreadsheet Inquire (microsoft.com)

[#4]

Yes, moons is just an expression, meaning many, many, many, months. Like I said, I was using excel 97.

And let me explain that I had only 12 tabs (one for each month). And I did my budget planning and changing in the "worksheet" budget window. My worksheet, had an area for monthly budget, and another area to manage cash flow. Once updated, it was copied to the month tabs for current month and following months.

I will look at your pivot table example though. I like to learn new ways.

But I have found the problem. I had some check boxes. Two boxes for each budget line item. I used those to check whether an item was paid in first half of month or latter. This amounted to about 40 check boxes. However, the way I was doing the copy was to copy everything from the "worksheet" to the individual month tabs. A really bad idea. Because everytime it copied, it just piled more checkboxes, onto the sheets. It didn't replace the others. So, after a long time of use, and copying just for test, I had thousands of check boxes on each spreadsheet. I have completely removed all the checkboxes and no longer need them.

I also had some external references that weren't needed. I've removed those too, but it was the checkboxes that were my problem.

After cleaning up the spreadsheet, it now opens almost instantly, and closes just as fast. The macros are speedy.

Thanks for the quick input. I will take a look at using pivot tables. My budget form is actually very simple form.

By the way, my filesize went from 42Mb to under 200Kb