Microsoft Office

Excel

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.

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.

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.

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.

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.