Contextures

Home > Structure > Worksheets > Objects

Excel Worksheet Objects

How to add objects, such as shapes and images, to a worksheet. If you copy data from a website, objects might also be copied. See how to list the objects on a sheet, or select and delete them.

Tip: To fix the Excel error, Cannot Shift Objects Off Sheet, go to the Excel Shift Object Error page.

selection pane list

Objects on a Worksheet

If you copy data from a website, and paste it into Excel, a few objects from the website might also be copied to your Excel sheet. In the screen shot below, there is a "Top of Page" icon -- one of several that was copied along with the data.

This tutorial explains how to find the objects, select them, and quickly delete them.

object copied from website

Video: Show List of Objects on Worksheet

Watch this video to see how to show a list of objects, and select and delete the objects. Written instructions are below the video, along with the full video transcript.

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Transcript: Quickly Delete Objects and Shapes on Excel Worksheet

Introduction

If you copy data from a website into Excel, you might end up with some little images that you don't want.

Perhaps there are some buttons from the website, or little icons that you would click on that site, to go to the top of the page.

Copy Data to Excel

So we'll see how to get rid of those images very quickly in Excel.

So here's a page on the Microsoft website -- there's a list of keyboard shortcuts

  • So I've selected all that. I'm going to copy it into an Excel file.
  • With all of that selected, I'll go to Edit, Copy
  • And then Paste Data to Excel
  • I'll switch to Excel
  • I'd like to paste it here.
  • So with this cell selected, I click the Paste button

We can see all the details that copied over very nicely

  • I'm just going to turn on Wrap, and then turn it off
  • And that will take the Wrap Text out of all the cells

But we can see here, a little icon that was copied from the web page

There are probably others. If I scroll down, I might see a few more. So there's another one.

Select Objects

To see a full list of the objects that have been copied,

  • I'll go to the Home tab
  • And at the far right, click Find & Select
  • Click Selection pane
  • And that opens up a list here

And I can see all the pictures

So there are four pictures that were copied from the website onto this page

If I click on one of these, it would select that object

So if I click here, picture 6, I can see that this object has been selected

Then I could just use the Delete key on my keyboard to get rid of that one

Now if there are lots of images, and you want to get rid of them all, it would take quite a while to go through, and select each one from this list, and delete it

So, I'm going to hide the list. I'll just click this X to close it.

Select All Objects

To select all of the objects, if I want to get rid of all of them:

  • I'll go back to Find & Select
  • Go to Special
  • And this opens up this dialog box, and I can select different things on this worksheet
  • I'm going to select objects

When I click OK, that object is selected, and if I scroll back up to the top, I can see this one

So everything is selected now, and I'll just have to press the Delete key on my keyboard

That gets rid of them all

Check the Results

And just to verify, I'll go back to Find & Select

Open the selection pane, and there aren't any more objects on the worksheet

For more Excel tips and tutorials, please visit my Contexture website at www.contextures.com

Show List of Objects on Worksheet

To see a list of the objects that are on the active worksheet, you can open the Selection Pane:

  1. On the Excel Ribbon's Home tab, in the Editing group, click Find & Select
  2. In the drop down list of commands, click on Selection Pane

    selection pane command

Excel Selection Pane

The Excel Selection pane opens, and usually it is docked at the right side of the Excel window.

To change the Selection pane's size or position, follow these steps:

  • Width: To adjust the width of the Selection pane, point to its left border. When the pointer changes to a two-headed arrow, drag left or right, to make the pane wider or narrower.
  • Position: To change the pane's position, point to the top of the pane, where its name is shown. When the pointer changes to a four-headed arrow, drag the pane to a different location.
    • You can drag the pane onto the Excel worksheet, close to where you're working
    • OR, drag the pane to the far left or right of the Excel window, until it snaps into place (docked). This usually happens when the centre of the pane is over the edge of the Excel window.

Select a Shape

In the Selection pane, you can see a list of all the shapes (objects) on the current worksheet.

To select a shape, or multiple shapes, follow these steps:

  • In the list of shapes, click on one of the object names.
    • That shape will be selected on the worksheet.
  • To select an additional shape, press the Ctrl key, and click on another object's name
    • Both shapes are selected on the worksheet
  • To toggle a shape's selection on or off, press the Ctrl key, and click on the object's name

selection pane list

Select All Objects

To quickly select all the objects on a worksheet, you can use the Go To Special command.

  1. On the Ribbon's Home tab, click Find & Select
  2. Click Go To Special

    object copied from website

  3. In the Go To Special window, click on Objects, and click OK

    object copied from website

  4. All the objects on the worksheet will be selected.

Delete Selected Objects

After you select all the objects on a worksheet, or select a single object, you can delete it.

  1. On the Ribbon's Home tab, click the Clear command
  2. Click Clear All, to delete all of the selected objects.

object copied from website

Macros - Delete All Shapes

Here are two macros that will delete all of the shapes on an Excel worksheet.

These macros can save you time, but use them with caution -- read the warning below!

  • Warning: Be sure to make a backup copy of your Excel file before running either of these macros. You cannot undo the worksheet changes made by an Excel macro.

Active Sheet - Delete All Shapes

This macro deletes all shapes on the active sheet only. It does not affect any other sheets in the workbook.

Warning: Be sure to make a backup copy of your Excel file before running this macro.

Sub DeleteShapesSheet()
'deletes all shapes on active sheet only
Dim iSh As Long
With ActiveSheet
  For iSh = .Shapes.Count To 1 Step -1
    .Shapes(iSh).Delete
  Next
End With
End Sub

All Sheets - Delete All Shapes

This macro deletes all shapes, on ALL sheets in the workbook.

Warning: Be sure to make a backup copy of your Excel file before running this macro.

Sub DeleteShapesAllSheets()
Dim ws As Worksheet
Dim iSh As Long
For Each ws In ActiveWorkbook.Worksheets
  For iSh = ws.Shapes.Count To 1 Step -1
    ws.Shapes(iSh).Delete
  Next
Next ws
MsgBox "Done"
End Sub

Create Macro Buttons with Shapes

Instead of using boring grey buttons, you can use colourful shapes to run macros on a worksheet.

For example, you can insert a rounded rectangle on a worksheet, then colour it to match your workbook's colour scheme.

Add Shape to Worksheet

  • On the Ribbon's Insert tab, click Shapes, then click the shape that you want to use as a button.

    object copied from website

  • Next, click on the worksheet, where you want the top left corner of the button to appear.

Shape on Worksheet

After you click, that shape will appear on the worksheet, in its default size.

  • The shape is automatically selected, and handles appear on its side and corners.
  • The shape's name appears in the Name Box, at the left of the formula bar.

In the screen shot below, the shape's name is Rounded Rectangle 1

rounded rectangle shape with name in Name Box

Change Shape Style

To make the shape look more like a button, instead of a flat rectangle, you can add a Shape Style:

  • First, select the shape
  • Next, on the Excel Ribbon, under Drawing Tools, click the Format tab
  • NOTE: To select a shape after a macro has been assigned, right-click on the shape.

object copied from website

Style Gallery

  • In the Shape Styles section, click the More button, to open the gallery of styles.

object copied from website

  • Then, click on one of the Style options, such as Intense Effect. It has a slight shadow, which gives it a 3D effect.

object copied from website

Bevelled Theme Setting

If you plan to make several shapes in the workbook, and want them all to have a Shape effect, such as Bevel, you can change one of the Theme settings.

  • On the Ribbon, click the Page Layout tab
  • In the Themes group, click Effects
  • Click on the Theme effect that you want to use.

In the screen shot below, I clicked on the the Office 2007-2010 option.

object copied from website

Theme Effect in Style Gallery

After you apply a Theme Effect, go back and look at the Style Gallery.

In the bottom row, the shapes should show the effect that you applied.

For example, in the screen shot below, the buttons have a bevelled effect, like the styles had in older versions of Excel.

object copied from website

Add Text to Button

To make it clear what the button does, add some text. In this example, the button will run a macro that toggles the column headings, from numbers, to letters, or letters to numbers.

To add text to the button, follow these steps:

  • First, select the button
    • Tip: To select a shape after a macro has been assigned, right-click on the shape.
  • Type the text for the button
  • Click the button's border, to select the button again (This will take you out of the Text Editing mode, where you can see the cursor.)

Format the Button Text

Next, follow these steps to format the button text:

  • With the button selected, use the tools on the Ribbon's Home tab, to make the text bold, larger size, centered, or any other formatting.
  • Click on the worksheet, away from the button, to deselect it.

object copied from website

Make the Button Run a Macro

To make the macro run a macro that has been stored in the workbook:

  • Right-click on the button, and click Assign Macro

object copied from website

  • In the list of macros, click the one that you want the button to run, then click OK

object copied from website

NOTE: To select a shape after a macro has been assigned, right-click on the shape.   🔼

Error - Cannot Shift Objects Off Sheet

In Microsoft Excel, if you try to hide or insert worksheet rows or columns, or if you try to apply a filter, you might see this Excel error: Cannot Shift Objects Off Sheet. Or, in some versions of Excel, you'll see this error: Can't push objects off the sheet.

A simple shortcut -- Ctrl + 6 -- fixed this problem for me, and there are more suggestions on the Excel Shift Object Error page.

This short video shows the Excel error, Cannot Shift Objects Off Sheet, and how you can try to fix the problem.

Video Timeline
  • 0:00 Introduction
  • 0:44 Open Excel Option Settings
  • 1:00 Advanced - Display Options Workbook
  • 1:17 For Objects, Show: All
  • 1:25 Test the Option Change
  • 1:38 Shortcut to Change Option

Get the Sample File

Macro Button: Get the sample file for the Macro Button With Shapes example. The zipped file is in xlsm format, and contains macros.

More Tutorials

Excel Data Entry Tips

Worksheet Tips

Worksheet Macros

FAQs - Excel Application and Files

 

 

Last updated: January 8, 2024 7:01 PM