Trend Analysis in Excel
aka Trend Charts Scorecard, Quality Scorecard, Visual Scorecard
If you just want a simple Trend Chart
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
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.
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 bar > Systems2win tab > Open a Blank Sheet
This blank sheet is where you will do your real work
(not on the Sample sheet)
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 your
Trend Analysis template
Video: Trend Analysis Scorecard
Watch this video >>>>>
This training video >>>>>
recaps the training on this page
for how to use your Trend Analysis template
Use the Page Navigation links
in the upper left corner of your template
to quickly jump to page sections.
your working document, using everything you know about familiar Microsoft Excel.
Simply unprotect the sheet. (there is no password)
Your Trends Analysis template
allows you to easily drag pre-programmed trend charts
into and out of the Print Area
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.
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:
You can also optionally summarize and analyze data from your production schedule, production results, scrap reporting, safety, or any other data relevant to your process.
Print your 'Trend Charts' scorecard, and post it where workers can easily see it
Whenever needed — analyze and improve
Analyze your well-organized data...
- 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
- 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
- using 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)
- 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.
Bookmark = PE
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?)
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 your
Trend Analysis template
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.
3) Pivot Tables and Filters
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
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 —
- 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.
- 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.
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
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
Trend Analysis template
with many other useful Six Sigma tools
Bookmark = history
Advanced Training for your
Trend Chart template
Optionally save detailed history in the 'History by Date' section
Although most users will prefer to either:
Not just templates.
Not just training.
But also systems
that you won't outgrow
- Discard detailed history
saving only the Averages for Prior Periods
- 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
Bookmark = print1
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.
1) Save your workbook
2) Perhaps Hide any rows not needed for your Check Sheet
perhaps rows already containing data
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
6) Close your workbook without Saving
to undo all of these temporary changes