Forum Discussion
Excel spreadsheet has grown larger and slower each year, hangs up with every new entry
My homegrown 'checkbook' workbook has stayed essentially the same since at least 2013. Each year I copy last year's spreadsheet, keep the formulas and wipe out the data, and start all over again. It has grown in size from 300K in 2013 to 6,000K in 2024 with the roughly same amount of data. It is now very slow to load and hangs up with each entry I make.
It consists of 12 spreadsheets, 1 for each month, where I enter transactions. There are 30 columns (for different categories/buckets of expenses) and about 50 rows for each monthly sheet. There are totals for each column at the beginning and ending of each month; the totals are in an array. The end of month totals become the beginning totals for the next month. I have a few simple formulas on each row to check that the deposits/withdrawals (columns C & D) equal the sum of the 20 possible 'buckets' of expenses or revenue (columns H to AA). There is one external link which stays disabled 364 days out of the year--I use it at the beginning of January when I create a new spreadsheet to pull in totals from the end of the previous December.
Sorry for all the detail, but I'm wondering if Excel has changed how these fairly simple formulas calculate (maybe from sheet to sheet within the workbook? maybe something with arrays? Maybe in the way I've named some formulas?) that now make my workbook unwieldly.
In April, I wiped the spreadsheet totally clean and recreated it...it started working quickly but by the time I set up all 12 months, it was back to bloated and slow.
So the problem seems internal to this (what used to be simple) spreadsheet. Many thanks for any suggestions!
Jennifer_Ebelhar Here are my findings, so others might learn:
On each sheet there are many, many tiny drawing objects, mostly invisible:
sht, shape count
JAN 2748
FEB 5761
MAR 10831
APR 35689
MAY 17955
JUN 16793
JUL 17453
AUG 13812
SEP 25634
OCT 37359
NOV 52986
DEC 66737Since Excel still tries to draw all of them when a sheet is activated, the file is dreadfully slow.
To get rid of them, follow these steps:
- Select a worksheet
- Press the F5 function key
- Click "Special"
- Select "Objects", click OK
- Hit the Del key on your keyboard (will take a while!)
- Select next sheet, repeat from step 2
These shapes probably came into your sheets by copying and pasting information from sources outside of Excel. Especially web pages are renown for causing this. Next time you paste something from outside of Excel, use Paste Special and have it paste just text.
I tried to do this on a couple of sheets, but Excel finds this very hard. Perhaps your best bet is to recreate the workbook. Create a new file with the same sheet tabs. Then open both the old and the new copy. Copy all cells from each sheet of the old file, select the sheet in the new file and do a paste special, formulas and a paste special formats. That should give you a pristine new file without the shapes.
7 Replies
Jennifer_Ebelhar Here are my findings, so others might learn:
On each sheet there are many, many tiny drawing objects, mostly invisible:
sht, shape count
JAN 2748
FEB 5761
MAR 10831
APR 35689
MAY 17955
JUN 16793
JUL 17453
AUG 13812
SEP 25634
OCT 37359
NOV 52986
DEC 66737Since Excel still tries to draw all of them when a sheet is activated, the file is dreadfully slow.
To get rid of them, follow these steps:
- Select a worksheet
- Press the F5 function key
- Click "Special"
- Select "Objects", click OK
- Hit the Del key on your keyboard (will take a while!)
- Select next sheet, repeat from step 2
These shapes probably came into your sheets by copying and pasting information from sources outside of Excel. Especially web pages are renown for causing this. Next time you paste something from outside of Excel, use Paste Special and have it paste just text.
I tried to do this on a couple of sheets, but Excel finds this very hard. Perhaps your best bet is to recreate the workbook. Create a new file with the same sheet tabs. Then open both the old and the new copy. Copy all cells from each sheet of the old file, select the sheet in the new file and do a paste special, formulas and a paste special formats. That should give you a pristine new file without the shapes.
- A belated thanks. Took a 2012 version and cleared the data out to recreate for 2024. Even that version had a few drawing objects! Guessing they multiplied over the years. 😂 The new spreadsheet is down to 178K from 6.200K and calculating like it should. Will stay vigilant to keep away these 'bugs' I never knew to look for.
- I have seen them! They sometimes show up when I try to drag a fill handle to copy, but it is a darker bold cross, not the normal thin black cross, and I end up with with those objects. How crazy is that? Many thanks....not something I ever would have figured out.
You could run this macro to get rid of all shapes. It shows its progress in the statusbar:
Option Explicit Sub ListShapects() Dim sht As Worksheet Dim shp As Shape Dim ct As Long Application.ScreenUpdating = False Application.PrintCommunication = False For Each sht In Worksheets For ct = sht.Shapes.Count To 1 Step -1 If ct Mod 100 = 0 Then DoEvents Application.StatusBar = sht.Name & ", " & ct Application.ScreenUpdating = True Application.ScreenUpdating = False End If sht.Shapes(ct).Delete Next Next Application.StatusBar = False Application.ScreenUpdating = True Application.PrintCommunication = True End Sub
Jennifer_Ebelhar Can you save a copy somewhere and clear it out (remove all data and other potentially sensitive information but leave in the current formulas)? If you upload it to e.g. OneDrive and share a link with us, we might have a look?
- Will do! Clearing out the spreadsheet takes some time, thanks.
- Hope you've solved the problem?
you can check if the version of the Excel is updated.
