Excel Charts

A powerful way to extend the usefulness of your Excel templates

Add your own charts

One of the huge benefits of being written in native Microsoft Excel
is that you can add any type of Excel chart anywhere in any Systems2win Excel template

Types of Excel charts

The most popular types of charts for Lean Six Sigma are:

Pareto Chart

Purpose: to identify the 20% of problems causing 80% of your headaches -
so that you can focus your team's attention to solving the few problems that will yield the greatest benefit.

The Pareto Chart requires some sophisticated programming,
and is therefore not offered as a standard Excel chart.

How to create an Excel Pareto Chart

You can use either the Systems2win Pareto Chart template
or Scorecard template to easily generate Pareto Diagrams.

Special features:

Copy a picture of your Pareto Diagram that can then be pasted to ANY other document
(e.g. PowerPoint, Word, etc.)
Copy a Pareto worksheet to ANY other Systems2win template
in a way that allows you to optionally link your data
so that your Pareto Chart automatically changes instantly every time that your data changes

Histogram Chart

Purpose: to graphically illustrate the probability distribution of something happening.

The Histogram also requires some sophisticated programming,
and is therefore not offered as a standard Excel chart.

How to create an Excel Histogram Chart

Here is a link to Microsoft's training for how to create a Histogram
using the free Data Analysis Toolpak.

To make your histogram look like ours:

Get rid of the Legend (right-click the Legend > Clear)
Change the background color to white
(right-click the Plot Area > Format Plot Area > select the color white)
Get rid of the gaps between bars
(right-click a bar > Format Data Series > Options tab > Gap Width = 0)

Your histogram will be created on a new worksheet.

You can simply copy and paste your chart to the same worksheet that contains your source data.

And you can optioanlly link to your source data
so your chart instantly changes every time that your data changes.

If your data is continuous (rather than discrete)
then there is also another free download to create a Histogram that is better for continuous data.

Run Chart trend analysis

also known as Excel Line Chart

Purpose: to analyze data over time - most commonly the outputs of a process

You're looking for trends and patterns over time in the variability of a process. Things to look for include:

Long "runs" of data points above or below the standard, average, or median
The total number of such runs in the data set
Extended trends - up or down

There are several variations of
Statistical Process Control Charts -
which perform the same purpose as a simple run chart,
but with additional lines for upper and lower control limits.

How to create an Excel Run Chart

Use the Scorecard template

where you can simply fill in your data for the pre-defined Run Chart

Or create your own:

Create your data
similar to our sample data
Select your data, then Insert > Chart > Line Chart

You can optionally format your Chart Area, Plot Area, each Axis, each Data Series, and other Chart Options by right-clicking them.

Control Charts

aka Statistical Process Control SPC Charts, Shewhart charts, quality control charts

Purpose: To determine whether a process is in a stable state of control.

Control Charts require sophisticated programming,
and are therefore not included within standard Microsoft Excel.

How to create an Excel Control Chart

Use the Control Chart template.

Special features:

Very easy to continue to enter or import unlimited data
while your chart displays only the most recent data for your specified Number of Rolling Periods.
User-defined threshold to switch between flat or wavey control lines
Update Chart button makes things extremely easy. Learn more
Copy a picture that can then be pasted to ANY other document
(e.g. PowerPoint, Word, etc.)

XY Scatter Plot Diagram

Excel Stratification Chart

Purpose: to analyze the relationship between two sets of data

For example, the relationship between the inputs to a process
and the resulting outputs from that process.

This Microsoft article explains the
differences between a scatter plot and a line chart
and when to use each.

One common use of a scatter diagram is the Stratification Diagram to make patterns visible when data is coming from a wide variety of sources. Learn more.

How to create an Excel Scatter Chart

Create your data
similar to our sample data
Select the data for your first data series
(in our example, the 2 columns of data for Machine 1), then Insert > Chart > XY (Scatter)
If you have multiple sets of data, then Add Series to define each of your additional sets of data

You can optionally format your Chart Area, Plot Area, each Axis, each Data Series, and other Chart Options by right-clicking them.

Other types of Excel charts

Select Insert > Chart and look around. Experiment.
Play with the many types of charts that comes as a standard feature of Microsoft Excel.

There are also dozens of free add-ins for additional types of charts - including:

More training for how to create and use Excel Charts

Excellent training is available as part of Excel's standard Help. Ctrl+F1

and a quick Internet search will find hundreds of excellent tutorials and videos
for how to create any type of Excel chart.

Bookmark = tips

More Tips for using Excel Charts

Chart Direction Arrow

Use a Chart Direction Arrow
to indicate the desired direction of performance.

You can copy Chart Direction Arrows
from the palette of standard Systems2win shapes.
Systems2win menu > Copy Shapes

Hidden data

You can set your charts to include hidden data - or not.

Tools > Options > Chart > Plot Visible Cells Only

Excel 2007+: Click the chart (which will then display new menu options in the Ribbon bar) > Design tab > Select Data > Show Data in Hidden Rows and Columns

Selecting what you want within a chart

You can optionally format your Chart Area, Plot Area, each Axis, each Data Series, and other Chart Options
by right-clicking them.

It is not uncommon to need to experiment with right-clicking in several places before finding the menu you're looking for.

It is sometimes easier to use:

The Excel 2007+ Chart tools menu:
which appears above your Ribbon menu when you click anywhere on a chart.
The Excel 97-2003 Chart menu:
which appears in your Command Bar at the top of the page when you click anywhere on a chart.

To select the entire chart, hold down the Ctrl key as you left-click anywhere on the chart.

Bookmark = 2007, align

Aligning chart bars with row heights

When using the Gantt Chart or Standard Work Combination Sheet...

to precisely line up the chart bars with their corresponding rows
(which are slightly different sizes in different versions of Excel)
click the radio button With Labels, and then select the entire chart (including its grey background area), and drag the top and bottom center handles to increase or decrease the height of the entire chart until the rows line up precisely.

How to manually re-align charts in Excel 2007+

When toggling between the With Labels and Without Labels radio buttons in Excel 2007+, you may need to manually re-size the width of the chart

Click on a blank area inside the body of the chart,
so that the entire body of the chart becomes highlighted
(as shown)

Then to resize the chart...
drag one of the square handles (in the middle),
or one of the round circles (in each corner)

Bookmark = copy

Copy Chart

Copy Chart button

Some Systems2win templates have a special Copy Chart button
that copies a picture of a chart to the clipboard,
where it can then be pasted to any other document.
(including Word, PowerPoint, etc.)

How?

Select the chart you want to copy
(hold the Ctrl key as you left-click to select the entire chart)
Select the Copy Chart button
Paste your chart anywhere

Even without the button

Although it is an undocumented feature,
you can actually use this same function to copy any chart from any Excel workbook.
(including your non-Systems2win Excel workbooks)