The VB Password Prompt is a (usually) rare problem that occurs when you are using a workbook or add-in with a password protected Visual Basic project. When you quit Excel, you are asked for a password to open the VB project.

You may not know the password, so you click Cancel once, twice, seventeen times before the prompt is dismissed.

*Note: Peltier Tech software does have such a password, but users do not need it to run the software.*

This may affect Peltier Tech add-ins, or some other add-in. I’ve written about the VB Password Prompt as part of my documentation for Peltier Tech Charts for Excel.

It is not the fault of the add-in, but of another third-party software application that apparently does not properly release resources when it finishes with them. Windows or Excel or VBA gets confused, decides a given add-in is at fault, and asks for the password so it can open the code and clean up the mess. If you do know the password and enter it at the prompt, your computer checks the project, decides the problem is elsewhere, and proceeds to close Excel.

Debugging the problem is difficult, because it is not consistent across even nominally identical computers. You have to disable any software that could possibly have an effect, and then turn them on one by one until you find the one that causes the problem. Normally the “solution” is either to disable the culprit, or live with the problem.

The kinds of software that may cause a spurious VB password prompt include applications that control printing, either to a physical printer or to pdf files, enterprise document management programs, database applications, and others. Adobe Acrobat has been implicated, as has Acrobat PDFMaker, though this was reported fixed in December 2013. Other third-party programs that have been blamed include ProjectWise, Palo, Hyperion SmartView, Tabs for Excel, Microstrategy, OmniJoin, CapitalIQ, and ManicTime.

A recent culprit in the VB Password Prompt bug is Dropbox. This is disturbing to me because I like Dropbox; among the various file syncing programs, I feel that Dropbox runs most smoothly and reliably. I use Google Drive more extensively than Dropbox, and it seems to work pretty well, with occasional hangs and closes. I used to have problems with Microsoft’s OneDrive, but I’ve heard they’ve made some reliability improvements, so I’ll be trying them out again. Nice to have options.

First reported in September 2015, the problem affects VB workbooks and add-ins that contain at least one UserForm. If the UserForm is displayed during operation of the workbook or add-in, then all open workbooks are closed, and then Excel is closed, the insidious password prompt appears. In some cases, clicking Cancel a number of times lets Excel close properly, but sometimes….

That’s no fun.

If you use Dropbox, there is a workaround. Dropbox includes a feature called Dropbox Badge, which is closely integrated into Microsoft Office, and allows advanced interaction among users working on the same Office files. Sounds good, except this feature seems to be causing the password prompt to appear. It’s easy enough to turn off Dropbox Badge:

- Click on the Dropbox icon in the Taskbar
- Click on the settings gear icon in the top right of the dialog
- Choose Preferences
- Under Dropbox Badge, choose ‘Never show’
- Click OK

Now you can continue using all your favorite Excel workbooks and icons, and let Dropbox sync your files.

The post VB Password Prompt when Closing Excel appeared first on Peltier Tech Blog.

]]>This discussion mostly concerns Excel Line Charts with Date Axis formatting. Date Axis formatting is available for the X axis (the independent variable axis) in Excel’s Line, Area, Column, and Bar charts; for all of these charts except the Bar chart, the X axis is the horizontal axis, but in Bar charts the X axis is the vertical axis. Any of the formatting described here applies to all of these chart types.

XY Scatter charts are different: X axes behave like Y axes. I could write a book just on this subject.

The usual problem here is that data comes from different places. While the data may span a similar range of dates, the different data sets may have varying intervals between recorded values. Perhaps you have daily temperature readings you want to plot against historic monthly temperatures.

I’ve generated the following arbitrary monthly and weekly data for this exercise.

When plotted in separate Excel line charts, this is how it looks.

Start by selecting the monthly data set, and inserting a line chart. Excel has detected the dates and applied a Date Scale, with a spacing of 1 month and base units of 1 month (below left). Select and copy the weekly data set, select the chart, and use Paste Special to add the data to the chart (below right).

To get to Paste Special, on the Home tab, click on the Paste dropdown, select Paste Special, and make sure you’ve selected the settings below:

Excel’s line charts use the same data for all series in the chart, or more precisely, for all series on a particular axis. So let’s assign the weekly data to the secondary axis (below left). Excel only gives us the secondary vertical axis, and we really needed the secondary horizontal axis. Using the “+” skittle floating beside the chart (Excel 2013 and later) or the Axis controls on the ribbon, add the secondary horizontal axis (below right).

Finally format the secondary date axis with the same settings as the primary date axis: Minimum: 1/1/2016, Maximum: 4/1/2016, Major Units: 1 Month, Base Units: Months.

And that looks horrible. With Base Unit of Months, Excel plots everything in a month at one horizontal position, so all weekly values in January are plotted with the monthly value for January 1. So let’s look at these base units.

Among the options for formatting a Date Axis are the units. These include Major Units (major tick mark spacing), Minor Units (minor tick mark spacing), and Base Unit. Base units are the categories that Excel uses to handle the dates in the data. If base unit is Days, then there will be a slot on the axis for each date within the span of the axis; if base unit is Months, then there is one slot per month along the axis; if base unit is Years, well, you get the picture.

I’ve used a darker line for the axes in the two charts below, I’ve formatted the major ticks to cross the axis and the minor ticks to lie outside the axis, and I’ve added faint droplines to the points, all to illustrate this concept.

The chart below left uses Months for its base unit. There is one slot for each month, the slot’s label is centered within the slot, and the slot’s data point is also centered. If there is no data point for a given slot, the line connecting points would have extended across the unpopulated slot. If there are multiple data points for a given slot, such as the four weekly points in each month in our first attempt above, these points will all be aligned with the center of the slot.

The chart below right uses Days for its base unit. Labels appear every month, so they are centered on the slots for the first point of each month. Data points also appear on the first of each month, and the slots for all the other days of the month lie empty, with the series line connecting points across the empty slots.

The points in the first chart are equally spaced horizontally, since the slots for each month are the same width. The points in the second chart are not equally spaced, since the slots for each day are equally spaced (given rounding errors across pixels) and the months have different numbers of slots (days).

You could count minor tick marks to verify this, but I’ve made the following illustration with two copies of this chart. They are identical: I’ve lightened the horizontal gridlines but added vertical gridlines, hidden the plotted data and chart titles, and colored one red and the other blue. I’ve offset the blue chart laterally so its 1/1/2016 gridline is aligned with the red chart’s 2/1/2016 gridline. Note that the next red gridline comes before the next blue gridline: that’s obvious once we note that February’s 29 days make up a shorter month than March’s 31 days. However, the red (4/1/2106) and blue (3/1/2016) gridlines after that line up, since February + March have 29 + 31 = 60 days on the red chart, and January + February have 31 + 29 = 60 days on the blue chart.

What’s really useful is that a 1-month spacing of labels on the line chart lets you put labels on the first of each month, even with months of unequal length.

Let’s start again by plotting the monthly data in a line chart. But let’s set our base unit to Days (below left). As before, copy the weekly data, and use Paste Special to add it as a new series to the char. Only the first four weekly points show up, aligned with the monthly point, because the monthly series only has four points (below right).

To allow display of all points in the weekly series, format it so it appears on the secondary axis. Excel at first only draws the secondary vertical axis (below left). Use the “+” icon floating beside the chart (Excel 2013 and later) or the Axis controls on the ribbon toadd the secondary horizontal axis (below right).

Rescale the secondary horizontal axis so it matches the primary: make sure the minimum and maximum units are the same (below left). Finally, you can do a little clean-up. Delete the secondary vertical axis, and all data will be plotted on the primary scale (which was the same anyway). Hide the secondary horizontal axis by formatting it to use no line and no labels (below right).

This is pretty good, but it’s a bit complicated, and if we have a third data set with different dates, we have no more axes to plot it on.

XY Scatter charts have X axes which are much more flexible, so let’s try one with our data.

Select the monthly data, and insert an XY Scatter chart.

That X axis is cluttered, and what’s up with those axis limits? 12/22/2015 to 4/20/2016? Those aren’t nice round numbers.

Let’s take another look at the data. I’ve duplicated the data in columns A to E in columns G to K, but I’ve formatted the dates as General numbers in columns G and J. Those nice dates that go from 1/1/2016 to 4/1/2016 are actually values that go from 42370 to 42461.

If we reformat our chart’s axis to show General numbers, the scale of 42360 to 42480 looks reasonable (see below). So an XY Scatter chart isn’t as smart as a line chart when picking dates. No matter, will fix it in post.

So let’s revert to dates on the axis.

Copy the weekly data, and use the by now ultrafamiliar Paste Special to add it to the chart (below left). Reformat the horizontal axis so it scales from 1/1 to 4/1/2016, and pick a reasonable major unit (below right).

But look at the tick labels. The reasonable major unit of 30 days gives me funny dates: 1/1, 1/31, 3/1, and 3/31. If I used 31 days instead, I’d get 1/1, 2/1, 3/3, and 4/3. Again, an XY Scatter chart isn’t so smart with dates, despite its flexibility in other ways.

Well, we can hide the axis labels and add a dummy series with data labels that provide the dates we want to see. Here is the data for our dummy series, with X values for the first of each month and Y values of zero so it rests on the bottom of the chart.

Hide the axis labels by using a custom number format of ” ” (a space surrounded by quotes). If we just set the axis to show no labels, the margin below the axis would have collapsed, but using this dummy number format uses a space character for each label, preserving the space for our replacement labels (below left).

Use Copy – Paste Special to add the new axis data to the chart as a new series (below right).

Add data labels below this new series (below left), and format the labels to show X Value, not Y Value (below right).

Format the Axis series so it uses no line and a gray cross marker (below left). Finally do some clean-up. Delete the Axis legend entry (click once to select the legend, again to select the Axis entry, then click Delete). Make the plot area a bit narrower so the date labels are centered under the markers. I deleted the vertical gridlines, because they did not line up with the axis markers (below right).

You could simulate vertical gridlines by adding plus error bars to the Axis series, but we’ve already spent way too much time on this stupid chart.

If you don’t need monthly increments along the X axis, then is makes sense to use an XY Scatter chart for your time series.

A more important case for using an XY Scatter chart for a timeline is when the spacing of points is on the order of hours or less, rather than days. The data below shows four unevenly-spaced points per day over a two day span. The Line chart with a base unit of Days plots all of the points for each day at one horizontal position, while the XY Scatter chart plots the points horizontally according to the time of day.

This was the same problem our first line chart attempt ran into while trying to plot multiple days within a month, when the base unit was Months.

Now for a short trip down Memory Lane.

In Excel 2003 and earlier, you could plot an XY series along a Line chart axis, and it worked really well. The line chart axis gave you the nice axis, and the XY data provided multiple time series without any gyrations.

So the process was, make a line chart from the monthly data (below left). Copy the weekly data and use Paste Special to add it to the chart. Only four points were visible, but be patient (below right).

Change the weekly series to XY Scatter type (below left). Finally, assign the weekly XY series to the primary axis (below right).

This was really nice, because you could get your nice axis even using a dummy/hidden line chart series. Then you could add as many time series as you wanted, with whatever arbitrary and different date values they contained, all using just the primary axis, so you didn’t have to change the scale on one to keep up with another, etc.

This behavior has been broken since 2007, and I suspect it’s gone for good. In fact, this is one reason I was so slow to adopt Excel 2007.

Try the same process in Excel 2007 or later (this is Excel 2016). Chart the monthly data (below left) and add the weekly data (below right).

Convert the weekly data to an XY type (below left), then move it to the primary axis (below right).

Everything was cool until the real maintenance saving step of using the primary axis for everything. But on the primary axis you can only plot as many XY points as there are points in the original line chart series. I guess you could plot a whole blank column as your line chart, and now that I’ve thought of it, maybe that’s what I’ll start doing.

In fact, there is a pretty reasonable and not too convoluted way to get multiple timelines with different date sequences on the same chart. It requires laying out your data differently, and tweaking the chart in the most minor but slightly obscure way.

Start with your monthly data in A1:B5. Add the weekly dates below the monthly dates (A6:A18). Add the weekly values below the monthly values, and one column to the right (C6:C18), with the weekly header in C1. (You can repeat this using more rows and columns for many more series as well.)

Select the data and insert your line chart.

Looks pretty good, except for the gaps. Why are there gaps, anyway? Excel puts a gap between points that have blank cells, but my data had no such gaps.

Well, in a line chart with a date axis, Excel sorts the data behind the scenes before plotting it. So if I sort my data, I can see the gaps in the worksheet range that correspond to gaps in the chart.

This is easy to fix. Select the chart, choose Select Data from the ribbon (or right click the chart and choose Select Data), and click the Hidden and Empty Cells button at the bottom left of the Select Data Source dialog. In the mini dialog that pops up, select the Connect Data Points With Line option for Show Empty Cells. The result is the nice multiple time series chart below right, without any gaps, all on one set of axes, with almost no messing around.

The post Multiple Time Series in an Excel Chart appeared first on Peltier Tech Blog.

]]>This is a trivial case, and probably not what people are asking about. But I’ll cover it just for completeness.

If I have a single block of data, I can select the block of data, or just a single cell within it, and Excel will build a chart using all of the data. The first column (if series are plotted by column) is used for X values, the rest of the columns become the Y values, and the first row is used for series names.

**Select Series Data:** If I somehow have a chart that uses only part of the data, I can right click on the chart and choose Select Data, or I can click Select Data on the ribbon, and the Select Data Source dialog pops up. I can then edit the Chart Data Range, either by manually editing the address, or by selecting a different range, to update the chart.

**Highlighted Chart Data:** But it’s even easier to do without the dialog. If I select the chart, I can see the chart’s data highlighted in the worksheet.

I can click on any of the handles on the corners of the highlighted ranges to stretch the amount of data used in the chart.

Easy peasy, right? I’ve written about this simple yet powerful technique for controlling chart data in Chart Source Data Highlighting, Chart Series Data Highlighting, and Highlighted Chart Source Data.

It’s not as easy to manipulate your chart’s data when the data resides in separate blocks of data, such as this:

You have to start by selecting one of the blocks of data and creating the chart.

**Select Series Data:** Right click the chart and choose Select Data, or click on Select Data in the ribbon, to bring up the Select Data Source dialog. You can’t edit the Chart Data Range to include multiple blocks of data. However, you can add data by clicking the Add button above the list of series (which includes just the first series).

The Select Data Source dialog disappears, while a smaller Edit Series dialog pops up, with spaces for series name, X values, and Y values.

Select ranges for each of these…

… then click OK and the new data appears as a new series in the list.

The chart now has two series. Note that in an XY Scatter chart, each series can have its own X values, independent of the other series in the chart.

Repeat as needed to fully populate the chart.

Not too bad, but I’m not a huge fan of the Select Data Source dialog. It just seems like too much work. And like the expansion of data within a single range that I started this article with, there’s a faster and easier way to add data to a chart from different ranges.

**Copy – Paste Special:** Select and copy the data you want to add to the chart, then select the chart, and from the Home tab of the ribbon, click the Paste dropdown, and select Paste Special. You will be greeted with the Paste Special dialog.

Make sure that the settings in the dialog are correct: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column.

The Replace Existing Categories setting would replace existing X values with those being pasted, which makes little sense for an XY chart that already has X values defined. We’ll talk about this setting when we discuss Line charts. For XY Scatter charts, I never ever check this box.

Click OK and the chart has a new series.

Copy the next range, select the chart, Paste Special.

Again, in an XY Scatter chart, each series can have its own X values, plotted along the same X axis scale, independent of the other series in the chart.

This is pretty easy. It’s even easier to use Paste instead of Paste Special, but sometimes Excel guesses incorrectly on those row/column, first row, first column settings, and you’ll have to undo the Paste and do Paste Special.

I’m using Line charts here, but the behavior of the X axis is the same in Column and Area charts, and in Bar charts, but you have to remember that the Bar chart’s X axis is the vertical axis, and it starts at the bottom and extends upwards.

When your data is in a single block, a Line chart works just like the XY scatter chart. The first column (if the series data is plotted in columns) is used as X values, or more accurately, X labels; the rest of the columns are used as Y values. The first row is used for series names.

When there are multiple blocks of data, Line charts still work mostly the same as XY Scatter charts. Let’s look at this simple data.

Start by creating a Line chart from the first block of data.

**Select Series Data:** Right click the chart and choose Select Data from the pop-up menu, or click Select Data on the ribbon.

As before, click Add, and the Edit Series dialog pops up. There are spaces for series name and Y values.

Fill in entries for series name and Y values, and the chart shows two series. The original X labels remain on the chart.

This dialog differs from the one seen when adding data to an XY Scatter chart, because there is no place for X values (or X labels). To change the X labels, click the Edit button above the list of X labels in the chart. The Axis Labels dialog appears.

The reason for this is that Line charts (plus Column, Area, and Bar charts) treat X values differently than XY Scatter charts. XY Scatter charts treat X values as numerical values, and each series can have its own independent X values. Line charts and their ilk treat X values as non-numeric labels, and all series in the chart use the same X labels.

Change the range in the Axis Labels dialog, and all series in the chart now use the new X labels.

The differences between Line and XY Scatter charts can be confusing. What is important is that the data can be formatted the same (markers or no markers, lines or no lines), while the X values are treated differently (numerical values in XY Scatter charts, non-numeric labels in Line charts).

**Copy – Paste Special:** As in XY Scatter charts, adding data to Line charts can be faster and easier with Copy and Paste Special than with the Select Data dialog.

Check the settings in the dialo: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column. If Replace Existing Categories is unchecked, the original X labels will remain in the chart. Click OK to update the chart.

Although both series are plotted against the original X labels, if we examine the series formulas, we see that the original series formula contains the original X labels range ($B$3:$B$8), while the new series formula references the new range ($E$3:$E$8).

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

` =SERIES(Sheet4!$F$2,Sheet4!$E$3:$E$8,Sheet4!$F$3:$F$8,2)`

The X labels specified in the first series formula is what Excel uses for the chart. If we had selected only the new Y values, ignoring any new X values, and kept Categories in First Column unchecked, both series formulas would reference the same X label range.

Here is what happens when we check Replace Existing Categories.

When we click OK to update the chart, the new X labels appear along the axis. In addition, both series formulas include the new X label range.

This usually isn’t what I want, so I almost never check Replace Existing Categories for any chart type.

The behavior even becomes stranger when we use mismatched data ranges. The second range below has many more rows than the first.

Here is the chart if we paste special with Replace Existing Categories unchecked. Both series use the same X labels, so the axis has enough spaces for the longest series. Since the first labels are being used, these fill the first part of the axis, overlapping excessively, while the rest of the axis remains unlabeled. The first series is pushed to the left of the chart along with the axis labels, since it only uses a fraction of the X axis labels.

Here is the same chart if we paste special with Replace Existing Categories checked. Both series use the new X labels, which fill the entire length of the axis, and they don’t overlap excessively since I wisely used one-character labels. The first series is again pushed to the left of the chart, since it has many fewer points than the second series.

You can assign one series to the primary axis and the other to the secondary axis, and each axis will be long enough for its labels. Below, the first series is plotted on the primary axis (bottom and left edges of the chart), while the second series is plotted on the secondary axis (top and right edges).

It is easier to assign the new series to the secondary axis because I kept the Replace Existing Categories unchecked. This kept the new X label range in the series formula even though the series was initially plotted against the original labels. When I switched the series to the secondary axis, it used the new X labels from the series formula. If I had used Replace Existing Categories, the original categories would have been removed from the original series formula, and I would have had to restore them.

I oversimplified when I stated earlier that all series in a Line (Column, Area, Bar) chart use the same X labels. It’s more accurate to say that all primary axis series in a Line chart use the primary axis labels, while all secondary axis series use the secondary axis labels.

You can try to do a little rescaling of axes to make the chart look better. Here I set the same maximum and minimum values for primary and secondary Y axes. I also formatted the Axis Position to On Tick Marks for both primary and secondary X axes: “One” lines up with “a” and “Six” lines up with “u”, and fortunately there are the right number of categories that each category on the primary scale lines up with a category on the secondary scale (“Two” with “e”, “Three” with “i”, etc.). This alignment was a happy accident.

I hardly ever have a secondary X axis on a line chart, since there is usually no relationship between the two sets of labels, but our eye insists on seeing such a relationship. I’ve written about this confusion caused by Secondary Axes in Charts, even when applied in a well-meaning way.

The post Multiple Series in One Excel Chart appeared first on Peltier Tech Blog.

]]>In case you’ve been stuck in a cave for the past 16 months or so, here in the US we’re in the middle of an interminable presidential election campaign. During a campaign, there are a lot of statements made by the candidates, some truthful, some less so.

An organization known as PolitiFact has been fact-checking politicians’ claims, and various charts of their results have circulated the internet in recent weeks. Most are in the form of stacked bar charts, since those are easier to produce in Excel and in other graphing packages. The one below, for example, was found on Andy Kirk’s Visualizing Data web site, as part of his series The Little of Visualisation Design; he got it from Michael Sandberg’s Data Visualization Blog. Andy noted that had he drawn the chart, he would have “pivoted” the bars so that the positive bars (true statements) extended to the right and the negative bars (lies) to the left.

RJ Andrews claims he made the following variation in 3 minutes in PhotoShop. He didn’t actually create the chart, he simply split the above chart into horizontal strips and offset the strips laterally to align the baseline. It obviously was done in a quick and dirty fashion: the gridlines don’t line up, since they were laterally offset with the adjacent bars. But it shows how such a chart would look.

So how do we go about drawing such a chart?

The data from the above charts is too complicated to show the process clearly, so I’ve dummied up the following data set.

Select the data and insert a 100% stacked bar chart.

Since there were more columns in the data set than rows, Excel didn’t plot the data the way we wanted it. No matter, it’s easy enough to click the Switch Rows and Columns button on the ribbon.

Now let’s format the data. We’ll format the bar colors to match the charts I’ve scavenged from the internet, and apply a gap width of 75% to shrink the white space between stacks. (You only need to apply the gap width to one series, and the rest will use the same value.)

Finally, as usual, Excel laid out the stacks from bottom to top, although the data was listed from top to bottom. This is a common issue, and is actually consistent with charting in general, but it annoys many people. I’ve written about it in at least two posts on this blog, Excel Plotted My Bar Chart Upside-Down and Why Are My Excel Bar Chart Categories Backwards? The solution is an easy two-step process: Format the vertical axis, check the Categories in Reverse Order box, and select Horizontal Axis Crosses at Maximum Category.

When you are exploring a new chart type in Excel, it often takes a few attempts to get it right. I’ll step through these attempts so you can see my thought process and perhaps learn more about Excel’s charting logic at the same time.

Since we want the lies plotted to the left of the baseline, we’ll make those numbers negative, and the vertical axis will become the baseline. Here is how the simple data set changes.

We select the data and insert a 100% stacked bar chart.

Oh yeah, then we switch rows and columns. You’ll get good at that before this tutorial is finished.

Apply the desired colors, and set gap width to 75%.

Finally reverse the order of categories and cross the horizontal axis at the maximum category.

So what’s right with our chart, and what’s wrong? The false statements go to the left and the truths to the right, that’s good. But while the truths are plotted from the axis starting with the least true, the lies are plotted from the axis starting with the most false. We need to reverse the order of the negative data columns, so they are stacked in the order we want them.

Here is the rearranged data.

Select the data, insert a 100% stacked bar chart.

Switch rows and columns, blah, blah.

Apply fill colors and gap width.

Reverse categories and cross the axis at the maximum.

So how are we doing? Well, the bars are all stacked in the appropriate order, that’s good. But the legend has the negative entries in the reverse order. How do we get the bars stacked in the right order, while also listing the legend entries in the right order, since they seem to be working against each other?

In hindsight, the way to get the appearance we want is easy. But when I first worked on this chart type a few years ago, it took me a couple days before I figured it out. What we need are a couple dummy series. The following adjusted data range will help clarify it.

Notice that there are duplicate column headings for False and Mostly False, but the cells contain no values. We will plot these columns with the others, so we will have duplicate series for False and Mostly False. The duplicate series will not appear, since they have no values. Then we will have duplicate legend entries, and we can delete the ones that are out of order.

Here’s another view of the data, showing which series will have visible legend entries and which legend entries will be deleted.

Select the data and insert yet another 100% stacked bar chart.

Switch rows and columns. This should be second nature by now.

I’ve widened the chart so the legend entries appear in a single row. Note the duplicate entries.

Apply the fill colors and gap width as before. Note that both False series have the same formats, as do both Mostly False series. Even if a series doesn’t appear in the chart, you can select it by selecting a visible series and using the arrow keys to cycle through the other series; once the hidden series is selected, you format it just like any other.

Delete the excess legend entries: click once to select the legend, then click again to select the legend entry then click Delete. If you delete the wrong one, simply delete the whole legend and reinstate it. When it appears with all legend entries, delete the ones you don’t want, only more carefully this time.

Finally, do the axis switcheroo: categories in reverse order, cross at maximum.

And that’s just what we wanted.

Here’s the data I manually digitized from the chart I got from Andy Kirk’s chart. This article is a tutorial on the making of a chart with this data, and I will not entertain any discussion on the data itself, its derivation, or its accuracy. There is plenty of that discussion in the various social media.

Select data, insert 100% stacked bar chart. Hey look, with more rows than columns, Excel plotted it the way we wanted, so no need to switch rows and columns.

Apply fill colors and gap width.

Format that vertical axis: Categories in reverse order, horizontal axis crosses at maximum category. While there, specify a label interval of 1; with so many labels, Excel automatically shows only every second label.

Finally, stretch the chart vertically, so it doesn’t look so crowded.

That’s a decent enough stacked bar chart.

Here is the politician data adjusted to make a diverging chart. Note the negative values and columns with blank values and duplicate labels.

Select the data, insert a 100% stacked bar chart.

Apply fill colors and gap width.

Delete the unneeded legend entries.

Reverse the categories on the vertical axis, move the horizontal axis to the maximum category, and show all labels.

Finally, stretch the chart.

And there’s our Diverging Stacked Bar Chart in Excel.

This technique for making diverging stacked bar charts is a bit tricky, especially with the repeated and deleted legend entries. I’ve built this chart type into the Advanced Edition my Excel charting software, Peltier Tech Charts for Excel 3.0, so the extra series are added and the superfluous legend entries removed automatically.

Starting with the stacked bar chart data set, you can click on the Diverging Bars button on the Peltier Tech Advanced ribbon tab. A dialog pops up with a few typical settings.

The color scheme options have been taken from Cynthia Brewer’s Color Brewer. You can select your color scheme, and set the order of the colors (i.e., left to right or right to left), and the program will use as many colors along the scale as you need.

A new worksheet is inserted, with negative values, duplicate series headers, and blank columns as needed, and a chart is generated.

As with any Excel chart, it’s easy to stretch the chart and modify its formats.

Many surveys have a neutral response. A diverging stacked bar chart would plot the neutral responses so that it spans the vertical baseline.

The data set below joins Pants on Fire with False into a new False category, and the Half True becomes a neutral category. The dialog works just the same.

To achieve a neutral bar spanning the vertical axis, the program splits Half True into a negative half and a positive half, both formatted the same using the central color for the selected color scheme.

Again, the chart is easily manipulated.

For more information about this Excel charting add-in, go to Peltier Tech Charts for Excel 3.0. Note that Diverging Stacked Bar Charts are included only in the Advanced Edition of the software.

The post Diverging Stacked Bar Charts appeared first on Peltier Tech Blog.

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

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

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:

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?

The post Copy Chart to New Sheet and Link to Data on New Sheet appeared first on Peltier Tech Blog.

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

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.

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.

This very simple dialog came up.

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

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.

The post Paired Waterfall Chart appeared first on Peltier Tech Blog.

]]>*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…

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

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

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.

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

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

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

You may also place the labels above the bars:

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

Finally, you can hide the labels altogether:

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

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:

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.

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

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:

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

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.

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

Resize Charts makes all of the selected charts the same size as the first selected chart, without moving any of the 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.

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.

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

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

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.

The post Peltier Tech Charts for Excel Upgrade – April 2016 appeared first on Peltier Tech Blog.

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

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.

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.

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.

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

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.

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

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.

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

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.

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

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.

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.

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

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

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

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.

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.

The post Excel Summit South 2016 Summary appeared first on Peltier Tech Blog.

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

We start by selecting the data and inserting a 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.

Let’s do the same for a bar chart. Select the data, insert a 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.

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.

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.

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.

Select the first two columns of the data and insert a 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.

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.

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

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

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.

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.

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

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.

The post Bar-Line (XY) Combination Chart in Excel appeared first on Peltier Tech Blog.

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

- 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.
- The pivot chart has optional field buttons that allow the same filtering capabilities directly in the chart that are available to the pivot table.
- 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.
- Pivot charts are plotted with category labels and series values in columns only. Pivot charts cannot be plotted by row.
- 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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

The post Working with Pivot Charts in Excel appeared first on Peltier Tech Blog.

]]>