Todayâs author, Ben Rampson, a Program Manager on the Excel team, talks about cleaning up spreadsheets.
I often receive files demonstrating issues customers are experiencing with Excel. Recently I have noticed a common problem in some of these files that impacts the fileâs performance and size: hidden and invisible objects.
When gathering data in Excel it is common to start with information found in a variety of sources, frequently lists or tables located on the Internet. Customers often copy and paste this data into Excel from the original source, but unfortunately this can also unintentionally paste many additional objects into the spreadsheet besides the data. These objects (shapes, text boxes, controls, etc.) are often not noticeable to the user after the paste, but can result in slower performance and larger file sizes. One step customers can take to avoid unwanted, hidden, and invisible objects in their document is to find and remove the objects after the paste operation as part of their data cleansing process.
Below is an image of part of a data set I copied and pasted into Excel 2007 from a website. The data set had 35 rows of data, but only the first few are shown.
After pasting my data (and extra objects) into Excel.
Looking at this image it is evident that some additional shapes were copied into Excel; an icon is shown in cell A1 and there are checkboxes in many of the rows in column B. I could select these visible objects and delete them, however, I would end up missing some of the objects that have been pasted into my sheet.
The following steps will help you find and delete all of the additional objects in your Excel 2007 worksheet.
The first thing you want to do is verify that you have additional objects on your spreadsheet. The easiest way to view a sheetâs objects is to turn on the on the Selection Pane (on the Home Tab go to the Editing Chunk > click the Find and Select Dropdown > select the Selection Pane option making the pane visible). Looking at the selection pane you will see a list of all objects on the current sheet and an indication if the objects are visible or hidden. If the selection pane is blank then you do not have extra objects on the sheet.
The top of the Selection and Visibility Pane for my sheet. Notice that 100 objects were pasted into my document. I have seen customer files with thousands of hidden objects.
Once you have the selection pane open and verified that you have objects on the sheet, the next step is to put your spreadsheet in Design Mode if it is supported on your current workbook. Design Mode will only be enabled in Excel if your sheet contains certain types of controls. Turning on Design Mode will allow you to select all controls, not just the basic shapes and form controls. To enter Design Mode select the Design Mode button in the Controls Chunk on the Developer Tab. If this button is disabled in the Ribbon then your sheet does not contain the types of controls that require this step. (Note: If you do not have the Developer Tab visible in the Ribbon you can enable it with the following steps: Office Button > Excel Options > check the âShow Developer tab in the Ribbonâ checkbox on the default Popular tab).
You now are ready to select objects using the Go To Special dialog. To select all objects in the sheet: Ctrl+G to open the Go To dialog > select the Special button > select Object > click OK. The objects will then be selected and their selection handles are visible on the sheet.
My sheet with all objects selected. You can see that in addition to the previously visible objects I now also have selected some textboxes previously hidden on my sheet.
At this point you can simply hit the Delete key and remove all of these objects from the sheet. You can also choose to be more selective about the objects you delete; if you have other objects already on your sheet you wish to keep, ctrl+clicking the objects in either the selection pane or on the sheet will remove them from the current selection prior to hitting delete.
With just a little data cleansing work to you can avoid having additional unwanted objects in your document, resulting in faster performance and smaller file size.