While many people know how to copy and paste data in Excel, few are aware of the program’s ability to duplicate data as a dynamic picture that can be resized, reformatted, and moved to an appropriate position.
Both tools are ideal when you’re creating dashboards or reports in Excel, as they allow you to duplicate key information in the form of an image. What’s more, because the pictures they produce are linked to the original data, they update to reflect any changes. However, although these tools serve the same purpose, there are some key differences that you should bear in mind.
The Camera and Linked Picture tools are only available in desktop versions of Microsoft Excel, not Excel for the web.
One of the biggest differences between the Camera and Linked Picture tools is where you’ll find them in Excel.
Finding the “Hidden” Camera Tool
The Camera tool—available in most desktop versions of Excel, including the earliest—is commonly known as the “hidden Camera tool,” as it’s not immediately accessible on the ribbon. As a result, you need to add it to your Quick Access Toolbar (QAT).
To do this, first, activate the QAT by right-clicking any tab on the ribbon and selecting “Show Quick Access Toolbar.” Then, click the QAT down arrow, and select “More Commands.”
Next, in the Excel Options dialog box, head to the Choose Command From menu, and select “Camera.” Then, click “Add” to add it to the QAT, and click “OK.”
Now, the Camera tool is added to your QAT. Even though locating and adding the tool initially takes a bit of time, once added to your QAT, it’ll stay there until you remove it manually.
Related
6 Quick Access Toolbar Must-Haves in Microsoft Excel
Perform your most valuable Excel commands with a single click.
Finding the Linked Picture Tool
The main thing to note about finding the Linked Picture button in versions of Excel from 2007 onwards is that it’s only visible when you’ve selected and copied something that can be pasted as a dynamic image. At all other times, it disappears.
Providing the above criteria are met, the Linked Picture tool can be found 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.
Related
The Best Excel Keyboard Shortcuts I Use as a Power User
The key to excelling in your spreadsheet work.
Capturing Regular Cells
Excel’s Camera and Linked Picture tools can both be used to take snapshots of a cell or range of cells that aren’t formatted as tables. That said, the images they produce differ slightly in their appearance.
Capturing Cells With the Camera Tool
To use the Camera tool to capture a range of cells that aren’t formatted as an Excel table, select the data, and click the Camera button on the QAT.
Next, head to the worksheet or other workbook where you want the snapshot to be located, and click once to produce it. Then, click and drag the picture to move it, and use the handles around the edge to resize it.
As you can see in the screenshot above, the pasted image is surrounded by a border, and the image background is opaque (in other words, you can’t see any cells behind it).
To improve the quality of the snapshot picture, zoom in on your Excel spreadsheet before you click the Camera tool, so that the data you’re about to capture fills the whole screen. This saves you from stretching the pixels if you make the picture bigger. You can then reduce the size of the image once you’ve generated it.
Since the snapshot is in the form of a picture, when you select it, the contextual Picture Format tab appears on the ribbon, where you can adjust the image’s appearance and properties.
If you make any changes to the original cells, such as their formatting or their contents, they’re reflected in the picture snapshot. Here, when I colored cells A1 to C1 light blue, changed the currency in column C from dollars to pounds, added a hashtag in cell A1, and changed the value in cell C8 to 66,208, these adjustments were reflected instantly in the image.
If you insert a new row or column in the middle of the original data, the picture duplicates this structural change.
However, if you add data to the row below or column to the right, the image doesn’t pick it up.
On the other hand, if you delete rows or columns in the source data, the picture reflects these changes.
Capturing Cells With the Linked Picture Tool
To take a snapshot of a range not formatted as an Excel table using the Linked Picture tool, select the data, press Ctrl+C to copy it, and click “Linked Picture” in the Paste drop-down menu.
Now, select the cell in the worksheet or other workbook where you want the pasted picture to be positioned, and press Ctrl+V. As with an image created using the Camera tool, you can move, resize, and format it like any other picture.
Where pictures generated through the Camera tool are opaque and contain a border, images produced through the Linked Picture tool are transparent (if the original cells aren’t color-filled) and borderless. As a result, when you use this tool, you should uncheck “Gridlines” in the View tab on the ribbon of the destination worksheet so they don’t get in the way.
In exactly the same way as images created through the Camera tool, linked pictures are dynamic, meaning they adjust to mimic any value or formatting changes to the source data.
Linked pictures also behave in the same way as pictures produced using the Camera tool when you add or remove columns or rows—they adjust to columns or rows added in the center of the data (not the edge), and they’ll reflect the structure of the source data if you remove columns or rows.
Capturing Formatted Excel Tables
The biggest difference between the Camera and Linked Picture tool—and probably the factor that swings the balance in favor of the former—is revealed when you want to take a snapshot of a formatted Excel table.
Related
Everything You Need to Know About Excel Tables (And Why You Should Always Use Them)
This could totally change how you work in Excel.
Capturing Formatted Tables With the Camera Tool
The Camera tool works in exactly the same way as it does on unformatted cells when used to capture formatted tables.
However, before you go ahead and use the tool, decide whether you want your table to have a total row, since Excel can’t add these to the picture retrospectively. To add a total row, select any cell in the table, and in the Table Design tab, check “Total Row.”
Then, select the total cell, and click the down arrow to choose how your data is aggregated.
Now, your table is ready to be selected and captured using the Camera tool.
Did you notice that the filter buttons—which are added by default when you format your data as a table—have also been duplicated in the snapshot? These could cause confusion, because they’re not clickable in the picture.
The best way to overcome this is to only display the filter buttons when you want to use them. When you unhide them by unchecking “Filter Button” in the Table Design tab, they also disappear from the image version of the data.
Also, since pictures created using the Camera tool are formatted with a border, they look odd when the copied table also has a border. So, select the image, and in the Picture Format tab, click “Picture Border > No Outline.”
Much like when you use the Camera tool to capture regular cells, you can add columns and rows to the center of the data, and the picture will pick these up. Similarly, if you delete columns or rows, this action will be reflected in the image.
If your table has a total row, this acts as a buffer at the edge of the picture. As a result, if you click and drag the table expander handle in the bottom-right corner to add extra rows, the picture will pick up these new rows.
As well as updating to match any changes to the data, the image will also reflect any adjustments you make to the table’s style, such as switching the design or adding or removing the banded rows feature. The same applies to any sorting or filtering you apply to the table.
Capturing Formatted Tables With the Linked Picture Tool (You Can’t!)
If you copy a table and go to paste it as a linked picture, you’ll notice that the button isn’t there.
This is because Excel tables are designed to grow automatically when additional columns or rows of data are added, and—as we discovered in the section above—linked pictures can’t expand to include extra columns or rows tagged onto the edge of an existing dataset.
Memory Consumption
Images captured through the Camera and Linked Picture tools 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, such as PNG or JPEG.
As a result, having lots of linked images in your Excel workbook or capturing large ranges will likely result in performance issues.
Related
7 Ways to Speed Up Your Excel Spreadsheets
Don’t twiddle your thumbs waiting for Excel to respond.
To compare the memory consumption of these two tools, I generated three snapshots of a range of data that is six columns wide and six rows down and reviewed their file size. The workbook that used the Camera tool was 19KB, and the workbook that used the Linked Picture tool was 16KB. So, even though this difference seems minimal, when you scale this up to large workbooks with lots of linked images, the Camera tool proves noticeably more resource-intensive.
Capturing Other Objects
While you can technically use the Camera and Linked Picture tools to create pictorial versions of other objects in Excel, there are good reasons why you don’t need to or shouldn’t.
Capturing Charts
In most cases, if you want to create dynamic copies of charts in Excel, you don’t need to create linked images of them—you can simply copy (Ctrl+C) and paste (Ctrl+V) them into another worksheet or workbook, and any changes made to the source data will be reflected in both versions.
Related
The 10 Most Common Excel Charts and What They’re Used For
Choose the best way to visualize your data.
That said, copied and pasted charts don’t change if you adjust the original chart’s formatting, design, or properties. In this scenario, the best bet is to re-copy and re-paste the chart once you’ve made these visual changes.
If you still prefer the idea of using the Camera and Linked Picture tools to capture charts, select the cells around the edge of the chart, activate the tool, and use the Crop tool in the Picture Format tab to cut out the surrounding from the image.
Capturing PivotTables
It’s surprising that Excel lets you capture PivotTables using the Camera and Linked Picture tools, as PivotTables are specifically designed to expand and contract to enable quick and easy data analysis.
Related
How to Use PivotTables to Analyze Excel Data
Use this powerful tool to crunch your numbers.
Here, when I click the “+” buttons in the PivotTable to expand the subrows, the images created through both the Camera and Linked Picture tools fail to expand to fully show the extra data.
As a result, I’d recommend you avoid creating linked pictures of PivotTables—instead, duplicate them using Ctrl+C and Ctrl+V, and press Ctrl+Alt+F5 to refresh them all whenever there are any changes to the source data.
Overall, when deciding whether to use the Camera tool or paste your data as a linked picture, several factors come into play. Here’s a summary of their main characteristics:
Characteristics |
Camera Tool |
Linked Picture Tool |
|---|---|---|
Availability | Most desktop versions of Microsoft Excel, including earlier releases | Desktop versions of Excel post-2007 |
Accessibility | Can only be accessed after it’s added to the QAT | Easily accessible through the Paste down arrow |
Picture format | Opaque and bordered | Transparent and borderless |
Compatibility | Works with regular cells and formatted tables | Works with regular cells only, not formatted tables |
Cell content changes | Reflects changes in the source data’s content and formatting | Reflects changes in the source data’s content and formatting |
Structural changes | Reflects new columns or rows added to the center of the source data, and columns or rows removed from anywhere in the source data | Reflects new columns or rows added to the center of the source data, and columns or rows removed from anywhere in the source data |
Memory consumption | Stores pictures as resource-intensive EMF files, and uses more memory than linked pictures | Stores pictures as resource-intensive EMF files, but uses less memory than images created through the Camera tool |