Conditional Charts

Lambda to Unstack Data Range into Multiple Chart Series

Tuesday, February 27, 2024 by Jon Peltier Leave a Comment

Tuesday, February 27, 2024 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Often your data is stacked, that is, multiple sets of X and Y values are stacked into two columns and identified by a label in another column (below left). When charted, the data is shown as one series (below center). In order to plot it as separate series in a chart (below right), you first need to unstack the data. In a moment I’ll get to a Lambda formula that unstacks data like this.

Stacked Data

The following data set is stacked. Column A has labels identifying which subset each data point (row) belongs in, while columns B and C contain the X and Y values for each point. Plotting the X and Y values results in a single chart series, though sometimes you can tell that the data should be subdivided.

Stacked, sorted data plotted as one series

If the data is randomly sorted (or is that an oxymoron?), you may not even be able to see that the data can be unstacked.

Stacked, unsorted data plotted as one jumbled series

Unstacking Data

This is similar to the problem described in Conditional Formatting of Excel Charts and Conditional XY Charts Without VBA. The splitting of data ranges into multiple series is covered in these articles and also in Split Data Range into Multiple Chart Series without VBA and VBA to Split Data Range into Multiple Chart Series.

For an Excel XY Scatter chart, you need one X column for all chart series and one Y column for each chart series; the X data remains stacked while only the Y data is unstacked. The simple protocol is illustrated below.

New columns (shaded below) are added for each separate series (each set of Y values). The top row (shaded green) has the unique items in the Label column. The rest of the region (shaded orange) uses a formula to put the Y value into the column if the column header matches the value in that row of the Label column; it inserts NA() if the labels do not match, which appears as #N/A in the cell and is not plotted in the chart. The formula in cell D2 and filled down and across to F13 is:

=IF($A2=D$1,$C2,NA())

In modern Dynamic Array Excel, you can fill the new column headers easily with this formula:

=TRANSPOSE(UNIQUE(A2:A13))

You can then enter a single formula in cell D2 to fill the shaded region:

=IF(A2:A13=D1:F1,C2:C13,NA())

Selecting B1:B13 and D1:F13 (use the Ctrl key to select multiple areas) and inserting an XY chart results in the following.

Unstack data into multiple series with worksheet formulas

You can take this simple Dynamic Array approach a bit further and write a single Lambda formula that will populate the entire chart source data range. This means that the chart will dynamically reflect the data range if changing the data results in more labels. The first version of the Lambda I wrote is:

=LAMBDA(input,
  LET(
    headers,TAKE(input,1),
    data,SORT(SORT(DROP(input,1),2),1),
    label,CHOOSECOLS(data,1),
    X,CHOOSECOLS(data,2),
    Y,CHOOSECOLS(data,3),
    labels,TRANSPOSE(UNIQUE(label)),
    split,IF(label=labels,Y,NA()),
    VSTACK(HSTACK(INDEX(headers,2),labels),HSTACK(X,split))
  )
)(A1:C13)

Broken down, the Lambda starts with A1:C13 as its input. The first row of this input is taken as headers, and the first row is dropped, then sorted twice, by column 2 (X) and by column 1 (Labels) to give us the data. The first, second, and third columns of data are chosen to be variables labels, X, and Y. A variable called labels is defined as the unique list of items in the label column. A variable called split generates an array of either Y values or #N/A. Finally, the requisite pieces are stacked together and output into the worksheet.

Diarmuid Early, a colleague on LinkedIn, told me I could use a single SORT command with an array of sort columns, which leads to this streamlined Lambda:

=LAMBDA(input,
  LET(
    headers,TAKE(input,1),
    data,SORT(DROP(input,1),{1,2}),
    label,CHOOSECOLS(data,1),
    X,CHOOSECOLS(data,2),
    Y,CHOOSECOLS(data,3),
    labels,TRANSPOSE(UNIQUE(label)),
    split,IF(label=labels,Y,NA()),
    VSTACK(HSTACK(INDEX(headers,2),labels),HSTACK(X,split))
  )
)(A1:C13)

The screenshot below shows this Lambda formula in cell J1 and the chart that plots the entire spill range J1# (or J1:M13).

Unstack data into multiple series with Lambda formula

You can paste the Lambda formula (excluding the A1:C13 argument and its brackets) into a new Name definition, shown below.

Now the formula can be called anywhere in the workbook as a regular Excel function called SplitXYbyLabel().

Peltier Tech Articles About Conditional Formatting of Excel Charts

Posted: Tuesday, February 27th, 2024 under LAMBDA Function.
Tags: Chart Data, Conditional Charts, LAMBDA Function.
Comments: none

Conditional Donut Chart

Wednesday, October 21, 2020 by Jon Peltier 7 Comments

Wednesday, October 21, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A recent post in the Mr Excel forum requested a conditional donut chart. The chart had to have 31 sections, one for each day of the month, and each section had to be colored according to a code in a worksheet cell. I started working on a solution, but I ran out of time. I tried returning to the post later, but I can’t find it, and Google can’t find it either.

It was a great question, though, so I’ll reproduce a simpler variation of it here.

The Conditional Donut Chart Problem

The donut chart must have eight slices, and the color of each slice must reflect a value in the worksheet.

Conditional donut chart with colors based on conditions in the worksheet.

The required color for each slice is shown in the worksheet range to the left of the chart. I have colored the cells red, yellow, and green based on whether they contain the letter “R”, “Y”, or “G”. This is easy to do in the worksheet, but there is no mechanism to change colors like this in a chart.

You could do this with VBA, of course. I’ve written a tutorial with the necessary code: VBA Conditional Formatting of Charts by Category Label. But this is a problem which does not require VBA. You can make it work just as well with a handful of clever worksheet formulas, without requiring that someone enable macros in your workbook, and without coming up with a mechanism to run the code when the data changes.

Set Up the Data

While our conditional donut chart must have eight visible slices, it will have three times that amount, and two thirds will be hidden.

I’ve reproduced the slice and color data in this screenshot. You can’t make a meaningful chart of this data, of course, so below it I have constructed an expanded data region. The Slice column repeats the slice number three times for the three colors, and the Color column repeats all three colors for each slice, not just the one which appears in the first range.

Expanding the Slice and Color data for our conditional donut chart

Insert the chart

Next I have added a column to the lower range, with a header of “Ones” and with a value of 1 in every cell. I can finally make a donut chart. With the chart selected, you can see from the data highlights in the worksheet that both the Slice and Color columns are used for category labels, and the Ones column is used for the donut slice values. The category labels are seen in the legend, and the donut slices are all the same size, 1/24 of the donut circumference.

A column of Ones was added to the data, and this was plotted in a donut chart

Format the Chart

Let’s do a little formatting. Double click on one of the donut slices to open the Format Data Series task pane, and under Series Options, change the Donut Hole Size from the default 75% to 50%. Click the plus icon that floats alongside the chart, and check Data Labels. Double click on one of the labels to open the Format Data Labels task pane, and under Label Options, check Category Name and uncheck Value. Finally, delete the legend.

Shrink the donut hole, add data labels, and remove the legend

I started formatting the slices by first making them all gray. This made it somewhat easier to see where I was in the process. To change all slices to the same color, click once on a slice to select all slices, then choose the color in any convenient way (Format Data Series task pane, ribbon control, or right-click pop-up). At the same time, keep the white border between slices, but change it from 1.5 points to 0.75.

Starting by formatting all slices to an unobtrusive color

Now, slice by slice, apply the colors. Here I have colored the slices in the first sector of the chart. To color an individual slice, click once to select all slices, then click to select a single slice, then apply the color.

Starting with the Red-Yellow-Green color scheme

Now finish coloring the slices. Here’s a trick that will work faster than clicking on and choosing its color, then clicking on the next, etc. Single click twice on the first slice to select it, then apply its red color. Then hold down Ctrl while you click the right arrow key three times, which brings you to the next red slice, and click the F4 function key. This should apply the same red color to this slice. Repeat the Ctrl+Right Arrow and F4 sequence until the red slices are formatted, then repeat for the yellow and green slices.

Properties Unfollow Chart Data Point

Before you change the chart source data, you have to apply the trick in Properties Follow Chart Data Point. This relatively unknown and totally misunderstood feature of Excel charts will otherwise screw up your chart. What it means is, if you have customized your plotted data, then change the data so the reformatted feature (chart series or data label) references a different range, some or all of the formatting will revert to its defaults.

Click on Excel’s File tab on the ribbon, choose Options, then click on Advanced. Scroll down to Chart, and you’ll see “Properties Follow Chart Data Point for All New Workbooks” and “Properties Follow Chart Data Point for Current Workbook”.

Excel Options - Properties Follow Data Points

Uncheck the box for “Properties Follow Chart Data Point for Current Workbook”.

Excel Options - Properties Don't Follow Chart Data Points

Now changing the range in the following step will not mess up the chart.

If you have Peltier Tech Charts for Excel, there is an easier-to-access checkbox on the Peltier Tech ribbon, in the Chart Format group.

Write Formulas to Apply Conditions

Now the chart is fully formatted. We just need to build the formulas that show and hide slices according to the data in the upper range.

Add two more columns to the second data range, and insert the headers “Labels” and “Values”. Ignore the Labels for now, but enter this formula into the range F13:F36:

=IF(INDEX($C$3:$C$10,B13)=C13,1,0)

The result is a column of zeros and ones, with two zeros and a single one for each slice number, according to the colors specified in C3:C10.

Click on the chart and notice the highlighted ranges in the worksheet. Drag the edge of the blue region so the highlight includes the Values column instead of the Ones column. The red highlight should move with the blue highlight if you had selected the chart or the plot area (which is the square that includes the donut). If you had selected one or all of the donut slices, the red highlight would not have followed the blue, but you can simply move it yourself.

Note that there is now only one visible slice for each of the eight sectors of the donut, and these are colored according to the values in C3:C10. Unfortunately we have a bunch of overlapping labels that we don’t want.

These labels correspond to the hidden slices, which are still in the chart. We need another formula that will let us show only the visible slice numbers. So enter this formula into the range E13:E36:

=IF(F13=1,B13,"")

Now select the pie slices (all of them or just one), drag any corner of the purple highlight so it occupies a single column, then drag the edge of the purple highlight so it includes the Labels column. The extraneous labels are gone, and we only see the slice numbers.

The Finished Conditional Donut Chart

This is our conditional donut chart. You can select and drag the lower data range off to the side of the chart; you can even cut it and paste it onto another worksheet, out of the way. Move the chart closer to the data it indirectly reflects.

When the colors for each slice are changed in the worksheet, the chart instantly reflects the change.

Change the colors in the worksheet again, and the chart updates again.

And again, and again,…

Properties Follow Chart Data Point

When I change this option as shown above, I always just uncheck the box for the current workbook. Because of a fear of missing out on something magical, I’ll admit that I always return here and check it later, and I never check the box for all new workbooks. Because not even I understand exactly what this setting does.

Highlight Min and Max Data Points in an Excel Chart

Thursday, July 9, 2015 by Jon Peltier 16 Comments

Thursday, July 9, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Here is a simple data set and an XY Scatter chart. We want to highlight the minimum and maximum points in the chart.

We could manually select the high data point and format it, then select the low point and format it. No big deal for a one-off chart. But if the data changes, we may have to manually change formatting again.

Instead we’ll add a couple columns to the data range, and use them to determine where to plot the minimum and maximum values.

The formula in cell C2 (copied and pasted in C3:C10) is

=IF(B2=MAX(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column C only if it’s the maximum value in column B. Otherwise column C contains the #N/A error value, which is not plotted with a marker in an Excel scatter or line chart.

Likewise, the formula in cell D2 (copied and pasted in D3:D10) is

=IF(B2=MIN(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column D only if it’s the minimum value in column B. Otherwise column D contains #N/A.

When we select the chart, we can see the chart data highlighted in the worksheet.

We can drag to extend the blue range to include columns C and D.

And now columns C and D are plotted in the chart. Column C (minimum) is plotted as a blue marker, and column D (maximum) as an orange marker).

Let’s do a little formatting. Right click on the Max point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Max”, and choose the bright blue text color. Format the marker so it’s an 8-point circle with a 1.5-pt matching blue border and no fill.

Right click on the Min point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Min”, and choose the gold text color. Format the marker so it’s an 8-point circle with a 1.5-pt gold border and no fill.

The nice thing about using this formula-based approach, instead of manually formatting everything, is that if the data changes, the new Min and Max are highlighted. In fact, if multiple points have the same min and max values, all will be highlighted.

This approach, of adding a series for any data you want to highlight, is very flexible. You don’t need to only highlight minimum or maximum values, you can highlight anything that you can write a formula for.

My colleague and fellow Excel MVP Leila Gharani has recorded a video on YouTube at Highlight Max & Min Values in an Excel Line Chart, which illustrates this technique. Leila has recorded many dozens of videos, which are well made and which clearly show a wide variety of Excel features and techniques.

Peltier Tech Articles About Conditional Formatting of Excel Charts

This is one example of conditional formatting in Excel charts; below are several other approaches.

Posted: Thursday, July 9th, 2015 under Formatting.
Tags: Conditional Charts, Conditional Formatting, dummy series.
Comments: 16

Conditional Formatting of Excel Charts

Monday, February 13, 2012 by Jon Peltier 195 Comments

It’s relatively easy to apply conditional formatting in an Excel worksheet. It’s a built-in feature on the Home tab of the Excel ribbon, and there many resources on the web to get help (see for example what Debra Dalgleish and Chip Pearson have to say). Conditional formatting of charts is a different story. People often ask how to conditionally format a chart, that is, how to change the formatting of a chart’s plotted points (markers, bar fill color, etc.) based on the values of the points. This can be done using VBA to change the individual chart elements (for example, VBA Conditional Formatting of Charts by Value), but the code must be run whenever the data changes to maintain the formatting. The following technique works very well without resorting to macros, with the added advantage that you don’t have to muck about in VBA.

Unformatted Charts

Here is the simple data for our conditional chart formatting example.

The data makes a simple unformatted bar chart. . .

. . . or a simple unformatted line chart.

We want our charts to show different colored points depending on the points’ values. Except for some simple built-in formats, conditional formatting of worksheet ranges requires formulas to determine which cells should take on the formatting. In the same way, we will use formulas to define the formatting of series in the charts. We will replace the original plotted data in the line and bar charts with several series, one for each set of conditions of interest. Our data ranges from 0 to 10, and we will create series for each of the ranges 0-2, 2-4, 4-6, 6-8, and 8-10.

Conditional Formatted Bar Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

=C1&"<Y<="&C2

The formula is cell C4 is

=IF(AND(C$1<$B4,$B4<=C$2),$B4,"")

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an apparent blank. The formula is filled into the range C4:G13.

When the bar chart is selected, the chart’s source data is highlighted as shown.

We need to change the source data, removing column B and adding columns C:G. This is easily done by dragging and resizing the colored highlights.

The chart now shows five sets of colored bars, one for each data range of interest. It’s not quite right, though, since it’s a clustered bar chart, and each visible bar is clustered with four blank values.

This is easily corrected by formatting any one of the bars, and changing the Overlap property to 100%. This makes the visible bars overlap with the blank bars.

Conditionally Formatted Line Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

=C1&"<Y<="&C2

The formula is cell C4 is

=IF(AND(C$1<$B4,$B4<=C$2),$B4,NA())

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an error, #N/A, which will not be plotted in a line chart. The formula is filled into the range C4:G13.

When the line chart is selected, the chart’s source data is highlighted as shown.

We need to expand the source data, keeping column B as a line connecting all points and adding columns C:G for the separately formatted series. This is easily done by resizing the colored highlights.

The chart now shows five sets of colored markers and line segments, one for each data range of interest.

A little formatting cleans it up. Remove the markers from the original series, remove the lines from the other series, and apply distinct marker formats to the added series.

Remove the unneeded legend entry (for the gray line) by clicking once to select the legend, clicking again to select the label, and clicking Delete.

Conditional Formatting Flexibility

This simple example has formatting formulas defined based on the Y values in the chart. It is possible to define formatting based on Y values, X values, or values in another column which is not even plotted. As in worksheet conditional formatting, the only limit is your own ability to construct formulas. This technique works on most useful Excel chart types, including bar and line charts shown here, and XY charts as shown in Conditional XY Charts Without VBA.

Peltier Tech Articles About Conditional Formatting of Excel Charts

Posted: Monday, February 13th, 2012 under Formatting.
Tags: Conditional Charts, Conditional Formatting.
Comments: 195

Conditional XY Charts Without VBA

Thursday, January 21, 2010 by Jon Peltier 14 Comments

It’s been a busy week, and I haven’t had time to write any new posts. However, in a comment to VBA Conditional Formatting of Charts by Value, Dean asked how to conditionally format points in an XY chart according to which quadrant they fall in, where the quadrants are determined by the mean X and Y values. Rather than writing a long comment in response, I decided to write a short post.

XY Chart With Formatting Applied According To Quadrant Where Each Point Is Located

Of course, you can format any chart’s data points with VBA, according to any conditions you want. But some things are easier to do and less complicated to maintain if you use Excel’s built in worksheet functions. I have covered worksheet-based conditional formatting of charts in Simple Conditional Charts and Simple Conditional Charts – 2. But it’s good to write up more examples of suitable formulas to match the formatting criteria, to help people see when they can use this technique.

To illustrate my answer to Dean’s question, I generated the following data. Columns A and B have the original X and Y data, which I sampled from normal distributions. The mean values for X and Y are computed in A1 and B1. Columns C through F contain the Y values for each of the quadrants, determined by a simple set of formulas.

The formulas in C3 through F3 are as follows, and are filled down to row 14:

C3: =IF(AND($A3>=$A$1,$B3>=$B$1),$B3,NA())
D3: =IF(AND($A3<$A$1,$B3>=$B$1),$B3,NA())
E3: =IF(AND($A3<$A$1,$B3<$B$1),$B3,NA())
F3: =IF(AND($A3>=$A$1,$B3<$B$1),$B3,NA())

Here is a chart of the original data, using A2:B14 as the chart’s source data range:

XY Chart Without Individually Applied Formatting

Here is the chart showing the data by quadrant, with one series per quadrant, using column A for all X values and columns C through F for the Y values of the four series:

Conditional Charts

Lambda to Unstack Data Range into Multiple Chart Series

Stacked Data

Unstacking Data

Peltier Tech Articles About Conditional Formatting of Excel Charts

Conditional Donut Chart

The Conditional Donut Chart Problem

Set Up the Data

Insert the chart

Format the Chart

Properties Unfollow Chart Data Point

Write Formulas to Apply Conditions

The Finished Conditional Donut Chart

Properties Follow Chart Data Point

More Peltier Tech Articles About Conditional Formatting of Excel Charts

Highlight Min and Max Data Points in an Excel Chart

Peltier Tech Articles About Conditional Formatting of Excel Charts

Conditional Formatting of Excel Charts

Unformatted Charts

Conditional Formatted Bar Chart

Conditionally Formatted Line Chart

Conditional Formatting Flexibility

Peltier Tech Articles About Conditional Formatting of Excel Charts

Conditional XY Charts Without VBA

More Peltier Tech Articles About Conditional Formatting of Excel Charts