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

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.

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.

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

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.

AutoFilter (to filter your data in useful ways)

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+=)

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

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

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

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

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

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

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.

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

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)

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:

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

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.

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:

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