Slicing One Element on a Chart Only

Consider a scenario where create a chart, say, a line chart, but you only want one element (one of the lines) to be modified using a slicer.   To illustrate, let’s have some data:

Imagine you were 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:

Do you see how the chart title changes automatically?  The question is: how do you do it?  Here is one idea, and feel free to follow along using the attached Excel file.

First of all, our data has been placed in the following cells (shown just so you may follow the formulae):

Before I play with this, I am going to highlight this entire table (cells F52:I64) and create a PivotTable (ALT + N + V, or go to the ‘Insert’ tab on the Ribbon and click on ‘PivotTables’ in the ‘Tables’ grouping) showing the months only in rows as follows:

I have to have a PivotTable or a Table to create a Slicer.  I tend to fallback on PivotTables (even though they are slightly more complex) as you can have one Slicer control multiple PivotTables which isn’t possible with multiple Tables. 

Next, I add a Slicer for the ‘Months’ field (ensure your cursor is situated in the PivotTable and then use the keyboard shortcut ALT + JT + SF, or else select ‘Insert Slicer’ from the ‘Filter’ grouping on the contextual ‘PivotTable Tools’ tab ‘Analyze’ on the Ribbon).  

Since the Slicer has originated from the created PivotTable, changing the Slicer, e.g.

automatically manifests the same modifications in the source PivotTable, viz. 

I can use this relationship to create a modified data table:

Columns F, G and H are based on the source data, but columns I:K have been calculated differently:

Starting with column J (rather than column I), this column contains the formula

in row 95.  The months only occur once in the PivotTable, so this COUNTIF function counts one [1] if the month is present in the PivotTable (i.e. the month was selected in the Slicer) and zero [0] otherwise

Column I contains the formula

which references the corresponding actual data for the month provided the value in column J is TRUE, i.e. any value other than zero.  Since I have already explained the only other value is 1, this formula is including the actual data if the Slicer has selected the month and puts #N/A otherwise.  Whilst prima facie errors are usually discouraged in spreadsheets, in this case #N/A causes the value not to display in the chart at all.

It should be noted you might have considered the SUBTOTAL or AGGREGATE functions instead, but as complex as they are, if the PivotTable were to be hidden, the wrong results might occur.  Sometimes, simpler is better! 

The cells F94:I106 are all that are required to display the chart.  Simply select this range and create a line chart (ALT + N + N, or else select a line chart from the ‘Charts’ section of the ‘Insert’ tab of the Ribbon):

Finally, column K is required to assist with the chart title, which I haven’t yet explained.  The formula here,

keeps a running total of all the months displayed (i.e. the first month selected is 1, the second is 2, etc.).  This is useful as the number 1 relates to the earliest month and the maximum value relates to the last month selected.  This latter number also represents how many months have been selected too.

In fact, the labelling requires several preliminary calculations, viz.

Let me go through them:

The formula in cell J115, ‘All Months Selected?’

checks to see whether the total of all of the 1’s from column I (above) equals the number of rows in the range.  This can only happen if all months have a ‘1’ allocated to them, i.e. all months have been selected.

The formula in cell J116, 

checks that one and only one month has been selected.

Cells J117 and J119 contain similar formulae.  For example, the formula in the former cell,

returns the name of the first month selected chronologically (the other formula identifies the last period selected).  This uses the INDEX MATCH combination which we have explained previously.

Cells J118 and J120 use similar calculations too.  The first formula

returns the corresponding month number (which is what the other formula does too).  Therefore, the formula in cell J121 

determines how many months would be included if all months between the first and last months had been selected.  This is because the final formula in cell J122,

checks whether the number of months selected equals the number of months in total between the first and last months previously selected.  If a month or more is missing, the result will be TRUE instead of FALSE.

These interim calculations are used as follows to create the chart title all in one cell:

Cell J126 contains the formula 

This concatenated formula will display the details required,e.g. “Comparative Actual Data for All Months or “Selected Actual Data for Aug”).

To get the formula from cell J126 into the chart itself, add a chart title, and while it is selected, click on the formula bar, type ‘=’ ad then click on cell J126.  Don’t type the formula =J126 or =Example!$J$126 in.  In some versions of Excel, for this to work, the cell has to be selected on the sheet for the title to become formulaically dynamic.

Simple (sort of)!