Working with Pivot Charts in Excel

About Pivot Charts

If you select a pivot table and insert a chart, Excel inserts a pivot chart. A pivot chart is a special Excel chart, with some strengths and some limitations. I used to avoid pivot charts because of these limitations, which included not being able to hide the field buttons and not being able to resize the plot area or move axis and chart titles. But Microsoft has kept improving them, and now the few remaining limitations seem pretty reasonable given the power and constraints of pivot tables themselves.

  1. A pivot chart is linked to its parent pivot table. Changes to the pivot table are reflected in the pivot chart, and vice versa. If the pivot table changes size, the pivot chart changes the number of its plotted series, and changes the lengths of these series, to accommodate the updated pivot table size.
  2. The pivot chart has optional field buttons that allow the same filtering capabilities directly in the chart that are available to the pivot table.
  3. All data in the pivot table is plotted in the pivot chart, except for subtotals and grand totals. No data from outside the pivot table is allowed in the pivot chart.
  4. Pivot charts are plotted with category labels and series values in columns only. Pivot charts cannot be plotted by row.
  5. Not all chart types are available in pivot charts. Line, column, area, bar, and pie charts are available, for example, but XY scatter charts and bubble charts are not.

Often, pivot charts are exactly what is needed. Sometimes, however, a regular chart must be used. For example, you might want a scatter plot of the pivot table’s data. Or you might want to add data from another source.

My colleague Debra Dalgleish has a brief Pivot Chart Tutorial on her Contextures.com web site, as well as a FAQ page on Pivot Tables and Pivot Charts. Debra also wrote Using Pivot Tables in Microsoft Excel on this blog.

This article will answer the following common questions about pivot charts in Excel.

  • How do I disconnect a pivot chart from its pivot table?
  • How do I copy a pivot chart and link it to another pivot table?
  • How do I convert a pivot chart into a regular chart and preserve its links to the pivot table?

The last question could be answered by Making Regular Charts from Pivot Tables. But the question is often asked by someone who has spent significant time formatting his pivot chart, and doesn’t want to lose this formatting or be forced to recreate it.

We’ll start by reviewing regular charts and how their data is handled. Then we’ll examine differences between regular charts and pivot charts. Finally we’ll investigate answers to the questions above.

Regular Charts

The screenshot below shows a table with some simple data located in B2:D14. The data is rearranged in F3:I7 (a pivot table could have done this). Below that is a regular Excel chart which plots the data from this second range.

Chart Source Data Highlighting

When you select a chart that has a well-behaved* source data range, the chart’s data range is highlighted in the worksheet. The highlighting for our simple chart in Excel 2013 and 2016 is shown below: the X values (category labels) are purple, the Y values are blue, and the series names are red. You can click and drag the highlighted borders to move the chart data, and you can click and drag on the highlighted corners to resize the chart data.

*Well-behaved means that the Y values of the series are in adjacent rows or columns, in order. Y values and X values (if present) must be aligned: in the chart below, the X values and all sets of Y values all begin on the same row and all end on the same other row, and the series names are aligned with the Y values.

When you select a plotted series, the data for that series is highlighted in the worksheet. The highlighting for the first series of our simple chart in Excel 2013 and 2016 is shown below: the X values (category labels) are purple, the Y values are blue, and the series names are red. You can click and drag the highlighted borders to move the chart data, and you can click and drag on the highlighted corners to resize the chart data. Note that our series is plotted by columns.

Chart Series Data Highlighting

When a series is selected, you can also see the corresponding SERIES formula in the formula bar. This series formula has the following components:

  • Series Name: Pivot!$G$3
  • Category Labels (X Values): Pivot!$F$4:$F$7
  • Y Values: Pivot!$G$4:$G$7
  • Plot Order: 1

You can edit this formula in place to adjust the chart data.

Another way to adjust a chart’s data is the Select Data Source dialog. To open this dialog, click the Chart Tools > Design tab > Select Data button, or tight-click on the chart and click Select Data from the pop-up menu.

Select Source Data Dialog

Here is the Select Source Data dialog for our regular chart. The box at the top shows the entire source data, which was highlighted when we selected the entire chart. You can edit this as text, or select another source data range in a worksheet. Caveat: if your selection in the Chart data range box intersects a pivot table, your chart will be converted into a pivot chart based on that pivot table.

Click the Edit button under Axis Labels in the bottom right part of the dialog, and the Axis Labels dialog appears, showing the range containing the axis labels. You can edit this as text, or select another axis label range.

Select a series in the bottom right part of the dialog and click the corresponding Edit button, and the Edit Series dialog appears, showing the range containing the Y values. You can edit this as text, or select another range of values.

If you click the Switch Row/Column button, the same data is used as the source data, but its orientation is switched. The category axis labels become the series names, and the series names become the axis labels. Note that our chart now has four series with three points each (and three axis labels), and the red and purple highlighted regions have changed places.

When we select the first series, we see that it is now aligned in rows.

If your chart’s source data intersects a pivot table, clicking Switch Row/Column will convert your chart into a pivot chart based on that pivot table.

Disconnecting From Worksheet Data

Here’s a little-known debugging trick for Excel formulas. You can use the F9 function key or the Ctrl+= shortcut (hold the Ctrl key while you press the = key) to evaluate part or all of a formula in the formula bar. Fortunately this works for a SERIES formula.

If you click in the formula bar and click F9 or Ctrl+=, every section of the formula is evaluated (and the links are disconnected), as shown in this before-and-after screenshot.

Click Esc to restore the original formula, or Enter to keep the evaluated formula.

If you select just part of a formula and then click F9 or Ctrl+=, just the selected part of the formula is evaluated. In this screenshot, the Y value range of G4:G7 is converted to the array {1;3;2;4}.

To unlink a regular chart from its worksheet data, select each series, click in the formula bar, and press the F9 key.

Copying the Chart

You can copy a chart and paste it anywhere in the same workbook, even onto a different worksheet, and nothing happens to the chart. It shows the same data that it was linked to (on the original sheet), and the series formulas still link back to the original data. This is familiar, expected behavior, although when you want to link the chart to the data on its new parent worksheet, it’s not so welcome. But see Make a Copied Chart Link to New Data if that’s what you need to do.

When you copy a regular chart to a new workbook, it still points back to the original data, which in the SERIES formula is referenced to the original workbook as well as the original worksheet, as “[Pivot.xlsx]Pivot”. So the regular chart behaves exactly as expected.

Pivot Charts

The screenshot below shows a table with the same simple data located in B2:D14. A pivot table in F2:I7 has rearranged the data. Below the pivot table is an Excel pivot chart which plots the data from the pivot table. Note the field buttons in the pivot chart, corresponding to the controls in the pivot table.

We can hide the field buttons (Pivot Chart Tools > Analyze ribbon tab > Field Buttons) and the chart will look just like our regular chart. But it still has the capabilities and limitations of a pivot chart.

Chart Source Data Highlighting

When the pivot chart is selected, no chart data highlights appear in the worksheet.

Chart Series Data Highlighting

When a series is selected in the pivot chart, no series data highlights appear in the worksheet. The SERIES formula appears in the formula bar, but you cannot edit the series data by editing the series formula. You can only change the series plot order by changing the last parameter in the series formula.

Select Source Data Dialog

Here is the Select Source Data dialog for our pivot chart. The box at the top shows that the source data is our pivot table; this cannot be changed. The axis labels cannot be edited, nor can the series values be edited.

If you click the Switch Row/Column button, the chart changes its appearance to match how our regular chart changed: three series of four categories becomes four series of three categories. But the chart’s data orientation didn’t change, because pivot charts can only plot columns of data. Instead, Excel switched the fields in the rows area of the pivot table with those in the columns area. The X and Series field buttons in the chart have changed places as well.

The series formula shows that the first series of our pivot chart is still plotted by column, with the category labels in column F and Y values in column G.

Disconnecting From Worksheet Data

Excel does not let you evaluate part or all of a pivot chart’s SERIES formula using the F9 or Ctrl+= trick, so you can’t use it to disconnect the pivot chart from its pivot table.

Copying the Chart

You can copy a pivot chart and paste it anywhere in the same workbook, even onto a different worksheet, and nothing happens to the chart. It shows the same data that it was linked to (in the original pivot tables), and the series formulas still link back to this pivot table.

Answering Those Questions

How do I disconnect a pivot chart from its pivot table?

Interesting things happen when you copy a pivot chart to a different workbook. The first thing you may notice is that the field buttons have disappeared, because the pivot chart has been converted to a regular chart. The second thing you’ll notice, if you check out the SERIES formula, is that the links to worksheet ranges have been changed into literal arrays of strings and numbers, as if we used our F9 (Ctrl+=) trick to evaluate the formula.

There’s the answer to our first question, how to unlink a pivot chart from its pivot table. Simply copy the pivot chart to a different workbook. Once the links are broken, you can copy it anywhere, even into the original workbook, and it will remain disconnected from the pivot table.

How do I copy a pivot chart and link it to another pivot table?

In the screenshot below, I’ve copied my unlinked chart and pasted it into the original workbook, in a different worksheet with a different Table of data and a different Pivot Table. As noted above, it’s still disconnected.

Here is the Select Source Data dialog for our unlinked chart. The Chart Data Range box at the top is empty, because the chart’s data is hard-coded into the chart’s SERIES formulas. You can click in the box and select a data range from the worksheet.

 

If you select a cell or range that overlaps with a pivot table and click OK, the chart will become a pivot chart and use the data from the selected pivot table. In the screenshot below the chart now has field buttons, so we know it has been converted into a pivot chart. The SERIES formula shows links to the data in the pivot table.

There’s the answer to our second question, how to copy a pivot chart but link it to a new pivot table. Copy the pivot chart to a different workbook to disconnect it from the first pivot table, then copy the chart to the sheet with the second pivot table, then use the Select Data dialog and select the new pivot table in the Chart Data Range box.

How do I convert a pivot chart into a regular chart and preserve its links to the pivot table?

If we avoid the Chart Data Range box, we can still use the Select Data Source dialog to reconstruct links to the pivot table data. This is essentially the technique in Making Regular Charts from Pivot Tables, but we’re using the pivot chart which may have had custom formats applied.

Under Horizontal (Category) Axis Labels, click Edit, and the Axis Labels mini-dialog will appear, showing the literal array of labels.

You can clear the box and select the axis label range from the worksheet using your mouse. Then click OK.

Now under Legend Entries (Series), select the first series from list, and click Edit. The Edit Series mini-dialog appears with the series name as a string, and the series values as a literal array of numbers.

You can clear each box and select the cell containing the series name and the column of cells containing the series values. Click OK, then repeat for the rest of the series in the chart.

The Select Data Source dialog now looks like this, with the Chart Data Range box displaying the range containing all of the pivot table data. Don’t click in this box, and don’t click Switch Row/Column, or your chart will become a pivot chart.

Click OK, and notice how the chart now plots the pivot table data. The data is highlighted in the worksheet, and the chart has no field buttons, because it remains a regular chart.

Selecting a single series shows the data is plotted by column, but again, the series highlights verify that the chart is not a pivot chart.

And this is the answer to our last question, how to convert our pivot chart to a regular chart but maintain links to the pivot table’s data. Actually, I’ve linked it here to a new pivot table, but I could link it to the original pivot table in the same way. Strictly speaking, this approach didn’t actually maintain the links, as we had to reconstruct them. There is no way to maintain the links while converting the pivot chart into a regular chart.

Peltier Tech Chart Utility

Dynamic Charts in Excel 2016 for Mac

A reader emailed to ask whether you could make a dynamic chart using OFFSET-function-based Names in Excel 2016 for Mac. Good question, and I wondered if he’d encountered some unexpected problem, perhaps a bug, in Mac Excel. So I dusted off my MacBook Pro and tried it out.

Bottom line: There are several ways to make dynamic charts in Excel, and there seems to be no difference other than cosmetic in how they work between different versions of Excel, and between operating system. The protocols are the same for Mac Excel and Windows Excel, and perhaps it’s time for a quick review. This exercise was done completely in Mac Excel 2016, and other than not knowing a few of the shortcuts I use everyday, it was not very different from working in Windows Excel 2016.

Dynamic Charts in Excel

It’s pretty easy to set up data and create a chart in Excel. But once you’ve created a chart, it keeps plotting data from the same cells. If the data in the cells changes, so does the chart, but if the data extends to more cells (or shrinks to fewer cells), the chart doesn’t seem to notice.

Static Excel Chart

There are a couple ways to create charts that will grow with your data. The easiest way is to use Tables as the chart source data. A bit more complicated is to use Excel’s Names to define the series data for your chart. Using Names can lead to more flexibility in defining the data in your charts. I’ll describe how to make dynamic charts using Tables, using Names, and using Names in a more flexible way.

Dynamic Charts Using Tables

The easiest way to make a chart’s contents reflect the size of a range of data is to put the data into a Table.

Tables made their appearance in Excel 2003, and were called “Lists”. These lists were a more structured container for your data, with a database structure of fields (columns) and records (rows), field headers (column headers) and filtering tools. You could sort and filter your data range easily, and any formula that used a whole column of your List updated to automatically keep using that whole column of the list. Lists became the favored source data for charts and also for pivot tables.

In Excel 2007, Lists became known as “Tables”, and their capabilities have been expanded in every version since.

The screenshot below shows the same data and chart as above, but the data is now in a Table.

To get your data into a table, you select it (or select one cell and let Excel figure out how far it stretches), and on the Insert tab of the ribbon, click Table. Excel asks if your table has headers, then it applies a Table style (the yellow style is shown below), it adds AutoFilter dropdown arrows to the field headers, and it puts a small backwards “L” bracket at the bottom right corner of the table.

Excel Chart Based On Table

You can change the size of the Table by clicking and dragging the bracket at the bottom left corner of the Table. If you type or paste data directly below the Table, the Table will automatically expand to include this new data. And a chart that uses all rows of the existing Table will expand accordingly.

Excel Chart Expands to Include Added Table Rows

If you type or paste data directly to the right of the Table, the Table will also automatically expand to include this new data. A chart that uses all of the existing Table will expand accordingly.

Excel Chart Expands to Include Added Table Columns

This little trick of adding a new series if the data expands accordingly is nice, but it requires that the chart already contain all of the Table’s data.

Names (a/k/a “Defined Names”, “Named Ranges”, etc.)

A Name is what Excel calls a variable that resides in a worksheet or a workbook.

Names are often assigned to cells or ranges; for example, you might place a sales tax rate into a cell and name the cell SalesTax, and subsequently use the cell’s name rather than its address in a formula. Because of this Names have been nicknamed “Named Ranges”.

However, the definition of the name includes a formula. If my sales tax rate was stored in cell A1, then my Name SalesTax would have a definition of “=A1”. Because of this, John Walkenbach proposed that Names should be called “Named Formulas”, but he’s smarter than the rest of us, so his suggestion didn’t stick.

We can use Names in our charts, but we need a distinct name for each dynamic range that the chart will need. We’ll need one Name for the X values if the series use the same X values range, and we’ll need one Name for the Y values of each series. In our sample, we will need three Names. I’ll call them XValues, Y1Values, and Y2Values, and I will define them as follows:

XValues
=OFFSET(Names!$A$1,1,0,COUNTA(Names!$A:$A)-1,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by one row and right by zero rows, then makes it as many rows tall as the number of alphanumeric cells in column A minus one (we don’t want to include the “Category” label), and one column wide.

So starting with cell A1, our range begins in cell B1, and is 6 rows tall and one column wide; our final range is A2:A7. It’s easy to see that adding another value into cell A8 will expand this range to A2:A8. However, if we add a value in cell A57, it will also expand our range to A2:A8, so we need to make sure the rows below our data are kept blank.

The other two definitions are easier:

Y1Values
=OFFSET(Names!XValues,0,1)
Y2Values
=OFFSET(Names!XValues,0,2)

We’ve already figured out how large each range needs to be, since the X and Y values have the same number of cells, so both of these OFFSET formulas start with the first name XValues as an anchor, and offset no rows down but one or two columns to the right. If we don’t specify sizes, then the new Names will define ranges the same size as the anchor.

Okay, that’s how to build a formula definition for a Name. Let’s actually create a Name.

On the Formulas tab of the ribbon, click the Define Name dropdown, and select Define Name… This pops up the Define Name dialog, shown below for the Mac. The Windows dialog is a bit more extensive, and Windows Excel has a much better Names Manager (this dialog happens to serve as the Mac’s Names Manager). For a truly powerful Name Manager, you should try out the free Name Manager add-in at the website of my colleague, Excel MVP Jan Karel Pieterse.

Static Excel Chart

If you’ve selected data before opening the dialog, Excel tries to guess how you want to name data based on labels in the top row and left column of the selection. But I’ve cleared all of this so we’re starting fresh.

Here I’ve typed the name of the Name. Note that I’ve included the worksheet name and exclamation point, which means the Name will be “in scope” (i.e., available) for the worksheet “Names”. Otherwise it would be “in scope” for the entire workbook.

Static Excel Chart

Then enter the formula where it says “Select the range of cells”. You can enter any formula that refers to cells, or a formula that calculates a value, or a constant value. I don’t capitalize my function names when I enter them; that way, if there’s an error, Excel won’t capitalize a bad function name. A misspelled keyword is easier to recognize if it is not capitalized (“offfset” vs. “OFFFSET”).

Static Excel Chart

Click the OK button to add the Name and exit the dialog, or click the Plus icon to add the name and keep the dialog open.

The name is listed in the box at the left; the worksheet name is listed as well to remind us that the scope of the Name is limited to that worksheet. The sheet name is removed from the name in the top right box.

Static Excel Chart

You can make sure the name refers to the intended range if you click in the box with the formula defining the Name. With the cursor in the formula, the range A2:A7 is highlighted in the worksheet. Perfect.

Static Excel Chart

Now enter the name and formula for the next Name, and don’t forget to include the worksheet name.

Static Excel Chart

Click the plus icon to add the name, and click in the formula to make sure that the Name references the desired range, B2:B7. Check.

Static Excel Chart

Enter the name and formula for the last Name, remembering to include the worksheet name. Click the plus icon, and check that the formula refers to C2:C7.

Static Excel Chart

Whew! Now we’re finally ready to make our dynamic chart.

Dynamic Charts Using Names

Every chart series has a formula that defines the data in the chart. The blue series in the static chart below is

=SERIES(Names!$B$1,Names!$A$2:$A$7,Names!$B$2:$B$7,1)

This means it uses cell B1 of the sheet Names for the series name (“Alpha”), A2:A7 for the X values, B2:B7 for the Y values, and it’s the first series in the chart. The formula for the orange series is

=SERIES(Names!$C$1,Names!$A$2:$A$7,Names!$C$2:$C$7,2)

We can use the Select Data dialog to modify these, but it’s easiest to simply edit the formula directly.

Select the blue series of the dynamic chart, and observe the formula in the Formula Bar. It probably looks like the first SERIES formula above (I invariably start with a static chart of the data I want to plot dynamically). Edit the formula to read as follows, and press Enter.

=SERIES(Names!$B$1,Names!XValues,Names!Y1Values,1)

If Excel doesn’t like the new formula, make sure you’ve spelled the Names correctly.

Similarly edit the formula for the orange series to read

=SERIES(Names!$B$1,Names!XValues,Names!Y2Values,2)

At first the two charts look the same.

Static Excel Chart

When we select the static chart, we can see the chart’s source data highlighted in the worksheet.

Static Excel Chart

We see the same data highlighted when the dynamic chart is selected. It’s convenient that Excel is smart enough to highlight the chart data even if it is defined by dynamic Names. I’ll select the dynamic charts in the rest of this tutorial to show the range included in these charts.

Static Excel Chart

Now let’s extend the data by a couple of rows. The static chart isn’t clever enough to notice, but the dynamic chart keeps up nicely, illustrated by the highlighted data in the worksheet.

Static Excel Chart

If we extend the data by a new column, the static chart doesn’t change, and the dynamic chart doesn’t add a series to represent the new data.

Static Excel Chart

I’ve added a third chart which shows the new data. I had to add an extra Name to the worksheet:

Y3Values
=OFFSET(Names!XValues,0,3)

and then I had to add a third series to the chart with the following SERIES formula:

=SERIES(Names!$B$1,Names!XValues,Names!Y3Values,3)

My new chart contains all the data, though I had to include it in the chart manually.

Static Excel Chart

Before Excel 2003, the only way to get a dynamic chart was by using Names. It’s a lot of work, and hardly seems worthwhile if using Tables is so easy. But if we know how to make a chart using Names, we can make a dynamic chart that’s more complicated than just expanding to add a row.

Dynamic Charts Using Complicated Names

How about a chart that doesn’t show all the data, but only the last several points. This might be useful if you want to show the last six months of sales, or high temperature for the previous week.

We’ll make a dynamic chart that plots the last five values. The setup is almost identical to the previous dynamic chart, but our definition of XValues will change.

XValues
=OFFSET(Names!$A$1,COUNTA(Names!$A:$A)-1,0,-5,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by the number of alphanumeric cells in column A minus one, and right by zero rows. This means now we’re starting at cell A7 instead of A2. Then we make the range -5 rows high, meaning we count upward, and one column wide. Our new X values range should be A3:A7. After entering the new name as before, check to make sure the correct range is highlighted.

Edit the SERIES formulas of your dynamic chart as we did above. We see that our static chart shows all six points of the data, but the dynamic chart shows only the last five points, categories B through F.

Static Excel Chart

Let’s add a couple rows of data. The static chart is, well, static, but our dynamic chart shows the new last five points, categories D through H.

Static Excel Chart

Summary

Dynamic charts can easily be created in Excel using data ranges from Tables.

With a bit more work dynamic charts can be created using skillfully defined Names. These charts can be more flexible than Table-based dynamic charts, depending on the formula skills of the Excel user. There are a few gotchas that I didn’t mention: some Name definitions seem like they should work, for example, but Excel charts won’t recognize certain functions. Also, some Name names may cause problems, especially those beginning with the letter “c”; you can’t enter them into the SERIES formula, but you can use them in the Select Data dialog.

Peltier Tech Chart Utility

Make Technical Dot Plots in Excel

What’s a “Technical” Dot Plot?

This chart is crisp and well suited for publication in a technical journal, hence the name I’ve given it, “Technical” Dot Plot. The chart below show results of a hypothetical clinical trial, where the X values (categories) are three different cleansing approaches, and the Y values are the individual responses, where a lower value indicates fewer incidences of infection.

Columns of Dots Plot

This “technical” dot plot chart shows each individual response, to give you an idea of the distribution of results. This is more detailed than a simple average, or even a box plot, which simplifies the data distribution into its min, max, median, and quartiles. If desired, each category could have different marker (dot) shapes, sizes, or colors. However, that isn’t necessary.

Other Kinds of Dot Plot

If you Google “Dot Plot”, or search Wikipedia or any other resource, you’ll learn that the phrase “dot plot” can mean many different things. In addition to what I call the “Technical” Dot Plot shown above, there are also “Cleveland” Dot Plots, “Kindergarten” Dot Plots, and Scatter Plots. Technical Dot Plots, Cleveland Dot Plots, and Scatter Plots are all effective means of displaying data. Unfortunately people who display data effectively do not always come up with effective and unique names for their charts.

“Cleveland” Dot Plot

This type of graphic is named for William Cleveland who described them in a 1984 paper with coauthor Robert McGill. They were presented nicely by Naomi Robbins in Dot Plots: A Useful Alternative to Bar Charts.

Dot Plot per William Cleveland

Peltier Tech Charts for Excel can create this type of chart as easily as any built-in Excel chart.

These are often a better alternative to horizontally-oriented line charts, especially since the category labels can be reasonably long and still remain horizontal for improved readability.

Line Chart

“Kindergarten” Dot Plot

I call this the “Kindergarten” Dot Plot, because it feels more like a fingerpainted art project than a serious means of visualizing data. It is built by adding a dot to the chart every time you encounter the given value in a set of numbers. See another value, dip your finger in paint and put another blot on the paper.

Kindergarten Dot Plot

To me, the histogram below is a better representation of the distribution above. The simple bars show the values without distracting me into counting all those stupid little dots.

Histogram

Peltier Tech Charts for Excel can create histograms easily in Excel. Excel’s old Analysis Toolpak used to make column charts that were passed off as histograms, and Excel 2016 for Windows has finally introduced native histograms.

You can represent a histogram as a line chart, below left, often called a “Probability Polygon”. It’s not a terrible representation, though I prefer the histogram.

Often you’ll see a probability polygon shown with markers and without line segments, and it’s also called a dot plot, below right. I find this inferior, because the dots are unconnected and seem to be strewn across the chart.

Probability Polygon and another Dot Plot

Scatter Plot

A Scatter Plot (a/k/a XY Chart, Scatter Chart, etc.) is often called a Dot Plot because dots (markers) are used to indicate individual data points.

Scatter Plot

Make a Technical Dot Plot

I’ll describe a few different ways to create Technical Dot Plots. The end result is the same, but the protocol differs because you may have different data layouts available for use.

Regardless of data layout, we will make a combination chart, using a column chart type to get the nice horizontal axis labels and XY Scatter types to get the dots.

Data Layout A – Multiple X and Y Series

The first data layout has separate X and Y values for each category in the chart. The X and Y ranges for each set of dots may be next to each other:

or the X and Y ranges for each set of dots may be separated from each other:

You also need a small table with the category names and zero values. Select this small table, and insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Copy and select the orange shaded data range, either B2:C12 in the example with the X and Y ranges together or B2:B12 and E2:E12 in the example with the separated X and Y ranges. To select multiple areas, select the first area, then hold Ctrl while selecting additional areas.

Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below left).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly reduces steps needed for this in a combination chart like this.

Copy and select the gray shaded data range, either D2:E12 in the example with X and Y ranges together or C2:C12 and F2:F12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as new series, values in columns, series name in first row, categories in first column. The result is a set of gray dots added to the chart (below right). Excel remembers that the previous added series was changed to an XY type with markers and no lines on the primary axis, so it uses these settings for the new series. The X values of 2 position the gray dots above the second category along the X axis.

Copy and select the gold shaded data range, either F2:G12 in the example with X and Y ranges together or D2:D12 and G2:G12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as before. The result is a set of gold dots added to the chart (below left). Excel again applies an XY chart type with markers and no lines and assigns the series to the primary axis. The X values of 3 position the gold dots above the third category along the X axis.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready for publication.

Data Layout B – Single X with Multiple Y Series

The second data layout has a single set of X values with three sets of Y values, as shown below. This is easier than the previous data layout, because it requires only one Copy-Paste-Special cycle. Don’t worry about the blank cells; Excel will ignore them in the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as three new column series (below left). The categories are temporarily messed up: the new series have many more points, forcing the three original categories to the left.

Right click on the first added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Change the chart type of the second and third added series from column to XY Scatter, so that there are three sets of colored dots on the secondary axes (below left).

Select the first series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below right).

Format the second and third set of dots so they are also plotted on the primary axis (below left). The X values of 2 and 3 position the dots above the second and third categories on the X axis.

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly streamlines the process to build a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready to go.

Data Layout C – Single X and Y Series

The third data layout has a single set of X values and a single set of Y values, as shown below. This is the easiest of all, because it requires only one Copy-Paste-Special cycle, and there is only one series to be modified once it’s been added to the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has way more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the  series of orange dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1, 2, and 3 align the points above the first, second, and third category along the horizontal axis (below right).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly simplifies formatting of a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

Your new technical dot plot is good to go.

Peltier Tech Chart Utility

Solve xkcd’s Velociraptor Problem with Excel

Velociraptor

The xkcd Velociraptor Problem #1

In Substitute, we learn of a substitute teacher’s “real-life” math problems, which include the following:

Velociraptor Problem - xkcd Setup

Ha, ha, funny commentary on a frustrated old substitute math teacher. (Disclosure: my wife is an 8th grade math teacher, and frustrated is often descriptive of her job.)

So how far could you get before you become Velociraptor Chow? Rhett Allain, a science writer at Wired Magazine, shows us how to figure this out in Here’s How to Solve the xkcd Velociraptor Problem With Code. Prof. Allain describes a numerical approach to the problem using some physics and a bit of python code. Essentially you have some initial conditions, such as speed and position of predator and prey, and some boundary conditions, such as acceleration and maximum speed of each. You select a suitably short time increment, compute the speed due to acceleration and distance traveled based on speed, and update the conditions. If the dinosaur hasn’t yet caught the primate, you again increment the time, update the conditions, and so on, until the two participants coincide in space and time.

You could use your best 8th grade algebra (or my wife’s) and compute the solution analytically, but that’s more suited to paper and pencil, not to a computer. And the numerical approach can be applied to many phenomena, physical and other (including financial).

An algebraic solution?
An algebraic solution?

We’ll use Excel to carry out these same calculations, both as a set of worksheet formulas and as a VBA routine.

Calvin and Hobbes
In his Calvin and Hobbes comic strip, Bill Watterson has touched on velociraptor-human conflict, not as a physics problem but as a solution to an ecological problem (human overpopulation).

Approach 1. Using Excel Table with Simple Formulas

Using an Excel table allows you to make iterative calculations easily. Once you get the formulas right, you can then extend the calculations by adding rows to the table.

Below is the initial setup of the velociraptor problem. Calculations are compiled in a table in the top left of the worksheet. Parameters for the problem are listed in a range nearby and are used in definitions for Names that make setting up the problem easier (see definitions below). The chart plots positions of the velociraptor and of the human on the Y axis vs. time on the X axis.

Velociraptor Problem - Simple Excel Table - Initial

This screen shot of Excel’s Name Manager dialog shows the Names which were defined to facilitate calculations. Delta_t is the time increment between calculated time points in the table. XstartV and XstartH are the initial positions of the velociraptor and of the human. AccelV and AccelH are the acceleration for the velociraptor and for the human. VmaxV and VmaxH are the maximum running speeds for the velociraptor and for the human.

Velociraptor Problem - Defined Names

The table contains these column formulas:

Time
=IF(ROW()-ROW(Table1[#Headers])=1,
    0,
    OFFSET([@Time],-1,0)+Delta_t)
Vvel (velocity of the velociraptor)
=IF([@Time]=0,
    0,
    IF(OFFSET([@Vvel],-1,0)>=VmaxV,
       VmaxV,
       MIN(OFFSET([@Vvel],-1,0)+AccelV*Delta_t,VmaxV)))
Xvel (position of the velociraptor)
=IF([@Time]=0,
    XstartV,
    OFFSET([@Xvel],-1,0)+[@Vvel]*Delta_t)
Vhum (velocity of the human)
=IF([@Time]=0,
    0,
    IF(OFFSET([@Vhum],-1,0)>=VmaxH,
       VmaxH,
       MIN(OFFSET([@Vhum],-1,0)+AccelH*Delta_t,VmaxH)))
Xhum (position of the human)
=IF([@Time]=0,
    XstartH,
    OFFSET([@Xhum],-1,0)+[@Vhum]*Delta_t)

This screenshot shows the table populated to an elapsed time of 2 seconds. The human has just reached his maximum velocity, but the velociraptor is still accelerating. The dinosaur has gotten slightly closer to the human.

You can extend the chase by extending the table, simply by clicking on the small angle-iron at the bottom right corner of the tab and dragging it down as far as needed.

Velociraptor Problem - Simple Excel Table - In Progress

Below, the table has been filled down to 6.5 seconds (the rows between 1 sec and 5 sec have been hidden). We see from the chart that the velociraptor has overtaken the human, and I’ve indicated with red text the row in the table where the position of the velociraptor has first passed the position of the human (row 62, 5.9 sec).

Velociraptor Problem - Simple Excel Table - End of Chase

This simplistic model doesn’t stop when the velociraptor reaches his dinner. We need to insert some intelligence into the formulas in our table.

Velociraptor

Approach 2. Using Excel Table with More Detailed Formulas

Using a more detailed table allows you to calculate the point of intersection of the paths of the velociraptor and the human, and stop the chase at that time.

Below is the initial setup of the velociraptor problem.

Velociraptor Problem - Detailed Excel Table - Initial

There are a few additional columns:

TTime (adjusted time)
=IF(ROW()-ROW(Table14[#Headers])=1,
    0,
    IF(OFFSET([@TTime],-1,0)<>OFFSET([@Time],-1,0),
       NA(),
       IF([@Xvel]<=[@Xhum],
          [@Time],
          (OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0))/
            (OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0)
            -[@Xhum]+[@Xvel])*Delta_t+OFFSET([@Time],-1,0))))
XXvel (adjusted position of the velociraptor)
=IF([@Time]=[@TTime],
    [@Xvel],
    OFFSET([@Xvel],-1,0)+[@Vvel]*([@TTime]-OFFSET([@TTime],-1,0)))
XXhum (adjusted position of the human)
=IF([@Time]=[@TTime],
    [@Xhum],
    OFFSET([@Xhum],-1,0)+[@Vhum]*([@TTime]-OFFSET([@TTime],-1,0)))

The formula for TTime does a lot of work. It’s equal to zero in the first data row, it fills with #N/A after the velociraptor reaches the human, and if the velociraptor catches the human during the current time increment, it interpolates to find the precise time that this happens (see the red entries in row 61).

Velociraptor Problem - Detailed Excel Table - End of Chase

The chart shows the paths of the predator and prey only up to the point of capture, at 5.8375 sec, or 29.325 m from the human’s initial position.

Velociraptor Problem - Detailed Excel Table - Chart

Velociraptor

Approach 3. Using Excel VBA

You can use Excel VBA to solve this velociraptor problem, and animate the chart which illustrates the chase. Initial and boundary conditions are found in the small table in columns G and H.

Columns A through E are the calculations, as in the first approach, except the results are not calculated by worksheet formulas, but instead are calculated by VBA and output to the table as values. These values are plotted in the larger chart.

The small table in columns J through L show the initial and current (i.e., in the most recently calculated iteration) positions of human and velociraptor. These values are plotted in the smaller chart.

Velociraptor Problem - Excel VBA - Initial

Clicking the Reset Chase button runs the ResetChase procedure (below), which sets conditions back to the start of the chase, by deleting all table rows after the first data row.

Sub ResetChase()
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1)
    Do Until .ListRows.Count = 1
      .ListRows(.ListRows.Count).Delete
    Loop
  End With
  Application.ScreenUpdating = True
End Sub

Clicking the Start Chase button runs the StartChase procedure (below), which starts the chase and runs it until the end.

Sub StartChase()
  Dim tTime0 As Double, tTime1 As Double
  Dim xXhum0 As Double, xXhum1 As Double
  Dim xXvel0 As Double, xXvel1 As Double
  Dim vVhum0 As Double, vVhum1 As Double
  Dim vVvel0 As Double, vVvel1 As Double
  Dim vVmaxH As Double, AccelH As Double
  Dim vVmaxV As Double, AccelV As Double
  Dim DeltaT As Double, DDelTT As Double
  Dim iDelay As Long, iLooper As Long
  Dim ws As Worksheet
  
  ResetChase
  
  ' initialize
  Set ws = ActiveSheet
  tTime0 = 0
  iLooper = ws.Range("Looper").Value2
  xXhum0 = ws.Range("XstartH").Value2
  xXvel0 = ws.Range("XstartV").Value2
  vVhum0 = 0
  vVvel0 = 0
  vVmaxH = ws.Range("VmaxH").Value2
  vVmaxV = ws.Range("VmaxV").Value2
  AccelH = ws.Range("AccelH").Value2
  AccelV = ws.Range("AccelV").Value2
  DeltaT = ws.Range("Delta_t").Value2
  
  ' loop
  Do
    tTime1 = tTime0 + DeltaT
    
    ' calculate human velocity and position
    If vVhum0 >= vVmaxH Then
      vVhum1 = vVmaxH
    Else
      vVhum1 = vVhum0 + AccelH * DeltaT
      If vVhum1 > vVmaxH Then
        vVhum1 = vVmaxH
      End If
    End If
    xXhum1 = xXhum0 + vVhum1 * DeltaT
    
    ' calculate velociraptor velocity and position
    If vVvel0 >= vVmaxV Then
      vVvel1 = vVmaxV
    Else
      vVvel1 = vVvel0 + AccelV * DeltaT
      If vVvel1 > vVmaxV Then
        vVvel1 = vVmaxV
      End If
    End If
    xXvel1 = xXvel0 + vVvel1 * DeltaT
    
    ' has velociraptor caught human?
    If xXvel1 > xXhum1 Then
      DDelTT = DeltaT * (xXhum0 - xXvel0) / _
          ((xXhum0 - xXvel0) + (xXvel1 - xXhum1))
      tTime1 = tTime0 + DDelTT
      xXhum1 = xXhum0 + vVhum1 * DDelTT
      xXvel1 = xXvel0 + vVvel1 * DDelTT
    End If
    
    ' add new time point data to row below table
    With ws.ListObjects(1)
      .ListRows(.ListRows.Count).Range.Offset(1).Value = _
          Array(tTime1, vVvel1, xXvel1, vVhum1, xXhum1)
    End With
    
    ' exit if we're done
    If xXvel1 >= xXhum1 Then
      Exit Do
    End If
    
    ' build in delay if animation runs too quickly on screen
    For iDelay = 1 To iLooper
      DoEvents
    Next
    DoEvents
    
    ' persist previous loop's data
    tTime0 = tTime1
    vVhum0 = vVhum1
    xXhum0 = xXhum1
    vVvel0 = vVvel1
    xXvel0 = xXvel1
  Loop
  
End Sub

As the VBA code runs and data is added to the table, you can watch the chase progress in the two charts. Here is the chase after two seconds.

Velociraptor Problem - Excel VBA - After 2 sec

Here is the chase after four seconds.

Velociraptor Problem - Excel VBA - After 4 sec

And here is the chase at its conclusion.

Velociraptor Problem - Excel VBA - End of Chase

Same result as the table based approaches, except for the added benefit of watching the animation as the VBA calculations proceed.

Velociraptor

VBA: Accuracy vs. Calculation Load

Obviously the accuracy of our numerical solution depends on the size of the time increment we use in our calculations. If we take smaller time increments, we can reduce the error resulting from treating nonlinear behavior (acceleration) as linear. On the other hand, taking smaller time increments means our program has to make more calculations, and therefore it will run more slowly.

I ran a modified StartChase procedure to determine how time increment size affected elapsed time, distance, number of iteractions, total calculation time, and calculation time per iteration. I turned off screen updating during this procedure and did not output the results of each increment to the worksheet, to ignore the time VBA spends communicating with the Excel worksheet.

Here are the effects of increment time on solution accuracy and calculation time. The blue shaded row shows the solution used in the examples above, an increment of 0.1 sec.

Numberical Analysis - Accuracy and Calculation Time

We learn some interesting things if we plot this data.

If we plot computed elapsed time to capture of the human vs time increment (Delta_T), we see a straight line with an almost perfect correlation (below left). The computed elapsed time gets closer and closer to the Y intercept as the time increment decreases. We could say that this Y-intercept is the actual time of capture, and our solutions get closer and closer to predicting it as the error in the incremental calculations is minimized. In fact, the difference between the time increment used above (0.1 sec) and the Y-intercept is only 0.05 sec. Maybe we can decide that our analytical solution using an increment of 0.1 sec is “accurate enough”.

If we plot distance to capture vs time increment (below right), we see a trend, but not a nice linear trend as with the elapsed time chart. However, we see that for time increments of 0.1 sec or shorter, there is barely any deviation from the 0.1 sec computation of 29.325 m.

Numberical Analysis - Accuracy and Calculation Time

Naturally, a smaller time increment will result in more iterations, in an inverse relationship. The chart below left shows a linear scatter chart, which isn’t very interesting; the data for a large increment (0.5 s) is found out on the X axis. Shortening the increment brings the points back along the X axis until they curve around and head up the Y axis. What is happening is shown better in the log-log plot below right. This is a straight line, and the exponent on X is -0.9995, extremely close to the inverse 1/X relationship we expect.

Numberical Analysis - Accuracy and Calculation Time

We would expect a similar inverse relationship between total calculation time and time increment. The linear chart below left shows the same axis-hugging behavior as above, and the log-log chart below right gives us another straight line. This one isn’t as perfectly straight, but a regression on the points for smaller increments (filled points) has a strong correlation and a nearly 1/X relationship.

For the time increment of 0.1 sec from the examples above, we have a total calculation time of 0.05 sec, which is pretty fast for an “accurate enough” solution. If we really want more accuracy, we could even go to a 1 sec solution based on a o.oo2 time increment.

Keep in mind that these calculation times are for a modified procedure, not the procedure that updates the table and chart after each calculation increment. Our 0.1 sec time increment may give the solution in 0.05 sec, but the time we spend watching each increment update the table and chart is more like 4 or 5 seconds.

Numberical Analysis - Accuracy and Calculation Time

Finally, we see that as the number of iterations increases, the calculation time increases linearly.

Numberical Analysis - Accuracy and Calculation Time

 

Velociraptor - Wyoming Dinosaur Center

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.