Final Friday Fix: May 2018 Challenge

On the final Friday of each month, we’re going to set an Excel challenge for you to puzzle over so that you can get your “Excel fix”.  Challenge your office colleagues to see who can solve the puzzle quickest.  There’s no prizes at this stage, you’re playing for bragging rights only!

Semi-Interactive Charts

What if you wanted to create a chart – say, a line chart – where you only wanted one element (line) to change?  Changing everything is easy with Slicers or formulae – but how do you do it for only one item?  Well, that’s this month’s challenge…

The Challenge

Imagine you had the following chart data:

You have been asked to put the following chart together with an associated Slicer:

So far, so good, but the Slicer should only affect the Actual line item (in red, with the black marker in our example). For example:

or:

or finally:

Get the picture? Well, you have several! Do you see how the chart title changes automatically too? Think you can recreate these examples using just a Slicer and one chart without using user-defined functions? Well, that’s this month’s mission should you choose to accept it…

Sound easy? Have a go. We’ll publish one solution in Monday’s blog. Have a great weekend!