Monday Morning Mulling: February 2021 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI 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.

The challenge of this month is to create a ‘conditional legend’ for a donut chart.

The Challenge

There is a donut chart created based on a group rating data set where the rating for each group ranges from one (1) to five (5) with an increment of one (1).

This chart has a colour conditional formatting for its series and legend. If the group ratings are changed, both the chart series and legend colour will also be adjusted accordingly.

We have created the chart for you. Your job is to get the ‘conditional legend’ working. To make it easier for you to follow, we have prepared a start file here and a solution file here so that you can have a play.

The Solution

First, we will need to create a range of cells for the legend i.e. cells D66:F70.

Next, we will apply the colour conditional formatting to cell D66 so that it will display the colour based on the rating by navigating to the Home tab on the Ribbon and choosing Conditional Formatting -> New Rules… as shown below:

We will need to repeat this process five times, with the value being increased to two (2), then three (3), then four (4) and finally five (5), viz.

Then, we want the number in cell D66 to be invisible so that only the colour is displayed. To do this, right-click on the cell D66 and select ‘Format Cell…’ or use the CTRL + 1 keys to open the ‘Format Cells’ dialog. Under the Number -> Custom, enter ‘;;;’ in the Type box to make the number invisible. You can read more about custom number formatting here.

We just need to copy the formatting of cell D66 to cell D68 and cell D70. We are now having a range of cells which looks like a legend which colour depends on the group rating.

To get the legend ‘image’ to the chart, we need a tool called Camera. The Camera tool allows us to take a dynamic image of a cell or range of cells. What we mean by ‘dynamic’ here is that when the source range of cells changes in value or formatting, the copied image will also be automatically updated.

In case you do not have the Camera tool, to get it, in the Quick Access Toolbar drop-down list, choose ‘More Commands…’

An ‘Excel Options’ dialog will appear. Under the ‘All Commands’ list, add Camera to the Quick Access Toolbar and click OK.

To take a ‘dynamic’ photo of the legend, select the range D66:F70 and click on the Camera icon on the Quick Access Toolbar:

then click somewhere on the sheet to get the image:

To remove the image border, right-click on the image and choose ‘Format Picture…’

In the ‘Format Picture’ panel, let Fill be ‘No fill’ and Line be ‘No line’:

Next, we just need to drag the legend image onto the donut chart.

Now that the group ratings are changed, the colours of the chart series and the legend are also updated accordingly.

We’re good to go.

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