Skip to main content

Excel Templates, Training, and Systems

How to personalize

your Systems2win templates

with all the power and familiarity of Microsoft Excel

Table of Contents

 

 

 

Video Help

Short Intro

This short video gives a good introduction to features for personalization

Long Training

In addition to the training videos found on this page, you can also watch the pre-recorded training session

 


How To

How to personalize

with all the power and familiarity of Microsoft Excel

Understanding the difference between
Working Documents and Master Templates

Working documents

are created from a Systems2win template

Once you download a Systems2win template to use as the starting point for your working document,you can store that working document anywhere — just like any other Excel file — and you can use everything you know about Excel to make that document your own.

Anyone can personalize anything on their own working document.

Master templates

are used as the starting point to create working documents

Only a few of your most senior team leaders have the authority to overwrite the master templates that everyone shares from your Systems2win Portal.

How to personalize a working document

created from a Systems2win template

Video Help

Video: How to personalize
a working document

  1. Save a copy of your template to any temporary working folder so you can make your changes in a safe sandbox

    The one place that you DON'T save your working document is in your S2winPortal folder, which should store only your company's master templates (not working documents)

  2. Unhide any hidden sheets that you want to edit

    such as the Template sheet, the CLog sheet, or the TT sheet for translations

  3. If the sheet is protected... (most aren't)

    then Unprotect the sheet

     

    Personalize it ...

    Excel training can be helpful

    How to personalize text and text boxes

    How to add custom shapes

    Additional tips to personalize your:

    Assessment templates  (such as 5S and Lean Assessment)

    Process Analysis template

    Work Instructions template

    Frequency Codes for your Standard Work template

    Leader Standard Work template

    FMEA template

    How to personalize a master template

    If you are replacing the MASTER template...

    (that everyone shares... not just your own working document)

  4. Video: How to personalize
    a master template

  5. Make an entry in your Customization Log

    Unhide the CLog sheet.

    For your personalizations to be found and transferred,the field 'Has this template been personalized?' must be TRUE

    To determine whether the Personalization Upgrade Utility will automatically find and transfer your personalizations the next time that you upgrade...

    use the table on this page

    For all other changes — make an entry in your CLog sheet — which will make future upgrades much easier.

     

    When you have finished personalizing...

  6. Save it in the exact condition that it will be when your users open it...

    Select the upper left corner of each sheet;

    so the cursor will be in the right place when your user opens it

    Reprotect any sheets that you unprotected

    Rehide any sheets that you unhide

    The 'Template', 'CLog', and 'TT' sheets should all be hidden.

    Important: Do NOT leave the 'Template' sheet unhidden. Learn why

    Save with the Help sheet showing

  7. Final review

    Email to Systems2win your (almost-completed) personalized template for final review

  8. Use Windows File Explorer to overwrite the original template of the same name within the \tTemplates\ folder in your master \S2winPortal\ folder

    You must overwrite the original with the exact same filename so that incoming hyperlinks remain functional.

    You might save a backup of the original template, or you can always restore the original using your S2winPortal exe file.

    email

  9. Send each of your users an email instructing them to 'Sync Now'

Sample email title:

Please reply to this email to confirm when you have successfully Synced your Systems2win templates

Sample email text:

The <XXX> template has been personalized

<optionally explain what and why>

Please Sync your Systems2win templates now.

In the Systems2win tab of the Excel Ribbon menu, select Setup > Sync Now

And then reply to this email to confirm success.

Thank you,

<your name>

Training Matrix

And then use your Training Matrix to ensure that each user confirms success.

Learn more about Systems2win Leadership Roles, and the Systems2win Sync utility.


Got a good idea?

If you believe that your proposed way of doing things might be better for many organizations, (not just your own), then...

Ask us to make the change for you

for free — as part of base code!!!

If Systems2win likes your idea, we will do the programming for you — for free!

Your change is incorporated into base code — so all upgrades will contain your change, and you won't have to pay your programmers to make the same modification each time you upgrade.


text

How to personalize text

4 ways to replace words with your own

Option 1) Simple text


How?

Just type words in the cell.

(It's okay to overwrite a formula linking to the Translation Table)

When?

Simple text is usually the best choice for editing your own working document

Advantages:

Video: How to personalize text

This is not the best choice for editing a master template

because you will need to manually copy & paste your cells each time that you upgrade.

If you do edit a master template with simple text, then be sure to make a brief entry in your Customization Log to remind you to make the same changes each time that you upgrade.

Option 2) User Substitutions

(Substitute Phrases)

How?

Learn how to personalize your own User Substitute phrases

that will be automatically found and transferred to your new upgraded master templates every time that you upgrade.

When?

User Substitutions are usually the best choice for editing a master template

because any User Substitutions in a master template will be automatically found and merged with the new version of that master template each time that you upgrade

If you need multiple languages or substitute phrases,

then User Substitutions can also be a good choice when editing your own working document.

Option 3) User-Defined section of the TT sheet

How?

Enter your desired phrase in the User-Defined Translations section at the bottom of the TT sheet

(enter your text in both the English and the 'Selected Translation' column — as well as in the columns for any translations you have purchased — and you can even optionally enter User Substitutions if needed)

Type the equals sign (=) in the cell where you want your text to appear, (on any sheet other than the TT sheet), then select the cell containing your desired text in the Selected Translation column in the User-Defined Translations section of the TT sheet, and click the ENTER key.

Or better yet, click the F4 key before clicking the Enter key — to make it an absolute cell reference.

Each time that you upgrade...
the entire User-Defined Translations section of the TT sheet will be automatically transferred to your new template, but you will need to manually recreate your Cell Link formulas from sheets that link to your TT sheet, so use your Customization Log.

Do NOT use Link Cells to link to phrases within the String Translations sections of the TT sheet — which must be linked using VLOOKUP. Optionally see more tips for how to Link Cells.

When?

Best choice for editing a master template — if there is no existing phrase

If the cell already links to an existing phrase in the TT sheet, then it is better to use User Substitutions.

Option 4) VLOOKUP

How?

Simply change the number in any existing VLOOKUP formula to reference the ID number for your desired row of text in one of the String Translation tables on the TT sheet.

When?

User Substitutions are almost always a better choice.

If you own multiple language translations, it can be convenient to simply link to a phrase that is already translated.

Disadvantage: Each time that you upgrade... you will need to recreate any formulas that you changed, so use your Customization Log.


Text Boxes

Usually, your text boxes will consist of simple text in a single language...

and then there is nothing to learn.

If you want your text boxes to switch between languages...
then the instructions are the same as above... except...

formulas must be entered in the Formula Bar (rather than directly into the text box itself)

text boxes don't support VLOOKUP, so you can only use Cell Links to the Shape Translations or User-Defined Translations sections of the TT sheet

Important Tip: If you want the Personalization Upgrade Utility to automatically find and transfer your personalized text boxes, then also see training for User Help Text Boxes.


distribute

How to distribute
your personalized templates

using a consistent standardized system for version control to ensure that all users have the most recent version of your (personalized) templates

When you have finished editing your new master template,

use Windows File Explorer to overwrite the original template of the same name within your master \S2winPortal\tTemplates\ folder.

You must overwrite the original master template with the exact same filename so that incoming hyperlinks remain functional

If your leaders have chosen to empower your teams with the Systems2win Sync utility, then you can also distribute your personalized templates to the local PC for every licensed user.

Whenever a users selects Systems2win menu > Setup > Sync Now,

your personalized templates are synced to that user's computer.

S2winSync

Can we use S2winSync to disseminate our company's own personalized (perhaps non-Systems2win) templates?

Yes. That's what the S2winSync folder is for.

Your own templates can optionally be distributed using your S2winSync folder.

Simply store your personalized templates in the S2winSync folder in your master S2winPortal on your shared server.

Your unique templates will now be disseminated to all other licensed users in your company the next time they sync with your company's master S2winPortal.

How do users find and open our synced custom templates?

Version 15 R161206 or higher...

Your users find your own (synced) templates right alongside their Systems2win templates.

Excel > Systems2win menu > System2win Templates

Lower versions...

If you want to sync your own templates, it's worth it to upgrade.

Can we choose to NOT distribute some of the Systems2win templates that we own?

Yes.

Multiple Versions

Multiple versions of a template

Upgrades are easier if all of your users are able to use one shared template.

There are, however, situations where you might want to create multiple versions of the same template.

Here are a few examples:

  • 5S — you might want one template for production, and another for office (or medical, or whatever...)

    Tip: It is usually a better idea to use User Substitutions within a single template, but some teams prefer to create multiple templates instead.

  • Lean Assessment — you might want templates for different types of assessments, such as a value stream, a facility, a process, a team, the Lean Leadership Team, the Executive Steering Committee, etc.
  • Checklist templates — personalized for unique purposes.

As a rule you should try to avoid multiple versions of the same template.

But if you must, then here is how to do it...

Simply make another copy of the master template, and personalize it like you would any other master template, but rather than overwriting the one master template in your portal, you instead save it with a different file name, somewhere else.

Where?

That's up to your team leaders and/or your IT Department.

If your organization purchased personalization features,

then the multiple versions of your shared templates can be synced to every licensed user in your company if saved in the \S2winSync\ folder of your S2winPortal.

If your team leader has created a unique version of a template...

then she can save that personalized template in any folder where your team members will easily remember to find it — on your own PC, or any server

Just don't overwrite the original master template with your alternative version.

If your IT Department has created multiple versions of a template to make available for everyone...

then they should edit your Document Storage & Naming Conventions PDF to let everyone know where to find them.

When you upgrade...

There is no rush requirement to immediately update each of the multiple versions of your old template, because old version templates continue to work with the new version of the Systems2win application.

When it becomes a priority, you can look over the new version of the master template to decide whether there are any new features that make it worth the time to update the other version(s).

If yes, then you can recreate your personalized templates, starting with a copy of the new master template, (which will be relatively easy if you correctly used your Customization Log).


Bookmarks = mass, what

Personalization Upgrades

to automatically find and transfer common personalizations every time you upgrade

Personalization Upgrade Transfers

When you own Template Personalization,

then each time that you upgrade your Systems2win templates, you will send us the latest copies of any master template that you have personalized,

so that the Systems2win Personalization Upgrade Utility can search YOUR legacy master templates to automatically find the most common personalizations, and transfer YOUR personalizations to your new upgraded templates.

Why is this valuable?

You can use everything you know about Excel to add your own finishing touches

and then a year later, you can upgrade to get all of the improvements that Systems2win added to their standard templates in the past year

with the confidence that you won't get locked into an old version just because you made a few personalizations.

All of your personalizations are automatically found and transferred

There are two types of personalizations

1) Common personalizations

When you own Template Personalization, then every time that you upgrade, it is a free service for us to run the Personalization Upgrade Transfer Utility to automatically find and transfer your personalizations from unlimited master templates.

See the section below to identify which types of common personalizations will be automatically found and transferred.

2) Uncommon personalizations

Customization Log

If you choose to make any (less common) personalizations that are not automatically found and transferred...

then you will be billed at our standard hourly rate for the time that it takes for us to manually recreate those personalizations in your new master template(s),

so be sure to make a quick entries in your Customization Log to provide helpful guidance for us to quickly find and transfer your uncommon personalizations.

What personalizations will be automatically found and transferred?

You can use everything you know about Excel to personalize any master template or working document

This section clarifies the common personalizations that are automatically found and transferred from your legacy templates to your new master templates each time that you upgrade.

Dropdown Lists

Many Systems2win templates have dropdown lists for faster, more accurate data entry.

You can personalize your dropdown lists on the DV sheet

using simple text, or any of the 3 approaches for dropdown lists for multiple languages.

When you upgrade from any legacy template that has a DV sheet,

the Personalization Upgrade Utility automatically transfers your personalized dropdown lists
(unless the Name of the gold cell at the top of the column ends in "SKIP")

Tip: To be transferred — Link Icons must be in the same cell with Descriptions.

Some old versions used to have a separate column for Links.

Translations and Substitute Phrases

All EXCEL templates (not Word or PowerPoint) use Translation Tables

so that with the click of a button, you can switch to your selected languages.

Even the English-only version can use User Substitutions to replace phrases with your own phrases.

When you upgrade, the Personalization Upgrade Utility will automatically transfer User Substitutions.

If you used any other ways to overwrite text, then be sure to use your Customization Log to remind you to make the same changes to your new upgraded template.

Your own shapes

It's easy to add your own custom shapes to provide your users with easy access to your own palette of icons for safety, tools, equipment, materials, supplies, and anything else that is better communicated through pictures and symbols rather than words. Learn how.

Although you can add custom shapes to any template or working document, the only custom shapes that get automatically transferred when you upgrade are:

1) the custom shapes in the specially marked User-Defined Shapes section of  \S2winPortal\tTemplates\shapes.xlsx.

(Shapes.xlsx is the file that appears when you select Systems2win menu > Copy Shapes. Learn more)

2) custom shapes in your Work Instructions template

Pop-up help for row or column labels

Systems2win's pop-up help already appears whenever you click any row or column header label, and you can supplement (rather than replace) Systems2win's help by inserting Cell Comments that will appear when your users hover their mouse over that row or column header label cell.

User-defined pop-up help is especially popular for language translation and lean healthcare.

When you upgrade, the Personalization Upgrade Utility automatically transfers your Cell Comments.

Tips: If the same row or column header is repeated, then enter your Cell Comments in only the first one.

For Center Across Selection, enter your Cell Comments in the one cell containing the text.

User-Defined Training

The Help sheet of every Systems2win template has a User-Defined Training section

When you upgrade your templates, the Personalization Upgrade Utility automatically transfers your User-Defined Training.

User Help Text Boxes

Use Systems2win menu > Copy Shapes to copy the green-bordered User Help Text Box to any sheet to provide extra help for your users.

For the Personalization Upgrade Utility to automatically transfer your User Help Text Boxes, each User Help Text Box must have a unique name

So... if you only copy 1 User Help Text Box to a sheet, then there is nothing else you need to do. Personalization Upgrade Utility will find it.

If you copy more than 1 User Help Text Box on the same sheet, then you need to rename the text box to "UserHelp2" or "UserHelp3", up to a maximum of 9.

If you group a User Help Text Box with associated Link Icons, then name the grouped shape (using the same naming system)

Links to your own training materials

In addition to the extensive on-line training & videos that come with your Systems2win templates,Link Icons make it easy to add links to your own training materials, which might be any web page or video on the Internet, and might be your own PDF's, Word documents, PowerPoints, intranet pages, videos, etc.

Tips: To avoid reference errors when your links are copied to a new workbook, (such as when upgrading), link only to outside documents and web pages (not places within the same workbook).

When you upgrade, the Systems2win Personalization Upgrade Utility automatically transfers the following special types of Link Icons:

any Link Icon within a Description column of a DV sheet,
any Link Icon within the User Defined Training section of the Help sheet,
any Link Icon grouped with a properly named User Help Text Box

Your Alternate User Interfaces

You can optionally create Alternate User Interfaces for people that speak different languages, or teams or departments that find it easier to have a navigation page custom tailored with links to their own favorite templates and online training.

Your own templates

You can optionally use Systems2win's Sync utility to sync your own (potentially non-Systems2win) templates.

When you upgrade from any legacy portal, the Personalization Upgrade Utility will automatically transfer any templates in your /S2winDontSync/ and S2winSync folder.

Policy documents

In your portal, use CTRL+F to search for keyword "policies" or "policy template".

Each of these policy templates are designed to serve as a starting point for your leaders to create a PDF of YOUR company's policies.

Each time you upgrade, the Personalization Upgrade Utility automatically transfers your StorageNaming.pdf and S2winRequest.pdf files, which are the only PDFs that should be stored in your \S2winPortal\S2winSync\ folder.

Any other policy PDF's that you choose to create are stored outside of your portal, in the same places where you store all of your existing company policy documents.

That's one of the purposes of the Document Storage & Naming Conventions PDF... to let your users know where to find your company's policies.

VC headers

Some templates have a special VC sheet for custom document header fields and default header data.

When you upgrade from templates purchased on or after September 21, 2011, the Personalization Upgrade Utility automatically transfers your personalized VC header data and cell formats.

And for templates released after February 21, 2016, it will automatically hide the same rows that you hid on the VC sheet.

Learn more

Hidden rows and columns
(row heights and column widths)

As of March, 2016, each time you upgrade, your hidden columns will be automatically transferred for the named range rngHideColTransfer, and your hidden rows in rngHideRowTransfer, or rngHideRowTransfer2.

(and there is even backward compatibility to correctly hide rows for some older legacy templates, including Work Instructions, 5S, and Lean Assessment.)

Important: If the number of rows or columns in these ranges are different between the old and new template, then you're on your own to hide the same rows or columns each time you upgrade. It is always best to hide, rather than delete rows and columns within these ranges.

User-defined rows, columns, sheets, charts,
formulas, formats, styles, data, scenarios, etc.

You've got all the power of Microsoft Excel. You can use everything you know (or learn) about Excel to personalize any template in any way.

When you upgrade, the Personalization Upgrade Utility will find and transfer only those common personalizations itemized on this web page,
so to make upgrades easier for any other types of changes, be sure to use your Customization Log to remind you to make the same changes to your new upgraded template.

VBA Macros

Your IT Department will appreciate that your Systems2win templates are delivered with VBA protected to protect against macro viruses, and to prevent unskilled users from messing up their templates trying to record amateur macros.

Your IT Department can, however, simply write to us to request authorization to edit VBA. We will then send written permission, the password, and instructions, so that you've got all the power of Microsoft Visual Basic for Applications to write your own macros.

To make upgrades easier, be sure to use your Customization Log to remind you to copy your custom VBA modules to your new upgraded template, and to test to make sure that your old macros still work correctly with the new template.

Tip: Support for custom macros is not included as part of your standard free technical support.

Language of Systems2win menu & dialogs

You can change the language of the Systems2win menu and most dialog windows by selecting Excel > Systems2win menu > Setup > Select Language for Menu & Dialogs.

When you upgrade, it will revert back to English, so you will need to do this again.

Revision Log

With the click of a button (in the RevLog template), you can add a Revision Log sheet to ANY Systems2win template

When you upgrade, the Personalization Upgrade Utility ignores the RevLog sheet, so to make upgrades easier, be sure to make a quick entry in your Customization Log to remind you to make the same changes to your new upgraded template.

Word templates

You've got all the power of Microsoft Word. You can use everything you know (or learn) about Word to personalize any template in any way.

When you upgrade, the Personalization Upgrade Utility ignores Word templates, so to make upgrades easier, be sure to make a quick entry in your Customization Log to remind you to make the same changes to your new upgraded template.

PowerPoint templates

You've got all the power of Microsoft PowerPoint. You can use everything you know about PowerPoint to personalize any template in any way.

When you upgrade, the Personalization Upgrade Utility ignores PowerPoint templates, so to make upgrades easier, be sure to make a quick entry in your Customization Log to remind you to make the same changes to your new upgraded template.

Blank template

New master templates that you create using your Systems2win blank templates will be automatically found and transferred when they are found when they are stored in your S2winSync folder.

Your Systems2win portal

You can optionally personalize your Systems2win portal, but it is rarely necessary.

If you make any changes to any other portal page, be sure to make a quick entry in your Customization Log to remind you to make similar changes to your new upgraded portal.

Remove templates

You can easily remove templates you don't want to distribute to your users.

Add your own templates

You can easily add your own Word and Excel templates developed either in-house or by any of your consultants.

Personalize your Systems2win templates

Why start from scratch?
when it's so easy to start with already-excellent templates that you can so easily personalize

Why lock yourself into an old version?
when you can so easily transfer your personalizations to templates that continuously improve


coach

Private Coaching

You can optionally hire Systems2win to help you:

  1. Design your personalizations to migrate as easily as possible for future upgrades
  2. Actually do it — personalize your first templates together
Private training

Private Training and Services

 

 
 

 

 

 

Upgrade

Before you personalize, make sure that you are starting with the latest version

 

 

 

 

 

Sabotage

You already knew about Systems2win?

Why didn't you tell me?

 

 

Tell a Colleague
share