Peltier Tech Chart Utility for Excel – Advanced Edition

Announcing the Peltier Tech Advanced Chart Utility

I’m happy to announce the release of the Advanced Edition of the Peltier Tech Chart Utility on Thursday, 12 June 2014.

Why the 12th?

Am I superstitious? Am I avoiding Friday the 13th?

No!

June 12th is my birthday, and this is my present to myself.

Also, the ten days between now and then should give me almost enough time to ready the utility for release. In the days between now and then I will finish up the last couple of features, perform a little testing, and write about some of the new (and old) features here.

History

In September 2008 I introduced the Peltier Tech Waterfall Chart Utility. This was much more popular than I would have expected, and soon it became my primary activity. From this modest beginning five and a half years ago, I grew the product line into a series of small chart utilities, each having a single function: create a non-native chart in Excel with a click of a button. This set of utilities was popular, but it made addition of new charts and features hard to accomplish. A whole new product just for that little thing?

A bit over a year ago I released the Peltier Tech Chart Utility, which combined most of the smaller individual program into one grand utility. The individual utilities were upgraded (and enhancements are continually being made), and were combined with several useful charting features. Now I have a place for new features, and my customers can get all of the goodies in one simple program.

A month ago I came out with a Mac version of the Standard Utility, with all of the features of the Windows version, and that has been twice as popular as I had expected.

Of course, I had loads of other ideas for my utility, but I didn’t have time to get everything into the final product.  So I released what I had into the Standard Edition of the tool. In the meantime I worked on these additional features.

These additional features are being incorporated into this new Advanced Edition, which includes everything from the Standard Edition and a whole lot more.

Comparison

In the grid below you can see that the Standard Chart Utility has quite a lot of custom charts, including the popular WaterfallCluster-Stack, and Box and Whisker charts. The Advanced Edition includes a few more variations on waterfall charts, a Diverging Stacked Bar Chart useful for plotting survey results, Histograms (two types) and Pareto Diagrams, and Cycle Plots.

Comparison of Custom Charts in Standard and Advanced Editions of the Peltier Tech Chart Utility

The following grid shows the extensive lineup of features of the Standard and Advanced Chart Utilities. In addition to functions that easily edit the series formula, handle labeling of series so the legend isn’t needed, opening the folder in which the active sheet is stored, and exporting a chart as an image file, the Advanced Edition can also export a range as an image file, export one or more charts into PowerPoint or Word in several formats, switch various X and Y aspects of a chart (data, axis scales, and/or axis titles).

Comparison of Charting Functions in Standard and Advanced Editions of the Peltier Tech Chart Utility

In addition, new charts and features under development will be added to the Advanced Chart Utility, and anyone with a valid license can simply email me to request a link to the update.

Macintosh Version

Is there an Advanced Chart Utility for the Mac?

No.

Will there be an Advanced Chart Utility for the Mac?

Of course. It will take a little time to develop, because Mac and Microsoft do not exactly spell compatibility. But plans are to have it available by the end of June.

Special Deal

The Peltier Tech Advanced Chart Utility will be available on Thursday, 12 June 2014. Anyone who has a valid license for the Standard Edition before this release date can upgrade to the Advanced Edition for free. You simply need to email me asking for a coupon code for a free upgrade between now and 11:59 pm EDT, Friday, 13 June 2014.

The Peltier Tech Advanced Chart Utility will retail for $99 US (the Standard Edition is $79). On Thursday and Friday, 12-13 June 2014, you can get the Advanced Edition for the same $79 price as the Standard Edition. There will be a coupon code for this discount right next to the Add to Cart button on the Peltier Tech Chart Utility web page.

As always, users of the older versions of the Peltier Tech Utilities can email me to get a coupon code for a 50% discount.

 

Peltier Tech Chart Utility

Link Excel Chart Axis Scale to Values in Cells

Excel offers two ways to scale chart axes. You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales. Wouldn’t it be great to be able to link the axis scale parameters to values or, even better, formulas in the worksheet? This page shows how to use VBA to accomplish this.

If you want a ready-to-use solution, try Tushar Mehta’s AutoChart Manager add-in, available as a free download at http://www.tushar-mehta.com/excel/software/autochart/index.html.

There are a few pieces you need to make this technique work. You need a chart, a set of values for the scaling parameters, and some VBA code to change the axis scales. The code can be either linked to a button, or run from a Worksheet_Change event procedure.

The Chart

The actual mechanics of creating this chart are incidental to the discussion, but we’ll use the following simple data and chart (named “Chart 1″, the default name of the first chart created in a worksheet).

Simple data and scatter chart in Excel

Axis Scale Parameters in the Worksheet

You need a place to put the axis scale parameters. In this example, the range B14:C16 is used to hold primary X and Y axis scale parameters for the embedded chart object named “Chart 1″. This example can be expanded to include secondary axes, or to change other charts as well.

Simple data and scatter chart with range containing axis scale parameters

The cells B14:C16 can contain static values which the user can manually change, or they can contain formulas with your favorite axis scaling algorithms. See how to set up axis-scaling formulas in Calculate Nice Axis Scales in Your Excel Worksheet.

Change Chart Axes with VBA

The parts of Excel’s charting object model needed here are the .MinimumScale.MaximumScale, and .MajorUnit properties of the Axis object (the .MinorUnit property could also be controlled, but I usually do not show minor tick marks). These properties can be set equal to constant values, to VBA variables, or to worksheet range or named range values, as illustrated in this code sample:

    With ActiveChart.Axes(xlValue, xlPrimary)
      .MaximumScale = 6
        ' Constant value
      .MinimumScale = dYmin
        ' VBA variable
      .MajorUnit = ActiveSheet.Range("A1").Value
        ' Worksheet range value
    End With

If you have a Line, Column, or Area chart with a category-type X axis, you can’t use the properties shown above. The maximum and minimum values of a category axis cannot be changed, and you can only adjust .TickLabelSpacing and.TickMarkSpacing. If the X axis is a time-scale axis, you can adjust .MaximumScale.MinimumScale, and .MajorUnit. You should turn on the macro recorder and format an axis manually to make sure you use correct syntax in your procedure. Any chart’s Y axis is a value axis, and this code will work as is.

VBA Procedure to Rescale Chart Axes

Press Alt+F11 to open the VB Editor. In the Project Explorer window, find the workbook to which you want to add code.

Project Explorer window of the VB Editor

Double click on the module to open it. If there is no module, right click anywhere in the workbook’s project tree, choose Insert > Module. Or use Insert menu > Module. Up will pop a blank code module.

Empty code module

If your module does not say Option Explicit at the top, type it in manually. Then go to Tools > Options, and in the Editor tab check the Require Variable Declaration checkbox. This will place Option Explicit at the top of every new module, saving innumerable problems caused by typos. While in the Options dialog, uncheck “Auto Syntax Check”. This will save innumerable warnings about errors you already know about because the editor turns the font of the offending code red.

Tools menu > Options in the VB Editor

You can use a simple procedure that changes the axes on demand. The following changes the scales of the active chart’s axes using the values in B14:C16. Select the chart, then run the code.

Sub ScaleAxes()
  With ActiveChart.Axes(xlCategory, xlPrimary)
    .MaximumScale = ActiveSheet.Range("B14").Value
    .MinimumScale = ActiveSheet.Range("B15").Value
    .MajorUnit = ActiveSheet.Range("B16").Value
  End With
  With ActiveChart.Axes(xlValue, xlPrimary)
    .MaximumScale = ActiveSheet.Range("C14").Value
    .MinimumScale = ActiveSheet.Range("C15").Value
    .MajorUnit = ActiveSheet.Range("C16").Value
  End With
End Sub

You can type all this into the code module, or you can copy it and paste it in.

Code module with sample code

Select the chart and run the code. You can run the code by pressing Alt+F8 to open the Macros dialog, selecting the procedure in the list of macros, and clicking Run.

Macros dialog with ScaleAxes procedure

Or you could assign the code to a button in the worksheet.

Here is the chart after running the code.

Simple data and scatter chart after rescaling axes

Worksheet_Change Event Procedure to Rescale Chart Axes

A more elegant approach is to change the relevant axis when one of the cells within B14:C16 changes. We can use the Worksheet_Change event to handle this.

For an introductory description of events in Microsoft Excel, check out the Events page on Chip Pearson’s web site (http://cpearson.com/excel/Events.aspx).

The Worksheet_Change event procedure fires whenever a cell in the worksheet is changed. To open the code module for a worksheet, right click on a worksheet tab and select View Code from the pop up menu. Or double click on the worksheet object in the Project Explorer window. The code module for the worksheet is opened. Now that we’ve set Require Variable Declaration, note that Option Explicit has appeared automatically atop the module.

Empty worksheet code module

You can write the entire procedure yourself, but it’s easier and more reliable to let the VB Editor start it for you. Click on the left hand dropdown at the top of this module, and select Worksheet from the list of objects.

Worksheet code module - Objects dropdown

This places a stub for the Workbook_SelectionChange event in the module. Ignore this for now.

Click on the right hand dropdown at the top of this module, and select Change from the list of events.

Worksheet code module - Events dropdown

You now have a couple event procedure stubs.

Worksheet code module with event procedure stubs

Delete the Worksheet_SelectionChange stub, which we will not be needing, and type or paste the Worksheet_Change code into the Worksheet_Change stub.

Worksheet code module with Worksheet_Change event procedure

The code is given below, so you can copy it. When the event fires, it starts the procedure, passing in Target, which is the range that has changed. The procedure uses Select Case to determine which cell was changed, then changes the appropriate scale parameter of the appropriate axis.

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ChartObjects("Chart 1").Chart
    Select Case Target.Address
      Case "$B$14"
        .Axes(xlCategory).MaximumScale = Target.Value
      Case "$B$15"
        .Axes(xlCategory).MinimumScale = Target.Value
      Case "$B$16"
        .Axes(xlCategory).MajorUnit = Target.Value
      Case "$C$14"
        .Axes(xlValue).MaximumScale = Target.Value
      Case "$C$15"
        .Axes(xlValue).MinimumScale = Target.Value
      Case "$C$16"
        .Axes(xlValue).MajorUnit = Target.Value
    End Select
  End With
End Sub

Peltier Tech Chart Utility

Peltier Tech Chart Utility for the Mac

After weeks and months of development, Peltier Tech is pleased to announce the Mac version of the Chart Utility.

All of the chart types and all of the other functions and features of the Windows version of the utility are present on the Mac.

There are a few small differences, mostly in the behavior of the user interface. The only significant difference is in the ribbon and menu interface. The Windows version of the utility has a modern detailed ribbon tab with descriptive buttons. Excel for the Mac does not (yet) support customized ribbons, so the utility relies on an old-school menu.

Menu for Peltier Tech Chart Utility for the Mac

Everything else about the Mac utility is the same as the Windows version. The guarantee, the price, the discounts for multiple licenses and for existing users, and the outstanding customer support.

The Mac utility has not been tested as extensively as the Windows version. If you encounter any strange behavior, please let us know. When reporting a bug, describe the data you were using, what you thought would happen, and what did happen. It would help if you provide the specific data that caused the problem.

If you’re interested in the Mac or Windows version of the Peltier Tech Chart Utility, please follow this link:

Peltier Tech Chart Utility

 

Peltier Tech Chart Utility

Colors Aren’t Needed to Make Data Easier to Read

In The Right Colors Make Data Easier To Read (Harvard Business Review), Sharon Lin and Jeffrey Heer report that using “semantically resonant colors” make charts easier to read. They offer this before and after figure to support their claim, where default colors are shown at left, and semantically resonant colors shown at right.

illustration of semantically resonant colors

See what they did there? Apples are green (except of course when they’re red), bananas are yellow (except for the green and brown ones in my kitchen), blueberries are blue, etc. Joking aside, this makes intuitive sense at first.

However, I didn’t think the changed colors made the second chart appreciably easier to read. Part of the problem is that the identification of the colored bars requires repeated eye jumps from the chart to the legend, and the seven colors in this chart use up the three to five to seven chunks of short term memory at my disposal.

I usually advise people to replace the chart’s legend with labels closer to the data points. In this case, category labels right on the axis work well. I’ve also rotated the chart to make use of easy to read horizontal labels. The numerical scale also has a greater resolution, since it uses the width of the chart rather than the shorter height. This is another advantage of a rotated chart, given a wider than tall aspect ratio.

No need for colors if proper labeling is used

Note that no colors are required to give the chart meaning.

Unless you need to look up a particular value by name, it is usually beneficial to sort the data by a factor other than alphabetically by name. In this case, I’ve applied a sort by decreasing value. With only seven points, looking up a particular fruit isn’t much of a burden despite not being sorted alphabetically.

Sorting data helps visualize it

Colors or shades of color can be useful to highlight particular values. If I were writing a brochure about the growing tangerine market, I could use a darker shade of my fill color to easily show where tangerines fit among their peers.

Use colors to highlight not identify

I realize the authors were using the simple bar chart to illustrate their point about semantically resonant colors. However, more basic best practices for simple bar charts tell us to ignore these colors.

If I wanted to differentiate different data sets in a line chart, of course, this use of colors would be helpful.

Colors help distinguish data in more complicated displays

Note the use of best practices here. There is no legend, instead, data labels have been applied directly to the data. The labels have also been colored to match the data.

Peltier Tech Chart Utility

Axis Labels That Don’t Block Plotted Data

Someone on Twitter asked me how to make axis labels stay on the side of the axis away from the data. This bar chart illustrates the desired outcome. When bars indicate positive values, the labels are on the negative side of the axis, and vice versa.

Axis labels that do not block the data

The Problem

This data and chart show the behavior of axis labels in Excel. Even though some data is negative, the labels are always on the negative side of the axis, where the labels overlap with the bars. Depending on the series fill color, this could make the labels illegible.

Default axis labels overlap negative data

Lame Attempts

You could play with axis formats to try to make things better. You could format the horizontal axis to make the vertical axis cross at value other than zero; I used -25 below left. This is a terrible solution, though, because now the bars don’t start at the baseline of zero, and even the negative values are plotted with positive direction bars. No good.

You could format the vertical axis labels to make them appear in the Low position, that is, below the lowest values on the horizontal axis, below right. In many cases this is appropriate, but it’s not ideal.

Workarounds to prevent axis labels from obscuring the data

The Solution

The trick is to realize that you can’t make the built-in axis labels do what you want, then think of another way to show the labels. In this case we’ll add a hidden bar chart series, which will use its own data labels.

I’ve added a column with small negative values next to positive plotted values, and small positive values next to negative plotted values.

Data for dummy axis label series

When you add the new data to the chart, the built-in axis labels still overlap with the negative bars (left). The bars are offset, but we can fix this by formatting either of the bars and changing the overlap to 100% (right).

Adding custom axis labels

I hid the built-in axis labels (left). Then I added data labels to the added series (right). The default labels show the plotted values (10 or -10), and I selected the inside base position for the labels.

Adding custom axis labels

The charts below show the four positions for data labels in clustered column and bar charts. Center means in the center of the bars. Inside Base means inside the bar next to the base (bottom) of the bar (next to the axis). Inside End and Outside End mean inside and outside the far end of the bar. Stacked charts can’t have Outside End labels, because these would overlap with any bar stacked on top of the one being labeled.

Data label positions in Excel bar and column charts

I’ve formatted the labels so that they display the category names instead of the values (left) Then I hid the dummy bar chart series by setting its fill to No Fill (right).

Adding custom axis labels

Data labels are not allowed to be as long as axis labels: note that the Omicron label is wrapped onto two lines. For general clarity it’s a good idea to keep your labels short, but here it’s doubly important.

Excel 2013 has finally enabled users to change the size of data labels (but not yet chart and axis titles). However, I’m using Excel 2010 right now, so I don’t have that luxury.

Since the data label size is related to the chart dimensions, you can make the label fit onto one line by stretching the chart.

Axis labels that do not block the data

If your chart size is constrained, you can change the font instead. Below left I’ve changed the Calibri labels from 10 t0 9 points. Below right I’ve changed from Calibri 10 points to Arial 8 points.

Axis labels that do not block the data

For this chart, I think I’ll keep Calibri 9 point labels.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

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

 

Site by: Dawud Miracle, Business Coach & WordPress Websites