Using the camera tool to toggle charts

In the video above (which is silent), I’ve used Excel’s camera tool to toggle between different versions of the same chart.

This post was inspired by Juliana Smith and I had enormous help from this article by Chandeep Chhabra to get to the solution.

The original inspiration

A couple of months ago, I attended a very interesting talk by Juliana Smith about accessibility in Power BI. (This event was part of the the Bristol Fabric and Power BI meetup. )

Juliana covered lots of informative topics, from Web Content Accessibility Guidelines (WCAG) to the challenges of selecting suitable colour palettes.

In her talk, she included a demonstration about how to add a toggle to Power BI to swap in a different version of a graphic. In her example, stakeholders wanted something in the traditional Red Amber Green (RAG) colours. However, this poses a particular issue for people with various forms of colour blindness, and so her solution was to provide a toggled alternative.

While watching this, a part of my brain was wondering how to do the same in Excel.

Introducing the Camera tool

The Camera tool has been around for a while (since at least 2010) but it’s hidden. It doesn’t appear on any of the Excel ribbons. You have to manually add it by going into File = > Options, then selecting the Quick Access Toolbar, and then searching for Camera in “All commands”.

It behaves almost identically to Linked Picture (i.e. where you copy and paste a link to a picture). That is, it allows you to take a picture of your data, and paste it somewhere else. The picture will update as your data does.

Apparently it’s quite heavy on the old memory, so you shouldn’t use it a lot in a workbook.

I remembered seeing in a couple of presentations about Data visualisation, most recently by Laura Szepesi, that you could use the Camera tool in some interesting ways.

I wondered if you could use it for this scenario.

Using the camera tool to toggle charts

I spent quite a while trying to work this out, because I decided to test Copilot for the job. That was a rather frustrating couple of hours, as it kept looping round solutions that didn’t work.

After a while, I gave up and did a more targeted search of some of my favourite Excel experts. I found this article by Chandeep Chhabra, which gave me pretty much everything I needed to know.

In summary, the approach is;

• Draw three graphs from the same data but with different colourschemes. I used the DaltonLens colourblindness simulator to check the schemes.
• Create a choice mechanism. In my example above, I’ve used a Data Validation dropdown. If you only had two options, you could perhaps use the Checkbox option to toggle something on and off. Or, like Chandeep, you could use the Developer tools.
• Whichever mechanism you select, connect it to a hidden cell where the output is translated into numbers (i.e. 1,2,3 in my example).
• Use the CHOOSE formula to relate these options to the three cell ranges containing your graphs.
• Name this formula (for example, call it “ChartView”)
• Use the Camera tool to “take a photo” of one of the graphs. You’ll see that this gives you a range in the formula bar.
• Now, replace that range with the Name of your CHOOSE formula.

Final thoughts

I’m absolutely not an accessibility expert, so please don’t take this post as offering advice about how to “do accessibility”. It’s definitely an area I’m interested in learning a lot more about, though.