Skip to main content

Lean Tools, Training, and Systems

Trend Analysis in Excel

aka Trend Charts Scorecard, Quality Scorecard, Visual Scorecard

If you just want a simple

Trend Chart Excel template

Then consider using your Run Chart template (RunChart.xlsx)

or your Control Chart template (ControlChart.xlsx)

or the online training for how to create simple Excel Charts

Run Chart for trend analysis in Excel
Control Chart for trend analysis in Excel
Trend Chart template

If you want a well-designed system to organize your data...

then use your Trend Analysis template (Trends.xlsx) and see how much fun you have dragging different types of trend charts into and out of the Scorecard Print Area — to easily analyze the trends that matter most for YOUR data.

How to use your

Trend Chart template

Find and open your template

Find and open your Trends Scorecard template


in the same way that you find and open your other 150+ Systems2win templates.

Excel Ribbon bar > Systems2win menu

Save your working document

following the usual document storage and naming conventions established by your leaders

Open a Blank Sheet

When you're ready to start doing your own real work...

click the button to 'Open a Blank Sheet'

Excel Ribbon > Systems2win tab > Open a Blank Sheet

This blank sheet is where you will do your real work

(not on the Sample sheet — which gives you sample data that is extremely helpful for learning how to use your new tool, but is the wrong place to do your real work)

Systems2win menu > Open a BlankSheet

Rename your new sheet.

If English is not your preferred language

Switch to your language, just like every Systems2win Excel template.

Now you are ready to start using

Trend Analysis in Excel


Watch this video

This training video recaps the training on this page
for how to use your Trend Analysis template

Video: Trend Analysis Scorecard

Get oriented

Use the Page Navigation links in the upper left corner of your template to quickly jump to page sections.

Personalize it

Optionally personalize your working document, using everything you know about familiar Microsoft Excel.

Simply unprotect the sheet. (there is no password)

Personalize the 'Scorecard Data' section:

Edit the headers at the top of the table.

If more space is needed - Use Insert Cells to copy similar blank rows or columns.

If less space is needed - Hide rows or columns.

Format fields the way you want them - for your desired number of decimal places, colors, fonts, alignment, etc.

All Scorecard Data cells containing formulas must be in the Formulas section on the far right. (Any formulas in the main section will be wiped out each time that you click the Period End Processing button)

If you want your time unit of measure to be seconds, hours, or days (rather than the default minutes)...

or if you want Units per Minute rather than Minutes per Unit...
then you know what to do...

Replace the text of those column headers, and perhaps edit the formulas.

Trend Charts Excel template

Your Trends Analysis template
allows you to easily drag pre-programmed trend charts
into and out of the Print Area

Personalize the 'Trend Charts' section:

Add, edit, or remove charts

Simply drag pre-formatted charts into and out of the Print Area.

And/or add your own user-defined charts, using everything you know about Microsoft Excel.

Add, edit, remove, or hide header fields

Don't change the number of rows, unless you are changing paper size or page orientation

If you change paper size and/or page orientation...

We suggest that you simply add or hide rows somewhere in the middle (not the very bottom thin row)

Do NOT add, remove, or change the width of columns

See online training for Systems2win Print Buttons

Enter header data

Enter data in the header fields in the 'Scorecard Data' section, which will auto-populate portions of the 'Trends' section.

On a periodic basis

perhaps daily or weekly

Transcribe shift data from your source documents to the 'Scorecard Data' section

Most commonly, you are tallying up the check marks that were manually hand written onto a print-out of either:

Check Sheet template

1) your Check Sheet template (CheckSheet.xlsx)
2) a check sheet printed from your Trend Chart template (how?)

You can also optionally summarize and analyze production scorecard data from your production schedule, production results, scrap reporting, safety, or any other data relevant to your process.

Tip: When copying to and from another document, perhaps use Paste Special > Values and perhaps Transpose horizontal to vertical data if needed

Print your 'Trend Charts' scorecard, and post it where workers can easily see it

Whenever needed — analyze and improve

Analyze your well-organized data...

  1. using the 'Trend Charts' section of your Trends Analysis template which you easily personalize by dragging different types of trend charts into and out of the Print Area
  2. using the 'Pareto' section that has features to allow you to easily populate the Pareto Chart with many different types of data for different time ranges
  3. Systems2win menu > Copy Chartusing the Copy Chart button to copy a picture of any chart to the clipboard where it can then be pasted to any other type of document
    (Word, PowerPoint, etc)
  4. as well as user-defined charts, Pivot Tables and everything you know how to do using familiar Microsoft Excel

Use Hansei to stimulate Lean Thinking.


At the end of each Analysis Time Period

perhaps weekly or monthly

use this feature to archive your historical data thereby enabling you to perform much more powerful trend analysis on both current and past periods.

1) Finish any final data entry and analysis

2) Save an archive worksheet or workbook optionally using the 'History' section. (how?)

Systems2win menu > Period End Procesing

3) In the Systems2win menu, click the button for 'Period End Processing' which will copy Current Period Averages to Prior Periods, and clear contents of Current Period cells

4) Save your working document which is now ready for you to begin entering Scorecard Data for a new Time Period

Benefits of

Trend Analysis in Excel

1) A clean system to organize, archive, and analyze your data

Most statistical software assumes that you already have a good system for organizing and archiving your data.

That's a big assumption, isn't it?

2) Able to handle real world volumes of data

Have you noticed how Check Sheets have a very short time horizon?

That's because they are designed to gather data, not to analyze it.

Is it your goal to just gather data?
Or do you want to analyze it to make decisions?

If you're going to make decisions, then is one week's worth of data sufficient?

Your Systems2win Trends Scorecard provides a clean system to organize, archive, and analyze reasonably large volumes of historical data.

Quality Scorecard

3) Pivot Tables and Filters

How are you going to use Filter or a Pivot Table on 1 week's worth of data?

Why even bother?

4) Side-by-Side Pareto Charts

Your Trends template features 2 side-by-side Pareto charts to easily compare your Current Period to historical data, so that you can see trends.

Use drop down lists to easily change your chart to display any prior period, or the average for all prior periods.

Learn more about Pareto Charts

Pareto Charts - Past and Current Periods

5) Trend Charts


Select any column of data to display in your trend charts

Select to chart either the Median or the Average line

Once you choose your column of data to chart,
it will populate two types of charts for the same data —

  1. A classic Run Chart / Trend Chart with special programming so that it correctly handles zero and blank values within your month-to-date data, and correctly ignores blank values for future dates.
  2. A bar chart that shows the same (month-to-date) data as bars — right alongside the summary data bars from previous months — so that you can quickly see how your current month is performing in the context of historical trends.
Trend Charts in Excel

6) Easily personalize your Visual Scorecards

If you're already familiar with Excel, then you're going to find everything very familiar whenever you personalize any Systems2win template.

1) Easily drag pre-defined charts into and out of the Print Area

2) Add any type of Excel charts

3) Personalize any of the existing fields

4) Easily add your own custom fields & data

5) Easily hide or unhide the pre-formatted section for detailed 'History by Date'

Unhide if you want to maintain detailed history on the same sheet with your Current Period and Prior Period Averages.

7) All the benefits that you've come to expect from every Systems2win template

Why reinvent

Why reinvent?

Consistent, standardized user interface — using familiar Microsoft Excel

Consistent lean systems for version control

On-line training and videos

Free New User Training

Helpful technical support when you need it

testimonial quote

Trend Analysis Excel template

with many other useful Six Sigma tools


Why re-invent?


Advanced Training for your

Trend Chart template

Optionally save detailed history in the 'History by Date' section


Although most users will prefer to either:

  1. Discard detailed history

    saving only the Averages for Prior Periods

  2. or they will save an archive workbook for each prior period

    following your company's Document Storage and Naming Conventions

there is also a third alternative: to save detailed history in the 'History by Date' section.


All of your detailed history is in one table - making it available for Pivot Table analysis.

How to do it:

At the end of each Analysis Time Period, just before clicking the button for 'Period End Processing', do this one extra step:

Use Insert Copied Cells to copy entire rows of data from the 'Current Period' section to the 'History by Date' section.

Refer to the sample data on the Sample sheet of your Trends Scorecard template

two bikers

Not just templates.
Not just training.

But also systems
that you won't outgrow


Optionally print a Check Sheet from your Trends Scorecard template

Although you might gather your data using a print-out from your Check Sheet template (CheckSheet.xlsx)

which is then summarized and analyzed within your Trend Analysis template…

you might also optionally print a Check Sheet from your Trend Analysis template itself.

Here's how:

1) Save your workbook

2) Perhaps Hide any rows not needed for your Check Sheet

perhaps rows already containing data

3) Change the Print Area

to exclude the columns for Formulas

4) Perhaps increase column width in the 'Problems' section

so that you have sufficient room for your expected amount of hand-drawn hash marks

5) Print

6) Close your workbook without Saving

to undo all of these temporary changes






New User Training

Your Systems2win templates come with free New User Training
to quickly learn features that are common to all 150+ templates






Six Sigma templates

The right DMAIC tools
to Define, Measure, Analyze, Improve, and Control
the quality of ANY process



Tell a Colleague



Own Yours Now

Own your own professional tools
that you can take with you for the rest of your career







Training and Coaching

Consider Training and Coaching to support your teams to succeed


two bikers

Training to get you started.
Tools you won't outgrow.




Schedule a Conference

Schedule a conference
to discuss your challenges
with an experienced lean advisor