Systems2win

Training & Education

Search
Training & Demos home page

 

Excel Drawings Portal PDF

Excel Online Training

Excel training, excel videos, and tips for getting the most from your Systems2win Excel templates

Note: These tips are intended to supplement (rather than replace) the excellent online help that comes with Microsoft Excel.

Color conventions: Instructions for Excel 97-2003 are in green. Instructions for Excel 2007 follow in orange (only if different).

Table of Contents

bulletWorking with Worksheets & Templates
bulletDeleting, Hiding, Inserting, Copying, & Moving
bulletEditing
bulletCalculations
bulletPrinting
bulletContinuous Improvement

Search

Use Ctrl+F to find keywords
Additional Search Tips 

Working with Worksheets and Templates

Overview of training that comes with every Systems2win Excel template

Refer to how to use the Systems2win Sample and Help pages.

Systems2win menu

The Systems2win menu appears whenever you open Excel or Word with Systems2win version 5 or higher installed.


Excel 2000, XP, or 2003:
the Systems2win menu
is in the Command Bar
at the top of the window
(next to Help)

Office 2007: The Systems2win menu is found in the Add-ins ribbon tab,
which appears whenever any Excel Add-in is installed.

How to find and open your Systems2win templates

Alternative ways to find and open your Systems2win templates.

How to make Office 2007 easier to use

How to make Office 2007 easier to use  (4 min training video)

Personalize your Quick Access Toolbar.
How?
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
Notice that you have the option to Choose commands from: Commands not in the Ribbon

At the extreme minimum, we HIGHLY recommend adding at least the following to your Quick Access Toolbar:

  1. The Select Objects arrow - which makes it far easier to select drawing objects.
    Home tab > Find and Select > Select Objects
  2. The Systems2win menu
    Add-ins > Systems2win menu

And while we're on the subject of making Office 2007 easier to use...
also see Known Issues for Office 2007

To avoid overwriting your master templates by mistake

Save your working documents anywhere BUT in your S2winPortal folder.
Hopefully, your IT Department followed the portal setup instructions to properly set up your portal so that most users only have read permissions - so that they don't accidentally overwrite your master templates with a working document.

To overwrite a master template (on purpose)

How to personalize your Systems2win templates.

User-defined help textboxes

All Systems2win Excel templates have green-bordered help textboxes,
which you can personalize for your own user-defined help.

Whenever you personalize a master Systems2win template, remember to
make an entry in your Customization Log to make upgrades easy.

 

To copy a worksheet

Do NOT use the traditional way of copying a sheet.
Use the special Systems2win utility to copy a sheet.

Either:

bulletSystems2win menu > Copy Sheet, or
bulletAlt+F8 > S2W_CopySheet

 

Tip: If Copy Sheet does not appear in your Systems2win menu,
you can always upgrade your Systems2win Add-in for free at any time
or maybe its time to upgrade your templates

Tip: If you get the HasFormula dialog box (pictured below),
it means that you didn't use the special Systems2win Copy Sheet utility, but HasFormula will still work just fine when you click Yes.

 

The traditional way to copy a worksheet

(which is NOT advised. Instead use the special Systems2win Copy Sheet utilities described above)

Right-click the worksheet tab
(at the bottom of the worksheet that you want to copy)
and select Move or Copy

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

 

 

Tip: If you are using Excel 2003 or lower, and you are working in a template that has a DV worksheet, and you already copied your worksheet incorrectly, and don't want to lose your data...
then you need to delete the local named ranges that Excel copied incorrectly:
bulletSave a backup copy of your workbook
bulletWith the copied worksheet selected...
(the one with the problem - usually a copy of the Template worksheet)
bulletSelect Insert > Name > Define
bulletSelect any name that has a Sheet name associated with it
(local Names have a Sheet name in the column on the right, and global Names don't)
bulletIf it Refers to an error, Delete the local Name
(Notice that as soon as you Delete the local Name, the global Name will appear. Don't delete the global Name.)
bulletRepeat for all local Names that refer to an error
bulletSave your work
and call your mother to celebrate your burgeoning Excel programming skills

 

 

To rename a worksheet

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

Workbook and Worksheet naming conventions: You can use the User Training Section on the Help worksheet, or a green-border User Help Textbox to explain your company's unique naming conventions - to give your user's guidance for where to store their working documents, and what to name the workbooks and worksheets that they create from a Systems2win template.

If the rename menu item is greyed out - use our FixRename utility.

View Zoom (to make anything easier to read)

View > Zoom

Excel 2007 bug: Shapes sometimes appear zoomed to 100% when rest of page has different zoom.
Solution: View > Zoom > 100%.
Then View > Zoom > to the percent that you want.
The shapes will now be sized correctly.  (See full list of Excel 2007 bugs)

To Protect or Unprotect a worksheet

Tools > Protection > Protect Sheet
Excel 2007: 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 worksheet with a password. (Or just protect it without a password.)

Tip: Most Systems2win Excel templates are delivered unprotected. Some worksheets are protected without a password, and can be easily unprotected. Those sections of Excel templates that are password-secured are intended to remain locked (in order to protect you from accidentally messing up formulas). If you believe you have a need to edit a password-secured section of an Excel template, please contact us.

To Unhide or Hide a worksheet

Format > Sheet > Unhide or Hide
Excel 2007: Home > Format > Hide & Unhide > Unhide worksheet or Hide Worksheet

Tip: There is a third type of worksheet, called Very Hidden, which is unaffected by the regular Unhide command. Any Systems2win worksheet that is Very Hidden is intended to remain that way.

Version Control

Some of the more sophisticated Systems2win templates have a VC worksheet for Version Control.

Version Control  (3 min training video)

Deleting, Hiding, Inserting, Copying, & Moving

Training video: Excel Editing   Power user tips to select, delete, hide, insert, copy, and move cells   (9 min excel training video)

To select entire rows or columns (not just a few cells)

Hover your mouse over the gray edge of the worksheet (where the row numbers or column letters are) until your mouse 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 right when the entire range of rows or columns turns blue.

To delete entire rows or columns

Select the rows or columns, then Edit > Delete
Excel 2007: Home tab > Delete cells (Ctrl+-)

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

Caution: Edit > Delete (Home tab > Delete cells) should only be used to delete entire rows or columns, (not individual cells), and is very different from Edit > Clear > Contents (Home tab> Clear > Clear Contents) or the keyboard Delete key, which are used for deleting the contents within cells (while leaving the empty cells).

To delete just the contents of a cell

Use the keyboard Delete key
(The keyboard Delete key is the shortcut for Edit > Clear > Contents or Excel 2007 Home tab> Clear > Clear Contents)

Caution: The Delete key won't delete cell formats, but will delete any unprotected formulas (so be careful not to accidentally delete formulas).

Caution: The Delete key is very different from Edit > Delete, which should only be used to delete an entire row or column.

How to avoid accidentally deleting your custom formulas

1) Keep your formulas separate from your data - by either using your Custom Formula Safe Zone (in the lower right quadrant of every Systems2win template worksheet), or by storing your custom formulas on a separate worksheet.

2) Highlight cells containing formulas

Hide rows or columns

Select the columns or rows to hide, then right-click within your blue selected area, and select Hide.

Or you can use Format > Column or Row > Hide
Or in Office 2007, you can use the keyboard shortcuts found in Home tab > Format Cells > Hide & Unhide

The Value Stream Mapping templates come with a special button to make it even easier to quickly Hide/Unhide Rows as needed.
Training video: How to use the Hide/Unhide Rows button (1 minute)

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

Tip: As an alternative to hiding rows and columns, you might experiment with resizing a column or row size to be 0.1 (Format > Column > Width). This will effectively hide a row or column while still including its data within a chart.

Unhide rows or columns

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

Or you can use Format > Column or Row > Unhide
Or in Office 2007, you can use the keyboard shortcuts found in Home tab > Format Cells > Hide & Unhide

Tip: Many (most) Systems2win templates come with pre-formatted hidden rows and columns (because it is 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.

Tip: For value stream mapping templates, we suggest that you save your As Is worksheet with every other pair of columns hidden - so that you always have a fresh Process Box that you can unhide between any other 2 processes.

 

AutoFilter (to filter your data in useful ways)

AutoFilter allows you to filter for only rows of data meeting your specified criteria.

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)
Then select  Data > Filter > AutoFilter. (Excel 2007: Data tab > Filter)
Drop-down arrows will appear on each column header.
Use any drop-down menu to filter your data.

Tip: Sort requires a "list". Any Systems2win template that suggests to use Sort has already been defined as a list. When creating your own lists, refer to Excel's online help for tips on how to define a list. (Which will quickly increase your appreciation for just how much time & confusion gets saved by using your Systems2win templates.) If you ignore our advice - and use regular Insert > Rows instead of copying pre-formatted blank rows - then you might need to copy one of the thin gold vertical line cells to all of the other gold cells in that column - because all of those cells need to contain data in order for Sort or AutoFilter to work correctly.

Tip: Do not use AutoFilter to sort your data. Use Sort instead.

Tip: If you need even more sophisticated filtering,
experiment with Data > Filter > Advanced Filter. (Data tab> Advanced Filter)

To Insert rows or columns    (do NOT use Insert > Rows)

Important Tip: Rather than using Insert > Rows or Columns, (Excel 2007: Home tab > Insert > Insert Sheet Rows or Columns),
it is almost always better to copy similar pre-formatted blank rows or columns - thereby duplicating ALL formatting and formulas.

To copy entire rows or columns - use Insert Copied Cells or Shift Down and Copy
(This is probably the single most important Excel skill you will learn)

Tip: Although Trial versions and Sample worksheets don't allow rows or columns to be copied, you can still practice these skills on any unprotected worksheet.

Option 1) Insert Copied Cells     9-minute excel training video (turn on sound)

  1. Select ENTIRE rows or columns (not just a few cells).
  2. Ctrl+C (or your favorite way of copying)
  3. Select the cell in column A of the row just below (or in row 1 just to the left of) where you want to copy
  4. Insert > Copied cells
    Excel 2007: Home tab > Insert > Insert Copied Cells (Ctrl+Shift+=)
Option 2) Shift Down and Copy
  1. Select ENTIRE rows or columns (not just a few cells).
  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 rows or columns until your mouse turns into
    a four-sided cross-arrows
    (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.

To move entire rows or columns - use Shift Down and Move

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

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

Option 1) Simple Copy (Ctrl+C), then Paste (Ctrl+V)

If the source and destination color schemes, styles, formulas, data validation, cell comments, conditional formatting, and other hidden elements are all identical...
then simple copy Ctrl+C and paste Ctrl+V will work just fine.

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 icon (that appears right after you paste anything in Excel 2002 or higher)
and/or Edit > Paste Special
Excel 2007: Home tab > Paste > Paste Special

 

Tip: Version 5.1 and higher uses a color scheme that is different from earlier versions.

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
(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).
For reasons explained below, we don't recommend using "drag n drop" to move cells, because it can mess up your formulas.

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

If you use "cut & paste",  your formulas and/or cell formats might get messed up. Unless you are certain that the cell you are moving is not referenced in any formula, we suggest using the following method - which is always safer:

  1. Copy (Ctrl+C)
  2. Either Paste (Ctrl+V)
    or Edit > Paste Special (Home tab > Paste > Paste Special)
  3. Then go back to delete the unwanted data - using the keyboard Delete key

To copy as picture

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

Highlight the cells that you wish to copy, then:
hold down the Shift key as you select Edit > Copy Picture
or 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.s

Microsoft humor?
Yes... in Office 2007, this special Copy command is hidden beneath the Paste menu. You know... just like the Shut Down command is hidden beneath the Start menu...

To save a screen shot as an image file

Use Print Screen.

To paste into PowerPoint or Word

After selecting cells, and using regular Copy (Ctrl+C)...
Experiment with the different options in Edit > Paste Special. (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, (which isn't always true when you use regular "paste")

 

Editing in Excel

Free application viewers

Free application viewers allow you to view, but not edit.

Undo

Keyboard shortcut: Ctrl+Z
Edit > Undo

To enter a "paragraph" within a cell

Alt+Enter.

Tab indentation within a cell

Ctrl+Alt+Tab

Or use the Increase Indent and Decrease Indent buttons on the Formatting toolbar.

Styles

Format > Style
Excel 2007: Home tab > Cell Styles

If a pre-formatted style is available, it is almost always advisable to format cells using Styles (rather than manual formatting) for the following reasons:

  1. When you select Systems2win menu > Highlight Cells > Unhighlight Cells, the background color of the cell will return to the default background color for the style.
  2. If you simply change the background color of a Thin Gold Line,
    (rather than applying the style 2winThinHorizontal or 2winThinVert),
    then Reset Thin Gold Lines, AutoFilter, and Sort might not work correctly.
  3. Once you spend 1 minute to learn how to do it, applying styles is simply easier than manual formatting.

Many styles influence only 1 or 2 aspects of the cell formatting. (Maybe just the background color, or maybe just the font size and color...)
(You can always tell what a style does and does not affect by selecting a cell formatted with the style, and then selecting Format > Style or Excel 2007: Home tab > Cell Styles > right-click a Style > Modify)
So after applying a style, you might want to then also apply some additional manual formatting.

Systems2win has provided many preformatted styles to choose from.
Systems2win menu > Styles provides extensive help for using Styles, and a convenient way to simply copy & paste the style you want.
(The menu help was added in December 2008, but pre-formatted styles have been provided for many years.)

Time-saving Tip: In Excel 2003 or lower, you can easily add a Styles drop-down list to your Formatting Toolbar. First, make sure that your Formatting toolbar is visible: View > Toolbars > Formatting. Then select Toolbar Options (the little arrow at the far right of the Formatting Toolbar) > Add or Remove Buttons > Customize > Commands tab > Format > Style

Borders

Format > Cells > Border
Or Formatting toolbar > Borders
Excel 2007: 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.

To Merge cells

Format > Cells > Alignment > Merge Cells
Excel 2007: 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.

Before merging horizontal cells, always first consider using Format > Cells > Alignment tab > Horizontal > Center Across Selection
Excel 2007: Home tab > Merge & Center > Merge & Center

Using Link Icons for hyperlinks


Excel or Word 2007: Add-ins > Systems2win menu > Insert Link Icon

Excel or Word 2003 or lower:
Systems2win menu > Insert Link Icon

   If you are using Excel...

The active worksheet must allow images to be edited
(You cannot Insert Link Icon on a Sample or Help worksheet)

If you are using Word...

The Word document must be unprotected

If you are using a Trial version...

Only the first row of Link Icons will be active

Then select the Link Icon that you just inserted, and
 Ctrl+K or right-click > Hyperlink
to bring up the Insert Hyperlink dialog window,
where you can Link to:
  1. Any Existing File or Web Page
  2. Any Place in This Document
    by typing the cell number,
    or by selecting a named cell reference
    (which you can create using a bookmark Ctrl+G)
    Tip: Use the "A" Link Icon when linking to a place in this document.
    ("A" stands for "anchor" - which is what a bookmark is called in HTML)

Follow all tips to avoid broken links


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

 

HUGE TIP: Turn off the annoying "sea of blue underline text"
that Excel auto-generates whenever you enter a URL in a cell.
And use our guidelines for how to create far more professional looking URL's.

AND... learn how to make active hyperlinks in your PDF's !!!

 

If you don't follow our advice...
and still let Microsoft generate those annoying seas of blue underline text, then here are a couple tips...

bulletTo select a cell containing a hyperlink, (without following the link), either:
a) Hold down your left mouse button for a couple seconds when selecting the cell, or...
b) Select a nearby cell, and then use your arrow keys to select the cell containing the link
bulletWhen you delete the contents of a cell that contained a hyperlink - the cell format will sometimes still have the blue underline hyperlink format. To restore the default cell format - copy and paste a similar cell.

 

How to reset picture size

Many shapes (including Link shapes) are set to move and size with cells. One big advantage is that these shapes are hidden when you hide a row or column, and reappear when the row or column is again unhidden. Prior to version 5, however, it was easy for a shape's size to become distorted.

To reset the size of a picture or shape,
right-click > Format Picture > Size tab
or Excel 2007:  right-click > Size and Properties > Size tab
then ensure that the checkboxes for "Lock Aspect Ratio" and "Relative To Original Picture Size" are unchecked before you click the Reset button.

How to resize picture

Right-click the picture or shape > Format Picture > Size tab
or Excel 2007:  right-click > Size and Properties > Size tab

Tips to avoid broken links

Every time that you create a working document (by starting with a copy of a blank Systems2win template), you will need to know...

“Where should I save my working document?” and "What should I name it?"

The answer to these questions are especially important because…
within your working documents, you might sometimes add hyperlinks to other related documents, and those hyperlinks can easily become broken if you later change filenames and/or folder locations - a situation that can be easily avoided if you follow our simple guidelines for how to avoid broken links.

Tips to avoid the need to change the names of folder paths, workbooks, and worksheets

  1. Your IT Dept should provide guidelines for where to store different types of working documents in well-organized folders on your shared network.
    (Ideally these guidelines are written, and stored where people can easily find them.)

     
  2. The Help page of every Systems2win Excel template has a User-Defined Training section,  which can be used to define your workbook and worksheet naming conventions for that template.

Common naming convention for workbooks...
    templatename + "_product, project, or team name"
Examples:
   FlowChart_product123.xls, gantt_projectABC.xls

For archived expired workbooks...
   Append the workbook name with the expiration date in YYMMDD format
Example:
   OrgChart_AccountingDept_081225

Common naming convention for worksheets... (the tabs within a workbook)
   Development status
Examples:
   AsIs, AsWas, ToBe, Future1, Future 2, Released, Expired_YYMMDD…

 

 

The easiest way to avoid broken links is to use tip #1 (below)
keep all related documents in the same folder
(or a sub-folder that will always retain its position relative to folders containing other related documents).
If you keep all related documents in the same folder, you can even move the entire folder, and all links will still work.

Follow these same tips when you insert a hyperlink or link cells with the value from another cell.

  1. If linking to another document that will always be in the same folder with this workbook -
    (or a sub-folder that will always retain its position relative to folders containing other related documents)
    Just browse and select the workbook (or the target cell or worksheet within the workbook)
  2. If linking to a document that will NOT always be in the same folder with this workbook -
    (or a sub-folder that will NOT always retain its position relative to folders containing other related documents)
    Specify the full pathname (e.g. starting with http:// or c:\)
  3. If linking to a cell or worksheet within the same workbook -
    Use the Bookmark button in the Insert Hyperlink window

If you need to change the name of a folder path, workbook, worksheet, or named celll that has links to it...

bulletIf all linked workbooks are open when the names are changed, then the hyperlinks will be updated automatically.
bulletIf you use File > Save As - all incoming links will be changed to link to the new file, rather than the original.
(If this is not what you want, then use My Computer to copy and rename the file - rather than using File > Save As.)

Cell Comments

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

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.
Or better yet, experiment with using Data Validation rather than Comments.

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

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)

and then complete the information asked within Data > Sort.

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

Tip: Sort requires a "list". Any Systems2win template that suggests to use Sort has already been defined as a list. When creating your own lists, refer to Excel's online help for tips on how to define a list. (Which will quickly increase your appreciation for just how much time & confusion gets saved by using your Systems2win templates.) If you ignore our advice - and use regular Insert > Rows instead of copying pre-formatted blank rows - then you might need to copy one of the thin gold vertical line cells to all of the other gold cells in that column - because all of those cells need to contain data in order for Sort or AutoFilter to work correctly.

Tip: Don't use AutoFilter for sorting.

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

Keyboard Shortcuts

Help > Microsoft Office Help > Accessibility in Excel > Keyboard Shortcuts

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

Split Window or Freeze Panes (to always have column or row labels visible as you scroll)

Position your cursor where you want the panes to be frozen, then select Window > Split or Window > Freeze Panes
Excel 2007: View > Split or View > Freeze Panes 

Excel drawings

Tips for working with Microsoft Office drawings

Language translation

Tips for language translation

Calculations

Highlight cells containing formulas (to avoid accidentally overwriting formulas)

Option 1) Right-click any blank cell. Select Highlight > Cells containing formulas.
(Prior to November 2008, it used to only highlight unlocked cells containing formulas. Now it highlights all cells containing formulas.)

Tip: When you Unhighlight Cells, cell background color will revert to the default color defined by the style.
So rather than simply overwriting the background color of a cell, you should use styles.

Option 2) Use styles to change the background color of cells containing formulas

Option 3) Highlight cells containing formulas - using HasFormula

You can add HasFormula to any additional cells by following the instructions for conditional formatting, and entering the following formula:  =HasFormula

Cells containing a formula will be highlighted with the background color of your choice.

Tip: Any cell with with both:
a) The HasFormula conditional formatting, and
b) Hidden protection (Format > Cells > Protection tab > Hidden, or Home tab > Format Cells > Format Cells > Protection tab > Hidden)
will be conditionally formatted as if it contained a formula

Tip: In version 5 or lower, HasFormula used to be named S2winFormula.
Tip: HasFormula only works in the English version of Excel - and any other languages that use R1C1 cell naming (unlike German, for example which uses S1Z1 cell naming) 

If you accidentally delete a formula

You can often repair it by simply copying a similar cell.

Conditional Formatting

  1. Select cells to which you want to apply the conditional formatting.
  2. Format > Conditional Formatting
    Excel 2007: Home tab > Conditional Formatting
  3. If your condition is a formula, select Formula Is from the drop-down list
    Excel 2007: 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

How to link to a cell to auto-update with the value from another cell

Option 1)

  1. Copy the cell (Ctrl+C)
  2. Edit > Paste Special > Paste Link
    Excel 2007: Home tab > Paste > Paste Special > Paste Link

Option 2)

  1. Type the equal sign (=) into the cell that you want to auto-update
  2. Then simply click on the cell that you want to auto-update the value from, and Enter

To link a range of cells
Follow the above instructions to create a link in the first cell.
Then edit the formula to remove the $ symbols. (To understand what the $ symbol does, refer to Excel's online help for "citations".)
Then copy the formula (with $ symbols removed) to the other cells in the range.

To link to a cell in another workbook, just click in the cell in that open workbook. 
Important: Tips to avoid broken links

To link to a named cell
Insert > Name > Paste
Excel 2007: Formulas > Name Manager > Use In Formula

Training video: The Pareto training video succinctly illustrates how to link cells. (4 mins)

Caution: If your source workbook uses scenarios, then your linked data will only be correct when the the source workbook happens to be displaying the correct scenario.

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

How to define cell Names  (aka cell references)

On an unlocked worksheet...

Insert > Name > Define
Excel 2007: Formulas > Define Name

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.
Example: Template!MyName

Caution: Be careful not to delete or change any cell Names defined by Systems2win!
Technical support for trouble-shooting custom formulas and/or accidentally-deleted Systems2win named variables is available at our regular hourly rate. Always keep a backup before you start writing your own custom formulas and Names.

How to use a "thin gold line" to keep your ranges intact

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 or sub-totals? (Ouch...)

A simple way to avoid this is to use a "thin gold line" above and below your range of cells, and then set your totals 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: Format thin gold lines by applying the pre-formatted Styles: 2winThinHorizontal and 2winThinVert.
If you simply change the background color, Reset Thin Gold Lines, AutoFilter, and Sort might not work correctly.

Reset Thin Gold Lines

Systems2win menu > Reset Thin 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 AutoFilter or Sort, they will simply magically work - even when an seemingly identical-looking table in your own home-made spreadsheet won't work)

Data validation

Experiment with using Data > Validation.
Excel 2007: Data > Data Validation.

In addition to its more traditional uses...
Data Validation can be used to to create pop-up help messages that appear when someone clicks on a cell.
How? On the Settings tab > Allow any value. On the Input Message tab > enter your help message.

Drop-down data validation

Some Systems2win templates feature a DV worksheet for drop-down data validation. Training video  (1 min)

If using Excel 2003 or lower - see known issues for using drop-down data validation lists on a copied worksheet.

To allow only drop-down choices:
Check the box for Data > Validation > Error Alert tab > Show alert after invalid data is entered
Excel 2007: Data > Data Validation > Data Validation > Error Alert tab > Show alert after invalid data is entered
To allow the user the choice of making a drop-down choice or entering other data:
Uncheck this box

Manual vs. Automatic Calculation

All Systems2win templates should be set to calculate automatically.

Tools > Options > Calculation tab > Automatic

Tip: 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.

Recalculate

F9 calculates the formulas in all open workbooks.

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

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

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

If a workbook has many formulas, it is possible to overflow Excel's ability to recalculate everything instantly. When that happens, you will see the word "Calculate" in the status bar at the bottom of the screen, and calculations will sometimes get stuck before completing a complete refresh. Possible solutions:

  1. Enter some new data in a cell (or two)
    Even if you change the data right back, entering new data will usually trigger a full recalculation
  2. Reduce the number of calculations:
    1. Close other open workbooks
    2. Split worksheets into their own workbook
    3. Delete some hidden rows or columns containing unused formulas
    4. Delete unused formulas (assuming that there are similar nearby cells from which you can re-copy the same formulas later)

Online help for all calculation functions available in Excel

Help > Microsoft Excel Help > Contents tab > Function Reference
Excel 2007: F1 > Function Reference

Scenarios                          Training video - Excel Scenarios (2 min excel video)

Use Scenarios to store and quickly display different sets of data - to see how they affect the rest of your data.

We suggest that you don't just read this - but actually follow along in Excel

Tools > Scenarios
Excel 2007: Data tab > What-If Analysis > Scenario Manager

If you have already defined Scenarios, just click Show to show the values for that scenario.
(If you then want to restore the previous numbers, you can use Ctrl+Z to Undo)

 

To create your first Scenario, click the Add button, then...

enter your Scenario name.

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

When you click OK...

you can then enter values in the Scenario Values window.

(Try doing THAT in Visio...)

Tip: Scenarios became available in all Systems2win templates as of April 2007. Yet another reason to keep your upgrades current.

Printing

Print

File > Print (Ctrl+P)

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
File > Print Area > Set Print Area
Excel 2007: Page Layout tab > Print Area > Set Print Area
Then Print (Ctrl+P)

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

bulletIf you are viewing in View > Page Break Preview mode, then you can simply drag the thick blue borders to establish your print area.
bulletWindow > Freeze Panes
bulletHide columns or rows
bulletResize column width to be 0.1 (Format > Column > Width, or Home tab > Format Cells > Column Width)
or row height to be 1 (Format > Row > Height, or Home tab > Format Cells > Row Height)

How to change Print Settings

(Print on a single page or multiple pages, etc.)

File > Page Setup
Excel 2007: Page Layout tab > click the tiny little arrow in the corner of the Page Setup section

How to change paper size (e.g. letter, legal, A series international sizes, CAD sizes...)

File > Page Setup
Excel 2007: Page Layout tab > Size

Also see A3 Reporting

Print Portrait or Landscape

File > Page Setup
Excel 2007: Page Layout tab > Orientation

To save a document in PDF format - File > Print > select your PDF writer software.

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

Tip: If your company has not already provided you with PDF writing software, refer to Systems2win help: How to download and use free PDF writing software.

To save a screen shot as an image file - Print Screen

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 the screen shot into any application, [e.g. Word, PowerPoint, or any image editing software such as Photoshop, or the free Paint that comes with Windows (Start > All Programs > Accessories > Paint)].

Tip: Print Screen can be used to print or copy & paste things can't be selected. For example:

bulletUse Print Screen to print instructions from Sample Help pages
bulletUse Print Screen to copy & paste pictures of protected images or charts

Tip: You might also want to experiment with Shift+Edit > Copy Picture.
(Hold down the Shift key while selecting the Edit menu)
Excel 2007: We haven't yet found a way to do this in Office 2007. If you know, please let us know!

To print cell comments at the bottom of the page

File > Page Setup > Sheet tab > Comments > At end of sheet
Excel 2007: Page Layout tab > click the tiny little arrow in the corner of the Page Setup section > Sheet tab > Comments > At end of sheet.

Printing & viewing tips for Excel drawings

Tips for printing & viewing drawings

I am using a Macintosh - is anything different?

If using a Macintosh - holding the Control button while clicking the mouse is usually the same as right-clicking the mouse in Windows.

The Macintosh version of Excel is similar to Excel 2000 for Windows. See summary of known issues.

Continuous Improvement

Known issues and bugs

Summary of known issues for Excel 2007, 2003, 2002 XP, 2000, 98, 97, 95, and Macintosh

Upgrade to the latest version

Systems2win is continually improving your tools for continuous improvement. Benefit from the time-saving enhancements to the newest versions, the new templates that weren't released when you originally purchased, and another year of free technical support.

bullet What's new?
bulletUpgrade

And remember, you get free or discounted maintenance when you refer a new client.

If someone outside of your licensed company or division expresses a desire to have an editable version of one of your templates, you can honor your license agreement , not risk your job, and earn a referral incentive when they purchase their own inexpensive license and put your company name in the "How did you hear about us?" field on the order form.

I have a question that's not answered here

Try these other resources:

bulletSample and Help page for each template
bulletTips for Microsoft Office Drawings
bulletSystems2win Portal training
bulletContact Systems2win (to get answers to your questions, or to share your ideas for how to make a template better)

Training & Demos home page

© Systems2win  Continuously improving tools for continuous improvement

All rights reserved. No portion of this website may be reproduced or distributed outside of licensed users without written permission. See License Agreement.

Upgrade to get latest templates and another year of free support and training. What's New?
Feedback Form to share your ideas for how to continuously improve your tools for continuous improvement
Contact Systems2win