Forum Discussion
Excel PAINFULLY slow - Why?
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 macr...
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).