Copy Chart to New Sheet and Link to Data on New Sheet

This is a quick post to answer this rather common question from the forums:

I have a worksheet that has data in a particular arrangement and a chart that plots this data. I have another worksheet with the identical data arrangement. How can I copy the chart to the new worksheet and have the chart link to the data on the new worksheet?

Here is my simulation of the problem. Worksheet “Original Data” has a particular data arrangement with a “nice” chart that plots this data.

Original worksheet's data and chart

If I click on a series in the chart and peek at the formula bar, I can see that it plots data from “Original Sheet”. Here are the two series formulas:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Great, just what I expect.

I went to a lot of trouble drawing this chart exactly to the boss’ specifications, and I don’t want to spend another hour and a half duplicating the chart for the data on the worksheet named “New Data”. So I copy the chart, and paste it on “New Data”. But that looks like it plots the numbers from “Original Data”.

New worksheet's data but pasted chart shows original worksheet's data

I can verify this behavior by checking the series formulas in the chart:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Most people don’t expect this behavior (though it does make sense, given the history and nature of charts).

In this simple case it wouldn’t take too long to actually edit the series formulas to make the new chart match the new data. But in real life, the chart contains 37 series, and you need to put the chart onto 47 different worksheets. (It’s not too hard to write some VBA to help you edit series formulas, but that’s not today’s lesson.)

So I’m going to show you an easier way to make sure the chart links to the data on the new sheet.

The original chart links to the data on the original sheet, right? If we make a copy of that original sheet, call it “Original Data (2)”, we can verify that the chart on this copied sheet links to the data on the copied sheet. See, series formulas:

=SERIES('Original Data (2)'!$C$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$C$3:$C$8,1)
=SERIES('Original Data (2)'!$D$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$D$3:$D$8,2)

I didn’t even have to touch the chart. Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter.

Look, the chart now is showing the data we just pasted on top of the copied data:

New worksheet's data and chart that shows new worksheet's data

All we need to do is remove the current “New Data” sheet, and rename this copied sheet to “New Data”.

It’s a small amount of work, but isn’t copying and pasting data a lot easier than futzing with a chart’s series formulas and source data?

 

Peltier Tech Chart Utility

Paired Waterfall Chart

The Paired Waterfall Chart

I came across a new (for me) style or waterfall chart last month, which I call the paired waterfall chart. It is designed to show how an investment fund’s total holdings, comprised of stocks or other vehicles and cash, changes during a time period, based on changes in value of its stocks and on proceeds from the sale of some stocks.

Here is an example of such a chart. The holdings of the fund totaled 1350 in 2015: 1100 in stock plus 250 in cash. The holdings of the same fund in 2016 totaled 1450: 675 in stock and 775 in cash. Stock A didn’t change in value. Stock B appreciated by 50 in value. Proceeds from the sale of some Stock C were 275, while the fund’s total value of Stock C declined by 250; the difference of 25 must have been additional appreciation of Stock C. Sales of some Stock D resulted in proceeds of 250 and corresponding loss in value of 250. Stock E went down 25 in value, while Stock F went up 50.

Paired Waterfall Chart

There is no provision for taking cash and buying more of a stock already in the portfolio or a new stock, so the upper set of floating bars are green only, that is, you can only gain cash. The lower bars can be red or green, but the value of your stock holdings can never drop below zero, because at worst the stock is worth nothing. These constraints make the chart slightly easier to build than a standard waterfall, though the horizontal and slanted lines are trickier than in the standard one.

For want of a better name, I call this the “Paired Waterfall Chart”. In hundreds of images of waterfall charts that Google found for me, none had this same structure. I tried to crowd source the chart’s name on Twitter, but the only suggestions that weren’t lame attempts at humor were “stacked waterfall” and “cascade”, and these refer to other chart types. If you know what this chart is really called, or if you have some examples you can share, please let me know in the comments below this article.

Stacked Waterfall and Cascade Charts

How To…?

Since I’ve never seen this kind of waterfall, and since nobody asked me how to make one, I have not prepared a tutorial. But someone did ask whether my charting software, Peltier Tech Charts for Excel 3.0, could make these charts. The answer was, not yet, but with the thought of perhaps selling a few licenses, I took on the task.

The Advanced Edition of Peltier Tech Charts for Excel 3.0 now includes a Paired Waterfall Chart feature, and that’s what I used to make the chart at the top of this article. The data consists of three columns of data, with labels for the horizontal axis, entries for value of the holdings, and entries for proceeds due to any sales. The top row consists of labels, and the totals in the bottom row are omitted, because the program can add better than you can.

If you are a licensee of the Advanced Edition of Peltier Tech Charts for Excel 3.0, shoot me an email and I’ll send you the link to download the latest build, which includes the Paired Waterfall Chart. If you are a licensee of the Standard Edition, let me know and I’ll send a coupon code so you can upgrade to the Advanced Edition for the difference in price. If you are a licensee of an older Peltier Tech utility, let me know and I’ll send a discount coupon code so you can upgrade. And you should upgrade, because Peltier Tech Charts for Excel 3.0 works in Excel 2016, and the same add-in file works on both Windows and Mac.

Like all of my utility’s charts, it is easy to make the Paired Waterfall Chart. Here I’ve selected the data, clicked the Waterfall dropdown, then clicked the Paired Waterfall Chart button.

Data and Ribbon Button for Paired Waterfall Chart

This very simple dialog came up.

Paired Waterfall Chart Dialog

I clicked OK, and the program generated a new worksheet with a bunch of formulas in a nice Excel Table, and the Paired Waterfall Chart (click on the image to see it full-sized).

Paired Waterfall Chart Output

I’m gradually transitioning as many of my program’s chart output sheets to use Excel Tables, because they make it easier for users to adjust the chart’s data after they’ve already built the chart. Tables will also make it easier when I finally come out with Peltier Tech Charts for PowerPoint and Word. The structured referencing in the tables can be a challenge, but Zack and Kevin’s book about Excel Tables helped a lot.

 

Peltier Tech Chart Utility

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

Peltier Tech Charts for Excel 3.0

 

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