Better Dashboards with the Linked Picture Feature

One of the challenges with dashboards and reporting in Excel is the formatting of its components. Combining charts, tables and text of various size requires fine tuning of row heights, column widths, fonts and alignment of multiple objects. Often this is difficult and painstaking process that need to be redone every time a new requirement comes in and a piece of the dashboard needs to be added, removed or reformatted. A cure for saving a good portion of it comes with the use of linked picture feature in Excel.


What it is and how to create one
Linked picture, also known as Camera Snapshots in earlier versions, has been part of Excel for a while but it has always been under the radar. It is a picture that is linked to the content of a sheet - it could be cells, charts, anything. What you see is a picture with all the properties and tools available for it in Excel and this picture shows everything that goes on with the range it is linked to - content,  format, etc. When something in the spurce range changes, the picture is automatically updated. To create one do as follows:
- Select the cells in the sheet you like to have picture of
- Go to the target sheet/cell
- From home ribbon select Paste> Linked Picture (under Other Paste Options) or right-click-menu > Paste Special> Linked Picture

The resulting picture could be cropped to remove unwanted parts from view and formatted and resized as you like with the tools available for a picture.

Why is it good
Linked pictures could be formatted uniformly on a dashboard, they are also easy to resize and align. I find it very useful when I have to combine on a sheet text of various sizes and lengths, charts of different types and sizes and other elements. It allows greater creativity with charts as some charts requirements are easily done with combining few charts into one without the formatting and moving nightmare that follows when putting together in a dashboard. Example for such charts are star-rating chart or chart with dynamic categories on the vertical axis - they are easily done combining few elements but are difficult to handle in a dashboard.

What it is good for
Linked picture is good for
- dashboards and reports with mixed and nonuniform content
- micro-charts -  like charts that have to appear in a cell. Create the chart normally, create linked picture to it and resize to fit the cell
- showing dynamic pictures - assign a conditional range to the picture and change it depending on a cell value.

It is not all that good but is manageable
Linked picture comes with a price - it slows down the workbook. The specific number depends on the workbook - number and type of formulas in it, data size, presence of other processor-time-eating objects, etc. In my experience, linked pictures could be one too many to turn your perfect excel file into not so perfect one. So do not overuse this feature. It is also a matter of design, e.g. a dashboard that needs too many of these maybe worth redesigning. Use linked picture only when you really have to, no matter how tempted you are. There is a workaround for the cases when it is necessary to use this feature and performance is poor. The gist of the workaround is in linking and un-linking the picture. Instead of picture directly linked to the cells, assign it to a conditional named range that is defined as something like =IF(LinkPics=1,RangeOfCells,""). Here LinkPics is a named range with assigned 1 when you need pic to be updated and 0 otherwise and RangeOfCells is the range containing the object to include in the linked picture. This technique allows to have a linked picture only when it needs to be updated and just a picture when just a plain picture the other time. This is a method that cover most of the situations but there are some when  it does not work due to some Excel bugs, e.g. I found it does not work well in combination of a combo box. For this case and in general the links could be updated with piece of VBA code toggling the FORMULA property of the picture to pointing to the source and then to nothing, e.g.
- link to source range: Worksheets(1).Pictures("PicureName").Formula = "='Source Sheet'!$GA$10:$M$54"
- unlink:  Worksheets(1).Pictures("PicureName").Formula = ""
Keep the pictures unlinked and the performance would not suffer. It is not that convenient with many linked pictures but is manageable with keeping list of sources and pictures.
An unpleasant side effect of the link-unlink method is the picture could resize itself in the process and special care should be taken to counter that. It does not happen in all implementations and the cure is not difficult. Overall, these methods work fast and help keeping the good performance of the workbook.


Linked picture is a great tool in the arsenal of tools for better presentation of data in Excel. Use it carefully and you will be rewarded. I have not included detailed description of how-to but I believe Excel users are quite savvy and quickly find how to do things, especially when hinted and directed.