XY Charts

Trendlines and Chart Types in Excel

Friday, October 21, 2022 by Jon Peltier Leave a Comment

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

tldr; Stick to XY Scatter charts if you need trendlines for your data. Line charts may misrepresent the relationships in your data.

Trendlines and Chart Types

A user had problems with my Trendline Calculator for Multiple Series and sent me his workbook. It turns out, he was using the program on a Line chart, and I recalled that Line charts can have problems when calculating trendlines.

Here is some simple data, plotted in a Line chart. A trendline has been calculated, and the formula and R² are shown in the chart. Wow, that’s a very nice straight line fit, with all points exactly on the line.

Trendline on a Line Chart: Bad Statistics Warning

But wait! Look at the X axis: the labels go from 1 to 2, then to 5 and 6, then to 9 and 10, and the spacing between labels is equal! That happens because unless Excel recognizes the X values as dates, it treats them as non-numeric text labels, and spaces them evenly as categories across the chart. Excel also uses the category number, not the inherent numerical value of the category label, when calculating the trendline formula. So Excel uses 1 through 6 as X values, which match the Y values perfectly, resulting in a perfect fit to the formula y = x.

The solution, of course, is to use an XY Scatter chart instead. X and Y values are all treated as numeric, and these numbers are used as is when calculating the trendline formula. The horizontal spacing of the points matches the X values, and we see that while the fit is rather close, the points are not all exactly aligned with the trendline.

Trendline on an XY Scatter Chart: Good Statistics

Another solution would be to format the X axis as a Date axis. This forces Excel to treat the numbers numerically, even though they are not dates. Now the horizontal spacing of the points matches their true values, and the trendline matches the one calculated for the YX chart above.

Trendline on a Line Chart with a Date Axis: Good Statistics, but Why?

But using an XY Scatter chart type is better than using a line chart in most cases, even with a Date axis (unless you are working with dates). The X axis does not begin at zero, for one thing. Other chart formatting is designed for line charts (you can change the formatting, but why bother?).

Why Do People Use Line Charts Instead of XY Scatter Charts?

When inserting a chart, a user encounters a set of icons similar to those below. The Line chart icon shows data points connected by lines, while the XY Scatter icon shows points without connecting lines. Through inexperience or haste, I think a lot of people insert Line charts because they want lines connecting their points.

Portion of Chart Type Selection User Interface

But this is not the difference between Line and XY charts. In both cases, you can format your data with or without markers, with or without connecting lines. The difference is in how the two chart types treat X values. As we saw above, Line charts will treat numerical X values as non-numeric labels, potentially spoiling your whole day.

More About Trendlines and Regression

Posted: Friday, October 21st, 2022 under Statistics.
Tags: Line Charts, Regression, Statistics, XY Charts.
Comments: none

Combination Chart for Multi-Factor Test Results

Monday, January 31, 2022 by Jon Peltier Leave a Comment

Monday, January 31, 2022 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

I’m not sure what to call this chart, other than it’s a combination chart (stacked column and XY scatter) and it requires some additional data manipulation. The chart shows test results from a test program that includes different factor levels: Groups (Alpha, Beta, Gamma), Classes (High and Low), and Treatments (A, B, C), with four replications per set of factors. The intent is to show each replication, while also showing how the groups, classes, and treatments compare against each other.

Test results from multi-factor program, plotted by treatment, class, and group.

About the Test Program

A few years ago I wrote Make Technical Dot Plots in Excel, which showed each test result from a relatively simple test program. A typical one of these charts is shown here, where all results from each of the three test conditions are shown.

The above chart shows three treatments, but what if we have other factors in the test program. We have two classes and three groups, which might refer to any other way to categorize testing conditions. Age groups of test subjects, species of an infectious agent being targeted, season of the year, etc.

A comment to the article cited above posted a data set, part of which I’ve shown below (after anonymizing it). You can download a CSV file with the data set here: test_results.csv. For each combination of Group, Class, and Treatment, there are Four replications. We want to plot each of these replications in the final chart.

Here is how the first several rows of data look when opened in Excel and converted into a Table.

Exploring the Data

Simple Charts

When I first get my hands on a new data set, I like to make a chart or three to see if there are any obvious insights. My first chart was a simple line chart of the data column. The X-axis is simply the point number, from 1 to 72 (the number of results in the program).

Line chart of test results by record number

Nothing is immediately obvious, other than the scores being skewed toward the upper end of the range (more 2s and 3s on a scale of 0 to 3).

I change this chart to use the Treatment column for category labels. No insights come to light.

I further adjusted the chart to include the Class column to the category labels. Nothing but clutter along the axis.

Line chart of test results by treatment and class

When I included the Group column to the category axis labels, the clutter increased.

Line chart of test results by treatment, class, and group

With the appropriate data layout, multiple columns of category labels can bring order to the chart. This is obviously not the appropriate data layout, but we’ll get to it shortly.

Pivot Table and Charts

Sometimes a Pivot Table helps with displaying data. The Picot Table below has Group, Class, and Treatment in the Rows area, Rep in the Columns area, and the Scores in the Values area. Each test result appears in the cross-tabbed Pivot Table, with one set of conditions per row and the four associated test results in that row. Easier to see all at once, but not (yet) easier to find any meaning.

Here is a pivot chart, type clustered column. It shows all results, but the bars are so pinched together it’s hard to make sense of it. That horizontal axis looks better, though, since it is laid out in a much nicer way than the cluttered one above.

A line chart of the same pivot data looks a bit less cluttered, but it’s hard to see how many points might be occupying the same space. It would be better if we could move some left or right by a small amount (the way the columns are spread out in the previous chart), but line charts do not allow that.

How do we get that nice layout? I’ve described it in Chart with a Dual Category Axis and numerous other articles, but I’ll describe it again. Below is the Pivot Table, where I’ve highlighted the chart source data. The blue highlights indicate the Y Values (the test results in the Pivot Table’s Values area), the red highlights indicate the Series Names (in the Columns area), and the purple highlights indicate the X Values or Category Labels (in the Rows area).

There are three columns of category labels, just like in the original Table of data. What makes the axis work nicely in the Pivot Chart is that repeated labels are replaced by blanks. These blanks tell Excel how to construct the labels: place A-B-C along the axis, center Low and High under successive blocks of A-B-C, and center Alpha, Beta, and gamma under blocks of Low and High.

What we can do is use these three columns of the Pivot Table for our chart’s category axis, and then combine this with XY Scatter series where we calculate X values that include the lateral offset to reduce overlapping of the markers. (We could also construct a range like this by hand, but the Pivot Table is here, so let’s use it.)

Calculating X Values

My previous article, Clustered Column and Line Combination Chart, shows how to calculate X values to position XY Scatter markers precisely over columns in a chart. We’ll use a similar approach here. I’ve columns to the Table to accommodate these calculations.

Each combination of factors in the Table results in one category in the Pivot Chart and in our ultimate chart. So we need to calculate Category Number (“CatNum” in the Table). The formula in cell F3 (which Excel fills into the whole column of the Table) is

=INT((ROW()-ROW($F$2)+3)/MAX([Rep]))

Each replicated test takes up a fraction of the width of each category (“Frac” in the table), with the first replication at zero and the last replication at 1. The formula in G3 is

=([@Rep]-1)/(MAX([Rep])-1)

I want to leave space around the reps within a category, leaving a space between categories. This is like the gap width in a column chart. I’ve placed a gap width of 0.5 in cell $N$3, and this formula in cell H3 (under Decimal)

=($N$3/2+[@Frac]*(1-$N$3)

Finally, I add this Decimal amount to CatNum minus one in I2 (under X) using the formula

=[@CatNum]-1+[@Decimal]

I certainly could have combined all of these calculations into a single “X” column, but for ease of explanation, I used several columns for intermediate calculations. Many Excel users love their big ugly formulas that take up fewer columns, but our column limit now stretches beyond 256 columns, while our cognitive limit seems to have shrunk. Helper columns are your friends.

Table of test results and calculated X values

Let’s just see how it looks. First I’ll plot the Score column in an XY Scatter chart, using lines and markers. Without specifying X values, Excel will just use the counting numbers 1, 2, 3, up to the number of points.

XY chart (markers and lines) of test results by record number

Similar to our first line chart at the beginning of this article. Those connecting lines need to go, and let’s format the markers to have no fill, which lets us see more easily when points overlap.

XY chart (markers only) of test results by record number

Now let’s use the calculated X values for the data. There are 18 categories, so let’s set the axis maximum to 18. The data is also grouped into threes, that is, A, B, and C, so let’s set the axis major unit to 3 and minor unit to 1. We have four replications within each vertical slice of the chart.

XY chart of test results by record number with axis divided into treatment, class, and group

We can make one further improvement. The tests within each category would stand out from other categories more visibly if we plot each category in a different color. To do this, I’ve added three more columns to the Table, labeled A, B, and C for the three treatments. The formula in J2, filled into the three added columns, is

=IF($C3=J$2,$E3,NA())

Table of test results and calculated X values with results split by treatment

Here I’ve plotted the three different treatment columns in place of the one Score column, and colored each series distinctly.

XY chart of test results by record number with axis divided into treatment, class, and group and with markers color-coded by treatment

Building the Chart

We’re going to make a combination chart. We’ll start with a column chart using the Rows area of the Pivot Table as category labels and averages for each category of tests as the column Y values. To this, we’ll add XY Scatter series using our calculations in the data Table for X values and the test results as Y values.

We can’t use a Pivot Chart, since a Pivot Chart is constrained to plot all data in the Pivot Table and no data from outside the Pivot Table. But that’s no impediment, as I described in Making Regular Charts from Pivot Tables.

I’ve added three columns, A, B, and C. to the right of the Pivot Table for the average test result values (much as I did for the test results in the Table). These will serve as a background to the individual test results. The formula in X4 (filled into the range X4:Z21) is:

=IF($R4=X$3,AVERAGE($S4:$V4),0)

Pivot Table with Averages in Nearby Range

One thing to be careful of is the sorting in the original data and in the Pivot Table. The original data lists Class in the order Low-High. Default sorting in the Pivot Table lists Class in the opposite order, High-Low. You can change the order by clicking on a pivot item label and dragging it to where you want it.

Create the Chart

Select the columns of average data; include the header row and Excel will use these cells as series names. Then insert a column chart. I’ve made a stacked column chart, so the overlap is 100% and all columns are centered within their categories. The chart’s source data is highlighted in the worksheet.

Column chart of averages by record number

Now add the category labels from the Pivot Chart. I’ll add them bit by bit so you can see how Excel builds up the multiple tiers of labels.

You could use the Select Data dialog to add the labels, but I find that tedious. It’s much easier to edit the SERIES formula. Select the blue columns and this will appear in the Formula Bar (the precise address will depend on the location of the Pivot Table in the sheet):

=SERIES(Sheet1!$X$3,,Sheet1!$X$4:$X$21,1)

The syntax of the SERIES function is

=SERIES([series name],[X values],[Y values],[series number])

Since there are no X values specified, Excel just uses 1, 2, 3, etc. in the chart. Do assign X values, put your cursor between the two commas where the X values should go, select the range in the sheet, then press Enter. The formula should now look like this:

=SERIES(Sheet1!$X$3,Sheet1!$R$4:$R$21,Sheet1!$X$4:$X$21,1)

You only need to add this to one series, since all series (except for XY Scatter series) share X values/category labels.

The worksheet now shows these highlights, and the column of Treatments is used as category labels in the chart.

Let’s include the column of Class labels in column Q. Simply edit the series formula to include the two-column range:

=SERIES(Sheet1!$X$3,Sheet1!$Q$4:$R$21,Sheet1!$X$4:$X$21,1)

The highlighted regions and the chart show the adjustment.

Column chart of averages by treatment and class

One more time: edit the SERIES formula to include Group in column P, so the formula looks like this. Of course, I could have just selected all three columns of the Rows area two steps ago, but I wanted to show how the category label structure develops.

=SERIES(Sheet1!$X$3,Sheet1!$P$4:$R$21,Sheet1!$X$4:$X$21,1)

The worksheet highlights and chart axis show the change.

Column chart of averages by treatment, class, and group

We have the nice category axis we wanted, now let’s do a little cleanup. Set the scale of the Y-axis to minimum=0, maximum=3, major unit=1. Select one of the column series and set gap width to zero so the columns fill the width of each category. Lighten up the colors: I set the transparency to 75% or so, which makes the colors light and lets the gridlines show through.

Add the Test Results

Now it’s time to add the actual test results. Select and copy the last four columns of the Table (X, A, B, and C) including the column headers. Select the chart, click Paste Special from the Paste dropdown on the Home tab of the ribbon, and make the appropriate selections in the dialog: Add as New Series, Values in Columns, Series Names in First Row, Categories in First Column.

Paste Special - Add as New Series, Values in Columns, Series Names in First Row, Categories in First Column

You can also Paste Special with Alt+E+S, which is a legacy of the old menu structure of Excel 97-2003, and which is permanently ingrained in my muscle memory. Another more recent shortcut that I can never remember is Ctrl+Alt+V.

Excel adds the new series as more sets of stacked columns.

Chart with test results added (as columns, temporarily)

That’s easy to fix. Right-click on any series in the chart, and choose Change Series Chart Type. This rich dialog pops up with a preview of the chart and a list of all series in the chart. Note that all series are Stacked Column.

Change Chart Type dialog before changing chart types.

Change each of the newly added series to Scatter using the Chart Type dropdowns. Excel will check the Secondary Axis box for each, which in this case is what we want.

Change Chart Type dialog after changing chart types.

The resulting chart is starting to look good.

Test results converted to XY scatter series on secondary axis

Format the scale on the secondary (upper) horizontal axis: minimum=0, maximum=18, major unit=3, minor unit=1.

Secondary horizontal axis formatted to nicely line up with primary horizontal axis

Add major and minor secondary vertical gridlines.

Format the secondary (upper) horizontal axis to show no labels. Delete the secondary (right-hand-side) vertical axis.

Finally, format the XY Scatter series so the markers have no fill. This makes overlapping points easier to see.

Posted: Monday, January 31st, 2022 under Combination Charts.
Tags: Column Charts, Combination Charts, Data Techniques, XY Charts.
Comments: none

Clustered Column and Line Combination Chart

Monday, January 24, 2022 by Jon Peltier 6 Comments

Monday, January 24, 2022 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Clustered Column and Line Combination Chart, with markers aligned over bars.

Combination charts in Excel are pretty easy, once you figure them out. But sometimes they present challenges. If you make a combination clustered column chart and line chart, it takes special treatment to align the markers over the columns.

I will warn you that adding several series of lines to a chart with several series of columns might make your chart cluttered and difficult to read, especially if the lines and columns need different axes to show different types of data (for example, dollar sales vs. percentages of target). If that’s the case, you should break down the data into more easily digestible pieces, and use multiple charts for improved clarity.

I often use this technique when I need some kind of special highlighting or labeling, and my added data is partly or totally hidden (by formatting with no markers and no lines).

Setup and First Attempt

We’ll start with data for three categories (Alpha, Beta, and Gamma) and three series (Red, Green, and Blue). These might correspond to three companies over three years, or any other grouping you have in mind.

In addition, we want to display columns and lines for each series, showing perhaps target sales values as columns and actual sales as markers on the lines. Here is our data and the separate column and line charts.

Data for the Clustered Column and Line Combination Chart, and separate clustered column chart and line chart.

Making a combination chart is pretty easy. Start by plotting all of the data using one of the chart types in the finished chart. I started with a column chart, but it would work the same if you start with a line chart.

Right-click on any series in the chart, and choose Change Series Chart Type from the pop-up menu to open the Change Chart Type dialog

If you right-click just anywhere on the chart, the menu option is Change Chart Type, and when you select Combo, Excel will use its own favorite combination, and you will have to override Excel’s choices. In this case it would have been okay, but generally Excel chooses something other than what you want.

Change Chart Type dialog for six clustered column chart series

Combo is selected in the list along the left of the dialog, there is a preview of the chart, and a list of all series in the chart, with a dropdown to select the chart type and a checkbox to select the axis of each series.

Change the chart type of each of the last three series to Line with Markers, and leave the Secondary Axis checkboxes unchecked.

Change Chart Type dialog for three clustered column chart and three line chart series

The resulting combination chart looks just like the preview (well, perhaps I’ve formatted it a bit).

Clustered Column and Line Chart - First Attempt

That was pretty easy, and it looks pretty good, except for one thing. The markers are all centered in each category and are not aligned over their respective columns. The red, green, and blue markers are all centered on the green column.

This is a consequence of how Excel draws line charts. Each data point fits into a category, and it is centered on a category. Unlike clustered column charts, where the points (the columns) are distributed within a category according to properties Gap Width and Overlap.

So we can’t use line chart series to align markers over columns, but all is not lost. We can instead use XY scatter series, because Excel will plot them along the category axis as if the category axis has a numerical scale.

Equivalent Category Axis Scaling

Let’s examine our simple column chart, with three text labels (categories) along its category axis.

Category Axis with Three Text Categories

Numerical Category Axis Scaling?

If we plot XY scatter data on the chart, Excel treats the categories as if the first category is at X=1, the second at X=2, and so on.

Category Axis with Three Numerical Categories

For the XY scatter data, we can consider the axis as a continuous numerical scale starting at the first category number minus 0.5 and ending at the last category number plus 0.5, or in our example, from 0.5 to 3.5. Each category takes up the space from the category number minus 0.5 to plus 0.5.

Category Axis with a Continuous Numerical Scale

We can calculate X values for our XY data to position the markers wherever we want them. If we had used X values of 1, 2, and 3, our XY series would line up on the green columns just like our line chart series. But we can see that our Red X values need to be a little less and the Blue X values a little more than the category number.

How do we know the precise X values? We can guess and adjust them by trial and error, but we can also calculate them easily enough (you’re not afraid of a few formulas, are you??).

Gap Width and Overlap

Excel’s column and bar charts use two parameters, Gap Width and Overlap, to control how columns and bars are distributed within their categories.

Gap Width is the space between bars in adjacent categories, given as a percentage of the width of a column in the chart. The default is 219%, which means the gap is 2.19 times the width of a column.

Overlap is the amount that columns in one series overlap columns in the next series within a category. The default is -27% (below left), meaning that there is a space 0.27 times the width of a column between adjacent series. A positive overlap of 27% (below right) means that the columns actually overlap by 0.27 times the width of a column.

I don’t know where Microsoft came up with these default values, but they are easy to adjust. Select any series and press Ctrl+1 (the universal shortcut to open the formatting user interface for the selected object in Excel). In the task pane, move the sliders or type in the desired values. I usually choose a gap width of 0.5 to 1.5 (and most frequently 0.75 to 1.0), and an overlap of zero (so adjacent series of columns are touching).

Calculating X Values

So let’s set up the calculations. Here are some variables:

BW: width of a bar (or column) = 100 GW: gap width as percentage of BW OL: overlap as percentage of BW iSrs: the number of the series nSrs: the number of series iCat: the number of the category

The X value of a point is the X value of the left edge of a category plus the distance the point is from that edge of the category divided by the width of a category.

The left edge of a category is the category number minus 0.5:

Category Edge = iCat - 0.5

Calculating X from Gap Width and Overlap

The red marker is half a gap width plus half a bar width from the edge of the category.
The green marker is half a gap width plus one and a half bar widths plus one overlap from the edge of the category.
The blue marker is half a gap width plus two and a half bar widths plus two overlaps from the edge of the category.

The general expression is:

Marker Distance = 0.5 * GW + (iSrs - 0.5) * BW - (iSrs - 1) * OL

The width of a category is the gap width (half on each side) plus the bar width times the number of series plus the overlap times the number of series minus one. Actually minus the overlap, because a negative overlap adds to the total width:

Category Width = GW + nSrs * BW - (nSrs - 1) * OL

The complete expression for the X value of a marker is:

X = iCat - 0.5 + [0.5 * GW + (iSrs - 0.5) * BW - (iSrs - 1) * OL] / [GW + nSrs * BW - (nSrs - 1) * OL]

We can simplify this formula since all red markers are offset within a category by the same amount, as are all blue markers, and all red markers. We can then calculate each offset only once and use a lookup formula for each point.

X = iCat + Xoffset Xoffset = - 0.5 + [ 0.5 * GW + (iSrs - 0.5) * BW

Here is the data range I have set up:

Range of calculations for X values of XY series in combination chart

B2:H6 contains my original column and line chart data. B15:D18 is where I calculate the Xoffset values based on values of Gap and Overlap entered in the appropriate cells. The formula in D16 (filled down to D18) is:

=($B$16/2+($C16-0.5)*100-($C16-1)*$B$18)/($B$16+MAX($C$16:$C$18)*100-(MAX($C$16:$C$18)-1)*$B$18)-0.5

B8:H13 contains the helper row of category numbers in column B and the helper row of series numbers in row 13. There are X and Y values for the three XY series. The Y values come from F4:H6 in the table above. The X values in cell C10 (and copied into C10:C12, E10:E12, and G10:G12) are calculated by this formula:

=$B10+XLOOKUP(C$13,$C$16:$C$18,$D$16:$D$18)

Clustered Column and XY Scatter Combination Chart

There are several ways to generate the combination chart. You can start with a column chart with three series then add the XY data. To add the Red XY data, copy the range C9:D12, select the chart, and choose Paste Special from the Paste dropdown on the Home tab of the ribbon. Select the appropriate options in the dialog: New Series, Values in Columns, Series Names in First Row, Categories in First Column, and DO NOT CHECK Replace Existing Categories.

The data is added as another column series; we’ll fix that shortly. Repeat with the Green data in E9:F12 and the Blue data in G9:H12. Here is the column chart:

Convert to a combination chart as we did above for the column-line chart. Right-click on any series, and select Change Series Chart Type from the pop-up menu. Change the chart type of the last three series to Scatter with Straight Lines and Markers, and UNCHECK the Secondary Axis checkbox for all XY series.

Change Chart Type dialog for three clustered column chart and three XY scatter chart series

Another way to generate the combination chart is to build a column chart using the block of data in C3:H6, changing the last three series to XY Scatter, then changing the data for these three series. Change the chart type before changing the data.

Changing the data is easy. You could right=click on the chart and choose Select Data from the pop-up menu and wrangle with that uncomfortable dialog, but my favorite way is to edit the SERIES formula directly. For example, the formula for the red scatter series starts out as:

=SERIES(Sheet1!$G$3,Sheet1!$C$4:$C$6,Sheet1!$G$4:$G$6,4)

but I edit it to:

=SERIES(Sheet1!$D$9,Sheet1!$C$10:$C$12,Sheet1!$D$10:$D$12,4)

You can type the new row and column addresses, or you can select a reference in the formula (select both the sheet name and the cell address), then drag to select the new data range in the worksheet with the mouse.

The Finished Clustered Column and XY Scatter Combination Chart

Here is the complete data range plus the chart.

Calculations and chart for 219% gap width and -27% overlap

Looks good. Let’s check with the same gap width but a positive overlap.

Also looks good. How about a smaller gap and zero overlap?

Calculations and chart for 150% gap width and zero overlap

Still looks good. What if we add a series and a category?

Calculations and chart for 100% gap width and zero overlap, with an added category and series

Looks good, except that all those lines make the chart a bit cluttered. What if we plot markers without lines?

Clean up the chart with markers and no lines

The calculations work and the cleaned-up chart looks pretty good.

Make Excel Chart Gridlines Square

Monday, November 15, 2021 by Jon Peltier Leave a Comment

Monday, November 15, 2021 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Excel does a pretty good job of scaling its chart axes. But there are times when you wish it would do a better job. One case is shown in XY Scatter chart below. All of the points have X and Y values between 0 and 7, but because the chart itself is rectangular, the gridlines are spaced differently along the X and Y axes. Wouldn’t it be nicer if the spacing was the same along both axes, giving you square gridlines?

Excel XY Scatter Chart with Rectangular Gridlines

There are a few ways to accomplish this, not including the tedious manual method of clicking and dragging with the mouse or trying a sequence of values for the axis maximums to try to make it work out. I’ll use VBA to handle this task.

Square Gridlines by Changing Axis Scales

The first approach works by measuring the chart’s plot area dimensions, locking in the axis scale parameters, and using the scale to determine how far apart the gridlines are horizontally and vertically. Then the axis which has the larger spacing has its maximum increased so its gridline spacing shrinks to match the spacing on the perpendicular axis.

I wrote this function: pass in the chart you want to be squared up, and the function does its job.

Function SquareGridChangingScale(myChart As Chart)
  With myChart
    ' get plot size
    With .PlotArea
      Dim plotInHt As Double, plotInWd As Double
      plotInHt = .InsideHeight
      plotInWd = .InsideWidth
    End With

    ' Get axis scale parameters and lock scales
    With .Axes(xlValue)
      Dim Ymax As Double, Ymin As Double, Ymaj As Double
      Ymax = .MaximumScale
      Ymin = .MinimumScale
      Ymaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    With .Axes(xlCategory)
      Dim Xmax As Double, Xmin As Double, Xmaj As Double
      Xmax = .MaximumScale
      Xmin = .MinimumScale
      Xmaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    ' Tick spacing (distance)
    Dim Ytic As Double, Xtic As Double
    Ytic = plotInHt * Ymaj / (Ymax - Ymin)
    Xtic = plotInWd * Xmaj / (Xmax - Xmin)
          
    ' Keep plot size as is, adjust max scales
    If Xtic > Ytic Then
      .Axes(xlCategory).MaximumScale = plotInWd * Xmaj / Ytic + Xmin
    Else
      .Axes(xlValue).MaximumScale = plotInHt * Ymaj / Xtic + Ymin
    End If
  End With
End Function

I’ll describe several ways to call this function later, but for now, this line of code should be fine, either in your own sub or just in the Immediate Window:

SquareGridChangingScale ActiveChart

The squared-up chart is shown below. The gridlines are square, accomplished by changing the X-axis maximum to 12.9777. Good thing we have VBA to calculate this for us.

Excel XY Scatter Chart's Gridlines Made Square by Changing Axis Scales

There is a strange blank edge to the chart, but you could make it look less strange by formatting the plot area border to match the axes.

Excel XY Scatter Chart's Gridlines Made Square by Changing Axis Scales, with Plot Area Border

Let’s try with another chart. This is like the first, but the X values are twice as large as before, leading to a different scale.

This is the resulting chart. Again, the gridlines are square and the right edge looks blank. But we see another problem. The X-axis tick spacings are 2 units apart, compared to the Y-axis with its 1 unit spacing.

Force Equal Major Unit Spacing

I’ll modify my earlier procedure by adding an optional argument EqualMajorUnit. If this is True, the code will apply the same spacing to both axes before adjusting the axis maximum values. If it is False or omitted, the code will ignore tick spacing.

Function SquareGridChangingScale(myChart As Chart, Optional EqualMajorUnit As Boolean = False)
  With myChart
    ' get plot size
    With .PlotArea
      Dim plotInHt As Double, plotInWd As Double
      plotInHt = .InsideHeight
      plotInWd = .InsideWidth
    End With
        
    ' Get axis scale parameters and lock scales
    With .Axes(xlValue)
      Dim Ymax As Double, Ymin As Double, Ymaj As Double
      Ymax = .MaximumScale
      Ymin = .MinimumScale
      Ymaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    With .Axes(xlCategory)
      Dim Xmax As Double, Xmin As Double, Xmaj As Double
      Xmax = .MaximumScale
      Xmin = .MinimumScale
      Xmaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    If EqualMajorUnit Then
      ' Set tick spacings to same value
      Xmaj = WorksheetFunction.Min(Xmaj, Ymaj)
      Ymaj = Xmaj
      .Axes(xlCategory).MajorUnit = Xmaj
      .Axes(xlValue).MajorUnit = Ymaj
    End If
      
    ' Tick spacing (distance)
    Dim Ytic As Double, Xtic As Double
    Ytic = plotInHt * Ymaj / (Ymax - Ymin)
    Xtic = plotInWd * Xmaj / (Xmax - Xmin)
          
    ' Keep plot size as is, adjust max scales
    If Xtic > Ytic Then
      .Axes(xlCategory).MaximumScale = plotInWd * Xmaj / Ytic + Xmin
    Else
      .Axes(xlValue).MaximumScale = plotInHt * Ymaj / Xtic + Ymin
    End If
  End With
End Function

Here is our chart after running the second version of the function.

Excel XY Scatter Chart's Gridlines Made Square and Equal by Changing Axis Scales

That’s better, the grids are square and have equal spacing. Now the unfinished edge is along the top of the chart. Again, matching the plot area line color to the axes makes it look less awkward.

Excel XY Scatter Chart's Gridlines Made Square and Equal by Changing Axis Scales, with Plot Area Borders

Square Gridlines by Changing Plot Area Size

We achieved square gridlines above by keeping the plot area fixed and adjusting the axis scales. But what if we shrink the plot area by the amount needed to square up the gridlines? We’ll get blank space along the edge of the chart without some gridlines hanging off in space, and we can then center the plot area within the chart.

Here’s the new function. Note that I’ve kept the EqualMajorUnit argument.

Function SquareGridChangingPlotSize(myChart As Chart, Optional EqualMajorUnit As Boolean = False)
  With myChart
    ' get plot size
    With .PlotArea
      Dim plotInHt As Double, plotInWd As Double
      plotInHt = .InsideHeight
      plotInWd = .InsideWidth
    End With
        
    ' Get axis scale parameters and lock scales
    With .Axes(xlValue)
      Dim Ymax As Double, Ymin As Double, Ymaj As Double
      Ymax = .MaximumScale
      Ymin = .MinimumScale
      Ymaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    With .Axes(xlCategory)
      Dim Xmax As Double, Xmin As Double, Xmaj As Double
      Xmax = .MaximumScale
      Xmin = .MinimumScale
      Xmaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    If EqualMajorUnit Then
      ' Set tick spacings to same value
      Xmaj = WorksheetFunction.Min(Xmaj, Ymaj)
      Ymaj = Xmaj
      .Axes(xlCategory).MajorUnit = Xmaj
      .Axes(xlValue).MajorUnit = Ymaj
    End If
      
    ' Tick spacing (distance)
    Dim Ytic As Double, Xtic As Double
    Ytic = plotInHt * Ymaj / (Ymax - Ymin)
    Xtic = plotInWd * Xmaj / (Xmax - Xmin)
          
    ' Adjust plot area size, center within space
    If Xtic < Ytic Then
      .PlotArea.InsideHeight = .PlotArea.InsideHeight * Xtic / Ytic
      .PlotArea.Top = .PlotArea.Top + _
        (.ChartArea.Height - .PlotArea.Height - .PlotArea.Top) / 2
    Else
      .PlotArea.InsideWidth = .PlotArea.InsideWidth * Ytic / Xtic
      .PlotArea.Left = .PlotArea.Left + _
        (.ChartArea.Width - .PlotArea.Width - .PlotArea.Left) / 2
    End If
            
  End With
End Function

When we run this code, we get square gridlines with no funny gridline extensions and no large blank areas in the chart. The plot area is nicely centered.

Excel XY Scatter Chart's Gridlines Made Square by Changing Plot Area Size

This is much better than the first approach. Let’s see how it works with the other data.

Okay, we forgot to use EqualMajorUnit = True, so the square grid has different tick spacing on the X and Y axes. Let’s try again.

Excel XY Scatter Chart's Gridlines Made Square and Equal by Changing Plot Area Size

Yes, this second function is a major improvement over the first. It may even be better to set the default value of EqualMajorUnit to True.

Square Gridlines by Changing Chart Size

When the second function resized the plot area, we ended up with a bit of white space in the resulting chart. In some cases, this amount of white space was substantial. What if we shrink the whole chart, not just the plot area, and absorb the excess white space?

Function SquareGridChangingChartSize(myChart As Chart, Optional EqualMajorUnit As Boolean = False)
  With myChart
    ' get plot size
    With .PlotArea
      Dim plotInHt As Double, plotInWd As Double
      plotInHt = .InsideHeight
      plotInWd = .InsideWidth
    End With
        
    ' Get axis scale parameters and lock scales
    With .Axes(xlValue)
      Dim Ymax As Double, Ymin As Double, Ymaj As Double
      Ymax = .MaximumScale
      Ymin = .MinimumScale
      Ymaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    With .Axes(xlCategory)
      Dim Xmax As Double, Xmin As Double, Xmaj As Double
      Xmax = .MaximumScale
      Xmin = .MinimumScale
      Xmaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    If EqualMajorUnit Then
      ' Set tick spacings to same value
      Xmaj = WorksheetFunction.Min(Xmaj, Ymaj)
      Ymaj = Xmaj
      .Axes(xlCategory).MajorUnit = Xmaj
      .Axes(xlValue).MajorUnit = Ymaj
    End If
      
    ' Tick spacing (distance)
    Dim Ytic As Double, Xtic As Double
    Ytic = plotInHt * Ymaj / (Ymax - Ymin)
    Xtic = plotInWd * Xmaj / (Xmax - Xmin)
          
    ' Adjust chart size
    If Xtic < Ytic Then
      .Parent.Height = .Parent.Height - .PlotArea.InsideHeight * (1 - Xtic / Ytic)
    Else
      .Parent.Width = .Parent.Width - .PlotArea.InsideWidth * (1 - Ytic / Xtic)
    End If
            
  End With
End Function

Some caveats apply to this approach. When you resize the chart, the chart title may decide it needs a line break, which will change the plot area size, and make the gridlines not square. Here are the results of our charts from the two data sets, without fixing the tick spacing mismatch of the second data set.

Excel XY Scatter Chart's Gridlines Made Square by Changing Chart Size

Here is the chart for the second data set, with EqualMajorUnit set to True.

Square Gridlines by Changing Chart Size

The only way to improve my functions is to ignore the first and combine the last two, passing in the parameter ShrinkChart which tells the function whether to adjust the plot area (if False) or chart size (if True).


Function SquareXYChartGrid(myChart As Chart, ShrinkChart As Boolean, _
    Optional EqualMajorUnit As Boolean = False)
  
  With myChart
    ' get plot size
    With .PlotArea
      Dim plotInHt As Double, plotInWd As Double
      plotInHt = .InsideHeight
      plotInWd = .InsideWidth
    End With
        
    ' Get axis scale parameters and lock scales
    With .Axes(xlValue)
      Dim Ymax As Double, Ymin As Double, Ymaj As Double
      Ymax = .MaximumScale
      Ymin = .MinimumScale
      Ymaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    With .Axes(xlCategory)
      Dim Xmax As Double, Xmin As Double, Xmaj As Double
      Xmax = .MaximumScale
      Xmin = .MinimumScale
      Xmaj = .MajorUnit
      .MaximumScaleIsAuto = False
      .MinimumScaleIsAuto = False
      .MajorUnitIsAuto = False
    End With

    If EqualMajorUnit Then
      ' Set tick spacings to same value
      Xmaj = WorksheetFunction.Min(Xmaj, Ymaj)
      Ymaj = Xmaj
      .Axes(xlCategory).MajorUnit = Xmaj
      .Axes(xlValue).MajorUnit = Ymaj
    End If
      
    ' Tick spacing (distance)
    Dim Ytic As Double, Xtic As Double
    Ytic = plotInHt * Ymaj / (Ymax - Ymin)
    Xtic = plotInWd * Xmaj / (Xmax - Xmin)
          
    If ShrinkChart Then
      ' Adjust chart size
      If Xtic < Ytic Then
        .Parent.Height = .Parent.Height - .PlotArea.InsideHeight * (1 - Xtic / Ytic)
      Else
        .Parent.Width = .Parent.Width - .PlotArea.InsideWidth * (1 - Ytic / Xtic)
      End If

    Else
      ' Adjust plot area size, center within space
      If Xtic < Ytic Then
        .PlotArea.InsideHeight = .PlotArea.InsideHeight * Xtic / Ytic
        .PlotArea.Top = .PlotArea.Top + _
          (.ChartArea.Height - .PlotArea.Height - .PlotArea.Top) / 2
      Else
        .PlotArea.InsideWidth = .PlotArea.InsideWidth * Ytic / Xtic
        .PlotArea.Left = .PlotArea.Left + _
          (.ChartArea.Width - .PlotArea.Width - .PlotArea.Left) / 2
      End If
    End If
            
  End With
End Function

Here is how you might call the function from one Sub, which determines which charts were selected and applies the function to each.

Sub SquareXYGridOfSelectedCharts()
  If Not ActiveChart Is Nothing Then
    SquareXYChartGrid ActiveChart, True, True

  ElseIf TypeName(Selection) = "DrawingObjects" Then
    Dim shp As Shape
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        SquareXYChartGrid shp.Chart, True, True
      End If
    Next

  Else
    MsgBox "Select one or more charts and try again.", vbExclamation, "No Chart Selected"
  End If
End Sub

Articles about Axis Scales on Peltier Tech Blog

Posted: Monday, November 15th, 2021 under Chart Axes.
Tags: VBA, XY Charts.
Comments: none

Precision Positioning of XY Data Points

Wednesday, October 14, 2020 by Jon Peltier 2 Comments

Often when I plot data in a column, line, or area chart, I want to plot additional points on that chart. For this I use an XY Scatter type series for the extra data points. These added points may be used for additional labels or other purposes.

There are a few tricks for positioning of XY data points. I use these tricks in many of my tutorials, and I describe them in the protocol. But it’s probably good to have a single dedicated page, like this, dedicated to precision positioning of these extra points.

Long ago, when this blog was very young and I was not so old, I wrote Stacked vs. Clustered, which compared stacked and clustered column charts and described what each is well suited for. I included the following throwaway image; well, I considered it a throwaway, until a reader asked how I added the lines and markers.

Thanks for the nudge, dear reader. What follows is the protocol for adding those markers so precisely to the chart.

Clustered Column Chart

Here is my data and my starting column chart.

Column chart of Quarterly Sales by Region

What I could do, of course, is add an XY Scatter series on the secondary axis, then adjust my X and Y values and the secondary axis limits until the points are positioned appropriately. But that is tedious to do in the first place, and if the original data changes, the tweaked X and Y values would probably need readjusting.

But Excel is nothing if not extremely flexible. I can plot my XY data points on the same primary axis as the column data, with my tweaked data points based on the column chart configuration. This means that all XY points will stay in the same position relative to the column chart data, with a minimum of adjustment.

Column Chart Axis Measurements

The two charts below illustrate how scatter chart X values can be calculated based on the column chart’s configuration.

First of all, each category of the column chart has a number, from 1 to the total number of categories. The chart below has categories numbered from 1 to 3. If I use these numbers for my scatter chart X values, the points would be centered on the categories (between the orange and blue columns).

I merely have to calculate how far to the left and right of each category I need my XY data points to be. I need to know my Gap Width and I need to know how many series there are.

The Gap Width is a number, stated as a percentage of the width of a column, that tells me how wide the gap is between the clusters of columns. A Gap Width of 100 means that the white space between clusters is 100% as wide as a column. In the schematic, I have indicated that the columns are 100 percent wide, so the distance between cluster centers is 100 times the number of series, plus the Gap Width. In this case, the Gap Width is 200%, so my categories are 600% from center to center.

Each category in a column chart has an X value of 1 to the number of categories. Columns are 100% wide, and the Gap Width is stated in percentage of a column width.

The X axis of a line chart or an area chart work in exactly the same way. A bar chart is a different story; the approach is similar, but it is more complicated, and you do need to use secondary axes.

The Algebra

The amount my data points need to be offset is easy to calculate. For example, to center a point on the tallest bar in the chart, I start with the category position, then add half the bar’s width divided by the distance to the next category:

X = 1 + 50 / (4 * 100 + Gap Width)

which turns out to be 1.08333333

Since I want my points at the top of the columns, I use the same Y values for the XY points.

Below is my data for the XY points.

I put the name of the category in the first column, and the offset (in column width percentages) from the center of the category in the second. The formula in cell C9 is

=MATCH(A9,$B$1:$D$1,0)+B9/(4*100+$F$9)

The MATCH function looks up the category name and returns the number of the category (1 for North, etc.). Cell F9 contains the Gap Width.

I’ve inserted a blank row between categories, so there is a gap in the line between categories.

Calculated X values for XY scatter points

Adding the XY Series

Copy the XY data in C9:D22. Select the chart, and use Paste Special from the Home tab of the ribbon. Click on New Series, Series in Columns, Series Names in First Row, and Categories in First Column.

Because it’s a column chart, Excel adds the data as another set of columns.

Right click on any series in the chart, and choose Change Series Chart Type. Scroll to the bottom of the list of series and select the added series (“Connect”). Use the Chart Type dropdown to select the XY Scatter with Markers and Straight Lines option (NOT a Line Chart option), and uncheck the Secondary Axis checkbox.

This results in the XY data points we want, except the lines and markers are in an ugly shade of green (well, for this particular color scheme).

The XY data points are there, but sure are ugly

But we know how to fix that.

Adjusting Gap Width

If I change the Gap Width in my chart, from 200% above to 75%, the XY data points are no longer lined up.

Data points misaligned by changing Gap Width

But all that is needed is to change the Gap Width in cell F9 of my calculation range; the X values recalculate and the chart looks great again.

XY Charts

Trendlines and Chart Types in Excel

Trendlines and Chart Types

Why Do People Use Line Charts Instead of XY Scatter Charts?

More About Trendlines and Regression

Combination Chart for Multi-Factor Test Results

About the Test Program

Exploring the Data

Simple Charts

Pivot Table and Charts

Calculating X Values

Building the Chart

Create the Chart

Add the Test Results

Clustered Column and Line Combination Chart

Setup and First Attempt

Equivalent Category Axis Scaling

Numerical Category Axis Scaling?

Gap Width and Overlap

Calculating X Values

Clustered Column and XY Scatter Combination Chart

The Finished Clustered Column and XY Scatter Combination Chart

More Combination Chart Articles on the Peltier Tech Blog

Make Excel Chart Gridlines Square

Square Gridlines by Changing Axis Scales

Force Equal Major Unit Spacing

Square Gridlines by Changing Plot Area Size

Square Gridlines by Changing Chart Size

Square Gridlines by Changing Chart Size

Articles about Axis Scales on Peltier Tech Blog

Precision Positioning of XY Data Points

Clustered Column Chart

Column Chart Axis Measurements

The Algebra

Adding the XY Series

Adjusting Gap Width

More Combination Chart Articles on the Peltier Tech Blog