So this blog post falls into the “I just think this is cool” bucket. Did you know Excel had camera functionality? Well, I didn’t. And this despite the functionality being there since 2003(!). This isn’t there by default, but if you turn it on in your Quick Access Toolbar, you can take advantage of it. What’s so special about this functionality in particular?
Basically, it allows you to take a picture of a collection of cells in your workbook. I know, amazing, right? Stay with me here – it’s a picture, but it’s a LIVE picture. Here’s a simple example using Excel 2016:
If I highlight those cells and use the camera tool, I can do the following in my workbook (I normally wouldn’t make the picture this big, but wanted to emphasize it was a picture and not just a linked table) –
Now I’ll change the numbers to text. When I do that, the picture updates automatically as well –
And since it is a picture, I can do all the normal things I could do to a picture in terms of formatting –
So could I do something like this with a Pivot Table? You bet, including one using “Analyze in Excel” in Power BI for the data! I tried this myself against a sample report I loaded in PowerBI.com. I chose “Analyze in Excel” from the ellipsis –
Then created my Pivot Table against the data and used the camera tool as I did in my earlier example.
Any change I made in the Pivot Table is reflected in the picture –
This is nice, but what I really want is this live picture in a PowerPoint deck that gets updated when my data is updated in Excel. Let’s give that a try.
After I’ve selected the range in Excel with my camera tool, in PowerPoint I can choose “Paste Special” and select “Paste Link” so I can paste it as a “Microsoft Excel Worksheet Object”. This will allow the data to updated dynamically whenever I have new data in my workbook. (You can also add Pivot Charts to your PowerPoint presentations via ‘Paste Link’, and the data will also update dynamically for those as well!)
For example, if I change the “AccountCount” to say “SSRS Rules!”, it changes dynamically.
I can also add a hyperlink to the picture back to the original report in Power BI if I wanted to jump there quickly during a presentation to do additional analysis on the data.
Something to keep in mind – if you share the PowerPoint deck with a user who doesn’t have access to the original Excel workbook, they can still open and use the presentation with the static images reflecting the last time the data was updated. I think this is valuable, since I know often the workflow at some companies is basically “Hey so and so, I need that slide deck for the meeting tomorrow. Can you update the slides from the previous meeting and send it to me?”
Thanks as always for reading – maybe you already knew this trick, but if not, hopefully it’ll save you some time in the future!