Forum Discussion
Excel spreadsheet has grown larger and slower each year, hangs up with every new entry
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.
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 66737
Since 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
