Peltier Tech Charts for Excel Upgrade – April 2016

Peltier Tech Charts for Excel has been updated to Build 3008 3009, on 11 April 2016 17 April 2016. If you are using an earlier build and you want to update to 3008 3009, send me an email.

Note: A minor bug was added in Build 3008 and an old bug was supposed to be fixed but wasn’t. Build 3009 remedies this and adds a bit of flexibility to box plot source data.

This is not a major upgrade, but it seems like more than a minor one. There are big enhancements to Waterfall and Rotated Waterfall charts, there are new chart resizing and alignment features, and Loess Smoothing can now perform moving quadratic regression.

Here’s what’s new…

Charts

There are no new chart types in this update, but there are major enhancements to some existing charts.

Waterfall Charts

The Waterfall Chart dialog has been changes, with a new “First Bar” setting and updated “Value Labels” settings. These are described below.

New Waterfall Chart Dialog

When selecting data, if you choose to output the chart on a new worksheet, you are allowed to use non-contiguous data ranges. For example, you could select category labels in the range A4:A12 and values in the range C4:C12. If you create the chart on the active sheet, the data still must be in adjacent columns.

When the waterfall chart is created on a new worksheet, the data and calculations are placed in an Excel Table. This makes it easier for you to modify the data, by inserting or removing table rows.

Waterfall Chart Calculations in Table

Placing the output into a Table will also make it easier for me to build versions of Peltier Tech Charts for PowerPoint and Word. I hope to have these available in a few months.

Regardless of the output location of the chart, the two new chart features are available from the dialog and also using the checkbox or listbox next to the output range.

If you choose the First Bar is Subtotal option, the first bar of the data set is treated like any other total or subtotal in the chart, and it has the color used for the full height bars (blue in this default chart):

First Bar Treated as a Subtotal

If you do not choose this option, the first bar is treated like an other changing value, and it has the color of an up br or a down bar (green or red in this default chart):

First Bar Treated as a Changing Value

As before, you can choose to center the value labels on the bars:

Label Option: Labels Centered on Bars

You may also place the labels above the bars:

Label Option: Labels Above Bars

The new option is that you can apply positive labels above the bars and negative labels below the bars:

Label Option: Labels According to Values

Finally, you can hide the labels altogether:

Label Option: No Labels

The Waterfall Chart changes apply to both the Standard and Advanced Editions of Peltier Tech Charts for Excel 3.0.

Rotated Waterfall Charts

Rotated Waterfall Charts have been enhanced in the same way as regular Waterfall Charts.

The new “First Bar” setting and updated “Labels” settings are highlighted in the new Rotated Waterfall Chart dialog:

New Rotated Waterfall Chart Dialog

When selecting data, if you choose to output the chart on a new worksheet, you are allowed to use non-contiguous data ranges. For example, you could select category labels in the range A4:A12 and values in the range C4:C12. If you create the chart on the active sheet, the data still must be in adjacent columns.

When the rotated waterfall chart is created on a new worksheet, the data and calculations are placed in an Excel Table. This makes it easier for you to modify the data, by inserting or removing table rows.

Rotated Waterfall Chart Calculations in Table

Regardless of the output location of the chart, the two new chart features are available from the dialog and also using the checkbox or listbox next to the output range.

If you choose the First Bar is Subtotal option, the first bar of the data set is treated like any other total or subtotal in the chart, and it has the color used for the full height bars (blue in the chart below left). If you do not choose this option, the first bar is treated like an other changing value, and it has the color of an up br or a down bar (green or red in the chart below right):

First Bar Options in Rotated Waterfall Chart

As before, you can choose to center the value labels on the bars (above left chart below), you may place the labels above (to the right of) the bars (above right chart), you can apply positive labels above (right of) the bars and negative labels below (left of) the bars (bottom left chart), or you can hide the labels altogether:

Rotated Waterfall Chart Label Options

The Rotated Waterfall Chart changes apply only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

Formatting

A few features have been added to help you resize and align charts. These can be found in a new dropdown on the Move and Resize Chart Elements button on the Peltier Tech ribbon.

New Chart Resizing Commands

Here are some typical charts of various sizes and positions before using the new features.

Original Charts

Resize Charts makes all of the selected charts the same size as the first selected chart, without moving any of the charts.

Original Charts

Resize and Align Selected Charts makes all selected charts the same size as the first selected chart, and arranges them in a grid with the first selected chart serving as the top left chart in the grid. A small dialog asks how many charts in each row of the grid.

Resized and Aligned Charts

Resize Charts and Margins makes all selected charts the same size as the first selected chart, without moving any charts. The interior plot areas of the charts are all resized so all charts have the same margins around the plot areas. Note that the bottom right chart has a margin for its missing title, and the top right has one for its missing legend; all charts have a wider left margin matching the first chart’s longer axis labels, and the blank right margins all match up.

Original Charts

These chart resizing features apply only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

Loess Smoothing

There is now an option to use a quadratic weighted moving regression as a basis for Loess smoothing.

New Loess Smoothing Dialog showing new Quadratic Moving Regression option

Quadratic regression takes slightly longer to calculate, but it may result in the smoothed line being closer to the middle of the input points. Curvature in the input data is smoothed more closely, especially at the ends of the input data.

This update to the Loess Smoothing feature applies only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

Peltier Tech Chart Utility

Excel Summit South 2016 Summary

Last month I and several of my colleagues descended on New Zealand and Australia for Excel Summit South 2016. This was a series of two-day Summit conferences, one each in Auckland, Sydney, and Melbourne.

The Conference

The first thing I have to say is that our major sponsor PwC was a perfect host. Their facilities were excellent. Our sessions were in conference rooms on about the 20th floor of their towers in each city (the views sometimes making concentration difficult).

I had a chance to present my on my specialties (VBA and, uh, charting), but also to see what my fellow MVPs had to say on topics I should learn more about. I also got a glimpse into financial modeling, which is not something I usually think much about. The attention to detail and aversion to mistakes are things I aspire to, and it was instructive to see these in a new context.

The People

The Experts

Besides myself, the Excel MVPs in attendance were Liam Bastick (from Australia), Ken Puls (Canada), Charles Williams (UK), Bill Jelen (US), Ingeborg Hawighorst (New Zealand), and Zack Barresse (US). Liam talked about avoiding spreadsheet layout errors, problems with Excel’s various lookup functions, and studying sensitivity and running simulations without VBA. Ken and I did a tag-team presentation, where he started by showing how to use Power Query to fix up some ugly data from disparate sources and I showed how to turn his cleaned up data into a flexible dashboard. Ken later did a session on Power Pivot. Bill “Mr. Excel” Jelen did a session on data visualization using conditional formatting, another on pivot tables, and finally he showed some cool Power Map analyses. Ingeborg showed us how powerful are Microsoft’s web and standalone Power BI tools. Zack showed off some little known features of Excel tables, and Zack and I both discussed using VBA to make life easier. Finally Charles presented on making VBA run like the wind.

Other experts included Felienne Hermans (Delft University), Ian Bennett (PwC, Australia), Smila Baliga (PwC, US), and Andrew RevFeim (F1F9 in New Zealand). Felienne described her research on testing spreadsheets. Smila discussed ways organizations can cope with their overabundance of spreadsheets. Ian and Andrew covered modeling standards and best practices.

Ben Rampson and Carlos Otero from the Microsoft Excel product team were at the sessions. The presented information about where Excel is going, with the emphasis on BI and with the new versions running on whatever platform you can name (Windows, Mac, iOS, Android, etc.). Their presence is evidence of Microsoft’s recent opening up to customers and users, sharing their plans and listening to feedback and suggestions.

The Attendees

Attendance was pretty good, increasing in the later dates as word about the sessions spread. The technical level of attendees at this kind of session is usually high, since the audience is self-selected. But at these meetings I felt the people who attended were very good at their jobs and at Excel. They asked challenging questions, and conversations between and after the sessions were stimulating.

I met a number of people in person that I’ve known for years over the internet. Jeff Weir(d), who has posted on Chandoo’s blog and Daily Dose of Excel. Doug Jenkins, who writes about using Excel for Engineering problems at Newton, Excel, Bach. Ed Ferrero, a former Excel MVP who now runs a winery. Probably more as well, and when I remember I’ll add them here.

The Places

One of the great things about this kind of trip is getting to visit new places. I didn’t get to do a lot of sightseeing, so I plan to come back. I’ll see if my wife wants to come along.

Besides the scenery, the cities we visited had nice restaurants. Among noteworthy meals we had outstanding Middle Eastern food in Auckland, an extensive feast in a Brazilian barbecue in Sydney, and delicious Indian food in Melbourne. The coffee in even the simplest shops was far superior than most donut shops in the States, but I guess that’s to be expected. Somehow I only managed to put on a couple pounds.

Tahiti

I got to visit Tahiti, but only for two hours while they refueled the plane. The airport was very small, with one landing strip and a taxiway. On one side of the runway was ocean, on the other were neighborhoods and the small terminal building. I don’t suppose they get more than one or two jet flights a day, and you need to use the portable stairways to board the plane.

It was raining hard, with temperature in the 90s (or as they say, the 30s) and humidity close to 100%.

Airport in tropical Tahiti

Auckland

Our first real stop was in Auckland, New Zealand. It rained for a couple days, which was okay, because Ken and I were stuck getting our joint presentation working.

The view of Auckland Harbor from the PwC tower was awesome.

Auckland Harbor from PwC Tower

The Sky Tower looms over central Auckland. Our hotel was a block or two away, as were numerous restaurants and shops.

Sky Tower looms over Auckland

Sydney

The weather in Sydney was gorgeous. My wife told me I’d better enjoy it, since it was snowing back in Worcester.

On a beautiful Sunday afternoon we took a ferry ride through the harbor. On the way we went under the Harbor Bridge and past the Opera House.

Opera House under the Harbor Bridge in Sydney

Our ferry ride took us to Manly Beach, which was very crowded because of a surfing competition.

Manly Beach, Sydney

I got to sick my feet in the Pacific Ocean. Being from Massachusetts I usually only get to the Atlantic, but they’re equally wet.

Dipping my feet in the Western Pacific

On the ferry ride back we again went past the Opera House.

Opera House, Sydney

This mother and baby humpback were gracefully swimming over a stairwell in Darling Harbor, a small branch of the enormous Sydney Harbor. I was captivated by this intriguing sculpture, showing skeleton inside translucent plastic skin.

Humpback whales swimming in Sydney stairwell

Melbourne

Early in the morning, you can see rowers on the Yarra River, like this single sculler and the half dozen or so in the distance.

Early morning sculler on Yarra River in Melbourne

We took this scenic footbridge across the Yarra from our hotel to the PwC facilities.

Footbridge over Yarra River in Melbourne

During a street festival on Friday night, Zack Barresse met up with a bagpipes-playing Cookie Monster.

Zack with bagpipes-playing Cookie Monster in Melbourne

Zack wrote about our travels in Excel Summit South in Review.

Home Again

All good things have to come to an end, so after two weeks I returned home again (which is also a good thing). On the first day of spring, a week after my return, Mother Nature greeted me with this springtime scene.

Home again to sunny Massachusetts

It was a very mild winter in Massachusetts, especially compared with last year. But since the springtime snow pictured above, we’ve had snow on two more days.

Peltier Tech Chart Utility

Bar-Line (XY) Combination Chart in Excel

Combination charts combine data using more than one chart type, for example columns and a line. Building a combination chart in Excel is usually pretty easy. But if one series type is horizontal bars, then combining this with another type can be tricky. I’m here to help with Bar-Line, or rather, Bar-XY combination charts in Excel.

Combination Charts in Excel

I’ll illustrate a simple combination chart with this simple data. The chart will use the first column for horizontal axis category labels, the second column for actual values plotted using lines with markers, and the third column using columns (vertical bars).

Data for our combination chart

We start by selecting the data and inserting a column chart.

Column chart

We finish by right clicking on the “Actual” data, choosing Change Series Chart Type from the pop-up menu, and selecting the new chart type we want. I’ve also used a lighter shade of orange for the columns, to make the markers stand out better.

Column-line combination chart

Let’s do the same for a bar chart. Select the data, insert a bar chart.

Bar chart

Okay, the category labels are along the vertical axis, but we’ll continue by changing the Actual data to a line chart series. That didn’t work out at all. The markers are not positioned vertically along the centers of the horizontal bars, nor horizontally where the data lies in the Actual column of the worksheet.

Bar-line combination chart

In the chart below I’ve shown all axis scales and axis titles to illustrate the problem. When we converted the Actual series to a line type, Excel assigned it to the secondary axis, and we have no ability to reassign it to the primary axis. The primary axes used for the bar chart are not aligned with the secondary axes used for the line chart: the X axis for the bars is vertical and the X axis for the line is horizontal; the Y axis for the bars is horizontal and the Y axis for the line is vertical.

Problem with bar-line combination chart

We can’t use a line chart at all. If we want to line up the markers horizontally with their proper position along the lengths of the bars, we need to use the Actual data as the X values of an XY series. We will need to generate some additional data for the Y values of the XY series.

Bar-XY Combination Chart

We will not try to make a Bar-Line combination chart, because the Line chart type does not position the markers where we want them. We will make a Bar-XY chart type, using an XY chart type (a/k/a Scatter chart type) to position markers.

Here is the new data needed for our Bar-XY combination chart. The factor labels and Target values will be used by the Bar chart series, and the Actual values and Heights for the XY series. Don’t worry about the Height values: I’ll show how they are derived in a moment. The nice thing is that we can use dummy values now and type in the proper values later and the chart will update.

Modified data for our combination chart

Select the first two columns of the data and insert a bar chart.

Bar chart

Since we probably want the categories listed in the same order as in the worksheet, let’s select the vertical axis (which in a bar chart is the X axis) and press Ctrl+1, the shortcut that opens the Format dialog or task pane for the selected object in Excel. Check the box for Categories in Reverse Order and also select Horizontal Axis Crosses at Maximum Category to move it next to Factor 5.

Bar chart with categories in reverse order

I’ve also recolored the bars orange, because blue markers show up better against light orange than orange markers against light blue.

Now copy the last two columns (Actual and Height), select the chart, and on the Home tab of the ribbon, click the Paste dropdown arrow, choose the options in this dialog (add cells as new series, values in columns, series names in first row, categories in first column), and click OK.

Paste special dialog

The data is added as another set of bars, which I’ve colored blue, but we’ll change that in a second.

Bar chart with data pasted special

Right-click on the added series, select Change Series Chart Type from the pop-up menu, and select XY with markers and lines.

Bar-XY combination chart

We see that the horizontal positions of the markers is just what we want to show.

Now we can see where the values in the Heights column comes from. The right hand vertical axis is used for the Y values of the XY series. Looking at the positions of the horizontal bars and the markers in their correct positions, we can see that the Factor 1 bar is centered on Y=4.5, the Factor 2 bar is centered on 3.5, etc. If you hadn’t guessed this at the beginning, type these values into your data range, and let the chart update.

Calculation ov Heights (Y values for XY series)

A few minor changes and we’ll be done. First, change the name of the XY series from Heights to Actual. The easiest way is to click on the series, then look at the highlighted ranges in the chart. The X values (C2:C6) are highlighted purple, the Y values (D2:D6) are highlighted blue, and the series name (cell D1) is highlighted red (highlight colors in Excel 2010 and earlier are different, but the concept is the same). Click on the red border of cell D2, and drag the highlighting rectangle to cover cell C2 to change the series name.

Modified data for our combination chart

Click on the red border of cell D2, and drag the highlighting rectangle to cover cell C2 to change the series name.

Modified data for our combination chart

Then, use a lighter shade of orange for the bars, so the blue markers stand out. Finally, hide the right-hand vertical axis: format it so it has no labels and no line color.

And there’s our completed Bar-XY Combination Chart.

Finished bar-XY combination chart

Peltier Tech Chart Utility

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

Peltier Tech Charts for Excel 3.0

 

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