The Peltier Tech Chart Utility

Six weeks ago I promised that it was coming. I thought it was two weeks away, but I kept thinking of ways to improve it, and I kept finding those last little dumb things that needed fixing.

This utility rolls together most of the existing Peltier Tech chart drawing tools, which are being retired, and adds quite a few nice chart formatting and manipulating tools as well. It runs in Excel 2013, for which the old ones were not designed.

Here’s a quick look at the new Peltier Tech ribbon tab (click for a full size view in a new browser tab):

 The utility draws a number of custom charts, including two types of waterfall, plus clustered-stacked bars or columns, Marimekkos, cascade charts, boxplots, dot plots, and XY scatter charts from a variety of data layouts. There are a number of helpful tools, most taken from the pages of this blog: series formula editing, labeling, exporting, and so on. For more details, go to The Peltier Tech Chart Utility, check out the Frequently Asked Questions, or read the documentation, Using the Peltier Tech Utility (PDF).

The list price of this new utility is $79US. Discounts will be applied as before to purchases of multiple licenses. Also, users of the existing utilities will get discounts. If you paid the regular list price of $49US for one utility, your upgrade to the new utility will be priced at $40. If you have licensed more than one of the old utilities, you will receive a larger discount; if your license was older and cost less, your discount will not be as large. Email Peltier Tech to get a discount code to enter at checkout, or make your purchase now and email me afterwards, and I’ll apply a discount to your transaction.

This is the “Standard” version of the new utility: there are a couple more versions in the works. The next version (“Advanced”) has all this, plus another 6 to 8 chart types and several additional tools. The final version (“DeLuxe”) will have 6 to 8 more chart types and more goodies. I can’t pretend to know when these will be available, later this spring and into summer. Don’t wait for them. If you license the standard version now, the upgrades to the Advanced and DeLuxe versions will cost the difference in their respective prices.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

In prior versions of Excel, if you selected a rectangular range with N columns and inserted an XY chart with data in columns, the chart would use the first column for the X values, and the other columns for Y values for N-1 series. If your series didn’t all use the same X values, you had to define each series separately.

While playing with the Insert Chart dialogs, I discovered that Excel 2013 will offer several additional options if the data is arranged a certain way.

I’ll illustrate this coolness with the following data. There is an 8×8 grid of numerical data, with text labels (Greek letter names) in the row above the grid, and text labels (Latin letters) in the column to the left of the grid. Thedata is better suited for a line or bar chart because of the non-numerical categories in the first row and column.

On the Insert tab, click on the XY Chart icon and you’ll see this dropdown.

Click on “More Scatter Charts” at the bottom, and you’ll see this dialog.

There are thumbnails for three options, which will be discussed shortly.

If you mouse over any of the options, you’ll see a preview of how that chart would look. Double clicking on the thumbnail or preview inserts the chart into the worksheet.

The first XY chart option is what you would get in prior versions of Excel. The first column is used for the X values, and the other N-1 columns are the Y values. In this case, the first column contains text labels, which an XY chart cannot accommodate, so it uses the counting numbers 1, 2, 3, etc. instead.

The second option available in this case shows one of the intelligent behaviors. Excel realizes that the first column doesn’t contain numerical values, so it ignores that column, and makes a chart with the number in the rest of the data range. The first numerical column is used for X, and the rest for Y.

I tested this with one, two, and three leading columns of non-numerical data, and Excel ignored them all. Presumably it will ignore any arbitrary number of leading columns of text.

That’s pretty smart. But the next trick, as we say here in Baaston, is “wicked smaat”.

Many times I’ve been asked how to select a range with alternating X and Y values and create an XY chart with one series for each pair of columns. In Quick Chart VBA Examples I presented code that parses such a range and spits out the desired chart. In that tutorial I showed how to process several different arrangements of X and Y, including alternating X and Y columns.

If your usable numerical data is contained in an even number of columns (either standalone, or with some data ignored as above), Excel will give you the option to treat the data as alternating columns of X and Y data. The result is this Wicked Smaat XY Chaat:

When the chart is selected, there is no highlighted data range, because the chart’s data is “too complicated” to display. This is because the series don’t share their X values.

If you select the first series, however, you can see that it uses the first column of the range for X and the second for Y.

And if you select the second series, you can see that it uses the third column of the range for X and the fourth for Y. And so on, for the rest of the series i the chart.

I discovered this intelligent behavior by accident, using some arbitrary data from a totally different example. But knowing about this intelligence, I can design a data range to take advantage of it.

Here is an even number of columns of numerical data, alternating X and Y values.

Here’s my wicked smart XY chart created using this data. The default formatting has markers but no lines.

You can use the Change Chart Type feature to change from a markers-only to a lines-and-markers format.

A lot of people are going to be very happy with this new functionality.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Let’s take a short stroll through Charting in Excel 2013.

These examples will use the following data. 8 rows and 8 columns of numerical values, plus a row of Greek letter names above and a column of Latin letters to the left of this grid.

Plus that all-holy blank cell in the top left, which will help any version of Excel parse the data into values, category labels, and series names.

Select one cell or the whole data range, and click on the Insert tab. Here it’s been shrunk to fit, but click on it and you’ll see it full size in a new browser tab.

Let’s focus on the Charts group within the Insert tab.

Looks pretty much the same as ever, except for the addition of “Recommended Charts”. What’s that look like?

This takes a peek at your data and proposes some chart types that might suit the data. Our data has text labels above and to the left of the data, so we get several variations of stacked and unstacked bar and column charts, with thumbnails of each.

We can click on the All Charts tab to select from the entire Excel chart cuisine.

The default as always is the clustered column chart. We have two choices, plot by rows and plot by columns.

Let’s look at the Line Charts options.

Again, two choices: data in rows and data in columns.

When I saw this dialog for the first time, I happened to move my mouse over one of the thumbnails, and I got a larger preview. Nice touch.

You can also get to the Line Charts dialog from the bottom of the line chart dropdown on the Insert tab of the ribbon.

And here’s my first line chart. The defaults are pretty nice. The gridlines are lighter (they were black in all previous versions of Excel), and the other lines and text are slightly muted. The default colors have changed from Excel 2007 and 2010: they seem a bit richer. I want to look at this chart. The series lines aren’t as thick as Excel 2007 and 2010, so the chart looks less like your first grader drew it with crayons.

The worksheet ranges used in the chart are highlighted, as in previous versions of Excel. But in previous versions the highlights were thin borders around the ranges, and to tell you the truth, I’d been using Excel 97 for several months before I noticed the outlines and realized what they were for. Here the lines are a bit wider, and the ranges themselves are filled with a faint tint of the outline color. The category labels are purple and the values are blue, like in previous Excel versions, but the series names have changed from green to red (though 8% of Excel users may never notice this change).

When the chart is selected, three icons appear to the right of the chart. These are new user interface elements that let you adding and removing chart elements (the plus sign), apply color schemes and gaudy visual effects (the paintbrush), and filter of the chart’s data on the fly (the funnel). The first icon is a nice addition, putting chart element controls right next to the chart instead hiding them on a ribbon tab that’s usually not even visible. The middle icon is an attractive nuisance, too tempting to people who like to sex up their charts. The third icon is way cool, because it lets you show and hide entire series, or data points for given categories, just by clicking on checkboxes.

I’ll tell you all about these icons in an upcoming post.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

In a comment to another post, a reader asked about fixing the series names in his charts. Apparently he has been given dozens of charts, and all the series are named Series1, Series2, etc., the default names you see if names have not been defined. It turns out his series data is in rows, and the series names are in the cells just to the left of the data.

So the data looks something like this:

This is what I call “nice” chart data. It’s all in a single block, not bisected by blank rows or columns, with the series names and category labels in the first row and column, and with the top left cell blank. The top left blank cell helps Excel parse the data range into category labels, series names, and values.

If you select such a data range and insert your chart, Excel automatically figures out the series names and category labels. But someone may have selected the range without including the series names, or perhaps the series names weren’t there at first but were filled in after the chart was created.

So here is the situation:

The category labels, highlighted in purple, are aligned in a row. The value data, highlighted in blue, are plotted in rows, parallel to the category labels. No cells are highlighted to indicate series names. The formula for the first series is shown below the chart. The reference for the series name should be between the open parenthesis and the first comma. Since it’s blank, Excel uses the boring Series1 nomenclature.

We’re going to use the series formula to figure out the series names. For this series, we can extract the arguments from the formula. To review, the arguments are: Series Names, Category Labels (or X Values), Y Values, and Plot Order. We will determine the range for the Y values, find the cell right before this range, and insert its reference into the series formula where we now have a blank.

The VBA Code

The code as scoped out above gets the cell to the left of a row of values, but note that I keep writing “the cell before the values”. We’ll make the code a little smarter than average: it will determine whether the data is in rows or columns, and if it’s in rows, take the cell to the left of this data, but if it’s in columns, take the cell above this data. Good thinking, eh? Burn me twice, shame on me.

Here is the procedure that does all the work. We pass in the series, and it fixes the series formula. The steps taken by the code are:

• get the series formula
• extract the comma separated list of arguments from within the parentheses
• split the CSV list of arguments into an array
• find the range corresponding to the third argument
• find the cell right before this range
• insert the cell’s address into the array of arguments
• reconstruct the formula
• reapply the formula to the series.

In other words:

Sub AssignSeriesName(srs As Series)
  ' assign series names to series in charts
  ' use cell above or to left of series values
  ' for series data by row or by column

  ' parse series formula
  Dim sFmla As String, sArguments As String, vArguments As Variant
  sFmla = srs.Formula
  sArguments = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
  vArguments = Split(sArguments, ",")

  ' get ranges
  Dim rYValues As Range
  Dim rName As Range

  Set rYValues = Range(vArguments(LBound(vArguments) + 2))
  If rYValues.Rows.Count = 1 Then
    ' by row: want cell to left
    Set rName = rYValues.Resize(1, 1).Offset(0, -1)
  ElseIf rYValues.Columns.Count = 1 Then
    ' by column: want cell above
    Set rName = rYValues.Resize(1, 1).Offset(-1, 0)
  Else
    ' dude
  End If

  ' get address
  Dim sNameAddress As String
  sNameAddress = rName.Address(True, True, , True)

  ' reconstruct & apply series formula
  vArguments(LBound(vArguments)) = sNameAddress
  sFmla = "=series(" & Join(vArguments, ",") & ")"
  srs.Formula = sFmla
End Sub

Okay. We can use this in three ways.

1. To select a series and get the series name for just that series:

Sub AssignNameToSelectedSeries()
  Dim srs As Series
  If LCase$(TypeName(Selection)) = "series" Then
    Set srs = Selection
    AssignSeriesName srs
  End If
End Sub

For the next two cases, we’ll use this helper procedure, which accepts a chart, and fixes all the series in the chart:

Sub AssignSeriesNamesToChart(cht As Chart)
  Dim srs As Series
  For Each srs In cht.SeriesCollection
    AssignSeriesName srs
  Next
End Sub

2. To select a chart, and do each series in the chart:

Sub AssignNamesToSeriesInActiveChart()
  If Not ActiveChart Is Nothing Then
    AssignSeriesNamesToChart ActiveChart
  End If
End Sub

3. And finally, to loop through all of the series in all of the charts on the active workbook:

Sub AssignNamesToSeriesInAllCharts()
  Dim chtob As ChartObject
  For Each chtob In ActiveSheet.ChartObjects
    AssignSeriesNamesToChart chtob.Chart
  Next
End Sub

To run your code, select the series you want to name or the chart you want to name, or activate the sheet with the charts you want to name, press Alt+F8 to open the Macro dialog, select the appropriate macro, and click Run.

The Results

Here is our first chart above after running the code. The highlighted ranges include the green outlined range with series names, and the names are also shown in the legend. The series formula shown below the chart now has a reference to the cell with the series name.

Let’s test the code with the following chart, with data plotted in columns and no series names. Before:

And after:

The series names are highlighted in the worksheet and appear in the legend, and the series formula has been updated with a reference to the cell with the label.

First look at Excel 2013

The last two screen shots look a bit different, don’t they? That’s because I did the second half of the experiment in Excel 2013. It looks a little lighter, because the row and column headings have no color and the chart outline isn’t so thick and gray when the chart is selected.

The highlighting of the ranges in the worksheet is more pronounced. The outline for the values are still blue, but the line is thicker, and the range is filled in with a light shade of blue. The outline for the category labels is still purple, but again, the line is thicker and the cells are lightly filled. The series name outline has switched from green to thicker red, and the cells are lightly shaded in 2013. I like this more obvious shading in 2013. Back in about Excel 97, I first noticed the outlining after I’d been using it for months. The thin outlines were not obvious enough until I already knew what I was looking for.

Also, when the chart is selected, three icons appear to the right of the chart. These are new user interface bits that handle adding and removing chart elements (the plus sign), applying color schemes and gaudy visual effects (the paintbrush), and on the fly filtering of the chart’s data (the funnel). The first of these icons is a nice addition, putting the chart elements right next to the chart instead of a mile away on a ribbon tab that’s usually not even visible. The middle icon will be too tempting to people who like flashy but nonsubstantive charts. The third icon is actually pretty cool, because you can show and hide whole series, or data points for given categories, just by checking and unchecking boxes in a dialog.

I’ll describe these new chart icons in an upcoming post. Stay tuned!

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

As a quick response I sent the following chart, which uses a hollow column on the secondary axis to indicate the baseline, and I wrote up the tutorial in Simple Baseline for Excel Column Chart.

In a comment to that post, a reader suggested a horizontal line, rather than a whole box, as the baseline marker. The following protocol describes how to accomplish this

Here’s the data for these two examples.

The first step is to create a clustered column chart.

The second step is to switch rows and columns in the source data orientation if necessary (it was).

Now we’ll clean up the chart a bit. Let’s remove the chart area border, remove the line for the vertical axis, use lighter gray lines for the gridlines and horizontal axis, and remove the tick marks from the horizontal axis.

Change the Baseline series to the XY (Scatter) type, which also moves it to the secondary axis.

Reassign the Baseline XY series to the primary axis.

Apply a consistent set of colors to the Q1 through Q4 series.

Add error bars to the Baseline series. The default is horizontal and vertical error bars of length 1 in the positive and negative directions.

Select and delete the vertical error bars.

Resize the horizontal error bars. Use trial and error, or if you’re good at math:

Error bar total width (2 error bars) = 4 column widths
Total category width = 4 column widths + gap width
Gap width = 150, which means 1.5 column widths
Single error bar width = 4/5.5/2 = 0.364

Format the error bars as desired.

Add data labels. If you select the whole chart and then use the Data Labels command on the ribbon, all series will be labeled in one step. This saves a lot of time over labeling one series at a time.

Change each set of labels from Value to Series Name, and change the Baseline label position to Below.

Finally, if desired, use a darker shade of the series colors for the labels. Change the Baseline marker style to none, and delete the legend

It’s a lot of steps, slightly more involved than the “hollow box” technique of the previous post, but it’s still quick and easy.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.