Training & Support for your Systems2win templates


Excel Training

Excel online training and videos to help you get the most from your Systems2win templates for process improvement for the most widely-used software in the world of business — Microsoft Excel


Table of Contents

Also search our other Excel training page





Don't read this
like a book!!!

Cartoon man reading upside down book

Search it

(like a web page)


to find keywords

(if and when you need help)

Bookmark = Help

Finding Help

How to find Help in the Systems2win website

1) To find web pages containing your desired keyword phrase...

Site Search

Use Google Site Search

(found in the upper right corner of every Systems2win web page)

2) To find a keyword phrase within a page...


How to find Help in a Systems2win template

Every time you begin with any new Systems2win tool...

Sample and Help worksheet tabs

The very first thing you should do is...

Start by reviewing the 'Sample' and 'Help' sheets,
that have training for THAT template

If you ever ‘get stuck' using Excel...

Excel Tips

Every 'Help' sheet has
Excel Tips most valuable for THIS template,
with Links that take you
directly to the training you need

The first thing to do is...

Scroll down near the bottom of the 'Help' sheet,
where every Systems2win template has a section of
Excel Tips most valuable for using THIS template

Even if you are an Excel expert —

You will usually learn something
when you get in the habit to...

Always review the
Excel Tips most valuable for using THIS template

every time you begin working with any new Systems2win tool

Bookmark = menu

Systems2win menu

Systems2win menu

The Systems2win menu appears in the Ribbon bar whenever you open Excel with the Systems2win add-in installed.

If the menu isn't there... see FAQ

Systems2win menu features

to extend the functionality of Microsoft Excel

  1. Training

    for Systems2win, Lean, and Excel

  2. Find and open your Systems2win templates
  3. Open a Blank Sheet

    Click this button to open the worksheet where you will do your actual work.

    And you can optionally click it again, to always have a fresh clean blank sheet.

  4. Unprotect or Protect Sheets

    Protect Sheet button has special features to eliminate known bugs

  5. Copy Sheet

    Copy Sheet button has special features to eliminate known bugs

  6. Setup

    With special features to Sync your personalized templates (for Version Control)

  7. Translations and Substitutions

    Switch languages between English, Spanish, Portuguese, French, German, or Chinese

    and switch between Substitute Phrases (perhaps for different types of 5S criteria, or different header titles)

  8. Link IconsInsert Link Icons

    to provide visual and space-efficient hyperlinks to related documents

  9. Copy Shapes

    to easily copy shapes to ANY Excel workbook

  10. Copy Chart

    to easily copy a picture of a chart to ANY other document

  11. Objects

    Easily view and select Objects and Hyperlinks
    even if you are blind or unable to use a mouse

  12. Excel Cell StylesStyles

    to learn tips for how to better use Excel Styles

  13. Change Printer

    to quickly switch between printers, plotters, and PDF writers

  14. Set or Reset Filter

    to make Excel's Filter feature easier to use, and more mistake-proof

  15. Link Cells

    to correctly handle linked cells with blank values

  16. Reset Gold Lines

    to reset the size of the thin gold lines that keep your formula ranges intact

  17. Systems2win Portal

    one of the alternative ways to find and open your templates

Bookmark = LearnExcel

Self-Paced Excel Learning Quiz

Rather than reading this online Excel Training like a book...

you will learn a lot faster and better
when you use your 2LearnExcel template

Learn Excel

Learn Excel - Excel template

You will find your
2LearnExcel template
on the home page of your Systems2win portal.


If you haven't yet
downloaded a free trial
you can do it now

Free Self-Paced Learning Quiz

  1. To quickly learn features of Microsoft Excel
    that will be most common and helpful when using your Systems2win templates for process improvement.
  2. To learn Systems2win's innovations
    that make Excel even more useful.
  3. When a learner 'brings it back green',
    with one quick glance,
    a leader can validate that a learner has learned
    what they need to know about Excel
    to 'not get stuck' using ANY Systems2win template .

"Although it took an hour and a half to complete the Learn Excel questions, I learned so much more from the interactive approach than from just reading the help text or viewing the short training videos. Right size, right time, right tools... simply inspires me to learn and do more."

~ Karyn Weimers, Lean Manufacturing Lead
URS Washington Division

Leverage your familiarity with Excel

Everything you learn from this free online Excel training,

you can use with anything you ever do with Microsoft Excel

and everything you already know about Excel,

you can use with your Systems2win templates

More links and videos to learn Excel

Systems2win's Excel training is intended to supplement (rather than replace)
the excellent online help that comes with Microsoft Excel.

Bookmark = Worksheets

Working with Excel
Workbooks and Worksheets

How to find and open your Systems2win templates

Alternative ways to find and open your Systems2win templates.

Where to save your working documents

Name, save, and organize your working documents in almost any way that best fits your needs.
Suggested policies for document storage and naming conventions.

Bookmark = blankTemplate

To open a blank Template sheet

When you first open a Systems2win Excel template, it will open to the 'Help' sheet.

The 'Template' worksheet (where you will do your editing) is hidden.

After familiarizing yourself with the help on the Sample and Help sheets...

click the 'Open a Blank Sheet' button

which will open a fresh blank sheet —
which you will use as the starting point for your working document.

Each template workbook
contains a Template worksheet

Open a Blank Sheet

Yes, we know it can be confusing...
but it only takes a moment to 'get it'...

Each Systems2win Excel template workbook
contains a hidden worksheet named 'Template'.

When you click the 'Open a Blank Sheet' button,
it copies (and unhides) a fresh blank copy of the Template worksheet.


  • If and when you need another fresh blank sheet, simply click the button again
  • With your Systems2win templates, you can never 'mess up your master worksheet'.
    A fresh blank master Template sheet is always available with the click of a button.
  • You can have unlimited worksheets in a single workbook.
    For example, you might have an AsIs sheet, Future1, Future2, etc.
  • This button overcomes most known bugs in the traditional way of copying a worksheet (see below)

Bookmark = copyWorksheet

Systems2win menu > Copy Sheet

To copy a worksheet

Use the special Systems2win ‘Copy Sheet' utility

Systems2win menu > Copy Sheet

Copy or Move sheet

DO NOT USE Excel's usual way
to copy a worksheet

Usual way to copy a worksheet —
(NOT recommended, because it has bugs)

Right-click the worksheet tab,
then select Move or Copy

Check the 'Create a copy' checkbox.
Choose where you want to copy your new worksheet.

Why use the Systems2win Copy Sheet utility?

Instead of the usual way to copy a sheet

Because it eliminates 5 Microsoft bugs

with the way that Microsoft Excel normally copies sheets

Tips for copying sheets

Quick Fix if you forgot to copy your sheet the right way

Copying a worksheet to another workbook

How to prevent most copy sheet bugs

Bookmark = renameWorksheet

To Rename a worksheet

Right-click the tab at the bottom of the worksheet. Select Rename.

Bookmark = Protect

To Protect or Unprotect a worksheet

Systems2win menu > Unprotect and Protect Sheet

Systems2win menu > Protect Sheet

Use this special Systems2win function
to eliminate known bugs
in the way that Microsoft normally protects sheets.

For older versions,
you can simply use Microsoft's usual function...

Review tab > Protect Sheet

You can then optionally check boxes
for what you want (and do not want) to protect

and you can optionally protect the sheet

Tip: Most Systems2win Excel templates are delivered unprotected.
Some worksheets are protected without a password, and can be easily unprotected.

Bookmark = HideWorksheet

To Unhide or Hide a worksheet

Home > Format > Hide & Unhide > Hide Sheet or Unhide Sheet

or... right-click the sheet tab (at the bottom of the page) > Unhide...

Several worksheets are usually hidden in most Systems2win templates:

Caution: Never delete a Help or DV sheet, but you can hide them instead.


Bookmark = View or Zoom

View, Share


to make anything easier to read

View > Zoom

Excel Zoom bug

Shapes are sometimes zoomed to 100% when the rest of the page has a different zoom size.

Solution: View > Zoom > 100%
then View > Zoom > to any percent that you want.

The shapes will now be sized correctly. 

(This Microsoft bug doesn't happen often, and is easy to fix)

Bookmark = refresh

Screen Refresh

It is not uncommon for part of your display screen to disappear, or become distorted.

When this happens in Excel, you can't use the F5 key to refresh the screen (like you can in your web browser).

So you must either:

  1. Scroll down, then back up again, or...
  2. Switch to another worksheet or another window, then back again

Bookmark = FreezePanes

Split Window or Freeze Panes

Freeze Panes

to always have column or row labels visible as you scroll

Position your cursor just to the right of the column, and just below the row,
where you want the panes to be frozen or split, then select...

View > Split     or     View > Freeze Panes

Document Author

The document Author is the only one authorized and responsible to edit the document.

Everyone else suggests ideas that are then accepted, rejected, or revised by the Author.

See online training for how to communicate with the document Author.

To save a document in PDF format

File (or Office button) > Save As > PDF or XPS

or    File > Print > select your PDF writer software

Why distribute documents in PDF format?

  1. Your Systems2win License Agreement:

    Any document created using a licensed Systems2win template that is distributed outside of licensed users must be in a non-editable format, such as printed paper, an image file, or a PDF.

  2. Controlled documents (e.g. ISO 9000, FDA, or any other regulatory control...)
    are usually not released in an editable format (such as Excel).


  1. How to use free PDF writing software
  2. How to make active hyperlinks in your PDF's

Bookmark = PrintScreen or CopyAsPicture or PowerPointPaste

Print Screen

To save a screen shot as an image file

Press the 'Print Screen' key on your keyboard (or ALT+Print Screen for just the active window)

Your screen shot is now copied to the clipboard.

Then Paste (CTRL+V) the screen shot into any application,

such as Word, PowerPoint, or any image editing software such as Photoshop,
or the free Paint that comes with Windows (Start > All Programs > Accessories > Paint).

To copy as picture

Copy as Picture

You can copy cells as a static picture — which then can be pasted
into any document as a static image.

Highlight the cells that you wish to copy, then:

Excel 2010+: Home > Copy > Copy as Picture
Excel 2007: Home > Paste > As Picture > Copy as Picture

For best results, choose the radio buttons for 'Appearance: As shown on screen' and 'Format: Picture'

Then paste (CTRL+V) the picture wherever you want it.

To paste into PowerPoint or Word

After selecting cells, and using regular Copy (CTRL+C)...

Experiment with the different options in Home tab > Paste > Paste Special

Tip: > Paste Special > Picture (Enhanced Metafile) will paste a non-editable snapshot of your selected Excel data

which will look exactly as it does in Excel (and this isn't always true when you use regular 'paste')

Bookmark = QuickAccessToolbar

Personalize your Quick Access Toolbar

You will greatly improve your productivity when you personalize your work space
to make your most frequently used menu items always instantly available.

Option 1) Right-click any command that appears in any Ribbon

and select Add to Quick Access Toolbar.

Option 2) On the far right of the Quick Access Toolbar

click the Customize Quick Access Toolbar symbol > More Commands

Extra credit: Notice that you have the option to Choose commands from: Commands not in the Ribbon

Bookmark = Copying

Select, Hide, Delete, Insert, Copy, Move

Excel Training Video

How to select, hide, delete, insert, copy, and move
entire rows or columns


7 min training video — turn on sound
Video Help


For tips on how to select, delete, insert, copy, and move
single cells or groups of cells (less than entire rows or columns)...
see training in the next few sections below

Excel training video
How to select, hide, delete,
insert, copy, and move
entire rows and columns

Bookmark = SelectEntireRow

To select entire rows or columns

(not just a few cells)

Hover your cursor over the gray edge of the worksheet
(where the row numbers or column letters are)
until your cursor turns into a small black arrow.

Then hold down left mouse button and drag
to select multiple rows or columns.

You will know you did it correctly
when the entire range of rows or columns turns blue.

Select rows


Also see Excel 2010 known issue
when selecting a large number of columns
in a protected sheet

Bookmark = Hide

Hide rows or columns

Select the columns or rows to hide, then right-click the selected area > Hide or Unhide.

or Home tab > Format > Hide & Unhide

Tip: It is usually better to hide rows and columns, rather than delete them.
You can always unhide them if you need them later.

Training Videos to Hide Rows or Columns

Long wait time?

If you experience a long wait time
when attempting to hide or unhide columns in a large workbook,
then follow this link for help.

Bookmark = Unhide

Unhide rows or columns

Unhide rows
or columns

Select the columns or rows surrounding the hidden ones,
then right-click within your shaded selected area,
and select Unhide.

Or Home tab > Format Cells > Hide & Unhide


Tip: Many Systems2win templates
come with pre-formatted hidden rows and columns

(because it's a lot easier to unhide rows or columns
that already contain formatting and formulas)

Look for 'jumps' in row numbers or column letters —

and then unhide.

Bookmark = delete

To delete cells, rows or columns

Delete cell range dialog window

Select the range of cells, (usually the entire rows or columns), then
keyboard shortcut CTRL+– (the minus key)

or... Home tab > Delete cells

Select your desired radio button from the window that appears >>>

Caution: It's usually better to hide rows and columns, rather than delete

You can always unhide them if you need them later.

Caution: Home tab > Delete cells (or shortcut CTRL+–)

actually deletes the cells
and is very different from using the keyboard DELETE key,
which will just delete the contents (while leaving the empty cells)

To delete just the contents

Use the keyboard DELETE key

(The keyboard DELETE key is the shortcut for Home tab > Clear > Clear Contents)

This will delete the contents within cells (while leaving the empty cells).

Caution: The Delete key won't delete cell formats, but will delete any unprotected formulas

(so be careful to avoid accidentally deleting formulas).

Caution: Home tab > Delete cells actually deletes the cells,

and is very different from using the keyboard DELETE key,
which will just delete the contents within cells (while leaving the empty cells).

Bookmark = InsertRows

To insert rows or columns

Caution: Rather than using right-click > Insert

it is almost always better to copy similar pre-formatted blank rows or columns
(thereby duplicating ALL formatting and formulas)

Use Insert Copied Cells, or Shift Down and Copy

Bookmark = ShiftDownCopy

Excel training video
How to select, hide, delete,
insert, and move
entire rows and columns

Also see the Training Videos
for the To Do List template

and Standard Work

To copy cells, rows or columns
use Insert Copied Cells
or Shift Down and Copy

This is probably the single most important Excel skill you will ever learn

Option 1) Insert Copied Cells

  1. Select the range of cells to copy

    To avoid breaking formulas... you will usually select the entire rows or columns

  2. CTRL+C (or your favorite way of copying)
  3. Select the cell in the upper left corner of where you want to copy to

    Or you can select a range that exactly matches
    the number of rows and columns that you copied

  4. Keyboard shortcut: CTRL+SHIFT++ (the plus sign), or...

Right-click > Insert Copied Cells
or Home tab > Insert > Insert Copied Cells

Option 2) Shift Down and Copy
  1. Select the range of cells to copy
    (perhaps the entire rows or columns)
  2. Then in the workspace (not the gray edge of the worksheet where the row numbers and columns letters are)
    hover your mouse over the edge of your
    blue highlighted cells, rows or columns

    until your mouse turns into
    a four-sided cross-arrows
          four arrow cursor
    (on a locked worksheet, the cursor will be a white arrow)
  3. Right-click and drag
  4. When you release your right mouse button, select Shift Down and Copy or
    Shift Right and Copy
Shift Down and Copy

To move entire rows or columns

Same as Insert Copied Cells — but CTRL+X to cut (rather than copy),
and then Insert Cut Cells (rather than Insert Copied Cells)

Same as Shift Down and Copy — but in the final step, select Shift Down and Move.

Also see additional ways (below) to move cells less than entire rows or columns.

Bookmark = CopyCells

To copy cells (less than an entire row or column)

In addition to the methods taught above (Insert Copied Cells and Shift Down and Copy),
here are a few more optional ways to copy cells that are less than an entire row or column.

Option 1) Copy (CTRL+C), then Paste (CTRL+V)

Simple copy (CTRL+C) and paste (CTRL+V) will work just fine IF (and only if)...
the source and destination color schemes, styles, formulas, data validation, cell comments, conditional formatting, and all other hidden elements are all identical

Option 2) Paste Special

Because hidden formulas and formatting are often not identical...

you will often want to use the options in
the Paste Options dropdown

that appears right after you paste anything

Paste Options

Right-click > Paste Special

Home tab > Paste > Paste Special


Most often, you will select either Values, or Formulas,
but why not experiment with them all?
so that you know what's available when you need it


Tip: To paste Validation, the sheet must be unprotected


Video: The Pareto video
teaches how to use Paste Special

Paste Special

Option 3) Copy & Drop

Highlight the cell(s) you want to copy.

Hover your mouse over the edge of the blue highlighted area
until your cursor turns into a four-sided-cross arrows four arrow cursor

(or a white arrow on a protected sheet)

Hold down the keyboard CTRL key as you hold down your left mouse button and drag.

When you release the left mouse button, your highlighted cells will be copied.

Tip: If you don't hold down the CTRL key, then your highlighted cells will be moved (rather than copied).

Bookmark = MoveCells

To move cells

less than an entire row or column

Warning: It is easy to mess up formulas when moving cells.
You might need to experiment with several different options.
It is always a good idea to back up your file before moving cells.

Option 1) Shift Down/Right and Move

  1. Select the cell(s)
  2. Hover your mouse over the edge of the selected cells until your cursor turns into a four-sided cross arrows Four arrow cursor
  3. Right-click and drag the cells
  4. Release the right mouse button
  5. In the pop-up menu that appears, select Shift Down and Move or Shift Right and Move

Option 2) Drag to move

  1. Select the cell(s)
  2. Hover your mouse over the edge of the selected cells until your cursor turns into a four-sided cross arrows Four arrow cursor
  3. Left-click and drag the cells
  4. Release the left mouse button
  5. Then go back to the cut cells, and perhaps apply styles formatting (or just copy a similar nearby cell)

Option 3) Cut and Paste

  1. Cut (CTRL+X)
  2. Either Paste (CTRL+V)

    or often much safer...
    Home tab > Paste > Paste Special > Values

  3. Then go back to the cut cells, and perhaps apply styles formatting (or just copy a similar nearby cell)

Option 4) Copy, Paste, Delete

This is usually the best option to move cells in the Gantt Chart and Standard Work Combination Sheet.

  1. Copy (CTRL+C)
  2. Either Paste (CTRL+V)

    or often much safer...
    Home tab > Paste > Paste Special > Values

  3. Then go back to delete the unwanted data — using the keyboard Delete key

Bookmark = CopyVisible

Go to Special

To Copy Visible Cells Only

After using Filter, if you use regular Copy & Paste,
it will copy & paste the hidden filtered cells, too.

To copy only visible cells,
select the filtered area that you want to copy, then
Home > Find & Select > Go To Special > Visible Cells Only

Bookmark = transpose

To Transpose

between horizontal and vertical data

Copy (CTRL+C)

then select the top or rightmost cell in your destination range,

Home tab > Paste > Paste Special > Transpose

Bookmark = Editing or FormulaBar

Editing and Formatting

The Formula Bar

Excel novices tend to do most of their editing in the cell itself,
and experienced Excel users do most of their editing in the Formula Bar (near the top of the Excel window).

Excel Formula Bar

Learning Exercise: Copy a few paragraphs from Word and paste them:

  1. directly into a cell

    and notice that data was also overwritten in the cells below — which is probably not what you wanted

  2. into the formula bar

    and notice that the 'paragraphs' are all correctly formatted within the single cell

Bookmark = Undo


Keyboard shortcut: CTRL+Z

Bookmark = shortcuts

Keyboard Shortcuts

Press the ALT key to see keyboard shortcuts.

And become familiar with the full list of available keyboard shortcuts:

(Excel 2007: F1 > Accessibility > Excel shortcut and Function keys)

Bookmark = paragraph

Cell paragraphs

To enter a 'paragraph' within a cell


Or within a formula (such as cells that link to the Translation Table...)
you can use CHAR(10) for a hard return between phrases.

Example: =VLOOKUP(6107,STC,4,FALSE)&CHAR(10)&VLOOKUP(6108,STC,4,FALSE)

Bookmark = FormatCells

Format Cells

Home tab > Cells (group) > Format > Format Cells (or right-click and select Format Cells)

Here you will find a large number of options for cell formatting, including:

number, alignment, font, border, fill, and protection

Bookmark = Styles

Excel Cell Styles


Home tab > Styles

To learn how to use Excel Styles
for quick and easy cell formatting,

see the online training page
for how to use Excel Styles.

Bookmark = Borders



Home tab > Font section > Borders

Tip: If you are having trouble keeping your borders consistent
as you insert or move columns or rows,
refer to tips to insert and move columns and rows.

Bookmark = Merge

To Merge cells

Home tab > Merge & Center > Merge Cells

Caution: You cannot change part of a merged cell — so it is not uncommon to need to unmerge, then re-merge cells if you need to insert, move, or delete rows or columns that intersect a merged cell.

Tip: If merging horizontal cells, consider using...

Home tab > Merge & Center > Merge & Center

Bookmark = Comments, DataValidation

Cell Comments

Right-click the cell, then select Insert > Comment.

Tip: The training video for the To Do List template includes a demonstration of how to use Cell Comments

Tip: How to print Cell Comments at the bottom of the page.

Tip: When you resize or move columns, cell comments can change size —
thereby hiding some or all of the comment text.

If this happens, right-click the cell and select Edit Comment.
Then pull the corners of the text box to resize it.

Tip: You can also add your own Cell Comments to supplement (not replace) the standard Systems2win pop-up help that appears when you click any row or column header (see animated image).

Animation - Two Types of Pop-up Help

And your user-defined pop-up help will even be automatically found and transferred to your new templates each time you upgrade — if you follow the guidelines for
how to personalize your templates.

Data validation

Experiment with using...

Data > Data Validation

Tip: You might need to unprotect the sheet

In addition to its more traditional uses...
Systems2win uses Data Validation to create pop-up help messages
that appear when someone clicks on a cell.

(see animated image)


On the Settings tab > Allow any value. On the Input Message tab > enter your help message.

Tip: Rather than editing Systems2win's pop-up help, it is wiser to personalize your templates
with Cell Comments to supplement (rather than replace) Systems2win's standard help.

Bookmark = Dropdown

Dropdown lists

Benefits of Dropdown Lists

1) Faster, easier data entry

Dropdown lists for faster data entry and fewer mistakes

It's simply faster and easier to choose from a list —
rather than trying to remember your choices

2) Avoid typos and inconsistent terminology

If Jack calls it "soda" and Jill calls it "pop",
or someone simply makes a typing mistack, mistaek, mistake...
your lists won't Filter or Sort correctly

Any Systems2win template

that might benefit from using Excel's powerful Filter or Sort capabilities
will feature a DV sheet where you can personalize your dropdown lists.

Templates like the FMEA, Control Plan, Gantt Chart, To Do List, Training Matrix,
8D Problem Solving, Leader Standard Work, and many more.

Once you learn how to use a dropdown list for ANY Systems2win template,
you know how to use dropdown lists for all.

Video: How to use Excel Filter and dropdown lists

How to use a dropdown list

Click the dropdown arrow that appears in the right side of the cell,
then select your choice from the dropdown list.

How to personalize your dropdown list

All dropdown lists are easily edited on your DV sheet.

Tip: There can't be any blanks in the middle of your Code lists.

You can even provide dropdown lists in multiple languages.

If you are personalizing your company's master template

(that everyone else uses as the starting point for their own working documents)

then your personalized dropdown lists will be automatically found and transferred
to your new upgraded master template each time that you upgrade. Learn more.

Tip: Each workbook has its own Dropdown Lists. If your company's master lists have changed,
then you can simply copy & paste them from the master template to your (older) working document.

How to add a new dropdown list to a data entry field

At the far right side of every 'DV' sheet, you will find 2 user-defined dropdown lists,
and a textbox containing short instructions for how you can simply copy & paste
either or both of the pre-defined cells to easily add a new dropdown list to any data entry fields where they might be useful.

Learning Review:
Personalize your own dropdown lists

Your leaders should have already personalized the DV sheet in your master template —
so that you are starting with typical dropdown lists for YOUR company.

Now you can make additional changes for your own unique working document (if needed).

Try It

One good way to become familiar with this
is to complete the Learning Exercises on the Sample sheet in your To Do List template.

Bookmark = SpellCheck

Spell Check

Keyboard shortcut F7, or...

Review > Spelling

Spell check does not work in password-secured documents.

Tip: First try to simply unprotect. Some worksheets can be protected without a password.

Tip: For password protected worksheets — copy the text into an unprotected worksheet to check spelling.


Bookmark = ConditionalFormatting

Conditional Formatting

Conditional Formatting changes the format of a cell if the value of the cell meets certain conditions

For example, the background color might be red if the number is negative,
or yellow if the cell contains the word 'Late'.

  1. Select cells to which you want to apply the conditional formatting.
  2. Home tab > Conditional Formatting
  3. If your condition is a formula...
    Highlight Cells Rules > More Rules > Use a formula to determine which cells to format
  4. Enter conditions
  5. Click the Format button and choose the type of formatting you want for cells that meet the conditions

Using International Symbols
for Conditional Formatting

To better support language translations,

conditional formatting in your Systems2win templates
is based upon symbols — rather than English words.

For example, with less capable software,

a Status cell might turn green when you select the word 'Done' from the dropdown list,
and turn yellow when you select the word 'Late'.

With your Systems2win templates,

conditional formatting is based on the symbol in front of the word.

So the same Status cell will turn green when you select either '▲Done' or '▲Hecho'

(or any other word in any other language — as long as the first character is the correct symbol).

Tip: Watch the training video for the To Do List template for a demonstration
of how to use numbers and symbols for conditional formatting

Tip: See known issues for conditional formatting in different versions of Excel.

Bookmark = FillSeries

Fill Series

Home tab > Editing (group) > Fill > Series

Experiment with this...

as a way to quickly populate cells
with a logical extension of the values you type in the first few cells of the series.

For example:

you might type 1, 2, and 3 in your first 3 cells, then select Home tab > Fill > Series to quickly populate any number of cells with the numbers that follow.

Alternatively, you could select the 3 cells, then right-click to select the lower right corner of this selected area, drag down in that column as far as is needed, and this area will automatically be sequentially numbered down to the end of the range you have selected.

Or you might type 'January' and 'February' in the first cells,
and it will automatically populate the next logical months.

Language translation

Systems2win Excel templates are available with language translation tables,

so that with the click of a button you can switch between languages.

Bookmark = shrink

Shrink to Fit

If text won't fit in a cell, you can either:

  1. Make the cell bigger
  2. Change cell format to Shrink to Fit

    Home tab > Cells (group) > Format > Format Cells > Alignment > Shrink to fit

Bookmark = hyperlink

Hyperlinks and Data Links

How to use Link Icons for hyperlinks

Tip: Before you can add a Link Icon,

you might need to unprotect the sheet

Systems2win menu > Insert Link Icon

Excel Ribbon bar > Systems2win tab > Insert Link Icon

Link Icons

Then, in the Link Icons window that appears, >>>>>>>>>>>>>

Hover over any link icon to learn more about it,
then select the one you want to insert, and click OK.

Then select the Link Icon that you just inserted

and CTRL+K or right-click > Hyperlink
to bring up the Edit Hyperlink dialog window (screenshot below)...

where you can Link to:

any Existing File or Web Page
or Place in This Document

(see left column in screenshot below)



Tip: Use the Screen Tip button
to add a message that appears
when a mouse rolls over the icon


See a demonstration

For a demonstration of
how to use Link Icons,
you can watch either of these videos:

* New User Training

* To Do List template



How to link to a Place in This Document

Use the option to Link to: Place in This Document

(see the screen shot above)

and then select either:

  1. a worksheet (which will take you to the top of the worksheet)
  2. a range name (which will take you directly to the place within the page where you defined your range name)

Tip: Use the anchor Link Icon Anchor Link Icon - when linking to a Place in This Document

'Anchor' is the HTML term for "a bookmark within page"

Bookmark = Bookmarks


Also known as "anchors" or "favorites"

Bookmarks allow you to go to a specific place within a page

The Systems2win website makes extensive use of bookmarks.

We make many of our bookmarks humanly visible —

so that Systems2win (or your own internal technical support staff)
can send a link to the specific place within a page that has the answer to a question.


Simply append a regular hyperlink with the # symbol, followed by the name of the bookmark

Example: Clicking #Bookmarks
will take you right back to where you are now.

Tips: Bookmarks should not have spaces between words. Bookmarks are caps sensitive.

Hyperlinks in PDF's

See training for
How to make active hyperlinks in your PDF's

Bookmark = sea

Formatting hyperlinks in cells

Avoid the ugly and dangerous

'sea of blue underlined text'

Sea of blue underline text

The problem

When the contents of a cell start with a hyperlink,

Excel will automatically convert the entire cell into a
"sea of blue underlined text".

This is not only ugly,
but is also difficult to edit, and dangerously easy to click by mistake.


You don't want this!

How to avoid this problem

Option 1) Don't start with a URL

If the URL is not the first thing in the cell, then it won't turn on the 'sea of blue underlined text'

and the cell won't become one big active hyperlink.

Option 2) Change the default setting in Excel

File > Excel Options > Proofing > Auto-Correct Options > Auto-Format As You Type tab >
uncheck the box for Replace As You Type: Internet and Network Paths with Hyperlinks.

To select a cell containing a hyperlink

(without following the link)


  1. Hold down your left mouse button for a several seconds when selecting the cell, or...
  2. Select a nearby cell, and then use your arrow keys to select the cell containing the link

When you delete a cell that contained a hyperlink

When you delete the contents of a cell that contained a hyperlink,
the cell format will sometimes still have the blue underlined hyperlink text format.

To restore the default cell format — copy and paste a similar blank cell.

When you delete a cell that contains a link icon

Before you delete a cell that contains ANY shape,
you need to first delete all shapes before deleting the cells.

Otherwise the shapes will be collapsed and invisible, but will still be there — not deleted.

Bookmark = brokenlinks

Tips to Avoid Broken Links

Absolute vs. Relative Links

  1. Relative links (specify the folderpath relationship between files)

    How? Just browse and select the workbook

    (or select the target cell or worksheet within the workbook)

  2. Absolute links (specify the full path)

    How? Specify the full pathname

    (e.g. starting with https:// or \\servername\)

Excel limitation

Excel does not allow you to specify an absolute link within your own computer or network.

Links that start with a drive letter or server name always get converted to relative.

Learn more

To avoid broken links

  1. The easiest way to avoid broken links...

    is to store all related documents in the same folder

  2. Use a relative link if...

    linking to another document that will always be in the same folder with this workbook

    (or in a sub-folder that will always retain its position relative to folders containing other related documents — even if the entire folder is moved)

  3. Use an absolute link if...

    linking to another document that will NOT always be in the same folder with this workbook

    (or in a sub-folder that will always retain its position relative to folders containing other related documents — even if the entire folder is moved)

    You cannot specify an absolute link within your own computer.
    Links that start with a local drive letter always get converted to relative.

Use your Document Storage and Naming template

See the online training for how to use your Document Storage and Naming template
to teach your users your ways to:

Bookmark = linkCells

Link Cells / Link Data

Also known as data links, linked data, data roll up, rolled up data

How to auto-update cells with values from other cells

so that the data instantly changes in both places


Option 1)

Video: How to use
special features to
Insert Sheet, and Link Data

  1. Copy the range that you want to link from (CTRL+C)
  2. Right-click the first cell of the range that you want to paste to

    Paste Special > Paste Link button

paste link

Option 2)

The How to Personalize Text
video has a section that demonstrates how to Link Cells
using Option 2

  1. Select the cell that you want to auto-update

    (you want this cell's value linked to the value from another linked cell)

  2. In the formula bar, type the equal sign (=)
  3. Then simply click on the cell that you want to auto-update the value from

    (which might even be in another workbook)

  4. Type the Enter key

Correctly handle linked cells with blank values

Anyone that has ever used Excel's feature to auto-populate one cell with the value of another

(following the above instructions)

has encountered the annoyance that a cell linked to a cell that has a blank value

will give the result of '0' (zero)
rather than mirroring the blank value of the original linked cell

Link Cells

So Systems2win provides a special button
to correctly handle linked cells with blank values.

Here's how you use it:

  1. Follow the usual instructions to link cells


  2. Select your cells that have the links
  3. In the Systems2win menu, select More > Link Cells

Cautions to observe when Linking Cells

When using either of the optional ways to Link Cells, always observe these cautions:

  1. If you link to another workbook — then both workbooks will need to be opened together
    in order to avoid #REF reference errors.
  2. If your source worksheet uses scenarios — then your linked data will be correct ONLY when the source happens to be displaying the correct scenario.
  3. Follow the usual tips to avoid broken links

Bookmark = linkChart

How to link data to a chart

Insert Sheet

Your chart might either be in a stand-alone workbook,
or perhaps you used the Insert Sheet menu button
to insert a chart sheet into any Excel workbook.

Perhaps a Run Chart, Pareto Chart, Histogram, Scatter Plot,
or other types of Excel charts

  1. Populate the chart data with linked data

    (following the instructions above)

    Tip: To provide enough room for your existing and possible future data,
    you might need to add rows or columns on either the source or target worksheets - or both.

    If so, then be sure to work within the thin gold lines that bound your data range,
    and remember that it is always wiser to use Insert Copied Cells, rather than just inserting rows or columns.

  2. Fix the linked cells to correctly handle blank values

    (following the instructions above)

  3. That's it. Your chart will now instantly update every time that your data changes.

    If you want a chart on the same sheet with the source data,
    then you can optionally simply copy and paste your chart to that sheet.

    Both charts on both sheets will instantly update along with your source data.

Bookmark = LinkTextBox

How to link data to auto-populate a text box

  1. Select the text box.
  2. In the formula bar, type the equal sign (=)
  3. Then simply click on the cell that you want to auto-update the value from, and Enter

Caution: Formula Auditing shows only incoming links from cells — not text boxes


Bookmark = AbsoluteRefs

Switch between
Absolute, Relative, and Mixed Cell References

A dollar sign ($) in a formula makes the cell reference absolute instead of relative.


Cell A1 contains the formula '=B3'

If you copy cell A1 and paste it to cell B2, then the formula in cell B2 will be '=C4'

In other words, the formula retains the relationship between the linked cells. It links to the cell that is 1 cell down and 2 cells to the right.

If cell A1 contains the formula '=$B$3',
then when you copy cell A1 and paste it to cell B2, the formula in cell B2 will be absolutely identical to the copied cell. It will be '=$B$3'

Use the F4 key to switch between reference possibilities.

As you type a reference into a formula, you can press the F4 key to cycle through the reference possibilities. Each time you press the F4 key the cell reference immediately to the left of your cursor will cycle between:

  1. $A$1 (absolute columns and row)
  2. $A1 (absolute column; relative row)
  3. A$1 (relative column; absolute row)
  4. A1 (relative column and row)

To link a range of cells

First copy the formula for one cell — as a relative reference.

Then copy the formula to the other cells in the range.

Then (optionally) convert all the cells to absolute references.

VC sheet (VC = version control)

A few Systems2win templates have a VC sheet, which uses linked cells for:

  • Populating similar cells with similar data on multiple worksheets
  • Easier integration with third party version control software

Bookmark = concatenate

Concatenate Linked Cells

If you want a single cell to be auto-populated based on the values of multiple Linked Cells...

then you need to learn how to concatenate.

There are 2 ways to concatenate, and they both work equally well:

Option 1) Use amperstand (&)

Follow the instructions (above) for how to link to a single cell,
and then in the formula bar, you can add an amperstand (&) followed by any other Excel formula,
most commonly perhaps:

  1. a link to another cell
  2. text (surrounded by double quotes)

Examples: =A1 & " " & A2
will display the contents of cells A1 and A2, with a space between them

A1 & CHAR(10) & A2
A1 & "
" & A2
Either one of the 2 examples above will display the contents of cells A1 and A2, as 2 "paragraphs"

For a "paragraph", you can either use CHAR(10)
or you can use ALT+ENTER as you enter text within double quotes

(The cell must be formatted to allow Word Wrap)

Option 2) Use the CONCATENATE function

Even though this command has been deprecated as a legacy function,
it still works, and some users like it better.


will display the contents of cells A1 and A2, with a space between them

" & A2)
Either one of the 2 examples above will display the contents of cells A1 and A2, as 2 "paragraphs"

Bookmark = EditLinks

Edit Links

Data > Edit Links

or for named cells...

Formula > Name Manager > Use In Formula

If your workbook contains unwanted links to external data,
then you can use 'Edit Links' to redirect links to your current workbook.


Tip: Even though 'Edit Links' redirects all links on all worksheets in the workbook —
it must be run from an unprotected sheet.

Tip: 'Edit Links' is all or nothing. It will attempt to redirect all links in the entire workbook.
If there are a few that you want to keep linked to the external workbook, then you will need to manually recreate them.

Tip: Some links might not be able to be redirected to the current workbook.
Broken links, links to worksheets that exist in the external workbook but do not exist in the current workbook, and some other situations will result in still having some external links even after using 'Edit Links'. When trying to track down remaining unwanted external links, be sure to also examine the named ranges.


Bookmark = CustomCalcs or SafeZone


Tips to avoid accidentally messing up your formulas

  1. Custom Formula Safe ZoneKeep your formulas separate from your data —
    by linking to and from cells in your 'Custom Formula Zone'
    (in the lower right quadrant of every Systems2win 'Template' worksheet)
  2. Rather than using Insert > Row, it is almost always wiser to copy a similar blank row
    (thereby also copying the formulas)
  3. Be especially careful when you move cells
  4. Highlight cells containing formulas

Bookmark = HighlightCells

Highlight cells containing formulas

so that you don't accidentally delete your formulas

Option 1) It is sometimes a good idea to permanently highlight cells containing formulas

Use styles to permanently change the background color of cells containing formulas

Option 2) Use Excel's standard ways to identify cells that contain formulas

a) Unprotected cells that have a formula have a green corner

A green triangle in the upper left corner

b) Formula Auditing

Bookmark = FormulaAudit

Formula Auditing

Formulas > Formula Auditing

Experiment with the many standard Excel tools for formula auditing.

Tip: When using Trace Dependents —

if there are incoming links from other worksheets,
then you need to double-click the arrow to view the list of incoming links from other worksheets.

This is especially useful in the TT Translations Table that has so many incoming links from other sheets

Tip: Formula Auditing only works with formulas in cells — not text boxes

Bookmark = AccidentallyDelete

If you accidentally delete a formula

  1. If you catch your mistake right away — simply Undo on the Quick Access toolbar (CTRL+Z)
  2. Later — you can often repair it simply by copying a similar cell.

Bookmark = Names

How to define range Names

aka cell references, cell names, named range, local named range, global named range

A range is a cell or a group of cells.

On an unlocked worksheet... select your cell range.

Formulas > Define Name

Global vs. local names

By default, names are "global" —

available to be used on every worksheet within the workbook.

To make a name "local" to only one sheet —

precede the Name with the name of the worksheet and an exclamation mark.

Example: Template!MyName

Caution: Be careful not to delete or change any cell Names defined by Systems2win!

Technical support for troubleshooting custom formulas and/or accidentally-deleted Systems2win named variables is available at our regular billing rate. Always keep a backup before you start writing your own custom formulas and Names.

Bookmark = NameBox

Excel NameBox

How to use the Name Box

to quickly select, highlight, and go to any Named Range

Simply use the dropdown list in the 'Name Box'
that you will find just to the left of the Formula Bar
in all versions of Excel.


How to use the Name Box to name a shape


Bookmark = ThinGoldLine or ResetThinGoldLines

How to use a 'thin gold line'
to keep your formula ranges intact

thin gold line

Has this ever happened to you?

You insert or copy or move a new row
to the bottom or top of a range of similar data cells . . .

and then notice (or worse — don't notice)
that the new row is not included within your totals?


A simple way to avoid this

is to use a "thin gold line"
above and below your range of cells, and then set your formulas to include the gold lines within the range.

Then whenever you insert, copy, or move new rows anywhere between the gold lines, you can be confident that the new rows will be included within the range.

Tip: Often there will be a thin gold line only at the bottom of a range

because you can include the (gold) header row as the top end of your range — accomplishing the same result.

Important: Don't ever delete a thin gold line

because it will mess up named ranges defined by Systems2win.

Important: Format thin gold lines by applying the pre-formatted styles: 2winThinHorizontal and 2winThinVert.

If you simply change the background color, then Reset Thin Gold Lines, Filter, and Sort might not work correctly.

Reset Thin Gold Lines

Reset Gold Lines

Systems2win menu > More > Reset Gold Lines will:

  1. Automatically reset the width of thin gold lines

    It automatically resizes any row or column containing any cell containing the styles 2winThinHorizontal or 2winThinVert

  2. Automatically populate every 2winThinVert cell
    with hidden data

    Thereby greatly increasing the probability that when you attempt to use Filter or Sort, they will simply magically work — even when a seemingly identical-looking table in your own homemade spreadsheet won't

Sometimes it can take over a minute to finish processing once you click 'Reset Thin Gold Lines'. Be patient.

Bookmark = AutomaticCalculation

Manual vs. Automatic Calculation

All Systems2win templates should be set to calculate automatically.

File > Options >Formulas > Workbook calculation > Automatic

Caution: Automatic vs. manual calculation can get changed inadvertently

because the first workbook that you open in Excel
can change the calculation setting for all workbooks opened subsequently.

Bookmark = Recalculate


Formulas > Calculate Now

Or use keyboard commands...


calculates the formulas in all open workbooks. To calculate the formulas in every worksheet of just one workbook, close all other workbooks.


calculates only the formulas in the active worksheet. Other worksheets in the same workbook won't be recalculated.


calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.


rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

Bookmark = precision


File > Options > Advanced > Set precision as displayed

In ALL Systems2win workbooks, this checkbox SHOULD NOT be checked.

Online help for all calculation functions

available in Excel

F1 > Function Reference


Bookmark = Analysis, Scenarios



Use Excel Scenarios Manager to store and quickly switch between different sets of data.

This is one of the most valuable features of Excel.

We highly recommend that you spend 2 minutes to learn it.


You will learn this better if you don't just read this — but actually follow along in Excel

Data tab > What-If Analysis > Scenario Manager


If Scenarios have already been defined...

Click the button to 'Show' the values for that scenario.

Tip: You might need to manually recalculate

Tip: If your original numbers have not yet been saved as their own Scenario,
then you can restore those original numbers by using CTRL+Z to Undo

Video: Excel Scenarios


Excel scenarios

To create your first Scenario...

click the button to 'Add', then...

enter your Scenario name.

Click the icon in the right of the Changing cells text box,
and then hold down the CTRL key while clicking the cells that you want to change in your scenario.

You can select up to 32 cells.

If you need to change more than 32 cells,
then use one of the other methods described in our online training for Alternative Ways to Show Multiple Scenarios in one Document.

Edit scenario

When you click OK...

enter values in the Scenario Values window.

And now you're ready

to switch between values with the click of a button

(Try doing THAT in Visio...)

scenario values

Bookmark = AutoFilter


Video: How to use Excel Filter and dropdown lists


Filter your data in useful ways —

displaying only those rows of data that meet your filter criteria.


If you are filtering a document created from a Systems2win template... (v13 and higher)

then you can use this special feature that makes Filter really easy...

In the Systems2win menu in your Excel Ribbon bar,

Systems2win menu > Reset Filter

select More > Set or Reset Filter

This will correctly set Excel's Filter to be ready to work

or (if it was already set), it will reset it...
clearing any Filter choices that you chose previously.

That's it. Easy.

If you are filtering a non-Systems2win Excel document...

then you are going to need to search the Internet to ensure that you fully understand what Microsoft means when they say that you first need to select the correct cells in what they call a 'list'

and then you need to manually select the correct cells for your 'list'.

and then (with the correct cells for your 'list' selected)... you can then select...

Data tab > Filter

All versions... (next step)

Dropdown arrows will appear on each column header,

which you can use to filter your data

as shown in the picture below.

Excel AutoFilter

Click one of the filter arrows in the gold headers to see your filter choices

Filter and Sort Tips

Bookmark = Sort


Highlight ALL cells in your list 

Include header titles in gold cells, and be sure to include ALL columns in your list — including thin gold vertical lines.

If your list has a Name, (such as A_FilterData), you can easily select it from the dropdown list in the Name Box

and then complete the information asked within Data > Sort.

Tip: It is a good idea to back up your file before sorting, and Undo (CTRL+Z) can be very useful.

Tip: Don't use Filter for sorting.

Tip: Some Systems2win templates might have disabled the Sort feature
(because sorting might not normally make sense with that type of data)

Bookmark = PivotTable

Pivot Tables

Insert > Pivot Table

There is not a single Systems2win template that requires you to know how to use Pivot Tables.

If you already know how to use Excel's powerful Pivot Table features, however,

there is nothing preventing you from using them.

If you don't already know how to use Pivot Tables,

then you can learn about them using Excel's standard Help (CTRL+F1).


One of the huge benefits of being written in native Microsoft Excel

is the ability to add unlimited charts of almost any kind.

See training for Microsoft Excel Charts


Bookmark = Printing

Printing in Microsoft Excel


File > Print or CTRL+P

Bookmark = PrintArea

How to change the Print Area

to include only selected rows and columns within your Print Area

Highlight the cells that you want to include in your Print Area

Page Layout tab > Print Area > Set Print Area

Then Print (CTRL+P)

view page break preview


If you are viewing in View > Page Break Preview mode

then you can simply drag the thick blue borders
to change your print area.

Also experiment with...
(and notice what changes in Print Preview)

Bookmark = PageBreak

How to set Page Breaks

This Video demonstrates
Page Break Preview, and how to set page breaks

In View > Page Break Preview mode,

you can simply drag a dashed thick blue line to convert it into a solid thick blue line,

which is how you can manually specify where Excel will stop one page, and start printing the next.

To remove a manual page break,

just drag it to merge with the nearest manual page break

(or edge border).

Bookmark = PrintButtons

Systems2win Print buttons

Print Areas

Some Systems2win templates have special features for printing different areas of a worksheet.

Print buttons in the Systems2win menu

Things to know:

1) You can also use Excel's regular Print features

As delivered, Print Area 1 always corresponds to the primary Print Area for the main section of the template.

which gives you the ability to use everything you know about Excel to manually change the Print Area and change Print Settings in ways that are more flexible than the pre-sets for the Systems2win predefined Print Area 1.

2) The Print Area for Systems2win print buttons are defined using named ranges

and are therefore unaffected by any changes that you make to Excel's default Print_Area.

so the way to preview what will be printed is to print as a PDF.

3) You can select the Print Orientation for each Print Area

Somewhere on the sheet, you will find dropdown lists,
where you can choose for each Print Area: xlPortrait or xLandscape

4) You can print to PDF



Bookmark = PrintSettings, PaperSize

How to change Print Settings

to print on a single page or multiple pages, etc.

Page Layout tab > click the tiny little arrow in the corner of the Page Setup section

Print Orientation

Portrait or landscape

Page Layout tab > Orientation

How to change paper size

(e.g. letter, legal, A series international sizes, CAD sizes...)

Page Layout tab > Size

Also see A3 Reporting,
and How to change print scale (below)

How to change print scale

Page Layout tab > Scale

You can experiment with:

  • Leave automatic
  • Fit to specified number of pages wide and/or high
  • Specify the % scale that looks best for your chosen paper size

Bookmark = PrintCellComments

To print cell comments

To print cell comments at the bottom of each printed page:

Page Layout tab > click the tiny little arrow in the corner of Page Setup section >
Sheet tab > Comments > At end of sheet

Bookmark = ContinuousImprovement

Continuous Improvement

How to personalize your Systems2win templates

One huge advantage of being written in Word, Excel, and PowerPoint

is that your Systems2win templates are easy to personalize,

and Systems2win has invented several innovative ways
to make it as easy as possible to personalize your Systems2win templates...

And... still make it easy to keep up with annual maintenance upgrades —

to make it as easy as possible to continuously improve your tools for continuous improvement.

Upgrade to the latest version





Video Help

Video: Which version of Excel
am I using?











menu bottom



If your organization has not yet provided a license, you can download your free trial