Excel Tips & Tricks #468 – Refreshing dealing with objects

This was previously covered in Tip #335 where we explored different methods to tidy up and align objects in Excel.

What is an object?

An object is anything that floats free of the cells in Excel and can be moved or resized independently. They exist in an "object layer" - floating above the cells. It's important to remember that this is different to the way "object" is used in VBA - where it means any element of Excel - to how they work within a worksheet.

Some examples of objects include:

• Images
• Charts
• Word or PowerPoint files
• PDFs
• Form controls
• Text boxes
• SmartArt, shapes, icons, or other visual elements
  
  

How can I insert an object into my spreadsheet?

Toolbar

You can use the Insert tab in the toolbar to insert various types of objects including images, text boxes, files, and shapes.

Link and embed objects

Excel also allows you to link or embed files from other programs, such as Word or PowerPoint, into your spreadsheets. This can be handy if you want to link and reference to other supporting documents or incorporate detailed written text in your spreadsheet.

You can do this via the ‘Object’ option in the Insert tab of the toolbar.

What differs between linked objects and embedded objects is where the data is stored and how the object is updated after you place it in the spreadsheet. For example, embedded objects are stored in the workbook that they are inserted in and aren’t updated. Linked objects on the other hand remain as separate files and can be updated.

So, if you want the inserted file in your spreadsheet to be updated when the data in the source file changes, it’s recommended that you use linked objects.

To link an object, from the Insert tab in the toolbar, click on the ‘Object’ option. This will open up a menu where you can click on the ‘Create from File’ tab.

From here, you can click on ‘Browse’ and select the file you want to link. Make sure you select the ‘Link to file’ checkbox and click ‘OK’.

In the example above, because I left the ‘Display as icon’ option unchecked Excel will display the first page of the file.

Remember, if you are sharing your workbook with others, if they don’t have access to the linked file, the object will break for them. If this will be an issue for you, or you don't want the file to be updated when it changes in the source file or need the file to be included within the workbook as a single ‘package’, it’s best to use an embedded object. This way, only the inserted version of the file is embedded in the workbook.

The steps are the same as above, however the difference is the ‘Link to file’ option should be left unchecked.

If you want to insert an icon into the spreadsheet instead of the contents of the file, make sure you select the ‘Display as icon’ checkbox and click ‘OK’.

The embedded file displayed as an icon will look like this.

You can open both linked and embedded objects by double-clicking on them.

Create new object in Excel

Excel also allows you to create a new object from inside the spreadsheet. For example, you can create an embedded document, such as a Word or PowerPoint file, in Excel.

To do this, from the Insert tab in the toolbar, click on the ‘Object’ option. This will open up a menu where you can click on ‘Create New’ tab.

In this tab, you will see a list of the programs you can use to create a new object. For this example, we will create an object using Word.

You can again either set your object to be displayed in the spreadsheet or as an icon that opens the file.

The result is this! An object created using Word that I can work in and edit in my spreadsheet.

How can I tidy up objects?

When inserted, objects aren't bound to cells by default so they can end up being in the wrong place or be tricky to make visually appealing. However, there are ways to adjust how the object is moved and sized in relation to cells via the object properties which you can access by clicking on the object and using CTRL + 1 to open up the ‘Format’ menu.

From this menu, the ‘Size and Properties’ tab will display the following options:

• Move and size with cells: If you resize the cells around the object, or insert rows or columns, the object will move along with the cells. When you resize cells that the object covers (or insert rows/columns in that range of cells), the object’s size will also change. For most shapes, this is the default option.
• Move but don’t size with cells: The object will move if the cells around it move, but its size won’t change regardless of resizing or insertion of cells in the range that it covers. For pictures and other objects, this is the default.
• Don’t move or size with cells: The object will remain the same size and in the same position, unaffected by any changes to cells on the worksheet.

What is not made clear is that this setting also controls the behaviour of the objects when using the cut/copy and paste functions or moving a block of highlighted cells. If objects are set to move with cells (either of the top two options), then when you cut or copy the cells that the object sits in front of, the object is copied too:

In some situations, there can be too many objects, or they can be too small, to select one by one. To select multiple objects, on the Home tab of the toolbar you can go to ‘Find & Select’ and choose ‘Select Objects’ to change the cursor into a tool to select all objects in an area of the screen by clicking and dragging. Once done hit the escape key to return to the normal cursor mode.

In some cases, you may want to control a number of individual objects – for this we can use ‘Selection Pane’ in the Page Layout tab of the toolbar to open up this menu.

From here we can easily select the items, choose their stacking order within the objects layer, or hide them. We can also rename the objects which may help with object management.

It’s also important to know how to get different objects properly lined up. To do this, we can select two or more of the objects, then use the ‘Align’ option in the Page Layout tab of the toolbar. There are also other options including ‘Snap to Shape’ and ‘Group’ that make it easier to align multiple objects. More on this has been covered in Tip #335.