Challenges: Monday Morning Mulling: August 2018 Challenge

On the final Friday of each month, we set an Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

Final Friday Fix: August Challenge Recap

As explained on Friday, imagine you have five business units: Alpha, Bravo, Charlie, Delta and Echo and data for each business unit under five scenarios, displayed using a clustered chart similar to the following:

It was important to note the consistent colouring of the business units for each scenario.

Next, you decide you want to create an interactive chart, so you add a slicer:

Selecting ‘Alpha’ generates

Alpha’s data is in blue – as in the summary chart. Then you decide to select ‘Bravo’ and ‘Echo’:

Again, the colours are consistent with the original chart and there is no unnecessary spacing.

The challenge was “simply” to replicate this chart’s behaviour – so how did you do?

Suggested Solution

One obvious thing to notice in this challenge was the absence of a displayed dataset, which sort of gave the game away:

It’s in a Table. And that happens to be important. But more on that shortly.

What most people would do is take their data

and create a PivotChart or a PivotChart with a PivotTable, depending upon preferences and what version of Excel they had:

Putting the ‘Business Unit’ field on the Axis and all five scenarios in the Values section, with a little bit of tinkering you can get a chart looking like this:

If you click anywhere inside the source PivotTable and choose ‘Insert Slicer’ from the ‘Filter’ grouping of the ‘Analyze’ tab in the ‘PivotTable Tools’ section of the Ribbon, you can select a ‘Business Unit’ slicer. Without bothering to format it, if I click on ‘Alpha’,

I get the following chart displayed:

Rats. I have inconsistent colouring, which is not what I required. Users now spend hours trying to change these colours so that they will work when the slicer inputs are changed. Maybe there is a solution out there this way, but there is a much simpler way of doing this.

Let’s scrap the PivotChart idea.

Instead, I go back to the original data and highlight it all (CTRL + A). Then, I convert the data into a Table (CTRL + T). You can find out more about Tables here. Ensure that you check the ‘My table has headers’ check box, viz.

The aim is to insert a clustered column chart:

With a little bit of formatting, it won’t take long to create a chart looking like the following:

Then, click anywhere inside the Table to active the context specific ‘Design’ tab for ‘Table Tools’ in the Ribbon. In the ‘Tools’ grouping, click on ‘Insert Slicer’ and again select ‘Business Unit’.

Simple manipulation will generate your slicer:

Without any further work, selecting ‘Alpha’ generates the required chart:

Furthermore, if you select ‘Bravo’ and ‘Echo’ you will get the required chart too:

It’s that easy. It just need you to create a Table – not a PivotTable – to begin with.

Word to the Wise

Slicer filters first appeared with the 2010 release of Excel and at that time, you could only use them to filter data in PivotTables. However, beginning with the 2013 release of Excel, you could then use Slicers also to filter data in tables. Apologies if you are reading this and have an earlier version of Excel – time to upgrade!

Further, slicers only filter fields in a table of data and fields must always be in a column. Therefore, any field you wish to filter on must be in a column – hence we can only slice on business units not scenarios here (as they go across a row). If we wanted to slice on the scenarios instead, we must transpose the table of data first.

You can read more about slicers here.

The Final Friday Fix will return on Friday 28 September with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.