Peltier Tech Charts for Excel Upgrade – April 2016

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

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

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

Here’s what’s new…

Charts

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

Waterfall Charts

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

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

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.

Formatting

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

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.

Loess Smoothing

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.