|
Excel Training: Tips for working with Systems2win Excel ToolsNote: These are some brief tips that are particularly helpful for getting the most from your Systems2win Excel templates. These tips are intended to supplement (rather than replace) the excellent on-line 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 TemplatesOverview of training that comes with every Systems2win Excel templateRefer to how to use a Systems2win SampleHelp page. To avoid overwriting your master templates by mistakeFollow the Portal Setup Instructions to set up your PC to be able to open your Systems2win templates from the File > New menu right within Word and Excel. (It only takes 2 minutes.) To overwrite a master template (on purpose)Whenever you overwrite a master template with your personalized version - make an entry in your Customization Log (to make upgrades easy)
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. To Protect a worksheetTools > Protection > Protect Sheet You can then optionally check boxes for what you want (and do not want) to
protect, Tip: Most Systems2win Excel templates are delivered unprotected. Those sections of Excel templates that are locked 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 locked section of an Excel template, please contact us.
Deleting, Hiding, Inserting, Copying, & Moving
To delete entire rows or columnsSelect the rows or columns, then
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) 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 cellUse the keyboard Delete key 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 formulas1) 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 unlocked cells containing formulas Hide rows or columnsSelect 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 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 columnsImportant Tip: Rather than using
Insert >
Rows or Columns, (Excel 2007: Home tab > Insert >
Insert Sheet Rows or Columns), To copy entire rows or columns - use Insert Copied
Cells or Shift Down
and Copy
|
Option 2) Shift Down and Copy
To move entire rows or columns - use Shift Down and MoveSame as Shift Down and Copy - but in the final step, select Shift Down and Move. |
![]() |
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.
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:
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")
Use Print Screen.
![]()
Free application viewers allow you to view, but not edit.
Keyboard shortcut: Ctrl+Z
Edit > Undo
Alt+Enter.
Ctrl+Alt+Tab
Or use the Increase Indent and Decrease Indent buttons on the Formatting toolbar.
Format > Style
Excel 2007: Home tab > right-click a Style
Tip: Systems2win templates make extensive use of Styles. As a rule, you don't want to just change the background color to match surrounding cells. You want to change the Style to match surrounding cells.
BordersFormat > Cells > Border Tip: If you are having trouble keeping your
borders consistent as you insert or move columns or rows,
|
![]() |
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
Every Systems2win Excel template comes with Link Icons that are perfectly sized and formatted to make highly visual hyperlinks easy.
Version 5 and higher, select Systems2win menu > Insert Link Icon.
![]()
| Then Ctrl+K or right-click
>
Hyperlink to bring up the Insert Hyperlink dialog window, where you can:
Follow all tips to avoid broken links |
![]() |
|
In older versions, the entire palette of Link Icons is on every worksheet. Simply copy & paste a link icon shape.
|
![]() Tip: Use the Screen Tip
button to add a Screen Tip 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.
If you don't follow our advice...
|
How to reset picture sizeMany 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.1, it was easy for a shape's size to become distorted. To resize a picture shape that has become distorted, right-click > Format Picture > 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. |
![]() |
![]() |
Follow these same tips when you insert a hyperlink or link cells with the value from another cell.
The easiest way to avoid broken links is to use
method 1 -
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.
If you need to change the name of a workbook or worksheet or named cell that has links to it...
| If all linked workbooks are open when the names are changed, then the hyperlinks will be updated automatically. | |
| Don't use File > Save As - because all incoming links will be changed to link to the new file, rather than the original. (Not what you usually want.) |
To avoid the need to change workbook or worksheet names...
|
![]() |
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.
Highlight all cells in your list, and then complete the information asked within Data > Sort.
Tip: Unlike AutoFilter, you need to highlight
ALL of the cells in your list (including header titles), not just the header titles.
Be sure to include ALL columns in your list.
Tip: It is a good idea to back up your file before sorting, and Undo (Control+Z) can be very useful.
Some Systems2win templates might have disabled the Sort feature (because sorting might not normally make sense with that type of data).
Help > Microsoft Office Help > Accessibility in Excel > Keyboard Shortcuts
Excel 2007: F1 > Accessibility > Excel shortcut and Function keys
Position your
cursor where you want the panes to be frozen, then select Window > Freeze Panes.
Excel 2007: View > Freeze Panes
Tips for working with Microsoft Office drawings
![]()
Option 1) Highlight unlocked cells containing formulas - using
Highlight Formulas macro
Right-click any blank cell. Select
Highlight > Cells containing unlocked formulas.
(This requires version 5.1 or higher)
Option 2) Use styles to change the background color of cells containing formulas
Tip: When you Unhighlight Cells, cells 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 3) Highlight cells containing formulas - using S2winFormula You can add S2winFormula to any additional cells by following the instructions for conditional formatting, and entering the following formula: =S2winFormula Cells containing a formula will be highlighted with the background color of your choice. Tip: Any cell with with both: Tip for Systems2win version 6 or higher users of the German version of Office: Follow these exact same instructions - but use HasFormula instead of S2winFormula. |
You can often repair it by simply copying a similar cell.
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 on-line 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 linksTo link to a named cell
Insert > Name > Paste
Excel 2007: Formulas > Name Manager > Use In Formula
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 intactHas 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. Version 5.1 or higher: You can automatically reset the width of thin gold lines, by selecting Systems2win menu > Reset Thin Gold Lines |
![]() |
Experiment with using 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.
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.
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.
Help > Microsoft Excel Help > Contents tab > Function
Reference
Excel 2007: F1 > Function Reference
ScenariosUse Scenarios to store and quickly display different sets of data - to see how they affect the rest of your data. Tip: Scenarios are among the most useful things you can
do with Excel. We suggest that you don't just read this - but actually follow along in Excel Tools > Scenarios If you have already defined Scenarios, just click Show
to show the values for that scenario.
|
![]() |
| 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, 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. |
![]() |
![]()
File > Print (Ctrl+P)
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)
| If you are viewing in View > Page Break Preview mode, then you can simply drag the thick blue borders to establish your print area. | |
| Window > Freeze Panes | |
| 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) |
Tip: Help page instructions can be printed with the click of a button in version 5 and higher. Yet another reason to keep your upgrades current.
(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
File > Page Setup
Excel 2007: Page Layout tab > Size
File > Page Setup
Excel 2007: Page Layout tab > Orientation
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.
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:
| Use Print Screen to print instructions from Sample Help pages | |
| Use 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!
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.
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. |
![]()
Summary of known issues for Excel 2007, 2003, 2002 XP, 2000, 98, 97, 95, and Macintosh
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.
| What's new? | |
| Upgrade |
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.
Try these other resources:
| Sample and Help page for each template | |
| Tips for Microsoft Office Drawings | |
| Systems2win Portal training | |
| Contact 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. Tips for printing web
pages (like this one)
Upgrade
to get latest templates and another year of free support and training.
What's
New? |