XY Charts

Shaded Quadrant Background for Excel XY Scatter Chart

Wednesday, August 28, 2013 by Jon Peltier 65 Comments

Wednesday, August 28, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Shaded Background Quadrants

People often ask how to shade the background of an XY Scatter chart. This may help to define certain regions of performance or cost-benefit. The desired end result is something like this:

XY Chart With Colored Quadrant Background

This is a rework of an older article. The protocol has been simplified and the steps reordered to work more reliably in Excel 2013 and other recent Excel versions.

Scatter Plot Data

Here is the simple XY data used in this tutorial. The data scales between 0 and 20 on both axes.

Background Data

These two tables show the data and calculations needed to draw the shaded background areas in the chart.

The first table shows relevant values for the X and Y axis, including the minimum and maximum, as well as where we want the divisions between left and right shaded areas and between upper and lower shaded areas. You don’t need to use such a summary table, but you might find it helpful.

In this example, we want the areas to extend from X=zero (the left edge of the chart) to X=12, and from X=12 to X=20 (the right edge of the chart). Likewise we want them to extend from Y=zero (the bottom of the chart) to Y=9, and from Y=9 to Y=20 (the top of the chart).

The axis parameters were selected arbitrarily here, partly to show that the quadrants don’t have to be of equal width or height. These are static values, but you could put formulas into the range C3:D5 if you wanted the divisions based on the statistics of the data or on any other variable criteria.

The second table above uses formulas that reference the first range:

The factor of 1000 is used in the formulas in C8:C11 simply because it will provide the appropriate resolution of the division between shaded areas.

This data will produce this background pattern:

The need for the “Bottom” area series is discussed at the end of this tutorial. It may be better to wait until indicated in the middle of this protocol before you read it.

Building the Chart

Start constructing the chart by selecting the XY data and inserting an XY Scatter Chart from the Insert tab of the ribbon. The legend has been included to help show all the series in the chart through this protocol.

Next, copy the data for the shaded regions, which is in C7:H11 (don’t include column B, which has intermediate values).

Select the chart, then click the little down-pointing triangle on the Paste button of the ribbon’s Home tab, and select Paste Special. You will see a dialog like this, and you should make sure these are the settings before clicking OK. In this case, Excel should figure them out correctly.

The following Paste Special dialog is for pasting data somewhere in a worksheet. This means that a cell or cells in the worksheet were selected when you clicked on Paste Special. Cancel this dialog, select the chart, and re-invoke Paste Special.

The chart now looks like this. Since the added series have a maximum X value of 1000, the original XY data with its maximum of 20 is pushed to the left edge of the chart. Don’t fret…

One by one, select each of the newly added series, and assign them to the secondary axis. This is the first setting you can control if you select the series and press Ctrl+1 to format it. It also can be done from the new Change Chart Type dialog in Excel 2013, shown at a later step of this procedure.

Excel has added a new vertical axis along the right edge of the chart.

Add the secondary horizontal axis. The easiest way in Excel 2013 is to click on the Plus icon next to the chart, click on the right-pointing triangle next to “Axes”, and check the appropriate box.

Using Excel 2013's Plus Icon to Add Secondary Horizontal Axis

You could also use Excel 2013’s Add Chart Element control on the Chart Tools > Design tab.

Using Excel 2013's Add Chart Element Control to Add Secondary Horizontal Axis

In Excel 2007 or 2010, go to the Chart Tools Layout tab, click the Axes down-pointing triangle, then Secondary Horizontal Axis, then select show Left to right Axis.

Using Excel 2007/2010's Chart Tools Layout Controls to Add Secondary Horizontal Axis

Here is the chart with all four axes deployed. Now the original XY data on the primary axis overlaps with the added data on the secondary axes.

Secondary Horizontal Axis Added to Chart

Now we need to change the added series from XY to Stacked Area. This is very easy with Excel 2013’s new Change Chart Type dialog. Right click on any series in the chart and choose Change Series Chart Type… The dialog shows the chart with a list of series in the chart. Beside each series is a dropdown showing its chart type and a checkbox showing its axis group.

We could have changed the axis group of the added series to secondary in this dialog, as long as we did so before selecting a different chart type.

This panel shows all series in the chart, after changing axis group of the added series, but before changing their chart type to Stacked Area.

Click on the chart type dropdown, and you’ll see a collection of chart types. The XY Scatter type is selected, and the cursor is hovering over the Stacked Area icon.

Here is the panel of chart series after changing the added series to Stacked Area.

If you’re using Excel 2007 or 2010, you have to right click on each series you need to change, one at a time, and select Change Chart Type. You will see this dialog. The XY Scatter type is selected, and the cursor is hovering over Stacked Area.

If you have to change one series at a time, the intermediate chart will look rather strange. This is the chart after changing one series to Stacked Area in Excel 2010. If you only changed one series on the secondary axis in 2013, it would also look like this.

Chart With One XY Series Changed to Area

But don’t panic: after changing all of the series, the chart will look the same in all three Excel versions.

Here is the chart with all added series changed to Stacked Area.

Chart With All XY Series Changed to Area

The need for the “Bottom” area chart series becomes evident in the chart above, with a little explanation. If you are interested, there is an explanation at the end of this tutorial.

The next step is to format the area series. Here I’ve made the “bottom” series completely transparent, and applied a transparency of 50% to the rest. This tones down the intense colors, so they don’t overshadow the actual data, and also allows the gridlines to show through.

Chart With Partially Transparent Background Areas

Select the secondary (top) horizontal axis and press Ctrl+1. In the formatting dialog or task pane, change the Axis Type to Date Axis. This changes the diagonal lines between adjacent shaded regions into vertical lines.

Secondary Horizontal Axis Converted to Date Scale

Select the secondary (top) horizontal axis, and press Delete. Then select the secondary (right) vertical axis and press Delete.

Finally, rescale both axes as appropriate. For this example, both should have a minimum of 0, a maximum of 20, and a major unit of 5.

Finally, delete the legend, and the chart is finished.

Finished XY Chart With Colored Quadrant Background

Variations

Once you know the approach, it is not hard to expand the grid to 3-by-3. In the table below I’ve lightened the zero values to enhance the data for the shaded areas.

XY Chart with 3-by-3 grid of background shaded regions

In fact, you can use any reasonable (i.e., visually and cognitively useful) number of shaded areas, and you don’t need the same number of rows and columns of colors.

It’s also easy to highlight a target region, especially since you only need the bottom area and the target area.

Why We Need the Bottom Fill Series

Here is the chart right after all of the secondary axis series have been converted to Stacked Area type.

The “bottom” series seems to be on top of all of the rest of the series, which is pretty strange.

If we delete the “bottom” series, the “lower left” series has disappeared from the lower left region of the chart and now it is seen on top of the chart. Broken!

So what’s going on, anyway?

Let’s start with only the “lower left” series, in the position we want it: the bottom left portion of the filled regions.

Look what happens when we move the category (horizontal) axis to the top of the chart, where the secondary category axis is found. Instead of filling below the “lower left” data points, the fill is above these points.

Appearance of Lower Left Series With Axis Above Plot Area

Aha! In an area chart, the first series fills between the category axis and the data points. So let’s start with the “bottom” series, with values of zero.

The “bottom” series starts from the category axis at the top of the chart, explaining why it appeared above the other areas, and extends to the bottom of the chart, or actually, to zero on the value (vertical) axis.

Let’s now add the “lower left” series, nearly transparent so we can see both series.

Bottom Series Gives Zero Baseline to Stack Lower Left Series Upon

The “lower left” values are added to the “bottom” values, so it fills below its data points, in front of the “bottom” area.

Let’s add the other three series:

Detail Showing Bottom Series Behind Other Transparent Areas

They all stack up nicely in front of the “bottom” series. Nice, just what we wanted.

Back to the non-transparent version that brought us here.

You may now return to the protocol.

Intelligent Excel 2013 XY Charts

Monday, February 18, 2013 by Jon Peltier 48 Comments

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

While writing last week’s introductory article about Excel 2013 charting, My First Excel 2013 Chart, I discovered a very cool bit of intelligence built into the function that inserts XY charts.

In prior versions of Excel, if you selected a rectangular range with N columns and inserted an XY chart with data in columns, the chart would use the first column for the X values, and the other columns for Y values for N-1 series. If your series didn’t all use the same X values, you had to define each series separately.

While playing with the Insert Chart dialogs, I discovered that Excel 2013 will offer several additional options if the data is arranged a certain way.

I’ll illustrate this coolness with the following data. There is an 8×8 grid of numerical data, with text labels (Greek letter names) in the row above the grid, and text labels (Latin letters) in the column to the left of the grid. Thedata is better suited for a line or bar chart because of the non-numerical categories in the first row and column.

Dumb data to show smart behavior

On the Insert tab, click on the XY Chart icon and you’ll see this dropdown.

XY Chart dropdown on Excel 2013 Insert ribbon tab

Click on “More Scatter Charts” at the bottom, and you’ll see this dialog.

Excel 2013 XY Charts dialog

There are thumbnails for three options, which will be discussed shortly.

If you mouse over any of the options, you’ll see a preview of how that chart would look. Double clicking on the thumbnail or preview inserts the chart into the worksheet.

Excel 2013 XY Charts dialog with mouseover view

The first XY chart option is what you would get in prior versions of Excel. The first column is used for the X values, and the other N-1 columns are the Y values. In this case, the first column contains text labels, which an XY chart cannot accommodate, so it uses the counting numbers 1, 2, 3, etc. instead.

Excel 2013 Default XY Chart

The second option available in this case shows one of the intelligent behaviors. Excel realizes that the first column doesn’t contain numerical values, so it ignores that column, and makes a chart with the number in the rest of the data range. The first numerical column is used for X, and the rest for Y.

Excel 2013 Smart XY Chart

I tested this with one, two, and three leading columns of non-numerical data, and Excel ignored them all. Presumably it will ignore any arbitrary number of leading columns of text.

That’s pretty smart. But the next trick, as we say here in Baaston, is “wicked smaat”.

Many times I’ve been asked how to select a range with alternating X and Y values and create an XY chart with one series for each pair of columns. In Quick Chart VBA Examples I presented code that parses such a range and spits out the desired chart. In that tutorial I showed how to process several different arrangements of X and Y, including alternating X and Y columns.

If your usable numerical data is contained in an even number of columns (either standalone, or with some data ignored as above), Excel will give you the option to treat the data as alternating columns of X and Y data. The result is this Wicked Smaat XY Chaat:

Excel 2013 Wicked Smart XY Chart

When the chart is selected, there is no highlighted data range, because the chart’s data is “too complicated” to display. This is because the series don’t share their X values.

If you select the first series, however, you can see that it uses the first column of the range for X and the second for Y.

Series 1 highlighted data

And if you select the second series, you can see that it uses the third column of the range for X and the fourth for Y. And so on, for the rest of the series i the chart.

Series 2 highlighted data

I discovered this intelligent behavior by accident, using some arbitrary data from a totally different example. But knowing about this intelligence, I can design a data range to take advantage of it.

Here is an even number of columns of numerical data, alternating X and Y values.

Data intentionally laid out for Excel 2013 Smart XY Chart

Here’s my wicked smart XY chart created using this data. The default formatting has markers but no lines.

Excel 2013 Smart XY Chart with markers only

You can use the Change Chart Type feature to change from a markers-only to a lines-and-markers format.

Excel 2013 Smart XY Chart with markers and lines

A lot of people are going to be very happy with this new functionality.

Posted: Monday, February 18th, 2013 under Excel 2013.
Tags: Excel 2013, XY Charts.
Comments: 48

Microsoft MVP Logo

Categorical XY Chart with Highlighted Categorical Ranges

Friday, September 14, 2012 by Jon Peltier 5 Comments

Friday, September 14, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

You may have sets of data for several categories, and you want to show the values for each category, and highlight some kind of range for each category (a permissible range, a target range, or some statistically computed range).

The following chart shows three categories (A, B, and C) with highlighted ranges and several data points for each.

Categorical XY Data with Highlighted Category Ranges

This combination chart is simple to make.

The Individual Values

We can plot the points as an XY Scatter chart series. This chart uses 1, 2, and 3 for categories A, B, and C.

XY Chart

There is substantial overlap between points, so it would be helpful to jitter the points, that is, spread them out laterally so they no longer overlap.

XY Chart with Jittered X Values

And of course, we want to show A, B, and C instead of 1, 2, and 3. Plus add the highlighted ranges for each category.

XY Chart with Jittered X Values and Text X Labels

The Data

We’ll use the following data. The floating columns are defined in B2:D5, and the actual points are described in F2:I17. Column F has the point identification, category and index, which will not be shown in the chart. Column G has X values 1, 2, and 3 corresponding to the categories A, B, and C. Column H has the jittered X values. Column I has the actual Y values.

Data for this tutorial

I’ve never encountered a useful jittering algorithm, so I fudged my own, which works well enough. The formula in H3 is

=G3+0.2*(RAND()-RAND())

This gives a random number within ±0.2 of G3, biased closer to the middle of the ±0.2 range.

Building the Chart

Make a stacked column chart with the data in B2:D5.

Stacked Column Chart

Format the bottom series (“Min”) so its fill is No Color. The result is the floating column chart that serves as a backdrop for the actual points.

Floating Column Chart

When an XY series is plotted on the axes of a column chart, an X value of 1 corresponds to the first category, 2 corresponds to the second category, etc. A value between 1 and 2 will be plotted proportionally between the first and second categories: 1.5 is midway between the columns at A and B. The jittered X values between 0.8 and 1.2 will fit within the width of the column at A.

Copy the range H2:I17, select the chart, and use Paste Special to add the data as a new series, with series in columns, series name in first row, category labels in first column.

Floating Column Chart with XY Data Added as Another Column

The new series was added as a new set of columns stacked on the floating columns and on extra categories further to the right.

Right click one of the added points, choose Change Chart Type, and select the XY variant with markers and no lines.

Floating Column Chart with XY Data Points on Secondary Axis

The XY points were initially plotted onto secondary axes, which are misaligned from the primary axes of the floating columns. Format the XY series so it is plotted on the primary axis to finish the chart.

Floating Column Chart with XY Data Points on Primary Axis

Posted: Friday, September 14th, 2012 under Combination Charts.
Tags: Column Charts, Combination Charts, XY Charts.
Comments: 5

Microsoft MVP Logo

Filled Histograms Using Excel XY-Area Charts

Wednesday, February 16, 2011 by Jon Peltier 6 Comments

I recently showed how to create Histograms Using Excel XY Charts. This technique produces a human-friendly numerical X axis scale, which is easier to read and harder to be deceived by than the bin labels used by column chart histograms.

The drawback of that technique is that it produces histogram bars in outline only, without a fill color. In this post I will show how to extend that technique to fill the bars, using the protocol from Fill Below an XY Chart Series with an XY-Area Combination Chart.

I started with the data from Histograms Using Excel XY Charts. I added a row at the top of the data for the left edge of the chart, and another row at the bottom for the right edge of the chart. I inserted a column between the Time and Count columns, containing calculated X values for the area chart that will provide the fill color.

The calculated values in the middle column are based on the small table to the right. It contains the minimum and maximum X values (minimum and maximum times) from the XY chart, and the minimum and maximum values I will use in the area chart series. Using 1000 for a chart that’s a few hundred pixels wide is a reasonable resolution, though you could use something larger, like 10,000. The conversion uses this formula (fill in the appropriate cell addresses for your worksheet):

Area Value = Area Scale Min + (Time - Time Min) / (Time Max - Time Min) * (Area Scale Max - Area Scale Min)

or something like this, depending on where the tables are in the worksheet:

=(A2-$F$2)/($F$3-$F$2)*($G$3-$G$2)+$G$2

We need to use both XY and Area chart types. The XY chart series provides the X axis formatting and border, and the Area chart series provides the fill.

If we just used an area chart, the time points would be equally spaced, so the points at the same time would not occur at the same X position, but at consecutive positions. There would be no vertical lines between bars; instead the bars would be shaped like trapezoids.

When we convert this axis to a date type, all the times are less than one (dates are whole numbers, remember, and times are fractions). The date axis treats days as whole number values, spaced according to their values, and ignores any times. Since the times occur within the same day (day zero), they occur along the vertical line at day zero, or January 0, 1900. To get the area series to look like bars with vertical sides, we need the times to be some integer multiple, hence our calculated values in the middle column.

Note: In cases where your bin boundaries are all integers, you can get away with using an area chart series without the XY chart series needed for the borders of the bars and for the axis. Simply use the protocol in Histograms Using Excel XY Charts with an area chart, and format both the fill and border of the area chart series as desired.

So let’s make the chart. Select the Time values and Counts in the first and third columns, not including the added first and last row, and create an XY chart. (Use Ctrl+Click to select multiple regions.) With one exception, these are the Excel 2003 version charts, but the Excel 2007 charts look the same, and with one exception, the steps are the same.

Copy the Area and Counts in the second and third columns, including the first and last row. Select the chart, and use Paste Special to add the data as a new series, categories in first column. Since the new X values are so much larger than the old, the first series is squashed against the left hand axis.

Format the new series so it appears on the secondary axis.

Excel adds only the secondary vertical axis, so we need to add the secondary horizontal axis. In Excel 2007, go to Chart Tools > Layout tab > Axes; in Excle 2003, go to Chart menu > Chart Options > Axes tab. The first axis makes its reappearance.

Convert the second series (red lines above) to an area type (colored in light blue with no border below).

Convert the secondary horizontal axis to a date scale axis. Excel 2007: right click on the axis and select Format Axis; Excel 2003: go to Chart menu > Chart Options > Axes tab. It’s filled above the lines, not below, but we’ll take care of that shortly.

Format the secondary horizontal axis so it is hidden. No line, tick marks, or tick labels.

In Excel 2003 you need to format the secondary vertical axis to make the secondary horizontal axis cross at the bottom. Uncheck the Axis Crosses At Maximum Value box.

Ignore this step in Excel 2007. If you make the date axis cross at the bottom, even if it’s hidden, it will carve out a little space for itself, and push the other horizontal axis away from the data. No matter how you juggle primary and secondary axes, no matter what order you do the steps, no matter which incantations you may use. Speaking of incantations, I have uttered many an epithet over this behavior. It’s only a few pixels, but it looks like crap. They must have been celebrating Geek Liberation Day or something the day they programmed this part of the Excel 2007 charting module. (I don’t know whether Excel 2010 has addressed this issue.)

The Excel 2007 chart is still filled light blue above the line. Select the secondary vertical axis, and press Delete. The Excel 2003 chart is filled below the line, so all we need to do is hide the secondary vertical axis (no line, no tick marks, no tick labels) or delete it.

Format your X axis with human-friendly scale parameters, add a few titles, et voilà! Oh yeah, I used a thicker line when the histogram was unfilled, but with it filled, you can use a thin line for the borders of the bars.

There is a slight misalignment of area and XY series in the exported Excel 2003 chart, evidenced by the sliver of white in a couple of the bars. This does not appear in the actual chart, only in the exported image. Excel 2007 does not exhibit this problem. If I worried about it, I could probably fudge the formula used to compute the area chart’s X values. Perhaps adding 1 to all the calculated values except in the first and last (highlighted) rows would do the trick, or using a larger scaling factor (10,000 rather than 1000), and nobody’d be any the wiser.

To me this is much less aggravating than the gap at the bottom of the Excel 2007 chart I had a mini-rant about earlier, because there is no teeny fudge factor that makes that misalignment go away, other than the scorched-earth approach of deleting the vertical axis.

More About Histograms on the Peltier Tech Blog

Posted: Wednesday, February 16th, 2011 under Chart Types.
Tags: Area Charts, Histograms, Statistics, XY Charts.
Comments: 6

Microsoft MVP Logo

Histograms Using Excel XY Charts

Monday, February 14, 2011 by Jon Peltier 11 Comments

I wrote a tutorial about Histograms long ago, and haven’t used any of that for my own histograms in years. I don’t use regular column charts, unless I need something really quick and dirty. Instead, I use XY charts for various reasons, and I follow the protocol in this post.

A histogram is basically a column chart with value ranges (bins) laid out in numerical order along the horizontal axis, and the height of the columns encodes the number of observations within that value range.

Data for a histogram typically looks like this, with the bin label in the first column and the count (frequency) within each bin in the second column.

The typical Excel histogram is a column chart from this data. It shows the data well enough, although having more than a dozen bins squashes the labels together making them hard to read.

Rotating the labels 90° unsquashes them, but they are equally hard to read unless you awkwardly swivel your neck.

Another problem with a plain old column chart is that the bins look numerical, but the axis is categorical, and does not represent the numerical values of the bin boundaries. Unless you can do math in your head, and can force your mathematical brain to ignore what your visual brain sees, you don’t know that the bins are of equal width. In this case they are equal, but I’ve seen histograms which have unequal bin widths represented by equal width bars. This is just another way of distorting the data.

To correct this problem, let’s modify the data, so we place the bin start values in the first column, and the count for the bin starting at each of these values in the second column.

Now the fun begins. This is a similar exercise to that used to generate step charts, but we need not just a vertical line segment connecting adjacent horizontal steps, we need vertical line segments connecting these steps to the horizontal axis at the bottom of the chart. So we need to modify the simple table above.

In the first block below I have stacked up three copies of the data. These are color coded so we can trace the data through the next steps.

In the second block I have indicated two cells that need to be deleted, with cells shifting up to fill the gap. The third block shows the data after deleting these two cells.

Finally I sorted the data by the first column, resulting in the striped last block below.

The rest is easy. Make an XY chart, using the lines-without-markers option, from this last block of data, and format it to look nice. The lines connecting the XY points trace the sides and tops of each histogram bar.

The advantage of this type of histogram is that the X axis is truly numerical. The axis can be formatted with the minimum, maximum, and spacing that you want. The min, max, and spacing can be natural “human-friendly” values, not values defined by statistical calculations to excessive significant figures. And you can see by inspection that the bins are of equal width.

The disadvantage of the XY chart histogram is that the bars are only present in outline: they are not filled with your favorite colors. In the next article I will show a technique to fill these bars. If you want a head start, you can refer back to the tutorial that shows how to Fill Below an XY Chart Series with an XY-Area Combination Chart.

More About Histograms on the Peltier Tech Blog

Posted: Monday, February 14th, 2011 under Chart Types.
Tags: Histograms, Statistics, XY Charts.
Comments: 11

Microsoft MVP Logo

XY Charts

Shaded Background Quadrants

Scatter Plot Data

Background Data

Building the Chart

Variations

Why We Need the Bottom Fill Series

More Peltier Tech Articles About Combination Charts

The Individual Values

The Data

Building the Chart

More About Histograms on the Peltier Tech Blog

More About Histograms on the Peltier Tech Blog