Utilities

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

Cycle Plots in Excel

Tuesday, June 12, 2018 by Jon Peltier 7 Comments

Tuesday, June 12, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Cycle Plots

When you make a chart in Excel, sometimes plotting all of the series in the same space make it hard to see the behavior of an individual series (below left). A cycle plot separates each series into its own section of the main chart, so it is not obscured by other series (below right).

Regular Chart and Cycle Plot

I first read about cycle plots in Naomi Robbins’ article Introduction to Cycle Plots on the Perceptual Edge web site.

Cycle plots aren’t standard Excel charts, but they’re not too hard to create. Soon after Naomi’s article was published, my colleague and former Excel MVP Charley Kyd wrote How to Create Cycle Plots in Excel to Chart Seasonal Sales Data.

A few years ago I wrote a tutorial, How To Make Cycle Plots Using Excel Charts, which showed a convoluted way of creating these charts, loosely based on Charley’s approach..

The tutorial below describes a technique which I hope is easier to follow and reproduce than the old technique. In addition, following the tutorial you’ll find information about Peltier Tech Charts for Excel, which has its own Cycle Plot features (in the Advanced Edition only).

Creating Cycle Plots in Excel

We’ll start with this simple compact data range for our cycle plot. The categories (X axis labels) are in the first column, highlighted in purple, series names in the first row, highlighted in red, and Y values are in the grid, highlighted blue.

Starting Data for Cycle Plot

We can make line, area, column, or bar charts from this data, and in fact, all of these types are suitable for cycle plots.

Non-Cycle Charts from Starting Data

Cycle Plot Data

To make the cycle plot work, we need to divide the chart into sections, or cycles, or panels. Each series in the chart will be plotted in its own cycle, with its own category labels. We leave the series names in place (again highlighted in red below), and duplicate the category labels (purple) in the first column so they appear once per series. Then we stagger the Y values (blue) for each series so each aligns with its own set of category labels.

Stagger Data to Get Cycles in the Chart

We then expand the data by inserting blank rows before, between, and after each cycle’s data, shown by the yellow shaded rows below. This provides a bit more horizontal separation between the data. It is helpful to put space characters into the first cells of the inserted rows (the darker highlights). When you make a chart from this data, Excel will see the space characters and make them part of the category labels for the chart.

Expand Data to Add Space Between Cycles in the Chart

Below is the data and chart, with the series names, category (X axis) labels, and Y values highlighted in red, purple, and blue as before. Note that each series is plotted in its own region of the chart, with its own duplicate set of X axis labels.

Another View of Expanded Data

Secondary Axis for Labeling

Let’s add some secondary data, which will provide gridlines to separate the cycles (panels). The secondary range is shown below the chart, with the series names from the main chart to be used as secondary axis labels (highlighted in purple), a new series name “secondary” (red), and zero values (blue).

Secondary Data for the Cycle Plot

Copy this new range of data, select the chart, and use Paste Special from the Paste dropdown button on the Home tab of the ribbon. Choose the options shown in the Paste Special dialog below.

Paste Special as New Series

We see a new series, which at first is present just in the bottom left corner of the chart. We’ll have to hide this series (format it with no markers and no lines) and move it to the secondary axis.

Secondary Data Added to Cycle Plot

We didn’t really have to add a new range of data for this secondary series. We could have used the top of the existing data range, as highlighted below. The series has no name in the legend, because it uses a blank cell (highlighted red) as its name, and it doesn’t appear in the chart even though the legend shows a yellow marker and line, because its Y values consist of blank cells (highlighted blue).

We Didn't Need to Add Secondary Data, We Can Use What We Already Have

Right click on the new series if you can see it, or right click on any series in the chart, and select Change Series Chart Type. The dialog lists the series in the chart, along with their chart types (which we do not need to change), and whether the series is plotted on the secondary axes. Check the Secondary box next to the new series, and click OK.

Change Series Chart Type - and Axis Too

The chart doesn’t really change much. The data is plotted where it was, but now there is a new vertical axis. Click the little plus “skittle” (that’s Microsoft’s term, and I wish more people knew it), and click on the right-pointing arrow next to Axes. The checked axes are the default ones when you’ve just moved a series to the secondary axis.

Here Are the Default Secondary Axes Excel Thinks We Want

For our chart, we need to uncheck Secondary Vertical and check Secondary Horizontal. The secondary horizontal axis labels are the original series names, and they are neatly positioned above the plotted data for each series.

Here Are the Secondary Axes Excel We Really Want

Formatting

Now a little formatting. If the secondary axis series is visible in the chart (below left), format the series so it has no markers and no line, to hide it (below right).

A little formatting...

Using the plus skittle, click the arrow next to Gridlines and check the Secondary Vertical box. This gives us lines that separate the data for each series, effectively forming panels (below left).

Note that the white spaces to the left of series alpha and to the right of series gamma are wider than the spaces between series and the interior gridlines. We can fix that by formatting the primary horizontal axis (along the bottom of the chart). Select the axis and press Ctrl+1, Under Axis Options, choose On Tick Marks for Axis Position (below right). The spaces are now uniform.

Finally, remove the legend, which was made redundant by the secondary axis labels at the top of the chart.

Cycle Chart Types

I showed this technique using a line chart, but you can also use a column chart instead.

Cycle Plots with Lines and with Columns

You can also use a bar chart.

Cycle Plot with Bars

You can use an area chart as well. Note that the default area chart plots blank cells as zeros, so the edges of each series are not vertical (below left). To correct this, right click the chart and choose Select Data, click the Hidden and Empty Cells at the bottom left of the dialog, and choose Show Empty Cells As Gaps and click OK twice. The area series now don’t encroach upon each other (below right).

Cycle Plot with Areas

You can even mix up the series in a combination cycle plot, but make sure this won’t complicate the visual or confuse your readers.

Combination Cycle Plot

Peltier Tech Charts for Excel

Creating Cycle Plots with Peltier Tech Charts for Excel

While this technique isn’t too difficult, it takes some time, and after your fourth chart in a row, it becomes tedious. But never fear, because Peltier Tech Charts for Excel, my Excel charting add-in, handles this task with ease.

Select a suitable data range, such as B2:E8 below. If you select a single cell inside such a range, the program will expand the selection outwards until it hits empty rows and columns, and use that as the source data.

Cycle Plot Sample Data

You can even use a discontiguous range, as shown below. You have to select all of the individual ranges.

Cycle Plot Discontiguous Data

The ranges you select also have to result in a “nice” range. A “nice” range is shown below in green; it includes the intersections of the entire row of the selection (yellow rows) with the entire column of the selection (blue columns).

Definition of "Good" Discontiguous Data

The discontiguous range can be split up a number of different ways. The range below works fine, with eight separate areas.

Cycle Plot Discontiguous Data

This range also works fine, with nine separate areas.

Cycle Plot Discontiguous Data

The following range is “too” discontiguous; the limit is somewhere between 9 and 12 areas before VBA chokes on a range. But that’s okay, because this is more complicated than you should be trying to plot.

Cycle Plot "Too" Discontiguous Data

Once you select a range, click on the split button labeled “Cycle Plot”,

Peltier Tech Ribbon - Cycle Plot Split Button

or on the Cycle Plot menu button which is also labeled “Cycle Plot”.

Peltier Tech Ribbon - Cycle Plot Menu Button

Up pops the Cycle Plot Dialog. Make sure the desired options are selected. (The program remembers the options you used last time.) Then click OK.

Cycle Plot Dialog

The program inserts a new worksheet with linked data that is appropriately arranged for the cycle plot, and creates a cycle plot using the selected options.

Among the options are Chart Type…

Create Cycle Plots with Lines, Columns, or Bars

… whether to plot by row or by column…

Create Cycle Plots by Row or Column of Original Data Range

… whether to format each series differently or all series the same…

Format Series Different or the Same

… whether to show dividers between series or not…

Create Cycle Plots with or without Borders

… and whether to add means, trendlines, or both to each series.

Add Means, Trendlines, or Both to Each Series

Converting Regular Charts to Cycle Plots with Peltier Tech Charts for Excel

Not only can Peltier Tech Charts for Excel build cycle plots from scratch, but if you have a regular chart that you’ve already formatted and you want a cycle plot of the same data with the same formats, Peltier Tech Charts can convert it for you.

Here is some data and a standard column chart, which is to be converted to a cycle plot.

Original Chart and Data

Select the chart (or select several charts: it will do them all), then click the Cycle Plot dropdown on the Peltier Tech ribbon, and click Convert Chart to Cycle Plot.

Peltier Tech Ribbon - Convert to Cycle Plot

The program inserts a new worksheet, and builds the appropriate data range with links to the data in the original chart. It copies the chart and pastes a duplicate chart on the new workbook, and adjusts the data in this new chart so the same series (with the same formatting) now refer to the cycle plot data. Finally, the program adds the secondary series that provides the labels above the chart and the vertical gridlines. The original chart and data remain intact.

Converted Chart and Data

Here are a handful of original charts.

Original Charts

The program created the resulting cycle plots, each from its own worksheet (with its own data).

Converted Charts

It took me longer to make these screenshots than it took my software to convert all of the charts.

Posted: Tuesday, June 12th, 2018 under Chart Types.
Tags: Cycle Charts, Cycle Plots, Utilities.
Comments: 7

Microsoft MVP Logo

Office 2016 For Mac Is Here (well…)

Monday, July 13, 2015 by Jon Peltier 22 Comments

Monday, July 13, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Note: This article was originally published on 13 July. I have made modifications on 17 July.

The big announcement from Microsoft last Thursday was Office 2016 for Mac is here! Ed Bott has a glowing review at New Office 2016 for Mac makes life easier for the cross-platform crowd.

We’ve known for months that Microsoft has been working on Office 2016 for both Windows and Mac. I’ve been playing a bit with the Office 2016 for Windows preview for a while, but I haven’t done much with the Mac preview.

Highlights

Mac Office 2016 User Interface

The Office 2016 for Mac user interface is very much like that for Office for Windows. It no longer feels like a toy program built by an 8th grader in shop class. This is a very good sign. Now Office looks and feels the same across all platforms: Windows, Mac, Android, iOS.

New Charts (Windows only, so far)

Earlier, Microsoft Introduc[ed] new and modern chart types now available in Office 2016 Preview. The latest Office 2016 preview features a few new chart types, including some that were previously only available to users of add-ins like the Peltier Tech Charts for Excel. Microsoft has added waterfalls, histograms, paretos, treemaps, and sunbursts.

Lowlights

Office 365

The Windows Office 2016 preview has only been available through Office 365 (the kludgy online subscription service). I never can remember which of my umpteen Microsoft logins is valid for Office 365, and even when I look it up, the correct user ID and password don’t work the first time. Updating Windows Office 2016 preview versions has never gone without several major hitches for me, followed by complete uninstallation then reinstallation from scratch.

So far, Mac Office 2016 is only available via Office 365, which Microsoft treats as a good thing. In Windows, installing from Office 365 wipes out any older versions of Office, so you can’t run Office 2007, 2010, and 2013 side-by-side to support different clients. On the Mac, I was glad to see that I could install Office 2016 without wiping out the previously installed Office 2011. I did have to rebuild the LaunchPad links to Office 2011 and put the icons back onto the Dock, but that only wasted an hour.

Mac Office 2016 VBA

This is the painful one. Mac Office 2016 does still support VBA, of course, and Microsoft has rebuilt the VBA Editor from scratch, which was drastically. Unfortunately the VBA Editor is not really ready to use: you can’t insert new modules or UserForms, and while you can edit existing modules, you can’t even view the UserForms.

Microsoft is making small improvements to the VB editor, and these improvements will be part of the monthly updates. but they are not expecting to make the editor as functional as that in Windows. They encourage developers to build their add-ins in Windows versions of Excel or in Excel 2011, then test in Mac Excel 2016.

Mac Office 2016 Custom User Interface Elements

First, a little background…

In Office 2003 and earlier, developers and users could construct custom menus and toolbars to access built-in and custom functionality.

Starting in Office 2007, Office for Windows has had the ribbon. It could readily be modified for developers willing to delve into RibbonX, a variation of XML used to control and customize the ribbon. Since Office 2010, the ribbon could also be easily modified through the user interface. While custom menus and toolbars were no longer supported in Office 2007, any custom menu items were put into a special Add-Ins ribbon tab. Windows Office add-in developers, myself included, have migrated to custom ribbon interfaces, to great effect.

Mac Office 2011 introduced a low-functioning ribbon, which did not allow any customization programmatically or through the Office interface. However, the custom menus and toolbars that were discontinued in Windows Office are still supported in Mac Office 2011.

Mac Office 2016 has a better ribbon than 2011, but it is still not customizable by the user, nor can it be it controlled using RibbonX. Custom menus and toolbars are no longer supported in Mac Office 2016, but there is an Add-Ins ribbon tab to handle legacy menu items.

Microsoft is working on implementing RibbonX interface controls for Mac Excel, and it will be rolled out as part of a regular update. Until then a Mac Office add-in developer should still build legacy menu-type user interfaces.

Sandbox Mode

One more drawback is that Mac Office 2016 now runs in a sandboxed mode, so certain features are more difficult for developers to implement, features such as accessing folders and opening files. I don’t know the full ramifications of this behavior, since my code isn’t yet running properly in Mac Office 2016.

Bottom Line

Therefore, if you are using any add-ins to extend the features of Mac Office, you should not upgrade until you know whether these add-ins will work properly in Mac Excel 2016.

Peltier Tech Charts

Peltier Tech Chart Utility 2.0

Peltier Tech has taken steps to prepare for Microsoft Office 2016. The Windows version of the current Peltier Tech Chart Utility was designed for Office 2007 through 2013, and it can to run in the preview of Office 2016, but it will not work in the eventual commercial release of Office 2016. As always, there are a few minor changes that need to be made to accommodate new features and syntax of the new Office version.

Peltier Tech Charts for Excel 3.0

In addition, Peltier Tech is working on a major upgrade to the utility, called Peltier Tech Charts for Excel. This edition will work in Windows Office 2016, but also 2013, 2010, and 2007. It will also run in Mac Office 2016, when that has become capable of supporting add-ins, and of course, it will run in Mac Office 2011. The exciting part is that the same add-in file will work in both Windows and Mac, so users stuck between platforms will not need to license two separate add-ins.

The new Peltier Tech Utility will feature all of the old charts, including the ones that Microsoft is introducing in Office 2016, because some Peltier Tech customers will still be using older Office versions. New chart types are being developed, including grouped box plots and sensitivity tornado charts. If you have a favorite chart type that neither Microsoft nor I have made available, mention it in the comments below, and Peltier Tech will add it to the long long list of suggestions.

The new Utility will also offer some new features, including more powerful chart data manipulation tools. Again, if there’s a feature you can’t live without, let us know in the comments.

The new Utility will likely have Standard and Advanced editions like the current Utility, and these editions will likely be priced at the same levels as the current utility. Existing users of the current Utility will be able to upgrade at a 50% discount, and users of the older family of utilities will qualify for a 25% discount. In addition, bulk and academic discounts will be available.

Another exciting development is Peltier Tech Charts for PowerPoint, and in all likelihood Peltier Tech Charts for Word. These are still now in their infancy, but it will bring easier linking of standard PowerPoint and Word charts to Excel data, as well as at least a partial menu of Peltier Tech custom charts.

Peltier Tech Charts for Excel 3.0 Beta

So far the new utility is only in the alpha testing stage, meaning new pieces are being incorporated and tested in-house only.

In about a month a beta version will become available. Current and potential new users will be able to test and comment on it.

During the beta testing period, users of the current Utility will be able to lock in their upgrade at a discount of 60%, users of the older family of utilities at 35%, and new users at a discount of 10%.

More details will become available over the coming weeks.

Posted: Monday, July 13th, 2015 under Excel 2016.
Tags: Excel 2016, Mac and Windows, Utilities.
Comments: 22

Microsoft MVP Logo

Marimekko Charts

Tuesday, February 17, 2009 by Jon Peltier 81 Comments

Tuesday, February 17, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

In a recent tutorial I showed how to make Variable Width Column Charts. A stacked version of this chart type, called a Marimekko chart, is popular in business, particularly where marketing departments analyze segmentation of their markets. The Marimekko chart gets its name from the patterns found in Marimekko fabric.

Marimekko Fabric

Marimekko Chart

Laying Out the Data

Suppose we have four companies, Alpha through Delta, competing in four segments, A through D, of a particular market. This could be like the companies Dell, HP, and Toshiba competing in the laptop, desktop, and home server segments of the personal computer market. A summary of the data would look like this table. The sum of the segment percentages is 100%, and the market segment shares of the companies in each segment (each row) also sum to 100%.

Marimekko Data - Original

Like most non-standard charts, we have to arrange the chart source data in a special pattern. Here is how the above data is rearranged. The first column, the X values, comprise a cumulative percentage of the segment percentages: 0, 40, 70 (40+30), 90 (40+30+20), and 100 (40+30+20+10). Each of these values is listed three times. In between each of these values, I’ve inserted another row with the average of the values above and below, with orange text. These orange values correspond to dummy data which will provide labels later in this protocol.

Note that the X values look like percentages, but are not. A date-scale axis, which we will use to spread out the widths of the area chart data, cannot render fractional values. These X values are whole numbers, and I’ve used a number format of 0"%" with the percentage symbol in quotation marks to place it as generic text after the whole number.

The Y values for each series and each segment are repeated three times, at the beginning, middle, and ending X values for this segment. For example, Alpha has 60% for Seg A in rows 3 through 5, next to X values of 0%, 20%, and 40%. The Y values return to zero at the end of each segment. Alpha shows 0% in rows 2, 6, 10, etc.

The column labeled “dummy” holds Y values for a dummy series that will provide the labels in the column marked “Labels”. I’ve colored this text orange to indicate labeling helper data, corresponding with the inserted orange rows above.

Marimekko Data - Laid Out for the Chart

Creating the Chart

Select the data in columns H through M and create a stacked area chart.

Marimekko Chart - 01

Convert series LabelsY into a line chart series.

Marimekko Chart - 02

If you clear the zero values in the LabelY data, column M, the points and connecting lines will not clutter up the chart.

Marimekko Chart - 03

Clear the legend to add more room for the chart.

Marimekko Chart - 04

Now the X axis needs to be adjusted. All the X values in column H are treated as text labels, equally spaced without regard to their numerical value.

In Classic Excel (2003 and earlier) select the chart and go to the Chart menu > Chart Options > Axes tab. The Primary Category axis is listed as Automatic. Change this setting to Time-Scale.

In New Excel (2007 and later) select the axis and press Ctrl+1 (numeral one) to open the Format Axis dialog. Under Axis type, change Automatically Select to Date Axis.

The result is that all Y values for equal X values are vertically aligned, as if in an XY chart. Of course, Excel has helpfully converted the values into dates, spaced one week apart.

Marimekko Chart - 05

Format the X axis scale so it runs from 0 to 100 in steps of 20 (not percent but whole numbers). Apply a number format of 0″%”, which places the percent sign in quotes so Excel adds it as text to the end of the whole number rather than converting the value to a percentage.

Marimekko Chart - 05 a

Add Labels to the Marimekko Chart

To add segment labels, I use Rob Bovey’s free Chart Labeler utility. This utility adds a menu item called “XY Chart Labels” to the Tools menu. It works on any chart type that accommodates chart labels, not just XY charts.

Using the utility, assign the labels in the Labels column to the LabelY series. The labels here have been positioned above the points.

Marimekko Chart - 06

Format the Labels series so it uses no markers or lines, and these points will no longer appear.

Marimekko Chart - 07

To add labels to the blocks in the chart, you need points which will be located in the center of each block. The X values are the same as above, but the Y values for each series (the names are the same as above, but they are distinct series) are calculated to place a point at the mid-height of each block.

Marimekko Data - Labels 1

Copy this data, select the chart, and use Paste Special to add the data as new series. Since the last series in the chart, LabelsY, had been converted to a line type, these are added as lines. Notice that the points are located exactly where needed, although there are also points along the category axis and lines everywhere.

Marimekko Chart - 08

Clear the cells with zero values. . .

Marimekko Data - Labels 2

. . . and only the desired points will remain in the chart.

Marimekko Chart - 09

Format each series so it uses no lines or markers, and so it also displays data labels using the series name. Then format the labels so they are centered on the points (the default is to the left of the points).

Marimekko Chart - 10

This chart has way too many labels. We could get away with one label per series. I’ve removed all Y values except for the ones that put points in the largest block for each series.

Marimekko Data - Labels 3

This is a much cleaner chart.

Marimekko Chart - 11

Naturally you could tailor the labels to show a dollar value or a percentage, or any other information, but you would need not only a table containing the X and Y values as shown in the table above the last, but also a table containing the values to be used for each label. You would use Rob Bovey’s Chart Labeler to apply the labels, as described for the segment labels along the top of the chart.

Marimekko Chart - 12

Marimekko Charts in Peltier Tech Charts for Excel

This tutorial shows how to create Marimekko Charts, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, is prone to error, and becomes tedious.

I have created Peltier Tech Charts for Excel to create Marimekko Charts (and many other custom charts) automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.

Please visit the Peltier Tech Charts for Excel page for more information.

Posted: Tuesday, February 17th, 2009 under Example Charts.
Tags: Marimekko Charts, Utilities.
Comments: 81

Microsoft MVP Logo

Error Bars in Excel 2007 Charts

Wednesday, December 10, 2008 by Jon Peltier 236 Comments

Microsoft has made a number of changes to how you use error bars in Excel 2007. I wrote an article some time ago, which covered how to use Error Bars in Excel Charts, for versions 97 through 2003. Now it’s well past time for an update.

Warning: This is a long post. It contains a lot of pictures though to keep you entertained, and near the end there’s a cool utility you can download and use for free.

Review of Error Bars in Classic Excel

It’s easy to add error bars in Excel 97 through 2003. Bring up the Format Series dialog, by double clicking on the series, by right clicking on the series and choosing Selected Object, by selecting the series and choosing Selected Data Series from the Format menu, or by selecting the series and clicking the shortcut, Ctrl+1 (numeral one). The dialog has a tab for Y Error Bars, and if it’s an XY data series, there is also a tab for X error bars.

Classic Excel insert error bar dialog

From this dialog you can assign values to the error bars, such as a fixed value, a percentage of the data point value, a couple of statistical measures, and custom values from a worksheet range. You can also assign the direction of the error bars (plus, minus, both, or neither). One nice feature is that the custom error bar value selection controls are right on the same dialog as everything else.

To format error bars, you first select them, then bring up the dialog with a double click, a right click, the Ctrl+1 shortcut, or the Format menu. In the chart below I have clicked once, which selects both X and Y error bars in an XY chart.

Classic Excel default error bars

The dialog has three tabs. Any changes you make on the Patterns tab affects both X and Y error bars.

Classic Excel format error bar dialog

The other two tabs allow you to reapply error bar values.

Classic Excel format Y error bar dialog

Rather than selecting both sets of error bars in this chart, I have clicked once to select both X and Y error bars, then again to reduce my selection to just the Y error bars.

Classic Excel select just Y error bars

The Format Error Bars dialog now only has a Patterns tab and a Y Error Bars tab. If you change any format on the Patterns tab it only affects the error bars you selected before opening the dialog.

Classic Excel format just Y error bars

Changes to Error Bars in Excel 2007

It is harder to apply error bars in Excel 2007. There is no convenient tab on the Format Series dialog. The Error Bar tab(s) as well as the tabs for Data Label, Up/Down Bars, High/Low Lines, and other features have been removed to make them more discoverable, at least that’s what we were told. To discover these features in Excel 2007, select the chart and navigate to the Chart Tools > Layout contextual tab. Click on the Error Bars button, and scratch your head while you try to decipher the options.

Excel 2007 error bars in the ribbon

Finally, select the More Error Bars Options at the bottom of the list. X (if it’s an XY chart) and Y error bars are added to the chart series, with the Y error bars selected, and the Format Error Bars dialog is displayed with the Vertical Error Bars tab showing. (If the chart has more than one series, and you had not specifically selected one series, there is an intermediate dialog asking which series to work with.)

Excel 2007 format vertical error bars

This dialog doesn’t look too unfamiliar. There is no obvious way to switch to the horizontal error bars. We are used to having not just a tab for the vertical error bars, but also one for the horizontal error bars.

Hmm, oh yeah, modeless dialog. Click on the horizontal error bars in the chart, problem solved.

Excel 2007 format horizontal error bars

Unlike Excel 2003 and earlier, you cannot select both X and Y error bars in Excel 2007. This means that if you want the same formatting to both, you have to apply it twice, and the F4 function key shortcut for Repeat Last Action only repeats the last single itty bit of formatting (if you change line color, thickness, and dash type in that order, for example, only dash type is repeated).

An inconvenient part of the user interface for error bars in Excel 2007 is that you need to click a button to enter custom error bar values, and this pops up a small dialog. This dialog is modal, so if the Format Error Bars dialog was partially covering the range you want to use, you have to clear the small child dialog, move the main dialog, and bring up the Custom Error Bars dialog again.

Custom error bars are described in more detail in Custom Error Bars in Excel Charts.

Excel 2007 custom error bar select values dialog

By default, each field contains a one element array with the element value equal to one. You can enter another constant value, and you don’t need to type the equals sign or curly brackets; Excel will insert them. More likely you want to select a range. Make sure you delete the entire contents of the entry box before selecting a range, or at least select it all, or Excel will think you meant to enter something like

={1}+Sheet1!$D$5:$D$13

If you want the value to be zero, enter zero. Don’t completely clear an entry box. because Excel will think you simply forgot and it will retain the previous value.

Excel 2007 custom error bar select values dialog: ={1} OOPS!

For all of the problems, using error bars in Excel 2007 isn’t completely terrible. You have some nice formatting options available to you. One that appeals to me is an alternative to the regular end cap appearance of the error bars.

Excel 2007 error bars with default caps

You can change to the end-cap-less variation, then format the error bar line style so they end with an arrow head.

Excel 2007 error bars with arrowheads

I don’t remember if this is the most useful, or the only useful, new formatting feature available in Excel 2007 charts.

Error Bar Utility

To make it easier to use error bars in Excel 2007 (and in Classic Excel), I’ve built a small utility, which you can download and use for free. It’s found in ErrorBars.zip. This zip file contains two versions, ErrorBars.xls for Excel 97 through 2003, and ErrorBars.xlam for Excel 2007. Install this utility by following the instructions in Installing an Excel Add-In or in Installing an Add-In in Excel 2007.

In Classic Excel, the utility places a new item, Add Error Bars, at the bottom of the chart series context menu. All you have to do is right click on the series and select Add Error Bars.

Peltier Tech error bar utility in Classic Excel

Despite all the assurances that context menus work the same in Excel 2007 as in earlier versions, you cannot add an item to an Excel 2007 chart-related context menu. What I’ve done instead is to add an Error Bars item to the end of each of the three Chart Tools contextual ribbon tabs. I know the new philosophy of Office is to place a command in only one place in the whole user interface. I prefer the old style philosophy, however, which is to place the command in every place it may be relevant. I never know where I may be when I want to use a command, and some people remember different hiding places than I do.

Peltier Tech error bar utility on Excel 2007 ribbon

Whether in Excel 2007 or in earlier versions, click on the added command, and the utility behaves the same. Up pops a simple dialog with four data entry boxes, for plus and minus Y error bars, and for plus and minus X error bars.

Peltier Tech error bar utility dialog for XY chart

If the chart type is not XY, the X error bar entry boxes are disabled.

Peltier Tech error bar utility dialog for non-XY chart

You can select a range or enter a constant into the entry boxes.

Peltier Tech error bar utility dialog: select range or enter value

I hope that this tutorial and the associated utility will make your life easier when working with error bars in Excel 2007.

Posted: Wednesday, December 10th, 2008 under Excel 2007.
Tags: Error Bars, Excel 2007, Utilities.
Comments: 236

Microsoft MVP Logo

Utilities

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

Cycle Plots

Creating Cycle Plots in Excel

Cycle Plot Data

Secondary Axis for Labeling

Formatting

Cycle Chart Types

Peltier Tech Charts for Excel

Creating Cycle Plots with Peltier Tech Charts for Excel

Converting Regular Charts to Cycle Plots with Peltier Tech Charts for Excel

Highlights

Mac Office 2016 User Interface

New Charts (Windows only, so far)

Lowlights

Office 365

Mac Office 2016 VBA

Mac Office 2016 Custom User Interface Elements

Sandbox Mode

Bottom Line

Peltier Tech Charts

Peltier Tech Chart Utility 2.0

Peltier Tech Charts for Excel 3.0

Peltier Tech Charts for Excel 3.0 Beta

Marimekko Chart

Laying Out the Data

Creating the Chart

Add Labels to the Marimekko Chart

Marimekko Charts in Peltier Tech Charts for Excel

Review of Error Bars in Classic Excel

Changes to Error Bars in Excel 2007

Error Bar Utility