Someone complained that one of his Excel workbooks took very long to open. The file size was 10MB and it will take about 3 mins to open up. There are 20 Excel worksheets in this Excel workbook. I went through all the worksheets and all of them were just texts and some form checkbox controls. There were no formula, picture, macros and VBA code. The file size seems too big for such a simple Excel workbook. I tried open and repair the Excel workbook but it did not help.
Then I did a print preview on one of the worksheets and I got the following warning message.
“The operation you are about to perform affects a large number of cells and may take a significant amount of time to complete. Are you sure you want to continue?”
This gave me the clue to what might have been the cause of the problem. There might be some texts or objects hidden at the extreme right or extreme bottom of the worksheet. I changed the view from “Normal” to “Page Layout” (click Page Layout on the View ribbon). I was shocked to see that there were 32,855,202 pages on just one worksheet.
I kind of suspected it was the checkbox that were causing the problem. By opening up the “Selection Pane…” showed that my guess was right.
There were 22,528 checkboxes on the Excel worksheet when there are supposed to be only 22!
After I have determined that 12 out of 20 worksheets were having this problem, I need a way to delete those hidden checkboxes.
I used the “Select Objects” and “Go To Special” functions to find and delete the hidden checkboxes. It might not be the most efficient way but it works. Here is how I did it.
From the “Home” ribbon, click on “Find & Select” and select “Select Objects”.
After that, select “Go To Special…”.
Select “Object” on the “Go To Special” windows and click the “OK” button.
This selects all the checkboxes on the worksheet. I will have to manually unselect the 22 checkboxes that I wanted to keep by holding down the “Ctrl” key and click on each of the 22 checkboxes. Once I have unselected the 22 checkboxes, just hit the “Delete” key and all the other hidden checkboxes will be deleted.
After clearing all the hidden checkboxes, the file size was reduced from 10MB to 500KB and it took just a few seconds to open it.
No comments:
Post a Comment