You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
I have seen similar problems myself.
What I also noted, was that the performance hit is far, far worse for Excel 2007, compared to older versions.
good article, but an easier way is to avoid pasting the objects in the first place by using paste special/values which will just give you the text from web site or wherever
hb - sometimes that boogers things up because it may paste everything into column A.
What I've found is easiest is this:
1. paste your data into a sheet
2. copy the whole sheet
3. in another sheet, select whole sheet, paste special / values
4. delete original sheet
Since copying from an Excel range in an open workbook in an Excel session includes sufficient metainformation to allow Paste Link into any range in any worksheet in any open workbook in the same Excel session, it should be possible to tell when the user is pasting from a source other than the same Excel session. How difficult would it be to add an option to make Excel *ALWAYS* display the [external source] Paste Special dialog when the user tries to paste using right-click/Paste, Edit/Paste, [Ctrl]+V or [Shift]+[Insert]? Even better, make that dialog default to plain text?
Is it truly equivalent to rocket science to come up with potentially useful user options like this?
my work demands me the lesson of this kind. and i was very upset that i was not able to cope up with the lesson thought by my seniors. if not because of you i would have been in a soup. thanks