Excel Online Training

Excel training and Excel videos

Table of Contents Finding Help Working with Workbooks and Worksheets How to use Excel as a drawing tool View, Share Select, Delete, Hide, Insert, Copy, Move Editing and Formatting Links Calculations Analysis Printing Continuous Improvement

Search We put all of this training on one big web page so that you can easily use Ctrl+F to find keywords Additional Search Tips   Video Help Video: Which version of Excel am I using?

Color conventions: Excel instructions are highlighted in green.
If different... instructions for Excel 2007 and higher are in orange,
and instructions for Excel 2003 and lower remain in green.

 

Bookmark = LearnExcel

Learn Excel Download free trial or use the 2LearnExcel template found on the first page of your licensed portal Free Self-Paced Learning Quiz To learn some of the most powerful and useful features of Microsoft Excel. To learn Systems2win's innovations that make Excel even more useful. To allow a Supervisor to validate that the Learner has learned - with one quick glance. "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

---

Bookmark = Help

Finding Help

How to find Help

Every time you begin working with any new Systems2win tool... The very first thing that you should do is... Start by reviewing the Sample and Help worksheets, which contain training for THAT template   Learn more Each Help worksheet has a section titled Excel Tips Most Valuable for Using THIS Template Even if you are an Excel expert - it is highly recommended to review those Excel tips most valuable for THAT template

More links and videos for learning Excel:

• Quick Start Initial Training - what every user should know to use any Systems2win template
• How to make Office 2007+ easier to use
• Using Excel as a Drawing Tool
• Summary of known issues for Excel 2010, 2007, 2003, 2002 XP, 2000, 98, 97, 95, and Macintosh

The most complete list of links to popular help pages can be found on the Support page:

• Support

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

• Excel > Help > Microsoft Excel Help, or just press the F1 key

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

with one exception:

1. Rather than copying a worksheet the usual way (which has known bugs) -
   use the special utility Systems2win menu > Copy Sheet      Learn more

Bookmark = menu

Systems2win menu

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

Excel 2003: the Systems2win menu is in the Command Bar (next to Help)

If you are using Systems2win versions 5-8 (prior to March 2013)... in Office 2007+ the Systems2win menu is in the Add-ins ribbon tab

Tip: You can easily change the language of your Systems2win menu to Spanish, Portuguese, Chinese, and any other language supported by Systems2win.

Bookmark = Tips2007 or QuickAccessToolbar

Tips for how to
make Excel 2007, 2010, and 2013 easier to use

• Excel 2007+ makes great use of keyboard shortcuts.
  Click the Alt key to view shortcuts available.
• Change your default Excel file format

  .xlsx will strip all macros - making the file irrecoverably useless

  and unfortunately Microsoft has made .xlsx the default file format -
  so you need to choose .xls .xlsm or .xlsb - which work correctly

• Define Trusted Locations
• When you first create (or copy) a new workbook -
  just get in the habit of closing and then re-opening Excel.
  

  This will eliminate a known bug
  when copying worksheets between workbooks in Excel 2007+.

  Once you have done this once -
  from then on you can just use your workbook normally.
  You don't need to do this every time you re-open your saved workbook.

• Study the Known Issues for Office 2007+

  Especially Protected View (perhaps disable the Protected View feature. Learn more)

• Personalize your Quick Access Toolbar (see instructions in the textbox below)

Training video: QuickAccess toolbar   Make sure sound is turned on Video Help Personalize your Quick Access Toolbar in Excel 2007 or higher 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 Extra credit for Over Achievers: 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: The Select Objects arrow - which makes it far easier to select drawing objects. Home tab > Find and Select > Select Objects The Systems2win menu Add-ins > Systems2win menu As of Feb 15, 2013, the Systems2win menu now has its own ribbon tab - so with the new version, there is no longer any need to add the Systems2win menu to the Quick Access toolbar

---

Bookmark = Worksheets

Working with 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

Suggested policies for document storage and naming conventions.

Bookmark = blankTemplate

To open a blank Template worksheet

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

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

After familiarizing yourself with the help on the Sample and Help worksheets...
click the Open a Blank Template Worksheet button
which will open a fresh blank worksheet -
which you will use as the starting point for your working document.

 

Each template workbook contains a Template worksheet Yes, we know it can be confusing... but it only takes a moment to "get it"... Each Systems2win Excel template workbook contains a worksheet named "Template". This Template worksheet is hidden. When you click the Open a blank Template worksheet button, it copies (and unhides) a fresh blank copy of the Template worksheet. Benefits of the Open a Blank Template worksheet button If and when you need another fresh blank worksheet, simply click the button again With your Systems2win templates, you can never "mess up your master worksheet". A fresh blank master Template worksheet 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 worksheet, a Future1 worksheet, a Future2 worksheet, etc. This button overcomes most known bugs in the traditional way of copying a worksheet (see below)

Bookmark = copyWorksheet

To copy a worksheet Do NOT use the traditional way of copying a sheet (which has known bugs - itemized below) Use the special Systems2win utility to copy a sheet Systems2win menu > Copy Sheet   This is the one thing that is different about using Systems2win templates You can use everything else you know about Excel, except that when you copy a worksheet - you want to use the special Systems2win menu > Copy Sheet utility that protects you from most known Excel bugs. Example: You might want to copy your 'As Is' worksheet to serve as the starting point for your 'Future' state. You use Systems2win menu > Copy Sheet.   How to use Excel Dropdown Lists, Filter, and Copy Sheet

Be careful when copying to another workbook Although it is always safe to copy worksheets within the same workbook... copying a worksheet to another workbook can be less reliable. If your copied workbook contains unwanted links to external sources - use Edit Links to redirect the links to your current workbook. Also... did you remember to follow our advice to always close and then re-open Excel when you first create a new workbook? See Tips for how to make Excel 2007+ easier to use.

 

The traditional way to copy a worksheet

is NOT advised, because it has many known bugs.
Instead use the special Systems2win Copy Sheet utility 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.

 

Known bug with traditional way to copy a worksheet: There are many named ranges that will get messed up if you copy your worksheet without using the special Systems2win Copy Sheet utility.

Known bug with traditional way to copy a worksheet: If you get the HasFormula dialog box (pictured below), the HasFormula will work just fine when you click Yes, but the fact that you got this dialog means that either:

1. you didn't use the special Systems2win Copy Sheet utility, or
2. you didn't save, close, and re-open Excel after first creating your new workbook

Known bug with traditional way to copy a worksheet: Dropdown lists won't work on your copied sheet unless you use the special Systems2win utility to Copy Sheet. If 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: Save a backup copy of your workbook With the copied worksheet selected... Select Insert > Name > Define Select 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)

If 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.) Repeat for all local Names that refer to an error Save your work Next time - use the special Systems2win Copy Sheet utility that avoids this bug.

Bookmark = renameWorksheet

To rename a worksheet Right-click the worksheet tab (at the bottom of the worksheet). Select Rename. Workbook and Worksheet naming conventions: Refer to the Document Storage and Naming Conventions defined by your company.
If you get the dialog: "You have modified a signed project. The signature will be discarded." just click OK, and everything will work just fine. See understanding digital signatures.

Bookmark = Protect

To Protect or Unprotect a worksheet

Excel 2003: 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 few 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.

Bookmark = HideWorksheet

To Unhide or Hide a worksheet

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

Several worksheets are usually hidden in most Systems2win templates:

• Template (the worksheet that gets copied when you click the Open a Blank Template worksheet button)
• CLog (Customization Log)
• TT (for Translation Tables)

Caution: You should never delete a Sample or Help worksheet, but you can hide them instead.

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.

 

---

Using Excel as a Drawing Tool

Excel can create drawings without the cost of Visio - and can do math and analyses that Visio can't.

Value Stream Map Excel template	Standard Work Layout Diagram Excel template	Relations Diagram Excel template	Root Cause Fishbone Excel template

See How to use Excel as a Drawing Tool

 

---

Bookmark = View or Zoom

View, Share

Zoom

(to make anything easier to read)

View > Zoom

Excel 2007+ zoom bug: Shapes sometimes appear zoomed to 100% when rest of page has different zoom. Solution: View > Zoom > 100%. Then View > Zoom > to any percent that you want. The shapes will now be sized correctly.  As of March 2009, we have brought this to Microsoft's attention, and they have acknowledged it as a bug, but have not yet announced when it will be fixed.

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, like you can to refresh the screen 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

to always have column or row labels visible as you scroll

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

Free application viewers

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

Caution: It is NOT okay to distribute a Systems2win Excel file to an unlicensed user - even if the user is only using a free application viewer.

To save a document in PDF format

Excel 2007+: File (or Office button) > Save As > PDF or XPS
Excel 2003: 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...)
   cannot be released in an editable format (such as Excel).

How?

1. If your company has not already provided you with PDF writing software...
   How to download and 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.
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).

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:
Excel 2003: Hold down the Shift key as you select Edit > Copy Picture
or Excel 2007: Home > Paste > As Picture > Copy as Picture
or Excel 2010: It is in the little dropdown list next to Paste on the Home ribbon menu

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.

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 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
Excel 2003: Edit > 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")

 

---

Bookmark = Copying

Select, Delete, Hide, 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 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.

Also see Excel 2010 known issue when selecting a large number of rows or columns in a protected worksheet

Bookmark = Hide

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
Excel 2007+, you can use the keyboard shortcuts found in Home tab > Format Cells > Hide & Unhide

Excel 2010 has a known issue when selecting a large number of columns in a protected worksheet,
so rather than selecting the entire columns...
you should select just the cells in one row - spanning the columns that you want to hide or unhide,
then Home tab > Format > Hide & Unhide.  Learn more

Tip: It is usually better to hide rows and columns, rather than delete 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 row or column width to1 or 0.1 (Format > Column > Width).
This will almost hide a row or column while still including its data within a chart,
even if you haven't set your chart to include hidden data.

Hide / Show Rows, and Add / Remove a Process Special Value Stream Mapping buttons Value Stream Mapping templates come with a special button to make it even easier to quickly Hide/Show Rows and the VSM-PowerTool and Supply Chain Map come with buttons to easily Add or Remove a Process, and to Add Columns Or more correctly - pairs of columns - preformatted with everything you need so all you need to do is fill in your data

Bookmark = Unhide 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 Excel 2010 has a known issue when selecting a large number of columns in a protected worksheet, so rather than selecting the entire columns, you should select just the cells in one row - spanning the columns that you want to unhide. And then Home tab > Format > Hide & Unhide.  Learn more Tip: Many 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.

Bookmark = delete

To delete cells, rows or columns

Select the range of cells, or the entire rows or columns, then
keyboard shortcut Ctrl+- (the minus key), or...
Excel 2007+: Home tab > Delete cells
Excel 2003: Edit > Delete

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) 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 Edit > Clear > Contents or Excel 2007+ 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 (Edit Delete) 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    (do NOT use Insert > Rows)

Caution: 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)

Use either...

Insert Copied Cells, or

Shift Down and Copy

Caution: Insert > Rows works correctly only when ALL of the following conditions are met: The row immediately above has exactly the same formatting as the inserted row, including: Obvious formatting, such as color, border, font, number format, style... Hidden formatting, such as data validation, conditional formatting, custom formatting... Obvious formatting that currently isn't obvious (perhaps the cell doesn't have any data) There are no hidden formulas that should have been copied from a similar row

Bookmark = ShiftDownCopy

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 (perhaps 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
4. Keyboard shortcut: Ctrl+Shift++ (the plus sign), or...
   Excel 2007+: Home tab > Insert > Insert Copied Cells  (or Ctrl+Shift+=)
   Excel 2003: Insert > Copied cells

Option 2) Shift Down and Copy Select the range of cells to copy (perhaps the entire rows or columns) 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 (on a locked worksheet, the cursor will be a white arrow) Right-click and drag When you release your right mouse button, select Shift Down and Copy or Shift Right and Copy

To move entire rows or columns

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

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

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 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 in Excel 2002 or higher)
and/or... Excel 2007+: Home tab > Paste > Paste Special Excel 2003: Edit > 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

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).

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) Drag to move

1. Select the cell(s)
2. Hover you mouse over the edge of the selected cells until your cursor turns into a four-sided cross arrows
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 2) Cut and Paste

1. Cut (Ctrl+X)
2. Either Paste (Ctrl+V)
   or often much safer...
   Excel 2007+: Home tab > Paste > Paste Special > Values
   Excel 2003: Edit > Paste Special > Values
3. Then go back to the cut cells, and perhaps apply styles formatting (or just copy a similar nearby cell)

Option 3) 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...
   Excel 2007+: Home tab > Paste > Paste Special > Values
   Excel 2003: Edit > Paste Special > Values
   
3. Then go back to delete the unwanted data - using the keyboard Delete key

Option 4) Shift Down/Right and Move

1. Select the cell(s)
2. Hover you mouse over the edge of the selected cells until your cursor turns into a four-sided cross arrows
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

Bookmark = CopyVisible

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
Excel 2007+: Home > Find & Select > Go To Special > Visible Cells Only
Excel 2003: Edit > GoTo > 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,
then...

Excel 2007+: Home tab > Paste > Paste Special > Transpose
Excel 2003: Edit > 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).

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

1. directly into a cell
   and notice that the data in the cells below were also overwritten - 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

Undo

Keyboard shortcut: Ctrl+Z
Edit > Undo

Bookmark = shortcuts

Keyboard Shortcuts

Full list of keyboard shortcuts:

• Excel 2007+: F1 > Accessibility > Excel shortcut and Function keys
• Excel 2003: Help > Microsoft Office Help > Accessibility in Excel > Keyboard Shortcuts

Bookmark = paragraph

To enter a "paragraph" within a cell

Alt+Enter

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)

Tab indentation within a cell

Ctrl+Alt+Tab

Ctrl+Shift+Alt+Tab to decrease indentation

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

Bookmark = FormatCells

Format Cells

Excel 2007+: Home tab > Format > Format Cells

Excel 2003: Format > Cells

Here you will find a large number of options for cell formatting, including:
number, alignment, font, border, fill, and protection

Bookmark = Styles

Styles

Excel 2007+: Home tab > Cell Styles

Excel 2003: Format > Style

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),
so after applying a style, you might want to then also apply some additional manual formatting.

Tip: You can tell what a style does and does not affect
by selecting a cell formatted with the style,
and then selecting...
Excel 2007+: Home tab > Cell Styles > right-click a Style > Modify
Excel 2003: Format > Style

Systems2win has provided several preformatted styles to choose from

Systems2win menu > Styles provides help for using Styles,
and a convenient way to simply copy & paste the style you want.

Styles dropdown list  Time-saving Tip In Excel 2007+, the Styles dropdown list is a standard feature. Home tab > Cell Styles In Excel 2003 or lower, you can easily add a Styles dropdown 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

Add your own styles

First format the cell the way you want it to be with your new style, then...

Excel 2007+: Home tab > Cell Styles > Type in a new Style Name
Excel 2003: Format > Style > Type in a new Style Name

Then uncheck the boxes for everything but the few attributes that you want your style to affect,
and then click Add.

Tip: You usually want your styles to change as few attributes as possible.

To add or modify styles - every sheet in the workbook must be unprotected Most sheets are unprotected. Some worksheets are protected without a password - so try simply unprotecting it. Prior to Feb 15, 2013, the Help sheet was protected (without a password), and Sample sheets were protected with the password Systems2win2win2win. Only the Value Stream Mapping Power Tool and Supply Chain templates are locked with a password - in order to prevent you from shooting yourself in the foot. You cannot add your own styles to those workbooks. Prior to October 2009, Systems2win templates did not allow you to add your own Styles. So you might need to upgrade.

Bookmark = Borders

Borders Excel 2007+: Home tab > Font section > Borders Excel 2003: Format > Cells > Border or Formatting toolbar > 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

Excel 2007+: Home tab > Merge & Center > Merge Cells
Excel 2003: Format > Cells > Alignment > 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: Before merging horizontal cells, always first consider using...
Excel 2007+: Home tab > Merge & Center > Merge & Center
Excel 2003: Format > Cells > Alignment tab > Horizontal > Center Across Selection  

Bookmark = Comments

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.

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

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 special instructions in how to personalize your templates.

Bookmark = DataValidation

Data validation

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

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.
How? 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 usually wiser to personalize your templates with Cell Comments to supplement (rather than replace) Systems2win's standard help.

Bookmark = Dropdown

Dropdown lists

Some Systems2win templates feature a DV worksheet where you can define dropdown lists.

Benefits of Dropdown Lists More accurate data entry Avoiding typos is especially helpful when using Filter or Sort Faster data entry It's faster to choose from a list - especially for long choices

Caution: When copying a worksheet that contains a dropdown list -
be sure to use the special utility Systems2win menu > Copy Sheet -
or the dropdown list on your copied sheet may not work.

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

There are several optional ways to define dropdown lists in multiple languages.

---

Bookmark = SpellCheck

Spell Check

Keyboard shortcut F7, or...
Excel 2007+: Review > Spelling
Excel 2003: Tools > 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. Excel 2007+: Home tab > Conditional Formatting
   Excel 2003: Format > Conditional Formatting
3. If your condition is a formula...
   Excel 2007+: Highlight Cells Rules > More Rules > Use a formula to determine which cells to format
   Excel 2003: select Formula Is from the dropdown list
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 With version 8 Systems2win templates, (that support language translations), conditional formatting uses symbols - rather than English words. For example, with the older versions - a Status cell might turn green when you select the word "Done" from the drop-down list - and turn yellow when you select the word "Late". With the international version - 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: You can change symbols, but don't change the font.

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

Bookmark = FillSeries

Fill Series

Excel 2007+: Home tab > Fill > Series

Excel 2003: Edit > Fill > Series

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

For example, you might type 1,2, and 3 in your first 3 cells, and it will automatically sequentially number the next several thousand cells that you select.

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

 

Language translation As of July 2011, Systems2win Excel templates are now available with language translation tables, so that with the click of a button you can switch between languages. Even before the language translation tables were released... Systems2win has been providing many other language tranlation features for many years.

Bookmark = shrink

Shrink to Fit

If words don't fit in a cell, you can either:

1. Change cell format to Shrink to Fit

   Excel 2007+: Home tab > Format Cells > Format Cells > Alignment > Shrink to fit
   Excel 2003: Format > Cells > Alignment > Shrink to fit

2. Make the cell bigger

   Tip: To auto-size row height, double-click the bottom border beneath the grey cell number
   (cell numbers are in the far left border)

 

---

Bookmark = hyperlink

Links

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

Excel or Word 2003: Systems2win menu > Insert Link Icon

How to use Link Icons for hyperlinks Systems2win menu > Insert Link Icon...   (see screen shots above) Then, in the Link Icons window that appears, >>>>>>>>>>>>> hover over any link icon to learn more about it then double-click the one you want to insert Then select the Link Icon that you just inserted and Ctrl+K or right-click > Hyperlink to bring up the Edit Hyperlink dialog window... where you can Link to: any Existing File or Web Page or Place in This Document.  (see screen shot below)

 

 

 

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

 

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

"A" stands for "anchor bookmark"

 

Bookmark = Bookmarks

Bookmarks

Also known as "anchors" or "favorites"

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

See the instructions above for how to specify a Place in This Document in Excel.

The Systems2win web site 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 your question.

How? Simply append a regular hyperlink with the # symbol followed by the name of the bookmark.

Example: Clicking http://www.systems2win.com/c/worksheets.htm#Bookmarks
will take you right back to where you are now.

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

Bookmark = brokenlinks

Tips to Avoid Broken Links

Absolute vs. Relative Links Relative links  (specify the folderpath relationship between files) How? Just browse and select the workbook  (or the target cell or worksheet within the workbook) Absolute links  (specify the full path) How? Specify the full pathname (e.g. starting with http:// or \\servername\) We have not found a way to specify an absolute link within your own computer. Links that start with a drive letter always get converted to relative. To avoid broken links: Use a relative link 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 - even if the entire folder is moved) Use an absolute link if linking to another document that will NOT 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 - even if the entire folder is moved)

More tips for how to avoid broken links

1. Training and tips for Document Storage and Naming
2. How to move or rename a filename or folder path without breaking the hyperlinks
3. How to make active hyperlinks in your PDF's
4. Two professional ways to create hyperlinks (and get rid of that annoying sea of blue underlined text)

Bookmark = linkCells

Link cells

How to auto-update one cell with the value from another

This is also how you link cells to your Language Translation Table

Option 1)

1. Select the cell that you want to auto-update
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), and click the Enter key

Option 2)

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

This training video succinctly illustrates how to link cells >>>>>>>>>>>

 

To link to a named cell

You can use either of the usual options above -
simply clicking a cell that happens to be a named cell,
or you can use...

Excel 2007+: Formulas > Name Manager > Use In Formula
Excel 2003: Insert > Name > Paste

 

Cautions to observe when Linking Cells When using any of the optional ways to Link Cells, always observe these cautions: If you link to another workbook - then both workbooks will need to be opened together in order to avoid reference errors. If your source worksheet uses scenarios, then your linked data will only be correct when the source happens to be displaying the correct scenario. Follow tips to avoid broken links

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. Example: 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: $A$1 (absolute columns and row) $A1 (absolute column; relative row) A$1 (relative column; absolute row) 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 Worksheet

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

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

Bookmark = EditLinks

Edit Links

Excel 2007+: Data > Edit Links
Excel 2003: Edit > Links

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 worksheet. So on the Value Stream Mapping Power Tool, you can run Edit Links from the UOMs worksheet.

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

Calculations

Tips to avoid accidentally messing up your formulas

1. Keep your formulas separate from your data -
   by linking to and from cells in your Custom Formula Safe 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 or S2winFormula

Highlight cells containing formulas

so that you don't accidentally delete your formulas

Option 1) Permanently highlight cells containing formulas

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

Option 2) Highlight cells containing formulas - using HasFormula conditional formatting

You can add the special Systems2win HasFormula function to any 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: If you are using Excel 2013 - see the Conditional Formatting bug that affects only Excel 2013.

Tip: Any cell will be conditionally formatted as if it contained a formula if it has both:
a) The HasFormula conditional formatting, and
b) Hidden protection
Excel 2007+: Home tab > Format Cells > Format Cells > Protection tab > Hidden
Excel 2003: Format > Cells > Protection tab > Hidden

Tip: In older versions - HasFormula only works in the English version of Excel.
You can upgrade to the new version that works correctly in any language.  

Tip: In version 5 or lower, HasFormula used to be named S2winFormula.

Option 3) Temporarily highlight cells containing formulas

(Excel 2013 does not support this option)

Right-click any blank cell. Select Highlight > Cells containing formulas.

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

Tip: Conditional formatting trumps any other formatting. So cells colored with conditional formatting will not be highlighted.

Option 4) Formula Auditing

Bookmark = FormulaAudit

Formula Auditing

Excel 2007+: Formulas > Formula Auditing

Excel 2003: Tools > 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, 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 worksheets).

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 Ctrl+Z
2. Later - you can often repair it by simply copying a similar cell.

Bookmark = Names

How to define range Names 

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

On an unlocked worksheet...

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

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. 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 billing rate. Always keep a backup before you start writing your own custom formulas and Names.

Bookmark = 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 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? (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: 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

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

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

Tools > Options > Calculation tab > 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

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 (perhaps there are similar nearby cells from which you can re-copy the same formulas later)

Bookmark = precision

Precision

File > Options > Advanced > Set precision as displayed

Tools > Options > Calculation > Precision as Displayed

All Systems2win workbooks should have this checkbox NOT checked.

Exception: If you want to use video time observation with the StdWk template -
you can manually turn this feature on - which will mess up any Work Elements that have a decimal place greater than 0.1.

Online help for all calculation functions

available in Excel

Excel 2007+: F1 > Function Reference

Excel 2003: Help > Microsoft Excel Help > Contents tab > Function Reference

 

---

Bookmark = Analysis

Analysis

Bookmark = Scenarios Scenarios Use Scenarios to store and quickly display different sets of data - to see how they affect the rest of your data. Huge tip: This is one of the most valuable features of Excel. We highly recommend that you spend 2 minutes to learn it.     We suggest that you don't just read this - but actually follow along in Excel Excel 2007+: Data tab > What-If Analysis > Scenario Manager Excel 2003: Tools > Scenarios   If you have already defined Scenarios... just click Show to show the values for that scenario. Tip: If you then want to restore the previous numbers, you can use Ctrl+Z to Undo	Video: Excel Scenarios
To create your first Scenario... click the Add button, 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. 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.

Bookmark = AutoFilter

Filter

Filter your data in useful ways - displaying only those rows of data that meet your specified filtering criteria.

Select ALL cells in your list 

If your list has a Name, (such as A_FilterData),
you can easily select it from the dropdown list in the Name Box
(That's the easiest way to select all cells in your list)

If you need to manually select a list that doesn't have a name,
be sure to include gold header columns,
as well as columns & rows with thin gold lines

Then select...
Excel 2007+: Data tab > Filter
Excel 2003:  Data > Filter > AutoFilter

Dropdown arrows will appear on each column header.
Use any dropdown menu to filter your data.

Filter and Sort Tips Use Text Filters (or Custom in Excel 2003) for AND/OR logic and Boolean operators like greater than, begins with, does not contain, etc. If you need even more sophisticated filtering, experiment with... Excel 2007+: Data tab> Advanced Filter Excel 2003: Data > Filter > Advanced Filter Do not use Filter to sort your data. Use Sort instead. If you have filters applied in several columns, rather than resetting each column back to 'Select All', it is sometimes faster to simply turn Filter off, then turn it back on again (following the above instructions). Filter and Sort require a "list". Any Systems2win template that suggests to use Filter or 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 select only the headers (rather than all cells within your list), then cells within the thin gold line column must contain data in order for Filter and Sort to work correctly. If you used regular Insert > Rows instead of copying a similar blank row - then the thin gold line for your inserted row will not contain data - and you will need to Reset Thin Gold Lines.

Bookmark = Sort

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_FilterList), 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

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).

Charts

One of the huge benefits of being written in native Microsoft Excel is the ability to add unlimited charts of almost any kind.

See our online training for Microsoft Excel Charts.

 

---

Bookmark = Printing

Printing

Print

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
Excel 2007+: Page Layout tab > Print Area > Set Print Area
Excel 2003: File > Print Area > Set Print Area

Then Print (Ctrl+P)

Or... 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)

• Freeze Panes or Split Window
• Hide columns or rows
• Resize 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)

Bookmark = PageBreak

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

If using a special Systems2win print button Some Systems2win templates have special buttons for printing different sections of a worksheet. Please note that: 1) You can always use Excel's regular Print features in addition to or instead of the special Systems2win print buttons. 2) For Print Preview Use the 'Change Printer' button to select the Microsoft XPS Document Writer 3) 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 you might need to (perhaps temporarily) change the Excel Print_Area to match the Systems2win-defined Print Area in order to view an accurate preview of what will be printed when you use the special Systems2win print button. 4) Page Breaks are based on Excel's default Print Settings For example: If the special Systems2win print button prints in Portrait print orientation and the default Print Settings are set for Landscpe... then you might need to (perhaps temporarily) change the default Print Settings to your desired Print Orientation in order to view an accurate preview of what will be printed when you use the special Systems2win print button. 5) You might want to install PDF writing software Although Excel 2007 and higher now have a standard feature for printing PDF's, File (or Office button) > Save As > PDF or XPS, a limitation of that feature is that it only prints the default Print_Area. So you still might want to install free PDF writing software - so that you can choose to print to your PDF writer when using your special Systems2win print button.

 

Bookmark = PrintSettings, PaperSize

How to change Print Settings

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

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

Print Orientation

Portrait or landscape

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

How to change paper size

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

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

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

How to change print scale

Excel 2007+: Page Layout tab > Scale
Excel 2003: File > Page Setup > Page tab > Scaling

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:

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
Excel 2003: File > Page Setup > 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 is as easy as possible to continuously improve your tools for continuous improvement.

Upgrade to the latest version

• What's new?
• Update your Systems2win Excel Add-in for free at any time
• Upgrade your Systems2win templates once a year