Today I want to introduce a new excel feature to you, called as Picture link.
Well, picture links are not really new, they are called as camera snapshots in earlier versions. They provide a live snapshot of a range of cells to you in an image. So that you can move the image, resize it, position it wherever you want and when the source cells change, the picture gets updated, immediately.
What is the use of Picture Links or Camera Snapshots?
At the outset picture links may seem like a useless feature. But they are pretty powerful. Here are few sample uses for picture links:
- In dashboards & reports: Usually in dashboards, we need to combine charts, tables of data, conditional formatting etc., all in one sheet. When the size of these are not uniform, aligning them on output sheet could be a huge pain. This is when you can use picture links. First create the individual portions of dashboard in separate worksheets. Then, embed picture links to these portions in final dashboard. Re-size them and align as you see fit.
See this in action: Project management dashboard in excel
. - In micro-charts & sparklines: While Excel 2010 has native support for sparklines and other micro-charts, if you want to create a micro-chart in earlier versions of excel you have to use trickery. This is where picture links can help. You can make a regular chart and take a picture link of that. Then resize the picture so that it fits in to a small area.
See this in action: Micro-charts using camera tool.
- In Dynamic Charts: Since picture links are nothing but images with a formula assigned to them, you can easily construct dynamic charts & dynamic dashboards using these.
See this in action: Dynamic chart using camera tool, Dynamic dashboard using camera tool
- In shared workbooks: When you share a workbook with a colleague or boss, a common worry is what if they change formulas or edit something. This is where picture links can be of great use. You can embed a picture link of actual data so that no one can edit it.
How to insert a picture link in Excel – 3 step tutorial:
To insert a picture link to your data, just follow these 3 steps:
- Select the cells. Press CTRL+C
- Go to a target cell. From home ribbon select Paste > As picture > Picture link option (see image below)
- That is all. Your picture link is live. Move it or play with it by changing source cells.
Do you use Picture Link / Camera Snapshot ?
I have been a fan of picture links / camera snapshots ever since I learned about them. I have used them in various dashboards, reports, workbooks to wow my clients, bosses and colleagues. However, one problem with picture links / camera snapshots is that, they do not print well. So I avoid using them for workbooks that get printed alot.
What about you? Do you use picture links often? Share your experience, tips and ideas using comments.
Read more quick tips to become awesome in excel, in less than a minute.
PS: Donut to Hui for telling me about Picture links feature.
12 Responses to “Analyzing Search Keywords using Excel : Array Formulas in Real Life”
Very interesting Chandoo, as always. Personally I find endless uses for formulae such as {=sum(if(B$2:B$5=$A2,$C$2$C$5))}, just the flexibility in absolute and relative relative referencing and multiple conditions gives it the edge over dsum and others methods.
I've added to my blog a piece on SQL in VBA that I think might be of interest to you http://aviatormonkey.wordpress.com/2009/02/10/lesson-one-sql-in-vba/ . It's a bit techie, but I think you might like it.
Keep up the good work, aviatormonkey
Hi Chandoo,
You might find this coded solution I posted on a forum interesting.
http://www.excelforum.com/excel-programming/680810-create-tag-cloud-in-vba-possible.html
[...] under certain circumstances. One of the tips involved arranging search keywords in excel using Array Forumlas. Basically, if you need to know how frequent a word or group of keywords appear, you can use this [...]
@Aviatormonkey: Thanks for sharing the url. I found it a bit technical.. but very interesting.
@Andy: Looks like Jarad, the person who emailed me this problem has posted the same in excelforum too. Very good solution btw...
Realy great article
"You can take this basic model and extend it to include parameters like number of searches each key phrase has, how long the users stay on the site etc. to enhance the way tag cloud is generated and colored."
How would you go about doing this? I think it would need some VB
Hi,
I found the usage very interesting, but is giving me hard time because the LENs formula that use ranges are not considering the full range, in other words, the LEN formula is only bringing results from the respective "line" cell.
Using the example, when I place the formula to calculate the frequency for "windows" brings me only 1 result, not 11 as displayed in the example. It seems that the LEN formula using ranges is considering the respective line within the range, not the full range.
Any hint?
@Thiago
You have to enter the formula as an Array Formula
Enter the Formula and press Ctrl+Shift+Enter
Not just Enter
Thank you, Hui! I couldn't work out how this didn't work
is there a limit to the number of lines it can analyse.
Ie i am trying to get this to work on a list of sentances 1500 long.
@Gary
In Excel 2010/2013 Excel is only limited by available memory,
So just give it a go
As always try on a copy of the file first if you have any doubts
Apologies if I am missing something, but coudn't getting frequency be easier with Countif formula. Something like this - COUNTIF(Range with text,"*"&_cell with keyword_&"*")
Apologies if I missed, but what is the Array Formula to:
1. Analyze a list of URL's or a list of word phrases to understand frequency;
2. List in a nearby column from most used words to least used words;
3. Next to the list of words the count of occurrences.