Let’s review basic picture links in Excel and then explore alternative solutions to picture link limitations. I’ve also included a fun Dashboard to help Oz du Soleil reassess his wardrobe!
What is a Picture Link?
- it gives you a live view of a cell(s) from any sheet
- changing the contents of the cell(s) changes the picture link
- picture links are commonly used in dashboards
How to create a Picture Link?
- copy a cell (or range of cells)
- on the Excel ribbon select ‘Home’
- under ‘Paste’ select tiny down arrow
- select bottom right icon to paste as a picture link
Excel File
Download my Excel file. It has basic and advanced picture link examples.
Picture Link Limitations & Solutions
(get my Excel file for more detailed instructions and examples)
Picture Link to Excel Table
You can’t directly paste a Table as a picture link. Alternative solution steps:
- Create name range using formula: =INDIRECT(“Table1″&”[#All]”)
- Copy & Paste the Table as a picture. Select it.
- In formula bar type “=” followed by your named range. Press enter.
Picture Link to Excel Chart
- Position your chart to fit within cell E15 (increase row height & col width)
- Add this formula in cell I13 =“PictureLinkToChart”&”!$E$15″
- Create named range using formula =INDIRECT(PictureLinkToChart!$I$13)
- Copy & Paste the chart as a picture. Select it.
- In formula bar type “=” followed by the named range. Press enter.
Slicer selects Tables & Charts for Picture Links
So far your picture link refers to the same table or chart. Wouldn’t it be nice to select from several tables or charts for your picture link?
Instead of hard-coding the formula reference to a single table (or chart) we can use a slicer selection to link to a table or chart in any workbook!
Wardrobe Audit
Oz du Soleil (Excel lover and fashion icon)
Oz wants to audit his wardrobe in Excel. Oz has separate sheets for pants, hats, socks and ties.
The dashboard pulls text, a chart and a table from selected sheet using a slicer. You learn more about linked objects and Oz reassesses his wardrobe!
My Excel file has blue colored sheets for Oz’s dashboard, pants, hats, socks and ties. Audit these blue sheets to learn more!
About Me
I’ve worked as a Data Analyst since 2001. Technical Administrator, databases, some programming (VBA & T-SQL) and of course tons of Microsoft Excel !!!
Check out my Recommended Excel Training and my YouTube channel (only Excel videos!)
Kevin, I found two problems.
The keyword “#All” in Basic_Table_Picture_Link only works for English Excel.
Resizing a table or a chart will not reflect in resizing the picture link. You have to re-do the copy-paste-insert-formula operation.
Hi XLARIUM,
Good point about the language issues with “#All”. Should be a quick change.. And the resizing is definitely an issue. Thanks for reading XLARIUM! What’s new with you?
Cheers,
Kevin
This is hilarious!!!!!
Good tutorial!
I was pretty sure that you would get a good chuckle out of this Oz! Thanks for reading!