Creating a Dashboard in Microsoft Excel? Paste Your Data as a Linked Picture

Quick Links

• How to Use Excel’s Linked Picture Tool

• Things to Note Before You Start

Excel’s Linked Picture tool lets you take a snapshot of some data and paste it as an image—handy if you’re compiling a dashboard of the key metrics from across your workbook. Unlike normal images, those created using this tool dynamically update to reflect any changes in the source data.

You can only paste data as a linked picture if you’re using a desktop version of Microsoft Excel, not the web-based or mobile versions.

Let’s say you want to create a dynamic copy of this range of regular cells on a dashboard worksheet in your Excel workbook.

The Linked Picture tool can only be used to capture snapshots of a cell or range of cells that aren’t formatted as Excel tables. This is because Excel tables are designed to grow automatically when additional columns or rows of data are added to the edge of the data, and linked pictures can’t expand to accommodate this.

To do this, first, select the data, and press Ctrl+C to copy it.

Next, head to the worksheet where you want to duplicate the dataset as an image. Then, in the Home tab on the ribbon, click the “Paste” down arrow, and select the icon displaying a clipboard, picture, and chain. Alternatively, you can use the Excel keyboard shortcut Alt > H > V > I.

Hover over an option in the Paste menu to see a tooltip containing the name of the feature. That way, you can check that you’re selecting the correct tool.

Because images created using the Linked Picture tool are transparent (if the source cells aren’t color-filled), you should hide the gridlines in the destination worksheet. Otherwise, they’ll be visible behind the pasted picture, making it look untidy and difficult to read. To do this, head to the “View” tab on the ribbon, and uncheck “Gridlines” in the Show group.

Related

Should You Show or Hide Gridlines in Excel?

It depends on what your spreadsheet contains!

Usefully, the linked picture can also be pasted into a different workbook altogether, as long as both the source and destination files are saved in the same location, like in a folder on your computer or in your OneDrive account.

Since the object you’ve just created behaves in the same way as any other picture in a Microsoft application, you can click and drag it to reposition it, use the handles to resize it, and navigate to the “Picture Format” tab on the ribbon to modify its appearance—such as adding a border or special effects.

Before you copy the cells you plan to paste as a linked image, zoom in on your Excel worksheet so that the dataset fills the whole screen. This means you won’t stretch the picture’s pixels if you need to make it larger.

The best feature of the Linked Picture tool is that the resultant image is dynamic, meaning it reflects any value or formatting changes to the source data. Here, when I filled the original dataset’s header row in blue and changed the value in cell B2 from 61 to 81, the linked picture updated accordingly.

Related

6 Microsoft Excel Formatting Habits You Need to Ditch

Tweaking your spreadsheet routines could make life much easier.

What’s more, if you add or remove columns or rows in the center of the source data, the pasted picture reflects this structural change.

You might have to resize the linked picture when you add or remove columns so the data doesn’t appear squashed or stretched.

However, bear in mind that the linked picture can’t expand to pick up columns or rows added to the edge of the data.

Things to Note Before You Start

Before you go ahead and use the Linked Picture tool in Microsoft Excel, here are some important pointers you should bear in mind.

First, the Linked Picture icon only becomes visible in the Paste drop-down menu when you select a cell or range of cells. At all other times, it disappears from the ribbon altogether.

Second, images captured through the Linked Picture tool are stored as enhanced metafile format (EMF) files, as they require a continuous connection between the source data and the resultant image. EMF files are typically larger than standard image formats, like PNG or JPEG, so if you have lots of linked pictures in one workbook, you’ll likely see a decline in the file’s performance.

Related

7 Ways to Speed Up Your Excel Spreadsheets

Don’t twiddle your thumbs waiting for Excel to respond.

Third, don’t use the Linked Picture tool to capture PivotTables, as they are designed to expand and contract to enable quick and easy data analysis and the Linked Picture tool cannot adapt to their dynamic structure.

Finally, you can use the Linked Picture tool to take snapshots of Excel charts by capturing the cells around the edge and cropping the resultant image. However, the best way to create a copy of a chart that dynamically updates to reflect any changes in the data is to simply copy (Ctrl+C) and paste (Ctrl+V) it. That said, copied and pasted charts don’t change if you adjust the original chart’s formatting, design, or properties. In this scenario, simply re-copy and re-paste the chart once you’ve made these visual changes.

Another way to create an image version of some data is by using the Camera tool. It works in a similar way to the Linked Picture tool, though you need to add it to the Quick Access Toolbar before you can use it.

Related

If You Don’t Use Excel’s Hidden Camera Tool, You’re Missing a Trick

Produce a dynamic image of your data.