Utilities

JWalk Chart Tools

Wednesday, November 1, 2023 by Jon Peltier 4 Comments

Wednesday, November 1, 2023 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Over twenty years after it was released, Excel users are still searching the internet for JWalk Chart Tools. And when they can’t find it, they ask if anyone knows of a replacement.

Tl;dr – there’s a new version, continue reading to the end to learn about it.

John Walkenbach, aka Mr. Spreadsheet

Two decades ago, John Walkenbach was one of the foremost experts on Excel, leading to his nickname, Mr. Spreadsheet. One of the first Excel MVPs, John had a website filled with helpful lessons and useful examples about Excel. John’s style was down-to-earth; even his VBA code read like a conversation, with variable names using common words and phrases. He wrote a series of books, many of which I own, including the Excel Bible, Excel Formulas, Excel Charts, Power Programming with VBA, and more. Each new release of Excel spurred a new set of John’s must-read books.

John is now retired, lives in a warm climate, and plays guitar with friends. He even gets paid gigs. He says he doesn’t use Excel much anymore and finds himself Googling for answers when he gets stuck. But then, so do I.

What is JWalk Chart Tools?

One of John’s many contributions to the Excel community was a small VBA add-in called JWalk Chart Tools, last released to the Excel community in 2002. This simple toolset filled a few gaps and eased some annoyances around Excel charts. Several of the features of JWalk Chart Tools are no longer needed: either the annoying behavior has been removed from Excel charts, or Microsoft has added the capabilities to Excel. Let’s remember what JWalk Chart Tools did for its users.

Data Labels

You’ve been able to label your series in an Excel chart for almost as long as there have been Excel charts. But the user interface limited you to simple labels, like the category names or the Y values. If you wanted custom labels, you would have to edit the text of each label, creating static labels; for dynamic labels, you could link each label to a worksheet cell.

Building custom labels was tedious, especially because few users knew about linking labels to cells. But JWalk Chart Tools provided a means to easily link series labels to a worksheet range.

Rob Bovey’s XY Chart Labeler, which predates JWalk Chart Tools and is still available, also has this capability to build links one series at a time. Despite its name, the XY Chart Labeler works on all charts that accommodate data labels. Rob also was one of the early Excel MVPs and was one of the authors of Professional Excel Development, a bit dated but still a must-read for heavy users of Excel VBA.

This feature is no longer needed. In Excel 2013, Microsoft introduced a new data label option, Value From Cells. Select a set of data labels in a chart and press Ctrl+1 to format the labels. Select Value From Cells in the Format Data Labels task pane and click Select Range.

Select the applicable data range, and the labels will be linked to the selected cells.

In at least one way, the Microsoft approach is better than the Walkenbach/Bovey linking approach. If part of a series is hidden by hiding the rows or columns of some of its data, the built-in Value From Cells labels don’t display the hidden labels. But the linked formulas are not always hidden. If you have unselected Properties Follow Chart Data Point, the linked labels from the hidden data points will appear on unhidden points in the chart.

Chart Size

It has always been possible to resize your charts, but it was not easy to make your chart precisely the size you wanted. JWalk Chart Tools gave you a way to see the dimensions of your chart, in points (72 points per inch), and to apply a precise size to the active charts or to all charts on the active sheet.

This feature is also no longer needed. Since Excel 2007, the Chart Format tab of the ribbon has had a Size group that includes the height and width of the chart, not in points but in the dimensions of the user’s locale. You can see the size and set a new size right on the ribbon. And if multiple charts are selected, the Shape Format ribbon tab has the same Size group.

Picture

This feature made a static image of your chart, aligned on top of the original chart. You had a choice of a color or grayscale image, in either a picture format (a group of lines, rectangles, and other shapes) or a bitmap (an array of tiny colored dots). This particular feature probably worked well in Excel 2003 but isn’t reliable in Excel 365: when I recently tested it, all pictures were bitmaps regardless of the setting, and no grayscale images were produced.

In Excel 2003 and earlier it was tricky to produce a picture of your chart. The Edit menu only had Copy, which copied the whole chart, data and all. There was no Paste Special if a chart was selected, so no option to paste as a picture. If you held down Shift while selecting the Edit menu, an obscure action that few users were aware of, Copy became Copy Picture, and you had some choices about how to copy the chart. Among the options were Picture vs Bitmap, the same options offered by JWalk Chart Tools. The on-screen vs. as-printed options never made sense to me, as I could never see any difference between them.

Excel 2007 made Copy as Picture slightly more obvious (maybe) by placing it under Home tab > Paste > As Picture. But I think I didn’t find it for a long time, and I’m sure some users never did.

If you did find Copy as Picture, the Copy Picture dialog had fewer options, including only one set of nonsensical on-screen vs. as-printed options.

If you simply copied a chart, you could go to Home tab > Paste > Paste Special and find a number of options for pasting an image of the copied chart. This was a big improvement over Excel 2003. Note that the dialog implies that you could paste a linked picture, but this option has never been enabled in Excel (though it can be enabled in PowerPoint and Word).

You can also select Home > Paste > As Picture > Paste as Picture, and Excel will paste a picture of your chart; it’s pasted directly as a bitmap, without a dialog to select options.

Finally Excel 2010 put Copy as Picture under Home > Copy, where it has a better chance of being discovered by users.

It’s safe to say that this feature is no longer needed.

Export

If you didn’t know VBA, the only way to get an image file of your chart was to take a screenshot (or copy it as a picture, as described above), crop it in an image processing application, and save it. JWalk Chart Tools gave users a way to export one or more charts as image files in a number of formats (GIF, JPEG, TIF, and PNG) into the folder containing the active workbook.

It wasn’t until recently, in Microsoft 365, that Microsoft finally added the capability to save a single chart as an image file. Right click on a chart, go most of the way down a very long context menu, and select Save as Picture. A Save As Picture dialog will open, allowing you to select the format and path of the image file.

Unless you need to export chart pictures en masse (which my commercial charting add-in can do), this feature of JWalk Chart Tools is not needed.

In Excel 2003 and earlier, charts had a special feature that would resize the text within a chart when that chart was resized. This sounds nice, until your carefully formatted chart would be distorted by a seemingly minor change in size. This frustrated many users, but JWalk Chart Tools came to the rescue, turning off this setting for the active chart or for many charts at once.

This feature disappeared in Excel 2007, so it isn’t needed any longer.

Chart Report

JWalk Chart Tools offers two reports for your charts. You can get a simple list of charts in your workbook, by worksheet, which includes the chart type, number of series, position, and size of each chart. Or you can get a more detailed accounting of a single chart, including chart name, position and size, chart type, title, description of axes used in the chart, and details about the chart type and data used in each series in the chart. You can choose to have a small thumbnail of each chart appear in the report.

This I think is the one feature that JWalk Chart Tools users miss when they lose access to the add-in. When I need this kind of report, I generally build a one-time VBA routine, then modify and rerun the routine as I realize I needed to include some other information.

JWalk Chart Tools 2.0

I’ve seen the forum questions about this add-in, and I’ve even received emails asking if I had anything like it, especially the Chart Report feature. But I never had anything I thought was ready for sharing.

Then I found an old version of the utility on an old computer and checked it out. I realized that most of the features were unnecessary, but the Chart Report would definitely be useful to many users.

I contacted John Walkenbach and asked whether I could refresh and redistribute the program, and he gave me his blessing.

I’m releasing the new add-in as JWalk Chart Tools 2.0, and it appears on the Chart Design tab when a chart is selected.

JWalk Chart Tools 2.0 on Excel's Chart Design Ribbon

I’ve removed most of the features, but Report and Export remain. I’ve repurposed John’s existing code, removing obsolete features and making minor changes to ensure that it is compatible with the latest versions of Excel.

Chart Report

Use this feature to generate information about your charts: either a simple listing of your charts or a detailed report about the active chart.

Export Chart(s)

Use this feature to export one or more charts in a single step.

About JWalk Chart Tools 2.0

Go to JWalk Chart Tools 2.0 to get your copy of this helpful Excel add-in.

Posted: Wednesday, November 1st, 2023 under Utilities.
Tags: .
Comments: 4

PBCharts Inflation Analysis

Wednesday, June 22, 2022 by Jon Peltier 2 Comments

Wednesday, June 22, 2022 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Last week we released the PBCharts (Process Behavior Charts) tool for performing SPC control chart analysis. I’ve been working on PBCharts for well over a year, and we had an extended beta testing period, during which we cleaned up numerous issues. I thought I’d walk through an analysis to show how easily PBCharts does its work.

My colleague posted a quick analysis of inflation since the start of 2019, and that looked like a good data set to analyze. The data file CPI YoY Pct.xlsx (click to download) looks like this, showing date and year-over-year percentage increase of the consumer price index:

I’ve described the manual process of generating control charts in Introducing Control Charts (Run Charts), based on Donald Wheeler’s Understanding Variation, an excellent introductory text on this topic. It can be tedious to set up your own control charts, but PBCharts makes it easy to apply the techniques to lots of data.

PBCharts Data

PBCharts uses a specially formatted Excel template for its data, calculations, and charts. A PBCharts workbook has four visible worksheets: Data, Run Chart, MR Chart, and I Chart.

There are a few ways to get the data into the PBCharts file. The PBCharts ribbon presents us with a few options.

Import Data into PBCharts: Click this button, then browse to a data file (CSV, TXT, or Excel) and PBCharts will import data from the selected file into a new PBCharts file.

Analyze Selected Data in PBCharts: Select your data range, or select one cell in the data range, and click this button, and PBCharts will populate a new PBCharts file with the selected data, or if a single cell is selected, with the larger data range that contains the selected cell.

Blank PBCharts File: Click this button to create a new, blank PBCharts file, then paste your data into the Data sheet of the PBCharts file.

The inflation data looks like this in the PBCharts data worksheet:

In general, you will have more than one column of data to analyze. For example, the view below shows the inflation data above, with another column showing a three-month moving average. The cell colors tell you about the data: the gray cells indicate that the cell contains a formula, the red cells indicate that they are blank. The blue header cell tells you which column is currently being analyzed (PBCharts analyzes one column at a time). If you click on a different header cell, PBCharts will focus the analysis on the newly selected column.

Inflation data and more in the PBCharts file.

The PBCharts Ribbon

This isn’t really a manual for PBCharts, just a demonstration, but I’ll show the rest of the ribbon, so my discussion below makes sense. I’ve already shown the section that is used to manage PBCharts files.

The Analysis section of the ribbon includes controls for selecting the column to be analyzed, as well as controls for defining stages, trends, and excluded points, for labeling points, and more.

The Charts section provides a means to override axis scales, to set and show a target value, and to select a run test to apply. You can choose a chart style (bold, medium, or light), you can save charts as text files or export them to PowerPoint or Word, and you can define a dashboard-like layout of multiple charts.

The PBCharts Charts

Run Charts

Click on the Run Chart worksheet, and you see a run chart of the data in the highlighted column of the data sheet. It starts out pretty boring in 2019, then in early 2020 there is a dip, followed by another boring stretch, and starting in early 2021, inflation started ramping upwards.

You can select whether to test the run chart for certain conditions using the dropdown in the Charts section of the ribbon. The run chart above has no test.

The chart below has one test, Runs About Median, selected. The median is displayed as a green horizontal line, and every time the data crosses the median, a label is displayed along the top of the chart.

Our data displays 6 runs about the median. For a data set of this size, we would expect 21 runs about median, or between 17 and 26 with a p-value of 0.05. Since our runs are so low, the data is classified as clustering.

The chart below has another test, Runs Up or Down, selected. This counts each time the points reverse direction.

We would expect 27 runs up or down, or 23 to 31 with p=0.05, for a data set of this size. Instead, we see a much lower number, 14, which indicates trending behavior. Not surprising, given the obvious upward trend over the last third of the chart.

The run chart tests tell us that the data is not uniformly distributed, but we can still use control charts to analyze the behavior.

I (Individuals) and MR (Moving Range) Charts

We turn to the MR (Moving Range) Chart. This shows us how much the data changes from one value to the next and is a measure of the variability in the data. We see immediately that there are two peaks in the MR data, indicating large changes in the data, and possibly process changes.

The green horizontal line shows the mean moving range value, and the red horizontal line shows the upper natural process limit (or upper control limit) on the moving range. The red square points indicate points which are out of statistical control, that is, which violate one of the rules, and the red labels at the top of the chart show which rules have been violated. There are four possible rules for MR charts, shown below, and our analysis is checking two of them, in bold. The two obvious peaks exceed the upper control limit.

Assignable Cause Patterns (Rules) for MR Charts

I’ll show the I (Individuals or individual values) Chart, but it’s not usable yet. The green line shows the mean of the data, and the red lines show the upper and lower natural process limits (control limits). There’s a lot of red, which means that the average and control limits calculated so far do not describe the data well.

The red square markers indicate points which are out of control, and the text above the chart indicate which rules were violated. There are eight I Chart rules built into PBCharts, and we are checking four of them.

Assignable Cause Patterns (Rules) for I Charts

Dividing the Data into Stages

Let’s return to the MR chart and label a couple of points. I’ll label March 2020 “Covid” since it’s when we in the United States started severely limiting our activities due to the Covid pandemic. I’ll label March 2021 “Suez Canal” because that’s the month when the huge cargo ship ran aground in the Suez Canal, blocking all traffic. That event didn’t by itself cause the runaway inflation, but it marked the point when we became aware of how problems with shipping, supply chains, and logistics began to hurt the global economy.

Now back to the I chart. I’ll click the Stages button on the ribbon, and define two new stages starting in April 2020 and April 2021 (the first stage automatically starts at the beginning of the data set).

Next I’ll click the Trends button, and assign a linear trend to the third stage.

The I chart now looks well behaved. There is an initial horizontal section, then an intermediate horizontal section at a lower inflation value, indicative of reduced economic activity. Finally, there is the final increasing section. We still see a few red out-of-control points, three at the beginning of the middle Covid section, and one at the transition between the Covid static range and the final increasing stage.

The MR chart shows no point exceeding the control limit where Covid started, though we still have the over-the-limit point where global shipping became an issue. I’m not going to be concerned with these violations.

We could modify the I chart further, using the Exclusions dialog to exclude the three out-of-control points at the beginning of the Covid stage. Doing so causes three other points out-of-control, so I would probably just leave those points in the analysis.

The important thing to ask now, is whether the inflation rate will keep increasing. The US Treasury has raised its prime rate in March and again in May to try to slow inflation. There is no sign that this is having any effect, since the latest data points fall within the process limits and no other assignable causes are seen. But we can keep watching, add data every month, and follow the trends.

Locking Limits

PBCharts allows us to select a period of time, and lock in the calculated limits. It is common practice, once a process is deemed stable, to lock the limits before plotting subsequent points. This way adding more data will not affect the displayed limits, and the added data is directly compared to the stable process behavior.

We can use the inflation rate data to illustrate locking of calculated process limits. Since the Fed first increased interest rates in March, let’s back up the data to that point, ignoring the last two points. Here is the I chart.

Now I’ll lock the limits in the last stage (April 2021 to March 2022). The dark circles have changed to squares to indicate the points used to calculate the locked limits.

The limits are the same in both charts. Let’s view the final stage side by side.

Let’s add back the data for April and May. The difference in the two charts is subtle: notice that the last two circles are closer to the green line without locked limits (left) than to the green line with locked limits (right). This is because the added points have changed the slopes of the green lines and red limits, rotating the right sides of these lines slightly downward.

Now let’s suppose that the Fed’s interest rate action has had an effect. To simulate this, and I’ll admit it’s a huge assumption on my part, I’ll repeat the last two actual points for April and May as presumed additional points for June and July. The unlocked green and red lines of the left-hand chart below have rotated further, so that the green line is now below the points for April and May, and the imaginary points for June and July are above the lower red line. The locked limits in the right-hand chart haven’t moved. The points for April and May are still below the green line, and the new points for June and July are below the red line and highlighted as out-of-control points.

These out-of-control points are a signal that something in the process has changed. We might presume that increasing interest rates has stopped the climb in the inflation rate.

Get PBCharts

PBCharts is a VBA add-in for Excel. It is limited to Windows, but it runs on any supported versions of Excel, from Excel 2013 through Microsoft 365.

To get your copy of PBCharts, visit the PBCharts website. You can download PBCharts on a 14-day free trial basis, or you can purchase a one-year license for $99.00.

Statistical Process Control Articles in this Blog

Posted: Wednesday, June 22nd, 2022 under Utilities.
Tags: PBCharts, Statistical Process Control, Utilities.
Comments: 2

Jon’s Toolbox – A New Utility from Peltier Tech

Thursday, January 23, 2020 by Jon Peltier 19 Comments

Thursday, January 23, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Jon’s Toolbox is a simple Excel VBA add-in that provides a handful of useful functions. I use it when I am preparing or teaching classes, writing blog posts, and working on projects.

Jon’s Toolbox is not a replacement for Peltier Tech Charts for Excel, nor does it require Peltier Tech Charts for Excel in order to operate properly. The two utilities run independently, and I have both installed on each of my computers.

Click Below to Get Jon’s Toolbox

Jon’s Toolbox is available for $35. Click this button to make your purchase, or scroll down to read about its features.

Check your shopping cart with this button.

What Jon’s Toolbox Does

I’ll do a quick run-through of the menu controls to give you a sense of what Jon’s Toolbox is all about. Peltier Tech Charts for Excel is a large collection of advanced features, for creating custom chart types which are not native to Excel, for manipulating charts, and for controlling data that charts are linked to. Jon’s Toolbox is a self-contained set of functions that help with quickly inserting data and charts, and with quickly formatting charts and worksheets.

Jon’s Toolbox Ribbon Commands

This is a quick look at the custom ribbon tab of Jon’s Toolbox. Click on the image to see it full size in a new browser tab.

The ribbon includes groups of controls, such as Insert Data – Chart – Pivot, Chart and Font Size, Chart Tools, Range Tools, Range and Chart, plus a teaser at the end, From the Blog.

Groups of Controls on the Jon's Toolbox Ribbon

Insert Data – Chart – Pivot

Insert Data – Chart – Pivot has controls to quickly insert data, charts, or a whole data-pivot table-pivot chart ensemble. This is nice when I’m running a demo of my other software, preparing for a class, writing documentation or a blog post. It’s especially nice if I’m running a class and I want to demo a feature of Excel and I want some data for a chart quickly, and I don’t want to make everyone watch me mistype a bunch of numbers.

Data Range

Insert Data Range inserts a handy dummy data set, ready for me to demonstrate Excel’s features, or the features of some software I’ve written. If the active sheet is blank, it puts the data there, otherwise it inserts the data onto a clean worksheet.

Nothing fancy, but it’s a good simple data range for demonstration purposes.

Column Chart, Bar Chart, etc.

There are chart buttons for all of the chart types I routinely use. Yes, it includes pie charts, get over it.

Each of the buttons is a split button. I’ll illustrate the functionality with the Column Chart button.

Column Chart SPlit Button on Insert Tab of Jon's Toolbox Ribbon

If you click on the icon (left), the program creates the chart type shown. If you click on the dropdown (middle), you get a dropdown list with the options for that chart type (right).

When you use one of the options from the dropdown, the split button icon changes to that chart type, which is created next time you click on the icon.

The way a chart is inserted varies, depending on what is selected.

If no data is selected, the program inserts a new worksheet, it inserts the dummy data range (see Data Range above), and it inserts a chart one row below the data range using this data.
If a data range is selected, or if a cell in a block of data is selected, a new chart of the selected type is inserted, one row below the data range.
If a chart is selected, the program inserts a new chart of the selected type, offset slightly from the original chart, using the same data as the previously active chart.

The charts created here are essentially the same as those from Excel’s own Insert tab, the same size and same series formatting, but they follow my own preferences for that chart type.

For example. Excel’s column charts have too much space between clusters, and unneeded space between columns in a cluster. Jon’s Toolbox fixes this.

Comparison Between Column Charts Created by Excel and by Jon's Toolbox.

Excel’s bar charts draw the axis in the opposite order of how the data appears in the worksheet. Jon’s Toolbox fixes this, while also changing to an aspect ratio that better accommodates the chart.

Comparison Between Bar Charts Created by Excel and by Jon's Toolbox.

In addition to being spelled with too many silent letters, Excel’s doughnut charts have too fat a white line surrounding the individual points, the default hole size of 75% is ridiculously large, and the legend is placed below the chart, shrinking the doughnut’s height and leaving wide swaths of white space on either side of the doughnut. Jon’s Toolbox creates a donut chart spelled with no excessive letters; with a thin white outline around the points; with a default hole size of 66% for one ring (one series), 50% for two rings, and 33% for three or more rings; and with its legend on the right side of the chart, allowing the donut to fill more of the space occupied by the chart. In addition, Jon’s chart is closer to square, which means the chart takes up less space.

Comparison Between Do(ugh)nut Charts Created by Excel and by Jon's Toolbox.

Data, Table, and Chart

Data, Table, and Chart inserts a new worksheet. The worksheet contains a Table of data, a Pivot Table constructed using the data in this Table, and a Pivot Chart based on this Pivot Table. Click on the screenshot to view in a new window.

Insert Data Table, Pivot Table, and Pivot Chart

This is a great way to quickly show someone about Tables and Pivot Tables and Charts.

Chart and Font Size

Are the default charts in Excel the right size for your purposes? In the US, the defaults are 5 inches wide and 3 inches tall, and frankly, that’s way larger than most of my charts need to be. Also, that 14-point chart title is much too large.

The Chart and Font Size group of controls provides the following predefined set of alternative sizes for charts and their fonts.

The different chart and font sizes look something like this. Note that they are independently applied to each chart. For my blog posts, I can easily fit two of the small charts, usually with medium font, side by side within a column of text.

Like many of the features of Jon’s Toolbox, if you select multiple charts before clicking the button, the formatting is applied to all selected charts.

Chart Tools

Jon’s Toolbox has a handful of useful chart tools.

The default legend in an Excel chart is pretty widely spaced out (below left). Condense Legend closes it up (below right).

You can easily Reverse Series (and then unreverse them) in a chart, for example in a clustered column chart…

Reversed Series in a Clustered Column Chart

…or in a stacked column chart.

Reversed Series in a Stacked Column Chart

By default, Excel plots a bar chart with the vertical axis in exactly the opposite order as the data appears in the worksheet. This makes sense on some level, but not from the standpoint of usability. Click Flip Bar Chart and fix your bar charts instantly. (Click it again to get the upside-down version back.)

Note that if you use Jon’s Toolbox to insert a bar chart, it is automatically plotted the right way.

The default Excel color palette is not too bad, but if large areas of your chart are filled with the colors, the chart may seem oversaturated (below left). In Excel’s color pickers, the next lighter shade is 40% brighter, which may make the charts washed out. Apply 20% Brightness applies an intermediate shade to the selected series (below right). Note that you have to select and format each series separately.

Finally, the Copy Bitmap button copies the active chart to the clipboard in bitmap format, saving you several clicks (Home tab > Copy dropdown > Copy as Picture > select Appearance and Format). Simply switch to PowerPoint or your image editing software and Paste.

Range Tools

Jon’s Toolbox contains a set of range formatting tools.

(Format as) Chart Data

When you select a chart or a series in a chart, Excel highlights the associated data in the worksheet.

Chart Data applies this formatting more permanently to the worksheet. The colors are the same, it only lacks the small square handles on the corners of the shaded regions. Great for tutorials and illustrations, though.

Click the Chart Data icon, and different things happen depending on what is selected.

If a chart is selected, the data range for the whole chart is formatted.
If a series in a chart is selected, the data range for that series is formatted.
If a range is selected, the range is formatted as if it were used in a chart. If more columns that rows are selected, the range is formatted as if series are plotted by row, otherwise it is formatted as if series are plotted by column.

If you need more control over the formatting, click the Chart Data dropdown.

You can format a range including X and Y values and series names as if plotted by row or by column. You can format a range as if it contains only series names, X values, or Y values.

In addition, you can format a range so it looks like a selected range (below left), as a copied range (below center), or by clicking the two buttons in succession, as a selected range that’s been copied (below right).

Format as Selected, as Copied, or as Selected and Copied

I have found these very useful when writing blog posts, class notes, or documentation for my software.

Borders

Excel has an extensive built-in Borders menu.

And the borders it creates are stark, in-your-face dark black. Sometimes I even want to pluck out my own eyeballs. (My daughter once determined that any statement is funnier if you say “eyeballs” than if you just say “eyes”, and I agree. She’s a scientist working on visual perception using primates.)

Below are Outside Borders, Thick Outside Borders, All Borders, and a combination of All Borders and Thick Outside Borders. Pretty harsh.

Nice Borders applies more visually appealing borders to a selected range. Medium is a medium gray, darker than the default cell boundaries, but not as irritating as black, while light is a light gray similar to the default cell boundary color. “Nice” borders means a medium gray outline with light gray internal boundaries, and the other options format internal borders and outlines, or just outlines, with medium or light gray borders.

These are illustrated below with cell gridlines (Gridlines on the View tab) displayed on the left and turned off on the right.

Medium Borders applies medium gray borders to each cell in the selection.

Medium Outline applies a medium gray border around the outer cell boundaries of the selection.

Light Borders applies light gray borders to each cell in the selection. The light gray is indistinguishable from the default cell gridlines. I use this to put gridlines only on a certain range, while turning off gridlines elsewhere.

Light Outline applies the light gray border around the outer cell boundaries of the selection.

Nice Borders applies a medium gray outline around the selection and light gray borders to cell boundaries within the selection.

If I am making screenshots for documentation, I will often either hide the default gridlines and use Light Borders (first screenshot below), or I will show the gridlines and use Nice Borders (second screenshot below).

Light Grid Formatting With Chart for Blog Screenshot

"Nice" Border Formatting With Chart for Worksheet

When you select one of these Border options, it becomes the new icon and default action for the command.

Clear Formats

Clear All Formats will clear all formats, except for number formatting, from a selected range. This is nice, because sometimes you need to get rid of some ugly border and fill colors, but recreating number formats is a chore.

In the Clear All Formats dropdown, the options are to

Clear border formatting,
Clear border and fill formatting,
Clear all formatting except for number formatting.

Page Breaks

Hide Page Breaks simply removes those unsightly page break dashed lines that make me crazy.

Range and Chart

The Range and Chart group has some controls that help you control how the chart interacts with the worksheet.

Select Data allows you to quickly select a new source data range for the active chart.

Cover with Chart lets you select a range for the active chart to cover. The chart is repositioned and resized to cover the selected range.

Stretch Chart resizes the active chart (or all selected charts) to fill the cells it partially covers.

Chart Before and After Stretching to Fill Range It Covers

Center Chart moves the active chart (or all selected charts) so it is centered within the range of cells it covers.

Chart Before and After Being Centered Within the Range It Covers

From the Blog

Finally, Jon’s Toolbox includes some nice little programs that were previously presented in the Peltier Tech Blog.

These programs were featured in the following blog posts:

Add Totals to Stacked Column Chart (but it works on stacked bar, stacked area, and stacked line charts, too)
Trendline Calculator for Multiple Series
Built In and Custom Lists in Excel

Get Jon’s Toolbox Here

Jon’s Toolbox is available for $35. Click this button to make your purchase.

Check your shopping cart with this button.

Bulk Discount

If you purchase multiple licenses, you qualify for a volume discount, shown in the table below. The discount is automatically applied in the shopping cart when you enter your quantity.

Volume discounts on Peltier Tech software.

Guarantee

Like all of Peltier Tech’s software products, Jon’s Toolbox is guaranteed for 90 days from the date of purchase. If you are unsatisfied for any reason, contact Peltier Tech directly to request a refund. Any feedback is appreciated, but none is necessary.

License

Each user of Jon’s Toolbox requires a license. Each license allows the user to install Jon’s Toolbox on up to two computers of which they are the primary user. If a user gets a new computer, the software can be uninstalled from the old machine and reinstalled onto the new one.

Installation

Jon’s Toolbox is a simple Excel VBA add-in, which can be installed in Excel for Windows or Excel for Mac. Follow instructions at Install an Excel Add-In, and you’ll be working in no time.

Questions?

Contact Peltier Tech with any questions about Jon’s Toolbox.

Posted: Thursday, January 23rd, 2020 under Utilities.
Tags: .
Comments: 19

Trendline Calculator for Multiple Series

Tuesday, February 12, 2019 by Jon Peltier 33 Comments

A couple months back I wrote Add One Trendline for Multiple Series which shows how to add a trendline to a chart, and have the trendline calculated for multiple series in the chart. In fact, that tutorial was based on my answer to a question on Quora, How can I have multiple scatter plots and one trendline for all of them combined in Excel? Some Quora questions can be kind of lame, but this was a good one, especially if I’m getting a second blog post out of it.

Feedback on that tutorial was positive, but it seems that people would like the process to be faster and simpler. Fair enough.

So I decided to write a small add-in that automates the process.

The Manual Process

If you recall, the original problem was that we had three series of data in the chart, and we can easily get a trendline for any or all individual series, but we want a trendline that covers all points in all three series. You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx.

Note: this approach will not work in Line charts; in general you should not use Line charts if you need trendlines for your data.

Here is the original chart from the earlier tutorial:

And here is the chart with a trendline for each individual series:

Multiple XY Series and Trendlines in One Chart

We created a new series in the chart that included all points from the first three series (the yellow markers cover the blue, orange, and green ones):

Multiple XY Series, Including the Combined Series, in One Chart

This was the tedious step, adding all the data to a new series, and this is the part that my add-in will speed through.

Then we hid the new series by formatting it without markers, and added a trendline:

Multiple XY Series with One Combined Trendline in One Chart

The VBA Code

The three original series in the chart have these formulas:

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11,1)
=SERIES(Sheet1!$E$2,Sheet1!$D$3:$D$11,Sheet1!$E$3:$E$11,2)
=SERIES(Sheet1!$G$2,Sheet1!$F$3:$F$11,Sheet1!$G$3:$G$11,3)

Remember, a series formula has four arguments (a bubble chart series has a fifth argument, but we’ll ignore bubble charts here):

=SERIES(Series Name, X Values, Y Values, Plot Order)

We’ll give our added series a new name, “Combined”, and it will automatically be 4th in the plot order. In between we will combine the X values and Y values of the original three series. Our constructed series formula looks like:

=SERIES("Combined",
    (Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11),
    (Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11),
    4)

The multiple X value ranges are enclosed in parentheses, as are the multiple Y value ranges.

What our code will do is count the series in the chart, read each series formula in turn, split out its arguments, and concatenate the separate X and Y values into combined X and Y values. The code will then add the new series, apply the arguments of the series formula, hide the markers, and add a trendline.

Here is the simple procedure:

Sub ComputeMultipleTrendline()
  If Not ActiveChart Is Nothing Then
    With ActiveChart
      Dim ixSeries As Long
      For ixSeries = 1 To .SeriesCollection.Count
        Dim SeriesFormula As String
        SeriesFormula = ActiveChart.SeriesCollection(ixSeries).Formula
        SeriesFormula = Mid$(SeriesFormula, InStr(SeriesFormula, "(") + 1)
        SeriesFormula = Left$(SeriesFormula, Len(SeriesFormula) - 1)

        Dim SeriesArgs As Variant
        SeriesArgs = Split(SeriesFormula, ",")

        Dim XAddress As String, YAddress As String
        XAddress = XAddress & SeriesArgs(LBound(SeriesArgs) + 1) & ","
        YAddress = YAddress & SeriesArgs(LBound(SeriesArgs) + 2) & ","
      Next

      XAddress = "=(" & Left$(XAddress, Len(XAddress) - 1) & ")"
      YAddress = "=(" & Left$(YAddress, Len(YAddress) - 1) & ")"

      With ActiveChart.SeriesCollection.NewSeries
        .Name = "Combined"
        .XValues = XAddress
        .Values = YAddress
        .Format.Line.Visible = False
        .MarkerStyle = xlMarkerStyleNone
        With .Trendlines.Add.Format.Line
          .DashStyle = msoLineSolid
          .ForeColor.ObjectThemeColor = msoThemeColorText1
          .ForeColor.Brightness = 0
        End With
      End With
    End With
  End If
End Sub

If you want to run this code, open the VB Editor (easiest way: use the Alt+F11 shortcut), find your workbook in the Project Explorer, and insert a fresh module (Insert menu > Module, or simply Alt+N+M).

If the new module doesn’t say Option Explicit at the top, type it yourself, then go to the Tools menu > Options, and on the Editor tab of the dialog, check the box labeled Require Variable Declaration, and you may as well uncheck the box for Auto Syntax Check. I discuss why in a decade-old tutorial, VB Editor Settings.

Skip a line after Option Explicit in your brand new code module, then copy the code from above, and paste it into the module.

Before you run the code, select a chart. Then press Alt+F8 to open the Macros dialog. Select ComputeMultipleTrendline and click Run. In the blink of an eye, the new series is added, though it’s not visible, and the trendline appears. I used a solid black line, rather than the default dotted line Excel uses, because I think a solid line makes it easier to see.

It doesn’t matter if all series use the same or different X values; the code doesn’t even compare the X values of the different series, it just puts them all into the series formula.

The Multi Scatter Trendline Calculator

I used the code above as the basis for my add-in. I added a custom ribbon tab named Multi Trendline with a custom button labeled Multi Scatter Trendline to invoke the code. I also designed a dialog so that you can select which series in the chart to include in your analysis (and which to exclude).

Preparing to Install the Add-In

You can download the add-in from this link: MultiScatterTrendlineCalculator.xlam. The add-in is packaged in a zip file. Unzip the file, and store the add-in in the User Add-in Library, which is

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns\

where USERNAME is your Windows login. You can get there quickly by pressing Win+R (Win = Windows key), typing %appdata% in the Run box, and clicking OK, which opens the Roaming directory, and drilling down to Microsoft and then AddIns.

You can actually store the add-in in almost any convenient folder, but when you use the Add-In Library, it’s easy to find the add-in from within Excel when you install it.

Windows protects your computer from malicious software that came from a different computer than yours, but it also protects your computer from useful software that came from my computer, so you need to unblock the add-in. Right click on the add-in file in Windows Explorer, and choose Properties. At the bottom of the General tab of the Properties dialog, there may be a notice that the file may be blocked, and there is a checkbox to unblock the file.

Check the box, and click OK.

Installing the Add-In

If you have the Developer tab showing on Excel’s ribbon, go there and click on Excel Add-Ins (or if it’s an older version of Excel that has no Excel Add-Ins button, click on Add-Ins) to open the Add-Ins dialog.

Otherwise, click on the File tab > Options > Add-Ins. Click the Go button near the bottom of the list to open the Add-Ins dialog.

Or you can use the old Excel 2003 shortcut, Alt+T+I to open the Add-Ins dialog.

If you stored the add-in in the User Library, it will appear in the Add-Ins dialog as MultiScatter Trendline Calculator. Otherwise you will have to click Browse, then navigate to find the add-in.

Check the box in front of this entry, then click OK, and the add-in is installed, available whenever you run Excel.

If you don’t want the add-in installed all the time, you can simply start it when you need it, using File > Open in Excel, double clicking in Windows Explorer, or dragging it from Windows Explorer and dropping it on Excel.

Using the Add-In

Select a chart, then click the button on the custom ribbon tab.

Multi Scatter Trendline Button on the Ribbon

The dialog pops up. Select which series you want to include, and click OK.

Multiple Series Trendline Calculator Dialog

The series is added invisibly, and a trendline is added using Excel’s default settings. You can format this just like any other trendline, to change the fitting model used, to show the trendline formula on the chart, or to change the trendline’s formatting.

Multiple Series Trendline Added to Chart

About the Add-In

I have left the add-in unprotected in case you want to see how it all works. There is XML code that handles the custom ribbon tab and button. There is code in a second module to handle clicks from the ribbon button. A UserForm (i.e., a dialog) has been added to get input from the user. The main procedure is more detailed than shown in this article, to accommodate this dialog, and to compile data selectively.

I enjoy doing this kind of project. Even with the ribbon components and the dialog, it only takes a few hours. If you need something like this done, send me your requirements and I’ll generate a quote.

I liked this little utility so much that I’ve added it to Jon’s Toolbox, which is a nifty set of tools for quickly manipulating charts and data for such purposes as teaching and preparation for publishing.

Change Series Formula – Improved Routines

Monday, March 28, 2011 by Jon Peltier 175 Comments

Ever make a copy of a chart, and all you want is do is change the worksheet containing the source data? Or change the X values of all series from column A to column B? It sure is a pain to edit each series’ source data range of series formula one by one.

The series formula is a simple text string, but there’s no Search and Replace feature in Excel that can access these formulas. But you can use some very simple VBA code to make wholesale changes to chart series formulas. Or you can skip all of the noise, scroll to the end of this article, and download the new Change Series Formula Utility.

About the Chart Series Formula

A more detailed description of a chart and its series formula is presented in The Chart Series Formula and How to Edit Series Formulas elsewhere on this site, but a brief description here is in order.

Every chart series has a formula which describes the data in the series. The simple chart in this example is shown below its data range; the series is selected to highlight the data used to plot the series and to show the SERIES formula in the formula bar.

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

This formula can be broken up into four elements as follows:

=SERIES([Series Name],[X Values],[Y Values],[Plot Order])

In our example:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

Sheet1!$B$1 contains the Series Name

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

Sheet1!$A$2:$A$11 contains the X Values or Category Labels

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

Sheet1!$B$2:$B$11 contains the Y Values

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

and the series is plotted first (1) among the chart’s series collection.

The Series Name can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The X Values can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Plot Order can only be a whole number between 1 and the number of series in the chart.

Editing the Series Formula

The series formula is a text-based Excel formula like any other. You can click in the formula bar, and edit the formula manually, to change aspects of the charted series. Select part or all of the formula, type in the text you want there instead, and press Enter to apply the changes (or sometimes to cause an error!). Alternatively you can select one range address in the formula, then click and drag with the mouse to insert the address of another range in the worksheet.

But if you have to do this for every series in your chart, and every chart on the worksheet, you will be editing in the formula bar all day. So let’s make VBA do some of the tedious work for us.

Editing the Series Formula with VBA

The meat of this technique involves this very simple VBA command:

Series.Formula = WorksheetFunction.Substitute(Series.Formula, _
    OldString, NewString)

Basically, Substitute works by replacing every instance of OldString in the input text string (Series.Formula) by NewString. You could use Replace instead of WorksheetFunction.Substitute, but this would not be compatible with Excel 97.

There are a few tricks I’ve learned to improve the reliability of this technique. First, OldString should be entered using the same case as is found in the formula. For example, “b” will not find any references to column B in the formula. Second, to make sure you don’t trash any worksheet name in the formula, use “$C$” rather than “C” if you are replacing references to column C.

In the simplest version of this utility, you can use a couple input boxes to ask the user for OldString and NewString. A little error trapping makes sure that there is an active chart and that OldString is valid.

Program to Modify the Active Chart

Sub ChangeSeriesFormula()
    ''' Just do active chart
    If ActiveChart Is Nothing Then
        '' There is no active chart
        MsgBox "Please select a chart and try again.", vbExclamation, _
            "No Chart Selected"
        Exit Sub
    End If

    Dim OldString As String, NewString As String, strTemp As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString= InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        '' Loop through all series
        For Each mySrs In ActiveChart.SeriesCollection
            strTemp = WorksheetFunction.Substitute(mySrs.Formula, _
                OldString, NewString)
            mySrs.Formula = strTemp
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

Program to Modify All Charts on the Active Sheet

Adding another loop to iterate through a sheet’s chart objects will change every chart on the sheet.

Sub ChangeSeriesFormulaAllCharts()
    ''' Do all charts in sheet
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString = InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        For Each oChart In ActiveSheet.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
                    OldString, NewString)
            Next
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

Program to Modify All Charts on All Worksheets of the Active Workbook

Adding another loop to iterate through a sheet’s chart objects will change every chart on the sheet.

Sub ChangeSeriesFormulaAllChartsAllSheets()
    ''' Do all charts in all sheets
    Dim oWksht As Worksheet
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString = InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        For Each oWksht In ActiveWorkbook.Worksheets
            For Each oChart In oWksht.ChartObjects
                For Each mySrs In oChart.Chart.SeriesCollection
                    mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
                        OldString, NewString)
                Next
            Next
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

How the program works

Here is our chart again, bet we’ve got an extra column of data. The chart shows column B plotted, but we want to plot column C instead.

Using the second version of the program above, all we need to do is run the code. I usually like to select one chart’s series, to remind me what string I want to change.

The program’s first input box asks for the string we want to replace ($B$ for column B):

The second input box asks for the string we want to replace that string with (“c”):

We see that the program has changed the series formula, while the chart now highlights the new range and shows the new data.

Warning: VBA Error

If the X value argument of a series formula consists of a Name instead of an array or a range address, VBA incorrectly reads the series formula, by enclosing the Name in single quotes. When assigning a formula to a series with a Name as its X value argument, VBA fails whether or not the erroneous single quotes are included. This is apparently a bug in Excel VBA, and it has been reported to Microsoft. The utility below has been upgraded with a workaround to avoid this error.

Essentially the workaround involves checking for a Name in the X values argument of the formula, temporarily removing the argument altogether from the new series formula, then reassigning the Name using the Series.XValues property.

The Change Series Formula Utility

These new algorithms have been incorporated into the Change Series Formula function of Peltier Tech Charts for Excel 4.0.

Posted: Monday, March 28th, 2011 under Utilities.
Tags: SERIES Formula, VBA.
Comments: 175

Microsoft MVP Logo

Utilities

John Walkenbach, aka Mr. Spreadsheet

What is JWalk Chart Tools?

Data Labels

Chart Size

Picture

Export

Text Size

Chart Report

JWalk Chart Tools 2.0

Chart Report

Export Chart(s)

About JWalk Chart Tools 2.0

PBCharts Data

The PBCharts Ribbon

The PBCharts Charts

Run Charts

I (Individuals) and MR (Moving Range) Charts

Dividing the Data into Stages

Locking Limits

Get PBCharts

Further Reading about Statistical Process Control

Statistical Process Control Articles in this Blog

Click Below to Get Jon’s Toolbox

What Jon’s Toolbox Does

Jon’s Toolbox Ribbon Commands

Insert Data – Chart – Pivot

Data Range

Column Chart, Bar Chart, etc.

Data, Table, and Chart

Chart and Font Size

Chart Tools

Range Tools

(Format as) Chart Data

Borders

Clear Formats

Page Breaks

Range and Chart

From the Blog

Get Jon’s Toolbox Here

Bulk Discount

Guarantee

License

Installation

Questions?

The Manual Process

The VBA Code

The Multi Scatter Trendline Calculator

Preparing to Install the Add-In

Installing the Add-In

Using the Add-In

About the Add-In

More Trendline and Regression Articles

About the Chart Series Formula

Editing the Series Formula

Editing the Series Formula with VBA

Program to Modify the Active Chart

Program to Modify All Charts on the Active Sheet

Program to Modify All Charts on All Worksheets of the Active Workbook

How the program works

Warning: VBA Error

The Change Series Formula Utility