Combination Charts

Salary Chart: Plot Markers on Floating Bars

Tuesday, July 21, 2015 by Jon Peltier 38 Comments

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

In an organization it is often informative to plot individual salaries and grades to see how they fall within salary bands for those grades.

This is easily accomplished by constructing a combination chart using a floating bar chart and overlaying the individual points as an XY scatter plot.

The screenshot below shows salary ranges for six grades of engineers, along with actual salaries and grades for eight engineers, with the desired chart.

The data needed to construct the floating bar chart is shown below, with Span calculated in the column between Min and Max. The chart is constructed by selecting the orange shaded cells (Grade, Min, and Span) and inserting a stacked column chart (top chart below).

Fix up the chart (bottom chart below) by deleting the legend, formatting Min to use no fill and Span to use a light fill color, and setting a gap width of 50 or 75%. Clean up the vertical axis labels by using a custom number format of

0,"k"

The lone zero means display the value without decimal digits, and the comma after the zero means show thousands, not ones. I’ve deleted the chart title, but you should use a title that describes the data being displayed.

Add the individual salary data as follows. Set up the data as shown, using a MATCH formula to find which bar the engineer’s grade falls within:

=MATCH(cell containing grade label,range containing list of grade labels,0)

This column should be to the left of the salaries, since it will be used as X values for the XY series we will plot. Copy the shaded range (grade index and salary), select the chart, use Paste Special from the Home tab of the ribbon, and select the options shown in the dialog screen shot below:

Add Cells as New Series
Values (Y) in Columns
Series Names in First Row
Categories (X Labels) in First Column

Individual Salary Data, Pasting into Chart

The data is added as another stacked column series (below left).

Right click on the added bars, choose Change Series Chart Type from the pop-up menu, and select the XY Scatter option with markers only (below right).

Select the XY scatter series, and press Ctrl+1 (numeral one) to open the Format Series dialog or task pane. Under Series Options, select Primary Axis, then choose a marker color and style that shows up clearly against the floating bars (below left).

Finally, you may like to stretch the chart to improve resolution (below right). Add labels to make it easier to track each engineer. In Excel 2013, add labels, then use the Values from Cells option to use the cells containing the labels. In earlier versions, you can manually change the text of each label, or you can use Rob Bovey’s Chart Labeler, a free add-in, to link the data labels to the cells.

I’ve described these data labeling options in more detail in my tutorial Apply Custom Data Labels to Charted Points.

This tutorial is a rework of my answer to the October 2014 question need to create salary data with salary bands on Stack Overflow.

Fill Under or Between Series in an Excel XY Chart

Monday, September 9, 2013 by Jon Peltier 187 Comments

Monday, September 9, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Shading between plotted lines with a light color can enhance some charts. The shading may help to indicate a target range for the data.

I’ve written earlier tutorials about this topic, but I have had to change sequences of steps in the protocol because more recent versions of Excel were not as flexible with order of operations as Excel 2003 (RIP).

XY-Area Combination Charts

Recently in Shaded Quadrant Background for Excel XY Scatter Chart I showed how to generate a background grid of colored rectangles.

XY Chart With Colored Quadrant Background

This technique plotted the XY chart data on the primary axes and the Area chart data on the secondary axes. It also took advantage of a trick using the category axis of an area (or line or column) chart: when used as a date axis, points that have the same date are plotted on the same vertical line, which allows adjacent colored areas to be separated by vertical as well as horizontal lines.

To fill under or between XY series, we’ll make use of this same approach: XY data on primary axes, Area data on secondary axes, with a secondary date axis.

Fill Under One XY Series

Let’s start with the simple case of filling color below an XY plot. Simple data, simple chart of type Scatter With Straight Lines and Markers.

We need to do some calculations to produce data for the area chart series (see below). The min and max X for the XY chart’s X axis are entered in B11 and B12 (these are Excel’s automatic scale limits in the XY chart above). To provide reasonable resolution, we’ll scale the area chart’s X data from 0 to 1000. The formulas for the area chart’s X values in column D are simple interpolation formulas to properly rescale the data.

The X values are extended so that the first calculated X value in D5 is repeated in D4, and a zero is placed in D3; also the last calculated X value in D9 is repeated in D10, and the X max of 1000 is entered into D11. These duplicate X values will provide the vertical edges of the shaded region.

The formulas for the area chart’s Y values in column E provide the same Y values for both chart series. The Y values are extended by two cells above and below the calculations, which contain zeros.

The chart already contained the XY data in the first two columns of the data range (B4:C9). Copy the next two columns (D2:E11) , select the chart, then click on the Paste dropdown on the Home tab and choose Paste Special (below left). The Paste Special dialog will appear; make sure the same settings are selected as shown below right.

Here is the computed data and the chart. The added series is an XY chart type, like the first. Don’t change it yet.

Select the newly added series, press Ctrl+1 (numeral one) to open the Format Series Task Pane (Excel 2013) or the Format Series Dialog (2010 or 2007), and under Series Options, select Secondary Axis (below left). Excel adds a secondary vertical axis along the right edge of the chart (below right).

Excel adds a secondary vertical axis along the right edge of the chart.

In Excel 2013, click the plus icon beside the chart, click on the right-facing arrow beside Axes, and check the Secondary Horizontal box. In Excel 2010 or 2007, you’ll have to trudge up the the Chart Tools > Layout tab, click on the Axes dropdown, click Secondary Horizontal, and finally click Show Left to Right Axis.

Now all axes are present and accounted for.

Right click on the “Area” series (which is still of type XY), and choose Change Series Chart Type. In Excel 2013, the Change Chart Type dialog appears. Click the Chart type dropdown in the Area series row, and select Area or Stacked Area (doesn’t matter which in this case, since there’s only one area series). You actually could have switched the area series to the secondary axis in this dialog (as long as you do it before changing the chart type), but I usually forget that it’s become this simple.

In Excel 2007 and 2010, select Area or Stacked Area from the pop-up window.

The series has now been converted to an area series.

Select the secondary horizontal axis (top of chart) and press Ctrl+1 to open the Format Axis Task Pane or the Format Axis Dialog. Under Axis Type, select Date Axis (below left). You may also have to change the Base Unit of the axis to Days.

The area chart series is now perfectly aligned with the XY series. Since it’s plotted on the secondary axis, the area series fills the space between the secondary horizontal axis at the top of the chart and the data points. So it’s above the data, not below. But we’re clever enough not to panic.

Delete the secondary vertical axis (right edge of chart) and the secondary horizontal axis (top of chart). The fill moves into position below the XY plot.

Fill Between Two XY Series

The protocol for filling between two plotted lines is pretty much the same as above. We start with two XY scatter chart series, one above and one below the filled area. We will also need two stacked area chart series, one for the clear region below the lower XY line, and one stacked on top to fill between the XY lines.

Here is the data and the initial chart for the two XY curves. It takes up space, but I’ll leave the legend there until the end to help us keep track of which series is which.

The formulas to calculate the area chart X and Y values are like those used in the simpler example above. Note that the Bottom Area data is the same as the Bottom Line data, while the Delta Fill data is the difference between the Top Line and Bottom Line data.

Copy the area chart data (E2:G11) , select the chart, then click on the Paste dropdown on the Home tab, choose Paste Special, then make sure the settings are correct. The new data is added as additional XY series, which is fine.

Select one of the Area data series (still plotted as XY), press Ctrl+1 to open the Format Series Task Pane or Dialog, and choose Secondary Axis. Excel gives us the secondary vertical axis along the right edge of the chart. Repeat for the other Area data series.

Right click on the “Area” series (which is still of type XY), and choose Change Series Chart Type. In Excel 2013, the Change Chart Type dialog appears. Click the Chart type dropdown in each of the Area series rows, and select Stacked Area. You could have switched the area series to the secondary axis in this dialog (as long as you do it before changing the chart type).

In Excel 2007 and 2010, select Area or Stacked Area from the pop-up window. The chart probably looks like it’s broken right about now, but don’t panic. Right click on the other area series, choose Change Series Chart Type, and again select Stacked Area.

Select the secondary horizontal axis (top of chart) and press Ctrl+1 to open the Format Axis Task Pane or the Format Axis Dialog. Under Axis Type, select Date Axis. The area chart series are now perfectly aligned with the XY series. Since they’re plotted on the secondary axis, the Bottom Area series fills the space between the secondary horizontal axis at the top of the chart and the Bottom Line data points. So it’s above the data, not below. The Delta Fill area has filled the space between the XY lines.

Delete the secondary horizontal and vertical axes. The Bottom Area now fills below the Bottom Line, while the Delta Fill fills between Bottom Line and Top Line.

Format the Bottom Area series so it has no fill.

Finally, delete the legend.

Fill Between Overlapping Regions

It is possible to extend this approach even further. Suppose we want to fill between two pairs of lines. The following simple data has four series, min and max for set A and min and max for set B. We want to fill between Amin and Amax, and between Bmin and Bmax. If these areas overlap, we’d like to see both.

We can set up the area chart data below, where we have an area below Amin (“Bottom”), one between Amax and Amin (“Afill”), one between Bmin and Amax (“Blank”), and one between Bmax and Bmin (“Bfill”). The “Blank” area between Bmin and Amax extends from Bmin down to Amax, if Bmin is greater, or from Bmin UP to Amax, if Bmin is smaller.

I won’t go through the whole procedure, because it’s really the same as filling between two lines, above. The lines are shown on the left, A in blue, B in orange. The finished overlapping filled regions are shown on the right, again A in blue and B in orange. I’ve used a transparency setting of 50% so we can see gridlines and other data behind the area fills.

The four charts below show each area in turn with the others hidden. First, “Bottom” fills from the axis up to Amin, then “Afill” fills from Amin up to Amax, “Blank” fills from Amax up (or down) to Bmin, and finally “Bfill” fills from Bmin up to Bmax.

Fill Under a Plotted Line: The Standard Normal Curve

For an example of this technique, see another tutorial I’ve written, Fill Under a Plotted Line: The Standard Normal Curve.

Another Simple Baseline for Excel Column Chart

Saturday, February 2, 2013 by Jon Peltier 17 Comments

Saturday, February 2, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A reader of this blog asked how to add a series to a 3D column chart, to show reference values. Excel doesn’t allow combination charts if one of the chart types is 3D (fortunately!), so she added lines from the Insert Shapes group on the ribbon. Here is the result.

3D Column Chart with Reference Lines

As a quick response I sent the following chart, which uses a hollow column on the secondary axis to indicate the baseline, and I wrote up the tutorial in Simple Baseline for Excel Column Chart.

Hollow Columns for Baseline

In a comment to that post, a reader suggested a horizontal line, rather than a whole box, as the baseline marker. The following protocol describes how to accomplish this

Here’s the data for these two examples.

Data for this simple example

The first step is to create a clustered column chart.

Create a clustered column chart

The second step is to switch rows and columns in the source data orientation if necessary (it was).

Switch rows and columns if necessary

Now we’ll clean up the chart a bit. Let’s remove the chart area border, remove the line for the vertical axis, use lighter gray lines for the gridlines and horizontal axis, and remove the tick marks from the horizontal axis.

Clean up the formatting

Change the Baseline series to the XY (Scatter) type, which also moves it to the secondary axis.

Change Baseline series to XY type

Reassign the Baseline XY series to the primary axis.

Move Baseline XY series to primary axis

Apply a consistent set of colors to the Q1 through Q4 series.

Apply 'nice' formatting to series Q1 through Q4

Add error bars to the Baseline series. The default is horizontal and vertical error bars of length 1 in the positive and negative directions.

Add error bars to Baseline series

Select and delete the vertical error bars.

Delete Baseline series vertical error bars

Resize the horizontal error bars. Use trial and error, or if you’re good at math:

Error bar total width (2 error bars) = 4 column widths
Total category width = 4 column widths + gap width
Gap width = 150, which means 1.5 column widths
Single error bar width = 4/5.5/2 = 0.364

Correctly size Baseline series horizontal error bars

Format the error bars as desired.

Format horizontal error bars

Add data labels. If you select the whole chart and then use the Data Labels command on the ribbon, all series will be labeled in one step. This saves a lot of time over labeling one series at a time.

Add data labels to all series in chart

Change each set of labels from Value to Series Name, and change the Baseline label position to Below.

Change all labels from Value to Series Name, move Baseline labels to Below position

Finally, if desired, use a darker shade of the series colors for the labels. Change the Baseline marker style to none, and delete the legend

Color label font to match series, change Baseline to no marker, delete legend

It’s a lot of steps, slightly more involved than the “hollow box” technique of the previous post, but it’s still quick and easy.

Posted: Saturday, February 2nd, 2013 under Combination Charts.
Tags: Column Charts, reference lines.
Comments: 17

Microsoft MVP Logo

Simple Baseline for Excel Column Chart

Friday, February 1, 2013 by Jon Peltier 5 Comments

Friday, February 1, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

3D Column Chart with Reference Lines

At first she insisted that the colors were Excel’s default, but when I asked which theme used such colors, she found out that her co-worker had designed it himself. When he’s cleared his backlog, I’ll have him design me a new template for my web site.

The protocol below shows how to achieve the same effect of a reference line in an easy and perhaps more aesthetically pleasing way.

Here’s the data my reader used for her chart. It’s totally made up, because she’s working on top secret government projects, and I don’t think y’all have the appropriate security clearance. I sure don’t.

Data for this simple example

The first step is to create a clustered column chart.

Create a clustered column chart

The second step is to switch rows and columns in the source data orientation if necessary (it was).

Switch rows and columns if necessary

Now we’ll move in the opposite direction of the 3D chart at the top of this article, and do some unformatting. Let’s remove the chart area border, remove the line for the vertical axis, and use lighter gray lines for the gridlines and horizontal axis. Oh, and remove the tick marks from the horizontal axis.

Clean up the formatting

Move the Baseline series to the secondary axis.

Move Baseline series to secondary axis

Delete the secondary Y axis (right of the chart) so all the data uses the primary axis.

Delete secondary vertical axis (right of chart)

Apply a consistent set of colors to the Q1 through Q4 series.

Apply 'nice' formatting to series Q1 through Q4

Make the Baseline series into a hollow box by removing the fill and applying a thick border of a contrasting color.

Format Baseline as hollow box (thick border and no fill)

Reduce the gap width of the Baseline series so each hollow box encompasses its corresponding cluster of columns. In this example, the gap width of the Baseline is 15%.

Reduce gap width of Baseline series

We can get rid of that legend.

Delete redundant legend

Let’s apply data labels to all of the series using the Outside End position. If you select the whole chart and use the Data Labels command on the ribbon, all series will be labeled in one step. This saves a lot of time over labeling one series at a time.

Add data labels in Outside End position

Change each set of labels from Value to Series Name, and change the Baseline label position to Inside End.

Change Baseline labels from Value to Series Name, move to Inside End position

Finally, if desired, use a darker shade of the bar colors for the labels.

Color label font to match series

It’s a lot of steps, but they’re quick and easy. My reader admitted that she likes this version better than her original. I haven’t yet heard from the graphic designer she works with.

For an alternative that has only the horizontal part of the “hollow box”, see Another Simple Baseline for Excel Column Chart.

Posted: Friday, February 1st, 2013 under Combination Charts.
Tags: Column Charts, reference lines.
Comments: 5

Microsoft MVP Logo

Fill Under a Plotted Line: The Standard Normal Curve

Wednesday, January 30, 2013 by Jon Peltier 22 Comments

Fill Below Standard Normal Distribution

A common request I get is to show someone how to apply shading under part of a plotted curve. This can be done easily using a combination chart. Combination charts may sound difficult, but they are actually pretty easy. We could use a line and area chart combination, but that’s not smart enough, so we’ll use an XY and area chart combination.

Area Chart Fill Below XY Plot of Standard Normal Distribution

Plotting the normal curve, in fact, plotting any function, is an easy task. Put the X values for the function into one column of a worksheet, then calculate the Y values in the next column, and plot this data. The data for the standard normal distribution is shown here (it extends down to row 65).

The X values in column A start at -3 in cell A5 and increment by 0.1 down to X=+3 in cell A65. This formula in cell B5 to calculate the standard normal distribution is copied into the range B5:B65.

=NORMDIST(A5,0,1,FALSE)

The data for the area chart (at least for the first cut) is shown below:

Cells C1 and C2 contain the minimum and maximum X values to fill below.

This formula in cell C5 is copied into C5:C65.

=IF(AND(A5>C$1,A5<C$2),B5,0)

First Cut at Filling Below a Plotted Line

This first exercise will show that, for this particular example, a line and area chart combination isn’t sufficient. For other uses, such a combination may be perfect.

This is the data in A4:B65 plotted in an Excel line chart. Note that the labels along the X axis do not denote numerical values. A line chart treats anything in the X values range as nonnumeric labels and plots data at equally spaced intervals along X.

This is the data in A4:C65 plotted in an Excel line chart.

Standard Normal Curve and Highlight Outline

This is how it looks when the “partial” line chart series is converted to area chart type.

Standard Normal Curve, Not Well Filled Under

This doesn’t work quite right. The edges of the shaded region are not vertical. Since the default text-style axis of a line chart (also used by area charts and column charts) plots each point above a category label, the edge of the shaded area slopes from the value at one category to the value at the next.

Filling Below a Plotted Line, Done Right

We’ll need a more clever way to get our chart, so we’ll use an XY and area chart combination.

We can take advantage of the behavior of the date-style axis of a line chart. This treats numerical X values as dates starting at 1 for January 1, 1900, and incrementing up one unit for each day. In fact, it starts at zero for January 0, 1900. Values are plotted horizontally according to date, so they no longer need to be equally spaced, if the days between points are not equal. You can even plot multiple values for the same date, and the points will be plotted along a vertical line. This is what we want for our shaded region.

Since the date axis only recognizes whole days as whole numbers, and ignores any fractions, we can’t use the same scale as the -3 to +3 for the normal curve, That is also no-go because the date axis only recognizes non-negative dates. But we can normalize our X values so they run from 0 to 10,000 (which is the same as 5/18/1927). The data table is shown below.

A few constants are included in the first two rows of the worksheet. E1, E2 contain min and max X values for the X axis of the normal curve. G1, G2 contain min and max X for highlighting under curve; change these numbers to change which region is shaded. F1 is the max for the area chart’s date axis (the minimum is zero).

The formula to calculate the standard normal curve is the same as in the previous example with the line chart. A5:A65 contains values of -3 to +3, incremented by 0.1, while cell B5 contains this formula, which is copied into B5:B65.

=NORMDIST(A5,0,1,FALSE)

The first two and last two rows of the area chart data range contain dummy values for the unshaded ends of the chart. The formula in cell E5 is:

=E$1

The formula in cells E6, E7 is:

=G$1

The formula way down in E57, E58 is:

=G$2

The formula in E59 is:

=E$2

Cells G5, G6, G58, G59 contain the value zero.

The range D7:D57 have values from zero to one, incremented by 0.02. I judged that this spacing has more than sufficient resolution for a chart 400 or so pixels wide.

The formula in E8:E56 computes the X values used to compute the normal curve for the area chart.

=G$1+(G$2-G$1)*D8

The formula in F5:F58 converts these X values into “dates” for the area chart’s X axis.

=(E5-E$1)/(E$2-E$1)*F$1

Finally, the formula in G7:G57 computes the standard normal values plotted by the area chart.

=NORMDIST(E5,0,1,FALSE)

Let’s start by selecting F4:G59 and inserting an area chart. Doesn’t matter if you use stacked area or unstacked, since there’s only one area series in the final chart, but I used unstacked.

Convert the default text-style axis to a date-style axis. Make sure it is scaled from zero to 10,000.

Convert X Axis from Default Text Axis to Date Axis

Copy the XY data in A4:B65, select the chart, and use Paste Special to add the data as a new series, with categories in first column and series names in first row. This data is added as another area series.

Copy XY Data, Select Chart, Paste Special, Add as New Series

Convert the new series to an XY type. If it doesn’t automatically go onto the secondary axes (and you don’t see axes on all four sides of the plot area), format the new series, and choose Secondary Axis.

Convert New Series to XY Type (Should Automatically Go onto Secondary Axis

Align the curve and the area by setting the secondary X axis (top of chart) so it ranges from -3 to +3.

Change Secondary X Axis (top) Scale to -3 Minimum to +3 Maximum

We want to use the top horizontal axis along the bottom of the chart, so we need to switch the horizontal axes. You have to format the corresponding vertical axis to set this. Format the secondary vertical axis (right of chart) so that the horizontal axis crosses at the automatic position, which means at zero.

Format Secondary Y Axis (right) so Horizontal Axis Crosses at Automatic Position (bottom)

Now format the primary vertical axis (left of chart) so the horizontal axis crosses at the maximum axis value.

Format Primary Y Axis (left) so Horizontal Axis Crosses at Maximum Value

Oh noes! the axis is where we want it, but now we’ve shaded ABOVE the plotted curve. This is because area charts and column charts start at the horizontal axis and rise upward or hang downward to reach the plotted value. Unless the corresponding vertical axis is missing, so select the primary vertical axis (left of chart) and click Delete. All series in the chart use the secondary Y axis if it’s the only one available.

Now delete the primay X axis (top), or hide it by formatting it so it has no line, no tick marks, and no tick labels.

Delete Primary X Axis (top) or Hide (No Line, Tick Marks, or Labels)

Finally, hide the secondary Y axis (right of chart) by formatting it so it has no line, no tick marks, and no tick labels.

Hide Secondary Y Axis (right): No Line, Tick Marks, or Labels

Remove the legend and write a descriptive chart title, and the chart is done.

As promised, changing the values in G1:G2 change where the curve is shaded. Instead of -1.75 to 0.75, let’s change the values to ± 1.5.

Or let’s highlight the curve from 1 to 2.

See how easy it is to use a combination chart to highlight some or all of the area under a curve.

Combination Charts

Salary Chart: Plot Markers on Floating Bars

More Combination Chart Articles on the Peltier Tech Blog

Fill Under or Between Series in an Excel XY Chart

XY-Area Combination Charts

Fill Under One XY Series

Fill Between Two XY Series

Fill Between Overlapping Regions

Fill Under a Plotted Line: The Standard Normal Curve

More Combination Chart Articles on the Peltier Tech Blog

Another Simple Baseline for Excel Column Chart

Simple Baseline for Excel Column Chart

Fill Under a Plotted Line: The Standard Normal Curve

Fill Below Standard Normal Distribution

First Cut at Filling Below a Plotted Line

Filling Below a Plotted Line, Done Right

More Combination Chart Articles on the Peltier Tech Blog