Tag Archives: Paste Special Linked Picture

The Excel Camera Tool, Part 2

Excel, Office, , , , , , ,

Create a Dashboard Using Camera Tool

We saw in Part 1 how to add the Camera tool to the Quick Access toolbar and how to use it for capturing linked pictures that automatically update when the source data is changed.

Another great use for the Camera tool is to create dashboards. The pasted objects can be sized proportionately and positioned anywhere on a worksheet. Here’s an example of sales figures and a chart:

Dashboard Example

This can contain as many objects as you choose. Note that the location of the original data is displayed in the Formula Bar.

Arranging as images on a worksheet in a dashboard-like mode is also a great fix for printing all your related information on one page. If you had selected all your original data separately, even from the same worksheet, the Add to Print command would have to be used which would automatically print each addition on a separate page. This way, you can add images, move, size and print at will.

The Camera tool captures everything as an image, including values, color formatting, shapes, even the gridlines. Because it is an image, the Picture Tools | Format tab is displayed on the ribbon so anything you can do to a picture, you can do here, including sizing and rotating. Use the right click menu to quickly perform commands such as Crop:

Moving Original Data

The Formula bar displays the path of the original data and the cell references are absolute by default. This is terrific because if the original data gets moved to another area, your pasted pictures reflect that, and continue to display any updates. Should be automatic but be sure that the workbook name (if different) and worksheet name display as well as cell references. If your workbook and worksheet are named, could look like this:

The Paste Special Option

The Paste Special Linked Picture option is available in Excel versions 2007 and above. If you prefer Paste Special, you can access it from the drop down arrow on the Paste icon in the Clipboard group or by right-clicking over desired destination cell and mousing over the Paste icons:

Paste Special Linked Picture

Tip: If you like keyboard shortcuts, press: ALT-H-V-I to paste a linked image.

Paste Special Linked Picture and Camera Tool Restrictions

Not too many downsides to using the Camera tool but here’s some cautions:

  • Some users say that it does not work with Tables; that the data must be converted to a range, but I have not experienced that with newer versions. The table copies, pastes and updates.
  • If using formulas such as IF function with Camera tool, you must use Named Ranges, rather than cell references to maintain the update connection.

Note: The Copy command in the Clipboard group on the Home tab, contains a Copy as Picture… option. This is handy but know that it pastes an image but not a link. Use when you just want a snapshot of your data.

Grab the post for Part 1 here: Camera Tool Part 1

See – don’t even have to be a photographer! Take some pictures and play with dashboards. What did you create?

Use the Excel Camera Tool to Combine Objects from Several Workbooks

Excel, Office, , , , , ,

Uses for the Camera Tool

There is a little known spiffy tool that has been available for a long time in Excel which allows you to take screenshots of data from multiple worksheets or workbooks and paste them in a separate workbook as objects with links to their original locations. This can include ranges, tables or charts. Even better, if the original object updates, so does the linked object on your “collector” worksheet.

Here’s a short video to give you a quick overview of what the Excel Camera tool can do for capturing data and objects from different areas:

Collect desired data on one worksheet

For instance, you want to know the sales or prices from workbooks saved in different locations. You have figures for sales reps and a corresponding chart in Workbook A, and expenses that you want to track in Workbook B. Someone else may be updating the data but because anything you copy and paste with the Camera tool is pasted as a linked picture, any changes made to original data will auto update your screenshots.

Print collected objects on one page

This is also a great way to collect different areas of the same or separate worksheets or workbooks for printing a variety of data on one page as you can resize and move the different objects anywhere on the worksheet.

Add Camera Tool to Quick Access Toolbar (QAT)

First things first…The Camera is not available on the Ribbon by default so needs to be added to the QAT with the Customize Quick Access toolbar command:

  • Click the drop down arrow at end of Quick Access Toolbar. Choose More Commands… (or right click on the Ribbon)
  • Choose All Commands from drop down arrow next to Choose Commands from:
  • Scroll down the alphabetical list and click Camera.
  • Click Add button to add to Quick Access toolbar.
  • Click OK button at bottom of dialog box to place the Camera icon at the end of the QAT.

How to Capture a Screenshot

Select a range, table or chart to activate the Camera tool. Note, if selecting a chart, select the cell above the top, left border of the chart and draw around it. When you release the mouse, the “marching ants” will be around the object as if you had used the Copy command:

  • Go to your destination; usually in another worksheet or workbook.
  • Click in the desired cell location and the linked picture will auto insert.
  • Move and size the object(s) as desired.

Similar Feature with Paste Special Linked Picture

The newer versions of Excel (2007+) have another feature which behaves the same as the Camera tool – the Paste Special Linked Picture. I still prefer the Camera tool as just clicking on the desired destination cell pastes the linked image, all ready for sizing and relocating but both work.

The Camera tool can also be used to create Dashboards. We’ll cover that and some of the other options in an upcoming blog.

Take a picture and let me know what you think!

Want more ways to use the Camera? See Part 2: Camera Tool Part 2