There are occasions when you may want to break the link between a chart and its underlying data. Maybe you copied the chart from another workbook, and you no longer have access to that workbook. Maybe you want to avoid the headaches that may arise from pasting a chart into PowerPoint or another program. Maybe you’re just tired of seeing this warning when you open the file:

There are several ways to disconnect your chart from its data source.

First let’s review chart data. I’ve written a lot about chart data, including

- Good Chart Data – The definitive description
- The Excel Chart SERIES Formula – also definitive
- Change Series Formula – Improved Routines
- How to Edit Series Formulas
- Simple VBA Code to Manipulate the SERIES Formula and Add Names to Excel Chart Series
- Edit Series Formulas

Below is a simple chart. A series is selected so the SERIES formula appears in the formula bar and the ranges in the formula are highlighted in the worksheet.

The SERIES formula looks like this:

`=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$8,Sheet1!$C$3:$C$8,1)`

The arguments in the formula describe the sources of the data.

`=SERIES(Series_Name,X_Values,Y_Values,Plot_Order)`

`Series_Name`

can be a link to a worksheet range, text (enclosed in double quotes), or blank.`X_Values`

can be a link to a worksheet range, an array enclosed in curly braces, or blank (and the chart will use 1, 2, 3, … for its X values).`Y_Values`

can be a link to a worksheet range, or an array enclosed in curly braces.`Plot_Order`

is an whole number between 1 and the number of series in the chart, signifying the order in which the series is drawn (complicated by chart type and axis group).

The cell addresses in the SERIES formula always use absolute references, such as $A$1, not relative references, like A1. But if you manually type relative references and press Enter, they will be converted to absolute references. The addresses always include the worksheet name.

If the chart links to data in another open Excel workbook, the SERIES formula includes the workbook name in square brackets before the worksheet name.

`=SERIES('[Data Source.xlsm]Sheet1'!$C$2,'[Data Source.xlsm]Sheet1'!$B$3:$B$8,'[Data Source.xlsm]Sheet1'!$C$3:$C$8,1)`

If the chart links to data in a closed Excel workbook, the SERIES formula includes the path, then the workbook name in square brackets, and finally the worksheet name.

`=SERIES('C:\Long Path[Data Source.xlsx]Sheet1'!$C$2,'C:\Long Path[Data Source.xlsx]Sheet1'!$B$3:$B$8,'C:\Long Path[Data Source.xlsx]Sheet1'!$C$3:$C$8,1)`

One way to represent an unlinked chart is to copy a picture of the chart, then paste it where desired.

Select the chart, then on the Home tab of Excel’s ribbon, under the Copy dropdown, select Copy as Picture…

… then select the appropriate options (usually Bitmap instead of Picture; I haven’t been able to figure out the difference between on screen vs. as printed) …

Then go to the other application, and Paste.

The disadvantage to this technique is that the pasted picture is no longer an Excel chart. You can no longer format any of the chart elements (rescale the axes, change marker styles or colors, etc.). Therefore, this method is unsuitable for use within Excel.

You can unlink chart data and still retain the actual chart with its formatting capabilities by editing the SERIES formula. Recall that the series formula in our first chart above was:

`=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$8,Sheet1!$C$3:$C$8,1)`

where the arguments referred to various links to the series data

`=SERIES(Series_Name,X_Values,Y_Values,Plot_Order)`

Select the series so that the SERIES formula appears in the formula bar, click in the formula bar so that the cursor is in the formula, and press F9. This keystroke converts references in the formula to their values:

`=SERIES("My Data",{"Jan","Feb","Mar","Apr","May","Jun"},{93,76,116,286,225,327},1)`

`Series_Name`

becomes `"MyData"`

, `X_Values`

becomes `{"Jan","Feb","Mar","Apr","May","Jun"}`

, and `Y_Values`

becomes `{93,76,116,286,225,327}`

. `Plot_Order`

is unchanged, of course, because it can only be a number, never a reference.

Press Esc to revert to the formula with references, or press Enter to keep the formula with hard-coded values.

If you select just one of the references in the formula, the F9 key only converts that reference to its value. These SERIES formulas are all valid:

`=SERIES("My Data",Sheet1!$B$3:$B$8,Sheet1!$C$3:$C$8,1)`

=SERIES(Sheet1!$C$2,{"Jan","Feb","Mar","Apr","May","Jun"},Sheet1!$C$3:$C$8,1)

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$8,{93,76,116,286,225,327},1)

=SERIES(Sheet1!$C$2,{"Jan","Feb","Mar","Apr","May","Jun"},{93,76,116,286,225,327},1)

Any repetitive task that you can do manually, VBA can do faster with much less tedium.

To unlink chart data from all series in the active chart, simply run this code:

Sub DelinkChartFromData0() If ActiveChart Is Nothing Then MsgBox "Select a chart and try again", vbExclamation, "No Active Chart" Else Dim srs As Series For Each srs In ActiveChart.SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End If End Sub

That’s nice enough, but I like to adjust a sub like this by including an argument, so I can pass in the object I want to process from any entry point. The function corresponding to this is

Sub DelinkChartFromData1(cht As Chart) Dim srs As Series For Each srs In cht.SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End Sub

To process the active chart, I would call it with this entry point:

Sub DelinkActiveChartFromData1() If Not ActiveChart Is Nothing Then DelinkChartFromData1 ActiveChart End If End Sub

To process all charts on the active sheet, I would use this:

Sub DelinkAllChartsFromData1() Dim chob As ChartObject For Each chob In ActiveSheet.ChartObjects DelinkChartFromData1 chob.Chart End If End Sub

To select on or more charts to process, and ignore the rest, I use this approach:

Sub DelinkSelectedChartsFromData1() If Not ActiveChart Is Nothing Then DelinkChartFromData1 ActiveChart ElseIf TypeName(Selection) = "DrawingObjects" Then Dim shp As Shape For Each shp In Selection.ShapeRange If shp.HasChart Then DelinkChartFromData1 shp.Chart End If Next End If End Sub

In fact, the last sub is all I need, since it does the active chart if there is one, replacing `DelinkActiveChartFromData1`

, and it does any selected charts, so I could select all charts and run it to mimic `DelinkAllChartsFromData1`

.

I was working on an example to show that it works for chart with lots of data points, and I happened to use dates as my X values.

I used `DelinkSelectedChartsFromData1`

and got the resulting chart.

The SERIES formula is pretty long, but is nowhere near the limit (see Excel Chart Series Size Limits).

`=SERIES("Value",{36526,36557,36586,36617,36647,36678,36708,36739,36770,36800,36831,36861,36892,36923,36951,36982,37012,37043,37073,37104,37135,37165,37196,37226,37257,37288,37316,37347,37377,37408,37438,37469,37500,37530,37561,37591,37622,37653,37681,37712,37742,37773,37803,37834,37865,37895,37926,37956,37987,38018,38047,38078,38108,38139,38169,38200,38231,38261,38292,38322,38353,38384,38412,38443,38473,38504,38534,38565,38596,38626,38657,38687,38718,38749,38777,38808,38838,38869,38899,38930,38961,38991,39022,39052,39083,39114,39142,39173,39203,39234,39264,39295,39326,39356,39387,39417,39448,39479,39508,39539,39569,39600,39630,39661,39692,39722,39753,39783,39814,39845,39873,39904,39934,39965,39995,40026,40057,40087,40118,40148,40179,40210,40238,40269,40299,40330,40360,40391,40422,40452,40483,40513,40544,40575,40603,40634,40664,40695,40725,40756,40787,40817,40848,40878,40909,40940,40969,41000,41030,41061,41091,41122,41153,41183,41214,41244,41275,41306,41334,41365,41395,41426,41456,41487,41518,41548,41579,4160`

9,41640,41671,41699,41730,41760,41791,41821,41852,41883,41913,41944,41974,42005,42036,42064,42095,42125,42156,42186,42217,42248,42278,42309,42339,42370,42401,42430,42461,42491,42522,42552,42583,42614,42644,42675,42705,42736,42767,42795,42826,42856,42887,42917,42948,42979,43009,43040,43070,43101,43132,43160,43191,43221,43252,43282,43313,43344,43374,43405,43435,43466,43497,43525,43556,43586,43617,43647,43678,43709,43739,43770,43800,43831,43862,43891,43922,43952,43983,44013,44044,44075,44105,44136,44166,44197,44228,44256,44287,44317,44348,44378,44409,44440,44470,44501,44531,44562,44593,44621,44652,44682,44713},{1.59180141468432,2.98367529941487,3.53968832672474,4.166896463019,5.49680350931305,6.60295325255014,7.92084805050608,8.42969525036707,9.03114848703803,10.5276619316506,11.6955893543266,12.7967815520366,13.9389611218954,14.7527663401269,15.056329735329,16.5993323777924,17.3515548037874,18.5751579754327,19.5310520490484,20.1024924707731,21.0585061447489,22.5723596205116,23.6423776067453,24.1433918431237,25

.8809999743178,26.9737430334888,27.2520490776847,28.9524027272893,29.1845029748314,30.9521946135505,31.6902106518488,32.6795741650195,33.0489731513874,34.3260591677559,35.6901951915975,36.5728985726155,37.399073960589,38.3469907111962,39.9779540070637,40.6458619797685,41.8270884435173,42.4800724464723,43.5366272985535,44.8875676117536,45.2053138791033,46.1944737321369,47.3914095233474,48.8633128550379,49.5985742034472,50.7164885501005,51.653002976414,52.173798695412,53.797369677567,54.336065160202,55.2589150631074,56.4371639546982,57.3504724537478,58.7576090168706,59.8397486773469,60.5570284284911,61.2384044080596,62.8073951248627,63.0734139584437,64.023944739976,65.4569502217557,66.441196439216,67.8040167607499,68.4732296280772,69.383544349419,70.6736709532193,71.8772550111681,72.7285185169436,73.0304159679703,74.4543215360901,75.6139703970569,76.6795157883573,77.9474917376374,78.8675574588127,79.2434751623046,80.8059856927601,81.9149434410426,82.0822571567436,83.1165049108337,84.5766331546624,85.0485197930

954,86.4516756787662,87.103472072234,88.6166607931416,89.5569751902823,90.2997637601853,91.4861009853597,92.738947366043,93.1212971468925,94.0166978760269,95.1888503720746,96.8377506737342,97.3036391294494,98.126217895739,99.339467893605,100.259054804445,101.840983590986,102.741718964783,103.082626950564,104.362149881911,105.760866364373,106.396030094412,107.226463877175,108.638218224396,109.379526574316,110.109027223762,111.420326031626,112.61771263781,113.468694206656,114.619213431217,115.278679680971,116.466011457757,117.537068146506,118.753523891166,119.704656141103,120.131070166454,121.822525894342,122.977159915521,123.640235472236,124.383000359254,125.608871736392,126.188489945214,127.414144604553,128.39601244846,129.115889297518,130.08797401699,131.159077879218,132.410010962127,133.535396508137,134.718791752223,135.443733080568,136.661870239714,137.29210822438,138.55656753413,139.308552070942,140.27987742426,141.456569779806,142.774325544055,143.243355927806,144.505167482867,145.111184143168,146.30748

3083571,147.464847220224,148.218427246658,149.769670436945,150.776656088281,151.515371748972,152.408346863735,153.29435331721,154.857749604455,155.212400832703,156.31398296817,157.430827877581,158.068214294863,159.246613771057,160.346285238108,161.703341756362,162.939983684318,163.581827103078,164.41963298477,165.632147153497,166.697413290467,167.83618109404,168.878480595484,169.817382059171,170.125826262302,171.174106119149,172.182319412083,173.683778271622,174.350253903931,175.280205756401,176.628020353313,177.602771096537,178.468667385597,179.645508668238,180.131396317678,181.258745156011,182.238164094744,183.821618545389,184.261328363069,185.932899850116,186.187494356367,187.586391685274,188.120772297618,189.794395110081,190.509727284739,191.179753691424,192.54771311589,193.831688905835,194.638416224214,195.906316127413,196.845527552348,197.02489756276,198.382679905726,199.860935825007,200.422827058144,201.110300744418,202.209577950068,203.499633489411,204.12638600329,205.314473205528,206.095829209215,20

7.070675561553,208.875077186077,209.071848934263,210.37641200788,211.34927750657,212.666535668482,213.400957572161,214.438684951166,215.464275837814,216.580366174425,217.797909408301,218.167376086905,219.366623356776,220.548163602681,221.316902416248,222.044068195978,223.356501739512,224.984206101239,225.14746583972,226.275382045765,227.310864151405,228.851337545147,229.175137589426,230.520419824664,231.361806010337,232.175186942816,233.450560796329,234.269397102542,235.272652848491,236.560089595496,237.571235289495,238.499139612489,239.133135844129,240.582849816789,241.827775202926,242.833982805547,243.988020788098,244.077263106469,245.085954088788,246.671199871053,247.086129757983,248.030390822573,249.043987276631,250.071893428251,251.525524796095,252.600631102785,253.754358498755,254.665241202158,255.625959629045,256.98261131318,257.320204395814,258.444509485282,259.533974191495,260.492799645349,261.603579334917,262.194476107706,263.615554634444,264.987806666296,265.830581271325,266.39664304882,267.294282

876198,268.348630086833,269.253293622165,270.082557692269},1)

Unfortunately, the X axis tick labels have lost their date formatting. In the sub, the `srs.XValues = srs.XValues`

converted the input dates into numbers, because internally Excel stores dates as the number of days since 1 January 1900. Easy enough to apply the date format manually.

Apparently, the nice spacing that comes with an actual date axis is gone. The chart above has dates, but the axis is a simple category axis. I’ll have to adjust the procedure to prevent a date axis from being changed into a category axis. Before I convert the links to values, I will apply the number format to the axis ticks. Then if it is a date axis, I will make sure the chart treats it as one; this is complicated by the fact that Excel often applies the date category type automatically based on the data.

Sub DelinkChartFromData2(cht As Chart) Dim iGrp As XlAxisGroup For iGrp = xlPrimary To xlSecondary Dim ax As Axis Set ax = cht.Axes(xlCategory, iGrp) With ax ' apply formats .TickLabels.NumberFormat = .TickLabels.NumberFormat If IsDateAxis(ax) Then ' apply date type ax.CategoryType = xlTimeScale End If End With Next Dim srs As Series For Each srs In cht.SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End Sub

This function tests whether an axis is a date category type. The `BaseUnit`

property is undefined unless the axis is a date axis.

Function IsDateAxis(ax As Axis) As Boolean If ax.Type = xlCategory Then Dim vTest As Variant On Error Resume Next vTest = ax.BaseUnit IsDateAxis = (Err.Number = 0) On Error GoTo 0 End If End Function

The corresponding entry point that I call the above with is familiar:

Sub DelinkSelectedChartsFromData2() If Not ActiveChart Is Nothing Then DelinkChartFromData2 ActiveChart ElseIf TypeName(Selection) = "DrawingObjects" Then Dim shp As Shape For Each shp In Selection.ShapeRange If shp.HasChart Then DelinkChartFromData2 shp.Chart End If Next End If End Sub

The resulting chart is now indistinguishable from the original:

It’s easy to link many of a chart’s text elements to a worksheet range. Select the text element, click in the formula bar, type `=`

and click on the cell or range containing the text you want displayed. The result is a link formula like `=Sheet1!$A$1`

, and the text element updates dynamically to display whatever is in the reference. This works for the chart title, axis titles, data labels, and textboxes and other shapes that contain text.

If you’re delinking the chart’s data, you probably want to delink the titles in the chart. A simple VBA routine to do just that is shown below. For each possible title, see if the formula begins with an equals sign (if not, the formula just shows the text), and if so, replace the title’s text with the title’s text.

Sub UnlinkTitles() If Not ActiveChart Is Nothing Then With ActiveChart If .HasTitle Then If Left$(.ChartTitle.Formula, 1) = "=" Then ' convert chart title link to text .ChartTitle.Text = .ChartTitle.Text End If End If Dim iAx As XlAxisGroup For iAx = xlCategory To xlSeriesAxis Dim iGrp As XlAxisType For iGrp = xlPrimary To xlSecondary If .HasAxis(iAx, iGrp) Then With .Axes(iAx, iGrp) If .HasTitle Then If Left$(.AxisTitle.Formula, 1) = "=" Then ' convert axis title link to text .AxisTitle.Text = .AxisTitle.Text End If End If End With End If Next Next End With End If End Sub

Let’s merge this into our last Delink The Chart routine (I’ve also included the test for a date axis:

Sub DelinkChartFromData3(cht As Chart) With cht If .HasTitle Then If Left$(.ChartTitle.Formula, 1) = "=" Then ' convert chart title link to text .ChartTitle.Text = .ChartTitle.Text End If End If Dim iAx As XlAxisType For iAx = xlCategory To xlSeriesAxis Dim iGrp As XlAxisGroup For iGrp = xlPrimary To xlSecondary If .HasAxis(iAx, iGrp) Then Dim ax As Axis Set ax = .Axes(iAx, iGrp) With ax If .HasTitle Then If Left$(.AxisTitle.Formula, 1) = "=" Then ' convert axis title link to text .AxisTitle.Text = .AxisTitle.Text End If End If If iAx = xlCategory Then ' apply formats .TickLabels.NumberFormat = .TickLabels.NumberFormat If ax.Type = xlCategory Then Dim vTest As Variant On Error Resume Next vTest = ax.BaseUnit If (Err.Number = 0) Then ' apply date type ax.CategoryType = xlTimeScale End If On Error GoTo 0 End If End If End With End If Next Next Dim srs As Series For Each srs In .SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End With End Sub

And we’ll call it using the familiar entry point:

Sub DelinkSelectedChartsFromData3() If Not ActiveChart Is Nothing Then DelinkChartFromData3 ActiveChart ElseIf TypeName(Selection) = "DrawingObjects" Then Dim shp As Shape For Each shp In Selection.ShapeRange If shp.HasChart Then DelinkChartFromData3 shp.Chart End If Next End If End Sub

The post Unlink Chart Data appeared first on Peltier Tech.

]]>Last week we released the PBCharts (Process Behavior Charts) tool for performing SPC control chart analysis. I’ve been working on PBCharts for well over a year, and we had an extended beta testing period, during which we cleaned up numerous issues. I thought I’d walk through an analysis to show how easily PBCharts does its work.

My colleague posted a quick analysis of inflation since the start of 2019, and that looked like a good data set to analyze. The data file CPI YoY Pct.xlsx (click to download) looks like this, showing date and year-over-year percentage increase of the consumer price index:

I’ve described the manual process of generating control charts in Introducing Control Charts (Run Charts), based on Donald Wheeler’s Understanding Variation, an excellent introductory text on this topic. It can be tedious to set up your own control charts, but PBCharts makes it easy to apply the techniques to lots of data.

PBCharts uses a specially formatted Excel template for its data, calculations, and charts. A PBCharts workbook has four visible worksheets: Data, Run Chart, MR Chart, and I Chart.

There are a few ways to get the data into the PBCharts file. The PBCharts ribbon presents us with a few options.

** Import Data into PBCharts:** Click this button, then browse to a data file (CSV, TXT, or Excel) and PBCharts will import data from the selected file into a new PBCharts file.

** Analyze Selected Data in PBCharts:** Select your data range, or select one cell in the data range, and click this button, and PBCharts will populate a new PBCharts file with the selected data, or if a single cell is selected, with the larger data range that contains the selected cell.

** Blank PBCharts File:** Click this button to create a new, blank PBCharts file, then paste your data into the Data sheet of the PBCharts file.

The inflation data looks like this in the PBCharts data worksheet:

In general, you will have more than one column of data to analyze. For example, the view below shows the inflation data above, with another column showing a three-month moving average. The cell colors tell you about the data: the gray cells indicate that the cell contains a formula, the red cells indicate that they are blank. The blue header cell tells you which column is currently being analyzed (PBCharts analyzes one column at a time). If you click on a different header cell, PBCharts will focus the analysis on the newly selected column.

This isn’t really a manual for PBCharts, just a demonstration, but I’ll show the rest of the ribbon, so my discussion below makes sense. I’ve already shown the section that is used to manage PBCharts files.

The Analysis section of the ribbon includes controls for selecting the column to be analyzed, as well as controls for defining stages, trends, and excluded points, for labeling points, and more.

The Charts section provides a means to override axis scales, to set and show a target value, and to select a run test to apply. You can choose a chart style (bold, medium, or light), you can save charts as text files or export them to PowerPoint or Word, and you can define a dashboard-like layout of multiple charts.

Click on the Run Chart worksheet, and you see a run chart of the data in the highlighted column of the data sheet. It starts out pretty boring in 2019, then in early 2020 there is a dip, followed by another boring stretch, and starting in early 2021, inflation started ramping upwards.

You can select whether to test the run chart for certain conditions using the dropdown in the Charts section of the ribbon. The run chart above has no test.

The chart below has one test, Runs About Median, selected. The median is displayed as a green horizontal line, and every time the data crosses the median, a label is displayed along the top of the chart.

Our data displays 6 runs about the median. For a data set of this size, we would expect 21 runs about median, or between 17 and 26 with a p-value of 0.05. Since our runs are so low, the data is classified as clustering.

The chart below has another test, Runs Up or Down, selected. This counts each time the points reverse direction.

We would expect 27 runs up or down, or 23 to 31 with p=0.05, for a data set of this size. Instead, we see a much lower number, 14, which indicates trending behavior. Not surprising, given the obvious upward trend over the last third of the chart.

The run chart tests tell us that the data is not uniformly distributed, but we can still use control charts to analyze the behavior.

We turn to the MR (Moving Range) Chart. This shows us how much the data changes from one value to the next and is a measure of the variability in the data. We see immediately that there are two peaks in the MR data, indicating large changes in the data, and possibly process changes.

The green horizontal line shows the mean moving range value, and the red horizontal line shows the upper natural process limit (or upper control limit) on the moving range. The red square points indicate points which are out of statistical control, that is, which violate one of the rules, and the red labels at the top of the chart show which rules have been violated. There are four possible rules for MR charts, shown below, and our analysis is checking two of them, in bold. The two obvious peaks exceed the upper control limit.

I’ll show the I (Individuals or individual values) Chart, but it’s not usable yet. The green line shows the mean of the data, and the red lines show the upper and lower natural process limits (control limits). There’s a lot of red, which means that the average and control limits calculated so far do not describe the data well.

The red square markers indicate points which are out of control, and the text above the chart indicate which rules were violated. There are eight I Chart rules built into PBCharts, and we are checking four of them.

Let’s return to the MR chart and label a couple of points. I’ll label March 2020 “Covid” since it’s when we in the United States started severely limiting our activities due to the Covid pandemic. I’ll label March 2021 “Suez Canal” because that’s the month when the huge cargo ship ran aground in the Suez Canal, blocking all traffic. That event didn’t by itself cause the runaway inflation, but it marked the point when we became aware of how problems with shipping, supply chains, and logistics began to hurt the global economy.

Now back to the I chart. I’ll click the Stages button on the ribbon, and define two new stages starting in April 2020 and April 2021 (the first stage automatically starts at the beginning of the data set).

Next I’ll click the Trends button, and assign a linear trend to the third stage.

The I chart now looks well behaved. There is an initial horizontal section, then an intermediate horizontal section at a lower inflation value, indicative of reduced economic activity. Finally, there is the final increasing section. We still see a few red out-of-control points, three at the beginning of the middle Covid section, and one at the transition between the Covid static range and the final increasing stage.

The MR chart shows no point exceeding the control limit where Covid started, though we still have the over-the-limit point where global shipping became an issue. I’m not going to be concerned with these violations.

We could modify the I chart further, using the Exclusions dialog to exclude the three out-of-control points at the beginning of the Covid stage. Doing so causes three other points out-of-control, so I would probably just leave those points in the analysis.

The important thing to ask now, is whether the inflation rate will keep increasing. The US Treasury has raised its prime rate in March and again in May to try to slow inflation. There is no sign that this is having any effect, since the latest data points fall within the process limits and no other assignable causes are seen. But we can keep watching, add data every month, and follow the trends.

PBCharts allows us to select a period of time, and lock in the calculated limits. It is common practice, once a process is deemed stable, to lock the limits before plotting subsequent points. This way adding more data will not affect the displayed limits, and the added data is directly compared to the stable process behavior.

We can use the inflation rate data to illustrate locking of calculated process limits. Since the Fed first increased interest rates in March, let’s back up the data to that point, ignoring the last two points. Here is the I chart.

Now I’ll lock the limits in the last stage (April 2021 to March 2022). The dark circles have changed to squares to indicate the points used to calculate the locked limits.

The limits are the same in both charts. Let’s view the final stage side by side.

Let’s add back the data for April and May. The difference in the two charts is subtle: notice that the last two circles are closer to the green line without locked limits (left) than to the green line with locked limits (right). This is because the added points have changed the slopes of the green lines and red limits, rotating the right sides of these lines slightly downward.

Now let’s suppose that the Fed’s interest rate action has had an effect. To simulate this, and I’ll admit it’s a huge assumption on my part, I’ll repeat the last two actual points for April and May as presumed additional points for June and July. The unlocked green and red lines of the left-hand chart below have rotated further, so that the green line is now below the points for April and May, and the imaginary points for June and July are above the lower red line. The locked limits in the right-hand chart haven’t moved. The points for April and May are still below the green line, and the new points for June and July are below the red line and highlighted as out-of-control points.

These out-of-control points are a signal that something in the process has changed. We might presume that increasing interest rates has stopped the climb in the inflation rate.

PBCharts is a VBA add-in for Excel. It is limited to Windows, but it runs on any supported versions of Excel, from Excel 2013 through Microsoft 365.

To get your copy of PBCharts, visit the PBCharts website. You can download PBCharts on a 14-day free trial basis, or you can purchase a one-year license for $99.00.

And readers of this blog get a discount of $10.00 by entering the coupon code

. This special price of $89.00 is good until 6 July, 2022.*PeltierTech*

The post PBCharts Inflation Analysis appeared first on Peltier Tech.

]]>How many points can I plot in each series of my chart? How large of a VBA array can I plot in my chart? Two good questions, which I’ll investigate here.

A colleague emailed me asking about the VBA array size limit for plotting in a chart. He said he thought the limit was 32,000 points, but couldn’t find any official documentation of this, and his trials only worked for half that many points. I couldn’t find any documentation of any limit on how large a VBA array can be used to populate a chart.

The number of points in a chart series populated by worksheet ranges is limited by available memory, as the spec states. This limit can be greater, perhaps much greater, than the number of cells in a worksheet column.

The number of points in a chart series populated by a VBA array is 32,000 if the array is a 2-dimensional vertical array. The limit drops to 16,384 if the array is a 1-dimensional horizontal array.

I’ll start with the VBA question.

If you generate data in VBA using arrays, you can plot this data in two ways:

- Put the arrays into a worksheet, and plot the ranges that contain the data;
- Put the arrays directly into the chart.

Below is a simple VBA procedure that generates small arrays for the X and Y data, creates a scatter chart with one series, and puts the arrays into the `.Values`

and `.XValues`

properties of the series.

```
Sub ChartWithVBAArrays()
' declare arrays
Dim X(1 To 10) As Variant
Dim Y(1 To 10) As Variant
' populate arrays
Dim i As Long
For i = 1 To 10
X(i) = i
Y(i) = i
Next
' create the chart
Dim cht As Chart
Set cht = ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Chart
' populate the chart
With cht.SeriesCollection.NewSeries
.Name = "VBA Arrays"
.Values = Y
.XValues = X
End With
End Sub
```

The resulting chart (Scatter with Markers and No Lines) looks like this.

Here is what the SERIES formula looks like:

`=SERIES("VBA Arrays",{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},1)`

The arrays are simply shown as comma-separated lists of values, enclosed in curly braces.

Easy enough. Now let’s see how much data we can put into those arrays.

I made two procedures: `PlotManyPoints`

which had two arguments, the number of points to (try to) plot, and the chart to plot those points in; and `TEST_PlotManyPoints`

, which pops up an `InputBox`

asking me how many points I wanted to plot.

```
Sub TEST_PlotManyPoints()
Dim cht As Chart
' in case I forget to select the chart
If Not ActiveChart Is Nothing Then
Set cht = ActiveChart
Else
Set cht = ActiveSheet.ChartObjects(1).Chart
End If
' ask how many points
Dim n As Long
n = Application.InputBox("How many points?", , , , , , , 1)
' run the procedure below
PlotManyPoints n, cht
End Sub
Sub PlotManyPoints(n As Long, cht As Chart)
Dim x As Variant, y As Variant
ReDim x(1 To n), y(1 To n)
' build arrays
Dim i As Long
For i = 1 To n
x(i) = i
y(i) = i
Next
' apply arrays to chart
With cht.SeriesCollection(1)
.Values = y
.XValues = x
Dim Points As Long
Points = .Points.Count
End With
' report results
ActiveSheet.Range("B3:B4").Value2 = WorksheetFunction.Transpose(Array(n, Points))
End Sub
```

The chart was already present, so the program simply replaced the chart’s existing data with the new data, and it recorded in the worksheet how many points were in the arrays and how many points were plotted in the chart.

I started small, with 10 points. Here is the output worksheet with an XY Scatter Chart with Lines and No Markers.

Then I tried 100 points, 1000 points, 10,000 points…

I’ve written extensively about Excel’s chart SERIES formula:

- The Excel Chart SERIES Formula
- Change Series Formula – Improved Routines
- How to Edit Series Formulas
- Edit Series Formulas
- Delink Excel Chart Data

The chart SERIES formula keeps showing all of the X and Y values in their arrays for longer than I expected. Here is the SERIES formula for 10 points:

`=SERIES(,{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},1)`

Here it is for 100 points:

`=SERIES(,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100},1)`

Here it is for, well, I won’t show you 1000 points, but the series formula showed all 1000 X and Y values. In fact, the series formula was complete all the way up to 1042 points. The formula was 8221 characters long, slightly more than the stated limit of 8192 in Microsoft’s documentation. If I try to edit the formula and hit Enter, Excel warns me:

When I plot 1039 points, the SERIES formula has 8191 characters. I can edit this formula without changing its length, and Excel will accept the change.

Beyond 1042 points, Excel starts leaving off characters in the array of Y values, then of the X values, to keep to the almost-limit of 8221. At 2000 points, the end of the 8221-character formula looks “broken”:

`,1852,1853,1854,1855,1856,1857,1858,1859,1},{1,2,3,4,5,6,7,8,9,}`

The X values are truncated in the middle of point 1860, the Y values after point 9, and we’ve lost the final plot order argument and closing parenthesis. Despite the lack of fidelity of the series formula display, however, Excel plods, I mean, plots along.

After reopening a workbook that has such “broken” formulas, the formulas can no longer be read. If you select the series, Excel leaves the formula bar blank, and shows this message.

You can still format the series and the rest of the chart. And you can still access all 32,000 data points in VBA using the `.XValues`

and `.Values`

properties of the series object.

In the old days (Excel 2003), the limit on the SERIES formula was 1024 characters, each of the four arguments was limited to one-fourth of this, and if the SERIES formula was “broken” by having too many characters, Excel would refuse to plot anything. The limit using this methodology was 81 points.

If your data had more than one or two digits, the limit quickly became smaller. Try more than the limit and Excel gave you a nasty error.

Can you believe I opened up Excel 2003 to try this? I keep a virtual machine around with Excel 2003 just to try stuff like this, because I sure can’t rely on my memory. And by the way, get offa my lawn!

When I tried 100,000 points, I hit another limit: the arrays contained 100,000 points, but the chart only accepted 16,384 of them.

So my result validates what my colleague told me: a VBA array can populate much less than the 32,000 points that we unofficially think is the limit.

But that number 16,384 is not a nice round number like 32,000. In fact, it looks like a power of 2. It happens to be 2^14, which is also the number of columns in an Excel worksheet.

A one-dimensional array such as I used in this code is a horizontal array. Perhaps the chart can only accept a horizontal array with as many values as there are columns.

We can make a two-dimensional array in VBA readily enough. I’ve modified my earlier routine `PlotManyPoints`

to do just that. My arrays `x`

and `y`

are dimensioned with `1 to n rows`

and `1 to 1`

columns.

```
Sub PlotManyPoints(n As Long, cht As Chart)
Dim x As Variant, y As Variant
' 2-dimensional vertical arrays
ReDim x(1 To n, 1 To 1), y(1 To n, 1 To 1)
' build arrays
Dim i As Long
For i = 1 To n
x(i, 1) = i
y(i, 1) = i
Next
' apply arrays to chart
With cht.SeriesCollection(1)
.Values = y
.XValues = x
Dim Points As Long
Points = .Points.Count
End With
' report results
ActiveSheet.Range("B3:B4").Value2 = WorksheetFunction.Transpose(Array(n, Points))
End Sub
```

I ran this for 10, 100, 1000, 10,000, and finally 100,000 points, and I hit the limit of 32,000 points when I tried to add 100,000.

So 32,000 points is the limit for VBA arrays as chart series source data. If you need more, you could do one of two things:

- Put the longer arrays into a worksheet, and plot the ranges that contain this data;
- Break the arrays into smaller 32,000-element arrays, and use these smaller arrays to populate separate series in the chart. Even with the limit of 255 series per chart (which has not changed), you’re allowed 8,160,000 points.

So where does this VBA array limit of 32,000 come from? Microsoft’s own Excel specifications and limits informs us that Excel 2007 had a limit of 32,000 points per chart series, but the limit for Excel 2010 and later is “Limited by available memory”. I can’t find anything other than hearsay and speculation about the limit on VBA arrays.

I suspect that somewhere in Excel’s source code, there is still a hard-coded 32,000 chart series limit that nobody can find. This limit is ignored for worksheet data but is still enforced intentionally or otherwise for VBA data.

One reason people cite for using VBA arrays instead of worksheet ranges is that the arrays result in smaller workbooks. I made four workbooks to investigate this. One workbook contained data for 32,000 points, but no chart. One workbook contained the data and a chart that plotted the data. One workbook contained no data but had a chart that was copied from one of the first two workbooks, so it was still linked to the workbook with the data. The last workbook had no data, and a chart populated with 32,000 row VBA arrays. The results are summarized in the table below.

The workbook containing only the data needs 413 kb of storage. Add a chart, and the workbook increases to 721 kb. Interestingly enough, if the chart is alone in the workbook and links to a different workbook, the file size is about the same as if its parent workbook contained the data, 724 kb. Most interesting, the workbook with only the VBA-populated chart, with its “broken” and hidden SERIES formula, was smallest of all, at 316 kb.

So the conventional wisdom is correct, and a VBA-populated chart requires a smaller workbook than a chart that links to worksheet data. In any case, these are not enormous files, and I feel safer with the data accessible in the worksheet rather than hidden by VBA.

What does “Limited by available memory” mean?

To finish my analysis, I thought it would be informative to see how many regular worksheet data points I could squeeze into a chart series.

I set up data in columns A and B of a worksheet that start with 1 in row 1 and continue down to 1,048,576 in row 1,048,576 (the last row in the worksheet). I plotted this in an XY chart (line and no markers) and got all of the points into the chart.

Note that hard-coded values work much nicer than formulas in these million-plus-row ranges. It takes a long time to calculate such a lot of cells.

So the limit appears to be 1,048,576 points.

Then I thought it might be possible to extend this. Let’s examine the following ranges and charts, and apply some SERIES formula magic.

I can plot A1:B4, shown in the first chart below.

The SERIES formula looks like this:

`=SERIES(Sheet1!$B$1,Sheet1!$A2:$A4,Sheet1!$B2:$B4,1)`

I can add the data in D1:E4 as a second series (orange) in the middle chart, with the following formula:

`=SERIES(Sheet1!$E$1,Sheet1!$D2:$D4,Sheet1!$E2:$E4,2)`

But I can also combine the X and Y values to create a single series using both sets of data, as shown in this series formula and the third chart below:

`=SERIES("A,D vs B,E",(Sheet1!$A$2:$A$4,Sheet1!$D$2:$D$4),(Sheet1!$B$2:$B$4,Sheet1!$E$2:$E4$),1)`

The X values are `Sheet1!$A$2:$A$4`

and `Sheet1!$D$2:$D$4`

, comma-separated and enclosed in parentheses. Likewise, the Y values are `Sheet1!$B$2:$B$4`

and `Sheet1!$E$2:$E$4`

, also comma-separated and enclosed in parentheses.

Let’s add more full columns of X and Y data.

I’ve already plotted the first series above with this formula:

```
=SERIES("A vs B",Sheet1!$A:$A,Sheet1!$B:$B,1)
```

I can add another series with the data in columns D and E with this formula:

`=SERIES("D vs E",Sheet1!$D:$D,Sheet1!$E:$E,2)`

Which produces this chart:

But as I’ve shown above, I can combine the two sets of X and Y data into one series’ worth of X and Y data, using this formula:

```
=SERIES("A,D vs B,E",(Sheet1!$A:$A,Sheet1!$D:$D),(Sheet1!$B:$B,Sheet1!$E:$E),1)
```

When I first entered this SERIES formula, I got an error, and I thought that I’d found a hard limit. But I went back and fixed a typo, and it all worked. I ended up with this chart:

So I don’t know what the limit is, except that it is in fact “Limited by available memory,” and this limit is more than the number of rows in a worksheet column. I leave it as an exercise for the reader to determine how many full columns they can squeeze into the SERIES formula.

When the data is comprised of static values in cells, it isn’t too slow to build and display these charts. If you have formulas that calculate the X and Y values, though, this calculation takes a minute or more each time, and it becomes very tedious while you wait for Excel to start responding again.

I tabulated file sizes for 32,000 points above to show that populating charts with VBA arrays can reduce file sizes. For completeness here are file sizes needed for charting full columns of data. These are large files. I’ll let you draw any further conculsions.

The post Excel Chart Series Size Limits appeared first on Peltier Tech.

]]>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.

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.

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).

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.

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

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.

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.)

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.

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.

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.

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.

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())

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

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)

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.

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.

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.

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.

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.

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.

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.

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 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.

The resulting chart is starting to look good.

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

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.

The post Combination Chart for Multi-Factor Test Results appeared first on Peltier Tech.

]]>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).

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.

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.*

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.

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

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.

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

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.

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.

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??).

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).

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`

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:

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)`

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.

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.

Here is the complete data range plus the chart.

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?

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

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

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

I wrote a similar post a couple years ago about Precision Positioning of XY Data Points. If the explanation above isn’t clear, you might try this other article.

In addition, I have written several more articles about combination charts:

- Add a Horizontal Line to an Excel Chart
- Horizontal Line Behind Columns in an Excel Chart
- Bar-Line (XY) Combination Chart in Excel
- Salary Chart: Plot Markers on Floating Bars
- Fill Under or Between Series in an Excel XY Chart
- Fill Under a Plotted Line: The Standard Normal Curve
- Excel Chart With Colored Quadrant Background

The post Clustered Column and Line Combination Chart appeared first on Peltier Tech.

]]>Everybody loves their Excel tips and tricks. I know I’m famous for my extensive collection of Excel charting tricks. But the most important charting trick is **Get The Data Right**. The secret of successful Excel charting begins and ends with **Good Chart Data**. I teach it in all of my charting workshops and seminars, so I was surprised that I did not have a blog post dedicated to good chart data. But now I do.

What do I mean by “good” data? Of course, “good” has to do with the quality of the data. Where is the data from? How was the data measured or collected? Is the source reliable and trustworthy?

All of this is important, for any data you consume in Excel or in any other program. But right here, “good” data means data that can easily be rendered in a chart, without having to make excessive adjustments to the chart. Good chart data may not be good display data, but data that has been optimized for display, say in that annual shareholder’s report, is almost guaranteed to be bad for charting.

“Good” data has a layout that makes chart creation easy for you, so Excel knows how to partition the data between the important parts of the chart. X values or categories. Y values. Series names.

“Good” data also has as little formatting as possible. Enough formatting to make it readable, but not so much that it causes retina pain.

You can make good charts from bad data, but it will take longer, and you will have to muck around with the chart for a while to make it work. It’s better to spend five minutes with the data now than to spend five hours trying to clean up a chart later.

Good chart data has the following characteristics:

- The data exists in a contiguous range: no blank rows or columns
- The data is minimally and consistently formatted: easy to validate visually
- The data is aligned with Y values in columns (shaded blue below)
- X values are located in the leftmost column (purple)
- Series names are located in the topmost row (red)
- The top left cell may provide some magical behavior (gold)
- The data may exist in an Excel Table

What if you want to plot your data but also display it in an optimally formatted way? The good new is, you can. The bad news is, it takes more work. You should have two different data ranges: one arranged for best charting outcomes, the other formatted for visual consumption. Both of these ranges should link to the same original source, to make sure changes are reflected everywhere, and you maintain one version of the data.

A good chart data range is contiguous, that is, it is comprised of data in a single block of cells. There are no completely blank rows or columns separating the data into separate areas.

Why is contiguous data important? Because Excel tries to be smart when you click a button. If you select any single cell in the range shown above and click any chart button to insert a chart, Excel tries to determine the extent of your data. Excel will go from the active cell up, down, left, and right until it finds either the edges of the worksheet or a blank row or column, and it will include all of the data within this range for the chart. This is so much easier than having to select the entire data range, especially if the data extends beyond the first visible screen.

Excel does the same hunt for the current region when you convert a range to a Table, or create a Pivot Table, or do any number of other things in Excel.

It’s perfectly fine to have blank cells within the data range, as long as there are no completely blank rows or columns.

Keep in mind that successful charting means that these “blank” cells should be completely empty. A cell with a formula that returns “” is not blank, because it contains a formula. If you have the formula return NA() instead of “”, the cell will not look blank, it will contain the #N/A error. This looks ugly, but a chart treats most #N/A values as blank cells, while it treats “” as text. Text might be plotted as a zero, or it might mess up an axis.

A cell that contains a space character is also not blank, because it contains that space character. Sometimes people get in the habit of typing the space instead of just skipping the cell. This kind of non-blank cell is hard to fix because both the cell and the formula bar appear blank.

You should only use as much formatting as you need to help you quickly scan the data.

The range below is a bit over-formatted. The header row is bold and multi-colored, and the red and yellow are distracting and may eventually cause eyestrain. Bold text and light gray fills might be okay for headers, though they are unnecessary if the headers follow one or more blank rows.

The dark borders on all of the cells break up the appearance of the data range and the dark lines compete for attention with the numbers. The default light gray cell borders are sufficient. I’ve programmed some buttons in my general purpose and charting software to apply light and medium gray borders to selected ranges.

The green shading can be distracting. (See my arbitrary pattern? All cells with 3 are shaded, while b and d are shaded because they have no 3 in their rows. I don’t know why.) If you find shading helps to identify certain values, don’t manually color the cells: use Conditional Formatting instead, so the shading goes away if the condition is no longer met.

You should avoid centering your data. It might look “nice”, but centering hides important characteristics of the data. By default, text is left-aligned in cells and numbers are right-aligned. A common feature of “bad” data is numbers stored as text, and centering everything hides this distinction.

Here is a data range with all of its cells centered. There are some small triangular flags in some cells that indicate a possible error, but I think most of us have learned to ignore warnings like this.

When we uncenter the data, it’s easy to detect the cells with numbers stored as text, even if we’ve ignored the green flags.

We can select the flagged cells, click the little warning dropdown, and convert them to numbers.

Now that we’ve converted text to numbers, we still have difficulty parsing the numbers, because they have inconsistent numbers of decimal digits. Some numbers look larger than others, but they merely have a longer tail after the decimal point.

When we apply a consistent format, we can tell that the larger numbers are in fact longer.

It’s now very easy to identify the largest numbers in each column. You might want to check that value of 345 in the “beta” column since it’s 100 times as large as the rest of the column.

Excel charts can work with data in columns or in rows. You can use either arrangement and sometimes one just works better than the other. If a chart’s source data has more rows than columns, Excel creates the chart with series in columns. A pivot chart always plots the pivot table with series in columns.

It is probably good practice to get used to using data in columns, because of the way a database table is structured. A database has fields and records. A field is a variable or measurement, such as date, eye color, serial number. A record is a single instance of a set of values for these fields.

When printed on paper or viewed onscreen (or imported into Excel), a database table is shown as a grid of rows and columns. Each column is a field or a variable, and each row is a record. The first row of the database table is a header row that contains the field label. Usually one column of the table, often the first column, contains a unique identifier or key for that particular record.

In the table above, we have fields for TLC, alpha, beta, and gamma. We have records for a, b, c, d, e, and f.

Like a database table, chart data should have a header row. When data is plotted with series in columns, this header row is used for series names, that is, the labels that appear in the legend of the chart.

Database tables have one header row only. Chart data ranges usually only have one row, but you can use more, often to good effect.

Subtotals and totals help to understand data, but they have no place in the source data for a chart. Suppose I have monthly values I want to plot.

If I put subtotal rows into my source data, it breaks up the visual appearance, so it’s hard to scan the individual values for discrepancies.

The quarterly subtotals disrupt the flow of data in the chart, and the much larger magnitude of the subtotals shrinks the monthly values.

If I include yearly totals, the quarterly subtotals shrink, and the monthly values are lost in the weeds.

Note that you can filter the quarterly and yearly categories from the chart in recent versions of Excel. Also note that a pivot chart will only show values from the pivot table and not totals and subtotals.

Just as a database table has a unique key field, the data range for a chart should have a header column identifying each row. This column is generally used for labels or values which are plotted along the X-axis of a chart.

This X-axis column should be the first column, to the left of any Y-axis values. This makes charting easier because Excel looks to the left for X values. But you would be amazed at how many people have trouble plotting their data when they have placed their Y values to the left of their X values. You can always tell Excel which data comes from where, but it is a lot more work, especially if you have to do it repeatedly, again and again, ad nauseam (can you feel the tedium?).

Another trick to help Excel identify your X values is to make the column of X values different from the Y values.

Probably the most common way to make the first column different is by filling it with text. While month names are a component of a date, a list of month names is text, as shown here.

When you select the data range (or one cell inside the data range), Excel uses the text labels in the first column as X-axis (category axis) labels, it uses the other columns as Y-axis (value axis) values, one column per series, and it uses the labels in the header row as series names. Here are line and column charts using this data (and area charts work the same way). Months from the first column are automatically placed along the category axis, headers from the first row are automatically used as legend entries (series names). and each column of Y values is plotted as a distinctly formatted series.

It’s the same with a bar chart, except that the category and value axes are switched. That’s right, in a bar chart, the X-axis is vertical and the Y-axis is horizontal. But the origin of the axes is at the bottom left in all of these charts, so values increase from left to right (like months advance from left to right in a line or column chart). Similarly, months advance from Jan to Jun moving bottom to top in a bar chart (like values increase from bottom to top in a line or column chart).

Confused? Since the months are listed from top to bottom in the worksheet, it would make sense to plot them from top to bottom in the chart. But there is logic in how Excel does it, and it is also pretty easy to fix when you know how. See Excel Plotted My Bar Chart Upside-Down for the simple technique.

XY Scatter charts are different: they have numerical axes for both X and Y (category and value) axes. When you plug in text for the X values, the chart doesn’t know what to do. Normally text is considered to have a value of zero, but for X values in an XY chart, Excel substitutes the counting numbers 1, 2, 3, up to the number of points. (Excel also does this if no X values have been specified.)

What if ever have an XY chart with the numbers 1, 2, 3, etc. along the axis, and you know you selected numerical data for the X values? Check the range of X values: there is probably a number stored as text, or an actual text label, somewhere in the range.

Using dates in the first column is another way to make the first column different. Excel recognizes the date formatting of the cell and parses the column as X values. An added bonus is that line, column, area, and bar charts have a special date type of axis (as opposed to the text axis shown above) that provides unique formatting options. This range has dates in the first column; notice that the dates are not uniformly spaced, but are taken on the 1st and 8th of each month.

Here are line and column charts made from this data. The line chart looks great and shows some of the enhanced date axis features. The data points are not equally spaced but reflect the non-uniform spacing of the data. Also, there is a tick mark and axis label on the first of each month, despite the nonuniform month lengths.

The column chart is wacky though. To accommodate the nonuniform data spacing, the chart has a slot for each day, even days without data. So each column chart series has to appear within the slot for its given day, and there are lots of days in between. This column chart should really be called a toothpick chart.

You can always change the axis type to text, and the column chart will look normal. But you lose the non-uniform nature of the dates and the first-of-the-month labels.

An XY scatter chart treats dates for X values just like any numerical X values. You can see the non-uniform spacing of the data.

But the axis is not labeled as naturally as the line chart above. Using the value axis algorithm, Excel picked an axis that begins at 44,180 and increases to 44,280 in steps of 20. (Excel stores dates as whole numbers starting on 1 January 1900, so 44,180 is 15 December 2020 and 44,280 is 25 March 2021.) You can format the XY chart’s axis to begin on the 1st of a month, and have a tick label on the 1st of the next month. But for a non-trivial number of months, it’s impossible to repeat this pattern with Excel’s default axis labels.

If you have numerical X values in the first column, they won’t be different from the numerical Y values in the other columns. *You* know those values in the first column are years, and I know they are years, and the header label even says “Year”, but Excel simply recognizes them as numbers.

This doesn’t matter for an XY Scatter chart: Excel almost always treats the first column of numbers as X values. This XY chart shows years along the X-axis, as intended.

When Excel created this line chart, it saw the numbers in the first column and decided to plot them as Y values. There are two consequences of this: First, there is a series of values near 2000 floating far above the intended Y values in the chart; second, no X values were specified, so Excel simply used the counting numbers 1, 2, 3, etc.

You could avoid this by selecting just the Y values when the chart is created, and specifying the X values later. You could also do this with years by entering dates in the first column (1/1/2015, 1/1/2016, etc.) and formatting them using a custom number format of YYYY, which will display just the year numbers. Since the column now is formatted as dates, Excel will plot them the way you want.

You cannot avoid this by formatting the first column of numbers as text, Excel is too smart, and it converts the numeric text as numbers, and therefore as Y values.

You may have noticed the label “TLC” in the header row of the first column. TLC stands for **Top Left Cell**, and it is a little piece of magic for Excel chart data.

One way to make the first column different from the rest is to clear the contents of the Top Left Cell, as shown below.

We already know that an XY Scatter chart will plot the first column of numbers as X values with a label in the top left cell. But it also works if the top left cell is blank.

The top left cell works its magic in line (and area, column, and bar) charts. This line chart was generated automatically with the column below the blank top left cell as X values, and the columns below actual labels as Y values. Finally, a way to plot numbers as X values in a line chart without worrying about formats (text or dates).

Here is an important difference between XY scatter charts and line charts. Just because you can trick Excel into plotting numbers as X-axis values in a line chart, you can’t trick Excel into plotting those X values as numbers. The next section shows a few different sets of data that will help illustrate this difference.

Evenly spaced X values seem to be similarly plotted in XY and line charts. In the XY chart, the X-axis begins at zero and extends beyond the highest X value. In the line chart, the first X-axis label is the first X value and the last X-axis label is the last X value, without the padding found in a scatter chart’s default axis values.

Unevenly spaced X values are plotted differently in XY and line charts. In the XY chart, the X-axis begins at zero and extends beyond the highest X value, and data points are plotted unevenly, reflecting the unevenness in the X values. In the line chart, the first X-axis label is the first X value and the last X-axis label is the last X value, data points and X-axis labels are uniformly spaced regardless of their apparent numerical values, and there are no X-axis labels for missing X values.

It is obvious that the line chart treats the X values as non-numeric text labels, ignoring any apparent numerical values. This is because the default axis for non-date X values is a text axis (below left). We can change this to a date axis (below right), and we see the data points are now plotted according to their unevenly-spaced numerical values. The first X-axis label is still the first X value and the last X-axis label is still the last X value. (Excel treats the numbers as dates with a format of “D”, so only the day shows.)

When numbers are out of order, the above behavior is even more different. The XY scatter chart draws the points and the lines connect them in order, moving left or right as the X values decrease or increase. The line chart shows the points in the order they appear in the data range: the first X-axis label is still the first X value and the last X-axis label is still the last X value, so the labels are out of order.

When we convert the text axis (below left) to a date axis (below right), we see that not only have the points been spaced according to their non-uniform values, but that the dates have been internally sorted prior to plotting. This internal sorting is a feature of charts with a date axis. The first X-axis label is the *smallest* X value and the last X-axis label is the *largest* X value, so the labels are in order and the points are plotted left to right.

Let’s look at the same charts with dates instead of regular numbers. The XY scatter chart (left) and line chart (right) plot evenly spaced dates along the X axis in a similar way. The data is evenly spaced, so the data points are plotted evenly. The XY chart extends its X-axis a little bit below to a little bit above its data range, while the line chart uses the earliest date as the first axis label and the latest date as the last axis label.

The XY scatter and line charts also plot unevenly spaced dates similarly. The points are spaced unevenly to reflect the pattern in the data. As before, the XY chart extends its X-axis a little bit below to a little bit above its data range, while the line chart uses the earliest date as the first axis label and the latest date as the last axis label.

Out of order dates are plotted in an XY scatter chart in the order they appear in the worksheet. The lines connecting the points start at the first point, and move left or right for earlier or later dates. In the line chart, the lines always connect from left to right, reflecting the internal sorting that takes place. As always, the XY chart extends its X-axis a little bit below to a little bit above its data range, while the line chart uses the earliest date as the first axis label and the latest date as the last axis label.

We’ve seen how a blank top left cell can help Excel to parse data correctly into X and Y values and series names.

But this concept of blank cells is more magical than that. Suppose you have two columns of category labels, for years and quarters. Each of these label columns has a blank cell in its header (blank cells are shaded gold). Each year only appears once, next to the first quarter, and there are blank cells next to the other quarters.

Excel sees the two blanks in the top left, and uses both columns as category labels. There are two rows of labels, with quarter labels in the first row and years in the second. Each year label is centered under the corresponding quarter labels, and a vertical tick mark extends from the axis to help delineate the labels.

This is a very nice effect, only available for line, column, area, and bar charts that are using a text style X-axis. It will not work for a date axis or for an XY scatter chart’s X-axis: in those cases you will get the numbers 1, 2, 3, etc.

You may have seen this kind of data arrangement in a Pivot Table, with multiple fields in the rows area, and noticed the multiple-tier category labels in the corresponding Pivot Chart. But you’re not stuck needing a Pivot Table, you can build this data layout yourself. You are not limited to two tiers of labels, either: below you can see a three-tiered axis, and I’ve seen it used for 5 or 6 tiers.

What if you indicate to Excel that you have two rows of series names? The same effect applies. The North label is combined with the alpha and beta series names, What if you indicate to Excel that you have two rows of series names? The same effect applies. The North label is combined with the alpha and beta series names, while the South labels is combined with the gamma and delta series names, to generate compound names.

You can combine the effects of multiple-tier category axis labels and compound series names in the same chart, as shown below.

Note that these blank cells must be totally blank, and not just look blank like a formula that returns a value of “”. And while a chart can treat #N/A as a blank in a chart’s Y values, #N/A will be treated as a text label, not as a blank, when used in the series name and category labels regions of the data range.

When a chart’s data doesn’t conform to these definitions of “good” data, the Select Source Data dialog shows only a blank for the chart data range. Below the range selection box, you are told “The data range is too complex to be displayed.” This means that the data is irregular. Not all series start and end at the same row, perhaps, or the series have different numbers of points. Perhaps the series names are misaligned from the Y values, or the series are plotted out of order. Anything that prevents Excel from indicating a nice rectangular block of data.

When the data does conform to our “good” data definition, the Select Source Data dialog happily displays the address of the data range.

In fact, the Select Source Data dialog is a bit more forgiving that my rules. If the data is separated by complete blank rows or columns, but otherwise fits within a rectangular range, the dialog shows the addresses of the various areas of the data range.

An Excel Table is a special data structure which provide advanced data handling capabilities; I have converted my data into a Table below. The header row has buttons for filtering and sorting of the Table. You can add a total row if desired. There are numerous styles you can apply, some of them approaching hideous; the Table below shows the default style.

There are many benefits to storing your chart data in a Table. The major benefit is that when you add data to the row below or the column to the right of a Table, the Table automatically expands to include the added data. What’s more, any formulas that reference a Table column or row will update automatically if the Table expands or contracts. This includes chart SERIES formulas and the Chart Data Range formula in the Select Source Data dialog. So if your chart uses a Table like this for its source data, when you add rows of data, each series in the chart will add the corresponding points; when you add columns to the Table, your chart will add series. Dynamic charts made easy!

One drawback to using a Table for your chart’s source data is that the header row of a Table can contain no blank cells. This means that a lot of the blank-cell based data parsing, especially the top left cell magic, may not work with a Table. But if your first column(s) are not numerical, Excel will still automatically parse them into X values. And you could still select just part of the Table, create your chart, then manually specify the X values.

The post Good Chart Data appeared first on Peltier Tech.

]]>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?

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Here is the chart for the second data set, with `EqualMajorUnit`

set to True.

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
```

- Calculate Nice Axis Scales with LET and LAMBDA
- Calculate Nice Axis Scales in Your Excel Worksheet
- Calculate Nice Axis Scales in Excel VBA
- Chart UDF to Control Axis Scale
- How Excel Calculates Automatic Chart Axis Limits
- Reciprocal Chart Axis Scale
- Custom Axis Labels and Gridlines in an Excel Chart
- Custom Axis, Y = 1, 2, 4, 8, 16
- Logarithmic Axis Scales
- Link Excel Chart Axis Scale to Values in Cells
- Consistent Axis Scales Across Multiple Charts
- Gantt Chart with Nice Date Axis
- Select Meaningful Axis Scales
- Bar Chart Value Axis Scale Must Include Zero
- Text Labels on a Horizontal Bar Chart in Excel

The post Make Excel Chart Gridlines Square appeared first on Peltier Tech.

]]>