How to personalize
your Systems2win templates
with all the power and familiarity of Microsoft Excel
Table of Contents
In addition to the training videos found on this page, you can also watch the pre-recorded training session
Bookmark = HowTo
How to personalize
with all the power and familiarity of Microsoft Excel
Understanding the difference between
How to personalize a working document
created from a Systems2win template
- 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)
- Unhide any hidden worksheets that you want to edit
such as the Template sheet, the CLog sheet, or the TT sheet for translations
- If the worksheet is protected... (most aren't)
then Unprotect the worksheet
Personalize it ...
Excel training can be helpful
Additional tips to personalize your:
Assessment templates (such as 5S and Lean Assessment)
Frequency Codes for your Standard Work template
How to personalize a master template
Video: How to personalize
a master template
If you are replacing the MASTER template...
(that everyone shares... not just your own working document)
- 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...
- Save it in the exact condition that it will be when your users open it...
Select the upper left corner of each worksheet so the cursor will be in the right place when your user opens it
Reprotect any worksheets that you unprotected
Rehide any worksheets that you unhid
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
- Final review
Email to Systems2win your (almost-completed) personalized template for final review
- 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.
Bookmark = email
- 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.
And then use your Training Matrix to ensure that each user confirms success.
Got a good idea?
If you believe that your proposed way of doing things might be better for many organizations,
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.
Bookmark = text
Video: How to personalize text
How to personalize text
For templates version 8 or higher (that have translation tables) —
there are 4 optional ways to replace words with your own:
|1) Simple text||
Just type words in the cell.
You might overwrite a formula linking to the Translation Table. That's okay.
Usually best choice for editing your own working document
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
2) User Substitutions
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.
Usually 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
Also a good choice when editing your own working document — if you need multiple languages or substitute phrases.
|3) User-Defined section of TT worksheet||
Enter your desired phrase in the User-Defined Translations section at the bottom of the TT worksheet
Type the equals sign (=)
Or better yet, click the F4 key before clicking the Enter key — to make it an absolute cell reference.
Optionally see more tips for how to Link Cells.
Best choice for editing a master template — if there is no existing phrase.
If there is an existing phrase, then it is better to use User Substitutions.
Each time that you upgrade...
Do NOT use Link Cells to link to phrases within the String Translations sections of the TT worksheet — which must be linked using VLOOKUP.
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 worksheet.
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.
Usually, your text boxes will consist of simple text in a single language...
If you want your text boxes to switch between languages...
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 worksheet
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.
Bookmarks = mass, what
Personalization Upgrade Utility
to automatically find and transfer common personalizations every time you upgrade
What is it?
When you upgrade your templates — the (optional) Systems2win Personalization Upgrade Utility
When you own the optional Personalization Synchronization feature,
Why is it valuable?
You can use everything you know about Excel to add your own finishing touches —
What can be personalized?
You can use everything you know about Excel
to personalize any master template or working document
This table clarifies the common personalizations that are automatically found and transferred from your legacy templates to your new master templates each time that you upgrade.
For any (less common) personalizations that are not automatically transferred...
you should make a quick entry in your Customization Log
to remind you to manually make the same changes to your new templates.
Many Systems2win templates have dropdown lists for faster, more accurate data entry.
You can personalize your dropdown lists on the DV worksheet
When you upgrade from any legacy template that has a DV worksheet,
Tip: To be transferred — Link Icons must be in the same cell with Descriptions.
|Translations and Substitute Phrases
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.
|Your own shapes
It's easy to add your own custom shapes to provide your users with easy access to
Although you can add custom shapes to any template or working document,
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,
When you upgrade, the Personalization Upgrade Utility automatically transfers your Cell Comments.
Tips: If the same row or column header is repeated — enter your Cell Comments in only the first one.
The Help worksheet of every Systems2win template has a User-Defined Training section
When you upgrade from templates,
|User Help Text Boxes
Use Systems2win menu > Copy Shapes to copy the green-bordered User Help Text Box to any worksheet
For the Personalization Upgrade Utility to automatically transfer your User Help Text Boxes,
So... if you only copy 1 User Help Text Box to a worksheet,
If you copy more than 1 User Help Text Box on the same worksheet
If you group a User Help Text Box with associated Link Icons,
|Links to your own training materials
In addition to the extensive on-line training & videos that come with your Systems2win templates,
Tips: To avoid reference errors when your links are copied to a new workbook, (such as when upgrading),
When you upgrade, the Systems2win Personalization Upgrade Utility
|Your Alternate User Interfaces
You can optionally create Alternate User Interfaces for people that speak different languages,
|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
In your portal, use CTRL+F to search for keyword "policies" or "policy template".
Each time you upgrade,
the Personalization Upgrade Utility automatically transfers
Any other policy PDF's that you choose to create are stored outside of your portal —
That's one of the purposes of the Document Storage & Naming Conventions PDF...
Some templates have a special VC worksheet for custom document header fields and default header data.
When you upgrade from templates purchased on or after September 21, 2011,
And for templates released after February 21, 2016,
|Hidden rows and columns
(row heights and column widths)
As of March, 2016, each time you upgrade,
(and there is even backward compatibility to correctly hide rows for some older legacy templates,
Important: If the number of rows or columns in these ranges are different between the old and new template,
|User-defined rows, columns, worksheets, charts,
formulas, formats, styles, data, scenarios, etc.
You've got all the power of Microsoft Excel.
When you upgrade, the Personalization Upgrade Utility
Your IT Department will appreciate that your Systems2win templates are delivered with VBA protected —
Your IT Department can, however, simply write to us to request authorization to edit VBA.
To make upgrades easier, be sure to use your Customization Log
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
When you upgrade, it will revert back to English, so you will need to do this again.
With the click of a button (in the RevLog template),
When you upgrade, the Personalization Upgrade Utility ignores the RevLog worksheet,
You've got all the power of Microsoft Word.
When you upgrade, the Personalization Upgrade Utility ignores Word templates,
You've got all the power of Microsoft PowerPoint.
When you upgrade, the Personalization Upgrade Utility ignores PowerPoint templates,
|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,
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
|Personalize your Systems2win templates
Why start from scratch?
Why lock yourself into an old version?
Bookmark = blank
Rather than starting with a blank Excel screen and a blinking cursor...
your Systems2win templates come with some templates
specifically designed to serve as a starting point
for creating your own custom process improvement tools
with all the familiar...
common to every Systems2win template
- A Help sheet that is always organized the exact same way,
so your users can quickly find the help and training they need
to use your new custom tool
- A DV sheet that makes it easy to define your own dropdown lists
- The Open a Blank Sheet button
so your users always have a fresh, clean blank template available —
and can't mess up the master sheet
- The Translations and Substitutions button
that your users already know how to use
and allows you to easily create custom templates
with multiple variations and/or language translations
- Pre-defined A_Filter data range
so your users have that familiar feature that makes Filter and Sort so much easier to use
- And so many more familiar features —
so when your users learn to use one Systems2win template;
they know how to use ANY Systems2win template
AND they know how to use your custom template.
How to create your own custom template
Before you start...
1) Learn what you're doing
Study the teachings on this training web page —
and watch the pre-recorded webinar for How to Personalize Your Systems2win Templates
Design a rough draft of your custom template on paper first.
Perhaps get ideas from your many other Systems2win templates
and if you find one that is close to what you want,
perhaps use a copy of THAT template as the starting point for your new custom tool.
Otherwise, start with either the Blank template, or the Checklist template
which are both designed to give you a good starting point
to create your own custom tools
Phase 1) Get Started
Save your new document to a temporary location.
Unhide the Template sheet.
Phase 2) Personalize Your Headers
Personalize the header fields at the top of the document
Perhaps insert rows, or hide unused rows.
Personalize the (gold) column headers
Perhaps replace existing headers with different words.
You can either simply replace VLOOKUP formulas with simple words,
or use User Substitutions to support language translations in your custom template.
Perhaps insert new columns.
Perhaps use Freeze Panes to keep your headers visible as you scroll.
Personalize row headers for document sections
The easiest way is to copy rows that have pre-formatted section headings.
Tip: Perhaps keep the pre-formatted section heading rows near the bottom of your working document —
and simply hide those rows when you're ready to publish.
Phase 3) Personalize Your Data Input Cells
Use everything you know about familiar Microsoft Excel
(and search our online Excel training for anything you don't fully remember)
Perhaps change cell formats
Perhaps personalize dropdown lists (on the DV sheet)
Perhaps personalize conditional formatting
Phase 4) Personalize Anything Else
Phase 5) Provide User Instructions
On the Help sheet of your custom template...
(which is always organized the exact same way,
so that your users know how to find help for your custom template
the exact same way they find help for every other Systems2win template...)
- Add your Instructions to use THIS Template
Major Steps in bold
Key Points in red
- Perhaps use Link Icons
to link to relevant training (your own, or on the Internet)
and/or Suggested Reading
- Edit the Excel Tips Most Valuable for THIS Template
removing tips that are not essential for using YOUR template
Tip: Before you delete a row — first delete the shapes in that row
(so that those shapes don't just get scrunched)
On the Template sheet of your custom template...
- Perhaps use Comments to provide pop-up help
for row and column headers
Phase 6) Do final cleanup
- Reprotect any sheets that you unprotected
- Leave the cursor in the upper left corner of each sheet
(because when your user opens your workbook — the cursor will be wherever you left it)
- Rehide and sheets that were unhidden
- Save your template to its permanent location
To Sync your template using the Systems2win Sync utility, save your template in the /S2winSync/ subfolder in the master /S2winPortal/ folder on your company's shared server.
Phase 7) Teach your users to use your new tool
Show your users where to find your custom template
Provide personalized training for how to use your template
Use your Training Matrix template
to identify who needs what training, and to track their progress.
Bookmark = BlankWord
Blank Word Template
Your Systems2win templates also come with a blank Word template,
to make it easy to create your own custom templates
with all the useful standard features
that have become so familiar and comfortable
with all of your other Systems2win Word templates.
Bookmark = upgrade
Each time you upgrade
Here's how your personalizations are kept up to date
If the 'Personalized' field on the 'CLog' sheet of any master template = TRUE,
then the next time you upgrade - the 'Upgrade Log' sheet in your MultiUserChecklist.xlsx workbook will remind you to visit the TT sheet for that master template to make manual changes to your upgraded template (if needed).
When you upgrade
the entire 'User-Defined' section (at the bottom of the TT sheet) is automatically copied to your new template —
but don't over-use this feature, because you will need to use Excel's 'Formula Auditing' on your legacy template to identify incoming links from other sheets, then manually recreate those links in your new master template.
Tip: Incoming links from your DV sheet will transfer. but links from other sheets won't.
Update your 'User Substitutions' for any rows that have been 'Revised' since the date of your previous most recent purchase or upgrade.
Bookmark = coach
Personalization Upgrade Coaching
If you have made a substantial number of complex personalizations,
then you can hire Systems2win to personally help you:
- Ensure that all of your personalizations are correctly migrated
- Design your personalizations to migrate as easily as possible for future upgrades
Contact us to learn more
Bookmark = 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 the Portal 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 laptop —
so they have your personalized templates even when they are working at home or at a customer site,
and don't have access to your company network.
Bookmark = 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
Version 14 thru v15 R161205...
If you want to sync your own templates, it's worth it to upgrade.
Contact Systems2win to lay out an upgrade plan.
Version 13 and lower...
Excel > Systems2win menu > Local Computer > S2winSync folder
Can we choose to NOT distribute some of the Systems2win templates that we own?
Bookmark = MultipleVersions
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.
That's up to your team leaders and/or your IT Department.
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.
Tip: If your IT Department has chosen one of the Portal Setup options that supports the Systems2win Sync utility, then the multiple versions of your templates can be synced to every licensed user in your company if saved in the \S2winSync\ folder of your S2winPortal.
When you upgrade...
There is no rush requirement to immediately update each of the multiple versions of your old template. 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 — and 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.
(Tip: This will be easier if you used your Customization Log)