Data Techniques

Text Import Wizard

Friday, September 8, 2023 by Jon Peltier Leave a Comment

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

The Text Import Wizard has been part of Excel for decades. For heavy use, Power Query has become a more powerful tool for importing and managing large amounts of data. But the wizard remains a useful tool in its own right.

Automatic Data Conversions

When you type or paste data into Excel, Excel will “helpfully” convert the entry into standard Excel types. Excel will convert whatever it can into a number, which means it will strip off any leading zeros and truncate it to 15 significant figures. If the text contains numerical digits surrounding the letter E, Excel will convert it to a number in scientific notation. If Excel thinks the text looks anything like a date, BAM! Excel turns it into a date. Sometimes these conversions are more like Automatic Data Perversions.

This problem has become critical especially for biochemistry data sets, for example, because researchers give genes names like MARCH1 (short for Membrane Associated Ring-CH-Type Finger 1) and SEPT1, but Excel converts them into the dates that they resemble, March 1st and September 1st. An estimated 20% of research papers in the field have been found to have such errors in gene names. The problem has gotten so bad that Scientists rename human genes to stop Microsoft Excel from misreading them as dates rather than learn how to deal with data in Excel.

Excel has recently added the ability for users to selectively disable these automatic conversions. Go to File > Options > Data, and use the checkboxes at the bottom of the dialog.

This is apparently a very recent feature, described in Control data conversions in Excel. Excel is changing so fast, it’s difficult to keep up with all of the innovations.

Text Import Wizard

If you use File > Open to open a regular text file (which may be comma-separated but has a file extension of .txt), Excel automatically opens the Text Import Wizard so you can import the data the way you want, bypassing Excel’s automatic conversions. If you open a regular text file by dragging it onto the Excel window, Excel just opens it as a new worksheet with the comma-separated in the first column. You can then use Text to Columns to split the data into columns, also bypassing any automatic data conversions.

CSV (Comma-Separated Values) files are a native file type. This is convenient, but also dangerous, because when Excel opens a CSV file, it can perform numerous unexpected conversions on the imported values. If we trust the new Data Conversion options (described above), Excel will warn us about them. But I think it’s best to explicitly control your data.

The usual suggestion by experts like me is to use Power Query to import your CSV files. Power Query allows you to import files singly or in batches and control the conversion and formatting of the data in each field.

If you’re only importing CSV files occasionally, one at a time, Power Query seems like a bit of overkill. Wouldn’t it be nice to simply use the Text Import Wizard on a CSV file? Well, you can, but you first have to activate it.

Go to File > Options > Data, the same dialog that has the Automatic Data Conversions options. Under Show Legacy Data Import Wizards, check From Text (Legacy). This will enable the Text Import Wizard for any new text files you open, including CSV files.

Using the (Legacy) Text Import Wizard

When importing a CSV file, the Text Import Wizard will not activate if you drag the file onto the Excel window or if you use File > Open. You have to follow the sequence Data > Get Data > Legacy Wizards > From Text (Legacy).

Select your file

This opens the Import Text File Dialog, which is the File Open dialog with a new caption and the text file filter activated. In this example I’m using a CSV file containing Microsoft stock price information.

Select your file and click Open. This starts the Text Import Wizard. If you use the Text to Columns Wizard, the Text Import Wizard will be very familiar. The term Legacy is a bit misleading, though. If you used the Text Import Wizard in pre-Power Query versions of Excel, the Legacy Text Import Wizard will look familiar, but new features have been added.

Step Through the Wizard

In step 1 of the wizard, you can decide whether the file is delimited (Excel guesses that it is), you can indicate which row to start importing from, and you can specify whether the first row of the file contains headers (the headers checkbox is new). There is a preview of the file to help you choose these options.

Click Next to go to step 2 of the wizard. Here you can select which character(s) to use as a delimiter. The preview shows the data split into columns based on your selections.

Click Next to proceed to step 3. Here you can define the import of each column. In this case, I left them all as General, which means Excel will do what it wants to do. But you can specify whether to import columns as text (so no converting genes into dates or truncating credit card numbers), whether to change the date conversion (in case the file came from a region that uses different day-month-year order), or to skip a column altogether.

Click Finish to exit the wizard.

Drop Your Data

You’re not done yet. There is one more dialog, which looks like the Close and Load dialog of Power Query. The default settings are to drop the data into a Table at the active cell and add it to the Data Model.

With these selections, the data is dropped into a Table, just as if it were imported using regular Power Query. Note the name of the Table: Excel uses the prefix “Table_” plus the imported file name without the file extension. The file MSFT.csv becomes Table_CSV.

You can choose not to add the data to the Data Model. This disables the options for a Table, Pivot Table, Pivot Chart, or Connection Only.

In this case the data is dropped into the worksheet, but not as a Table. Note that Excel names the range using the imported file name minus the file extension: MSFT.csv becomes the range named MSFT. Import the file again into the same workbook, and it will be named MSFT_1, etc.

The Text Import Wizard is a quick and useful way to import CSV files cleanly into Excel, avoiding any issues with data corruption.

Posted: Friday, September 8th, 2023 under Data Techniques, General.
Tags: .
Comments: none

Unlink Chart Data

Thursday, June 23, 2022 by Jon Peltier 2 Comments

Thursday, June 23, 2022 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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:

Security Warning: Automatic update of links has been disabled

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

Chart Data

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.

A chart, its SERIES formula, and its data 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)

Copy a Picture of the Chart

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…

Copy Picture command in the Excel Ribbon

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

Change the Cell References to Hard-Coded Values

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)

Automate with VBA

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

Simple VBA Algorithm

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

More Flexible Code

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.

Fix the Date Axis

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.

Unlinked Chart with dates on axis as category labels

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:

Unlink the Chart and Axis Titles

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

Posted: Thursday, June 23rd, 2022 under Data Techniques.
Tags: Chart Data, SERIES Formula.
Comments: 2

Excel Chart Series Size Limits

Monday, March 28, 2022 by Jon Peltier 3 Comments

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

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.

tl;dr

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.

VBA Arrays as Chart Series Data

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.

Scatter chart with ten points, based on two ten-element VBA arrays.

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.

VBA to Explore Series Length

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.

Output of VBA array testing for 10 points.

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

Output of VBA array testing for 10,000 points.

A brief aside while I obsess about the SERIES formula…

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

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:

Don't try to enter a formula with more than 8192 characters, even if Excel did it first.

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.

Excel 2003 had a much lower limit on VBA arrays in chart series. Also, it was way uglier.

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.

Excel 2003 wouldn't even try to overload the SERIES formula.

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!

Back to the main topic

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.

Output of VBA array testing for 100,000 points: only 16,384 were plotted.

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.

Smarter VBA to Explore Series Length

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.

Output of vertical VBA array testing for 100,000 points: only 32,000 were plotted.

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.

Where Does 32,000 Come From?

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.

File Sizes for 32,000 Points

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.

Worksheet Ranges as Chart Series Data

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.

Scatter chart containing data for all 1,048,576 rows of the worksheet.

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.

Multiple-Area Worksheet Ranges as Chart Series Data

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.

Data and charts showing one series, two series, and one series combining both sets of data.

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.

Data for scatter chart of two series, both using all 1,048,576 rows of the worksheet.

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:

Scatter chart of two series, both using all 1,048,576 rows of the worksheet.

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:

Scatter chart of one series, using all 1,048,576 rows of the worksheet twice.

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.

File Sizes for Whole Columns of Points

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

Follow-Up 2.5 Years Later

Just for fun, I extended my chart up to 4 full columns of X and Y values, a total of 4,194,304 points. The file size was 80.7 MB and the SERIES formula was:

=SERIES(,(Sheet1!$A:$A,Sheet1!$D:$D,Sheet1!$G:$G,Sheet1!$J:$J),(Sheet1!$B:$B,Sheet1!$E:$E,Sheet1!$H:$H,Sheet1!$K:$K),1)

Excel chart series containing 4,194,304 points

As you might expect, Windows was a little bit slow, and Excel was slipping in and out of Not Responding Mode.

Posted: Monday, March 28th, 2022 under Data Techniques.
Tags: Chart Data, SERIES Formula, VBA.
Comments: 3

Convert Line Chart to Step Chart with VBA

Monday, December 21, 2020 by Jon Peltier Leave a Comment

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

I’ve written a tutorial about making Step Charts in Excel. Given a timeline of points where a value is set and where that value is constant until the next time it is changed, I showed how to build a line chart that showed the periods between changes as horizontal lines, and the changes as vertical lines. I will show how to use VBA to convert a line chart to a step chart.

Review: Build a Step Chart by Hand

For example, here is a table and chart of first class postage rates in the US. Obviously, connecting the data points with diagonal lines does not reflect the price between points.

Here is the original data again, and I’ve added dummy points at the beginning and end of the arbitrary time period I’m watching.

I’ll start my chart data range using all but the first X values (dates) and all but the last X values. These points stagger the values to one date later.

We need to connect the two sets of points: a horizontal line from each blue (original) point to the next orange (staggered point), and a vertical line from each orange point to the next blue point.

And we don’t need those markers.

Lines connecting original and staggered points

If we append the intact original data range below the staggered data, and make the chart using this new extended range, Excel internally sorts the dates, and produces the steps we want.

Extended USPS first class postage rates and step chart

Using VBA with the SERIES Formula

This isn’t the most difficult chart protocol I’ve ever described, but it requires an extended data range and a bit of work. Wouldn’t it be nice to run a simple macro and make it happen?

We’re going to do just that, with a short VBA procedure which will hack the chart SERIES formula to mimic generating the extended data range above. I’ve written about the SERIES formula in The Excel Chart SERIES Formula, and I wrote a tutorial about using VBA to hack the SERIES formula in Switch X and Y Values in a Scatter Chart.

If I select the chart series, I can see the series formula in Excel’s Formula Bar. (The artificial endpoints are colored red.)

The formula looks like this:

=SERIES(Postage!$C$2,Postage!$B$3:$B$17,Postage!$C$3:$C$17,1)

The four arguments are:

=SERIES(SeriesName,XValues,YValues,Index)

As shown in the manual approach above, I need to combine all but the first X value with all of the X values, and all but the last Y value with all of the Y values. In the series above, our X values will become

Postage!$B$4:$B$17 and Postage!$B$3:$B$17

and our Y values will become

Postage!$C$3:$C$16 and Postage!$C$3:$C$17

You can use multiple-area ranges if you separate each area’s address with commas, and surround them with parentheses:

(Postage!$B$4:$B$17,Postage!$B$3:$B$17) (Postage!$C$3:$C$16,Postage!$C$3:$C$17)

Our final SERIES formula will look like this:

=SERIES(Postage!$C$2,(Postage!$B$4:$B$17,Postage!$B$3:$B$17),
(Postage!$C$3:$C$16,Postage!$C$3:$C$17),1)

Outline the VBA Procedure

Start with the active chart (and abort if there is none).
Get the SERIES formula for each series.
=SERIES(SeriesName,XValues,YValues,Index)
Split this formula at the commas to create an array.
{"=SERIES(SeriesName","XValues","YValues","Index)"}
Find the range of X values, then find the range from the second cell to the last, get the address, and append the address of the original complete range.
Find the range of Y values, then find the range from the first cell to the next-to-last, get the address, and append the address of the original complete range.
Finally, put the new X and Y references into the array, and join them into a new SERIES formula, and put that into the chart.

The VBA Procedure

Here is the procedure, which took me about 15 minutes to write.

Sub LineToStepChart()
  If Not ActiveChart Is Nothing Then
    Dim iSrs As Long
    For iSrs = 1 To ActiveChart.SeriesCollection.Count
      Dim srs As Series
      Set srs = ActiveChart.SeriesCollection(iSrs)
      Dim nPts As Long
      nPts = srs.Points.Count
      
      Dim SrsFmla As String
      SrsFmla = srs.Formula
      '' =SERIES(name,xvalues,yvalues,number)
      
      Dim vFmla As Variant
      vFmla = Split(SrsFmla, ",")
      '' array {"=SERIES(name","xvalues","yvalues","number)"}
      
      If iSrs = 1 Then
        ' only needed on first series since all series use the same X values
        ' fix X values: all but first point then all points
        Dim sXVals As String
        sXVals = vFmla(LBound(vFmla) + 1)
        Dim sXSheet As String
        sXSheet = Left(sXVals, InStr(sXVals, "!"))
        
        Dim rXVals As Range
        Set rXVals = Range(sXVals)
        Set rXVals = Range(rXVals.Cells(2), rXVals.Cells(nPts))
        Dim sNewXVals As String
        sNewXVals = "(" & sXSheet & rXVals.Address & "," & sXVals & ")"
      End If
      
      ' fix Y values: all but last point then all points
      Dim sYVals As String
      sYVals = vFmla(LBound(vFmla) + 2)
      Dim sYSheet As String
      sYSheet = Left(sYVals, InStr(sYVals, "!"))
      
      Dim rYVals As Range
      Set rYVals = Range(sYVals)
      Set rYVals = Range(rYVals.Cells(1), rYVals.Cells(nPts - 1))
      Dim sNewYVals As String
      sNewYVals = "(" & sYSheet & rYVals.Address & "," & sYVals & ")"
      
      '' array {"=SERIES(name","new xvalues","new yvalues","number)"}
      vFmla(LBound(vFmla) + 1) = sNewXVals
      vFmla(LBound(vFmla) + 2) = sNewYVals
      
      SrsFmla = Join(vFmla, ",")
      '' =SERIES(name,new yvalues,new xvalues,number)
      
      srs.Formula = SrsFmla
    Next
  End If
End Sub

After selecting the last chart above (with markers removed) and running this procedure, here is the resulting chart and series formula:

Step chart with SERIES formula — *click on image to view full size*

Note that this only works for Line or Area charts, for X values which are dates or at least treated numerically. Line charts sort numerical X values before plotting them, which makes this macro approach so easy. If the X values are text (non-numeric labels), the following chart results:

It did what I specified, but with a text axis, it failed to produce steps. We could test for the right kind of axis, and the right kind of data, but that’s a lot of work for a quick and dirty program.

Step Charts in Peltier Tech Charts for Excel

Step Charts are included in the many custom chart types available in Peltier Tech Charts for Excel 4.0.

Peltier Tech Charts for Excel ribbon - Step Chart

Click the button to open the Step Chart dialog, or click the dropdown arrow. The first menu item also opens the dialog, while the second will convert your existing chart to a step chart using an approach like the one above, but with more error checking and flexibility built into it. Actually, it converts a copy of your chart, in case you change your mind.

Peltier Tech Charts for Excel - Step Chart Options

The Step Chart dialog lets you select your data range and specify its characteristics, then select a chart type.

Peltier Tech Charts for Excel Step Chart Dialog

The result looks great, but it’s much easier than the manual approach. The chart is built on a new worksheet because of the data manipulation required; below I’ve simply pasted the chart next to the original data.

Peltier Tech Charts for Excel Step Chart

Even if your X values are non-numeric text, the program will give you a step chart.

Visit Peltier Tech Charts for Excel 4.0 to purchase a license to this powerful Excel add-in.

Posted: Monday, December 21st, 2020 under Data Techniques.
Tags: Line Charts, SERIES Formula, Step Charts, VBA.
Comments: none

Add One Trendline for Multiple Series

Wednesday, December 5, 2018 by Jon Peltier 23 Comments

A member of the online forum Quora asked, How can I have multiple scatter plots and one trendline for all of them combined in Excel? I interpreted this to mean, “I have multiple scatter series in my chart, how do I get a trendline for the combined data in the chart?”

I made up some dummy data, and generated this XY chart. Note: this approach will not work in Line charts; in general you should not use Line charts if you need trendlines for your data.

You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx.

Of course you can add a trendline to each of the series, and you get this cluttered mess. It has multiple trendlines, not a multiple-trendline.

Multiple XY Series and Trendlines in One Chart

Note: In a recent version of Excel (I don’t recall if it was 2013 or 2007), trendlines changed from black to the color of the points, which was good for visibility, and they also became a dotted line, which was bad for visibility. I always change to a solid line.

Well, this is not what the person wanted to know. But it’s no big deal to get what they wanted.

First, let’s just check out the chart. The three series have these formulas:

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11,1) =SERIES(Sheet1!$E$2,Sheet1!$D$3:$D$11,Sheet1!$E$3:$E$11,2) =SERIES(Sheet1!$G$2,Sheet1!$F$3:$F$11,Sheet1!$G$3:$G$11,3)

I’ve color coded the formula arguments to show series names in red, series X values in purple, and series Y values in blue (the colors of the series highlights in the worksheet). The numbers at the end are the plot order of each series.

What we need to do is add a series to the chart that uses all of these X values and all of these Y values. There are at least two ways to get this series.

Select Data

Right click on the chart and click on Select Data from the pop up menu.

The Select Data Source dialog appears.

Click the Add button, and the Edit Series dialog appears.

Click in the Series Name box, and add a descriptive label. “Combined” works.

Click in the Series X Values box, then with the mouse select the first range of X values. Type a comma, and select the second range of X values. Type another comma, and select the third range. Note: instead of typing a comma, you could hold the Ctrl key while selecting the second and third ranges, but I find that I make fewer errors if I type the comma and then don’t have to worry about how many buttons I’m holding with how many fingers.

Click in the Series Y Values box, delete the “={1}” that is there, then with the mouse select the first range of Y values. Type a comma, and select the second range of Y values. Type another comma, and select the third range.

The populated Edit Series dialog looks like this:

The Series X Values and Series Y Values boxes contain

=Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11

and

=Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11

Excel will put parentheses around these comma-separated addresses, and double quotes around the Series Name.

Click OK twice, and a new series will appear, whose markers obscure those of the existing three series.

Multiple XY Series, Including the Combined Series, in One Chart

The formula for the added series is:

=SERIES("Combined",(Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11), (Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11),4)

We can see that it includes the X values and the Y values for the three original series.

Add Series Formula Directly

You don’t need to use the Select Data Source dialog to add data to a chart. If you select the chart area (just the outermost rectangle containing the chart), you can click in the Formula bar, and enter your formula. Note that you have to enter double quotes around the series name and parentheses around the multiple areas of the X and Y values. You can type the addresses of the individual names, which is inconvenient since you have to remember to include the sheet name and exclamation point; it’s easier to select the ranges with the mouse. You can hold Ctrl while selecting multiple areas, but I find it easier to type a comma between range addresses. Don’t forget to end the formula with a comma, the Plot Order 4, and the closing parenthesis.

When you press Enter, the chart has a new series that hides the old series, just like above.

Add Trendline

Select this new series, click on the plus “skittle” next to the chart, and check Trendline.

Now format the “Combined” series to hide it (no lines or markers), and format the trendline to enhance visibility and to display the trendline formula and R² value.

Multiple XY Series with One Combined Trendline in One Chart

Update: Trendline Calculator for Multiple Series

For anyone who wants to apply this technique but finds it tedious to add a series with all the X and Y ranges, I’ve built a small utility that does the heavy lifting for you.

It’s a simple Excel add-in that lets you select a chart, choose which series in the chart to include (and exclude), and it builds a hidden series with a visible trendline combining all series that you’ve selected.

In the article I show some simple code that compiles the data into a new series. The add-in is free and unlocked, so you can look into its inner workings: the code, the dialog (UserForm), and the custom ribbon elements.

See Trendline Calculator for Multiple Series for information.

Trendline and Regression Articles on this Blog

Posted: Wednesday, December 5th, 2018 under Data Techniques.
Tags: Statistics, Trendlines.
Comments: 23

Microsoft MVP Logo