You must have heard and used a concept called Conditional Format in your excel dashboards.... Ever heard of a conditional chart?
If not, let me tell you something about it....
An old friend of mine called me today and asked for some help. He was trying to create a chart which picks up the data dependent upon a condition. In other words, the data the chart should display was dependent upon the result of a formula or a user input.
My immediate response was we can use the VBA code to create a dynamic chart. But here comes the major problem... he was looking something in excel alone without the use of a macro.
I had another solution for him... it was using the named ranges. Yes, the old friend (name range) of ours, which helps us reduce the size of excel formulas can also help in creating a dynamic chart.
In the next few steps I will explain the problem first followed by the non VBA solution for it.
Problem
The requirement is of a single chart, which shows Result A series as the data if Result A is chosen from a drop down in cell D2. Similarly if Result B is choosen in cell D2, then the chart should show Result B series.
Solution
There are ways in VBA to create this kind of chart but if you need a non VBA way, then follow the steps given below:
Step 1:
Create two named ranges, one each for X Axis labels given in column A and one for data to be shown on the chart Y Axis.
To create the X Axis label, go to the Defined Names group on the formula tab and click on Define Name, give the name as XRange and select the range from the sheet.
To create the name range for Y Axis data series, go to the Defined Names group on the formula tab and click on Define Name, give the name as YRange and use the following formula in the refers to field
=If(Sheet1!$D$2="Result A",Sheet1!$B$5:$B$9,Sheet1!$C$5:$C$9)
This formula is first checking the contents of cell D2 and dependent upon it giving the range to YRange
Now add a chart from Insert tab, Charts group by selecting Column & the first option in the 2D column
Once the chart is displayed, click on the Select Data and click on the Edit button for the Legend Entries (Series)
In the Edit Series Dialog Box, give a caption for the series name and provide the YRange name range as the data to be displayed. You need to use File Name ! Name range to specify the name range
Now click on OK, and click on Edit for Axis Labels
Provide the XRange as the axis label
And the chart is ready. You can choose Result A or Result B from cell D2 and the data in the chart will change depending upon your selection.
Result A
Result B
