MS Excel Software Tips

PLEASE NOTE: The software tips and shortcuts shown below may also work if you’re using the Office 2007 version.

Tips concerning keyboard shortcuts may still work in 2007; in addition, tips that involve using the lower Office version’s ‘menu’ options may have an equivalent function in 2007 using the new Ribbon and OFFICE button (that replaces the previous menu/toolbars). We will be revising our Software Tips pages as part of our upcoming web redesign and we’ll make necessary additions at that time.

TIP #1: USING SHORTCUT KEYS (All Versions)

Lots of people using Excel are constantly using the mouse to move up to the formula bar (just below the toolbar buttons) simply to edit a formula.

If you link data in Word as an Excel 7.0 worksheet, you won't see any gridlines. However, if you make some changes to the linked worksheet in Excel, the gridlines will appear in Word. This happens because Excel creates a default printer (not screen) metafile and Word therefore displays the sheet as it would print. If Excel is set to print gridlines, then they will show in Word. To get around the problem, you can switch to Excel after you establish the link and retype a cell's contents. Word will now display it correctly. Alternately, you could set Excel to print the gridlines. To do this, choose File|Page Setup. Choose the Sheet tab and select Gridlines. Click OK.

TIP #4: SAVE YOURSELF A LOT OF TIME AND TROUBLE (All Versions, although version 2002 requires a specific AutoSave AddIn installed)

For Versions Prior to 2003

AutoSave is a handy Excel feature--one that saves many of us from disaster. Although Excel doesn't use AutoSave by default, you can easily add it.

After you select AutoSave, the command will appear in the TOOLS menu. Choose Tools/AutoSave now and you can tell Excel how often you want to invoke AutoSave. After you enter the time period of your choice, click OK.

From the Tools menu, choose Options and then select the Save tab. Check the "Save AutoRecover info every:" check box and select the number of minutes in the selection box provided. Click Ok.

TIP #5: AUTOMATICALLY DISPLAY PAGE BREAKS (Versions 5.0 and 7.0 & 2003)

Automatically display page breaks and get a better idea of how your worksheet will print out.

You will notice a dotted line on your worksheets where the page breaks will be.

NOTE: In Excel 97 - 2003, you VIEW/Page Break Preview

TIP #6: SET THE PRINT AREA QUICKLY AT THE CLICK OF A BUTTON (Versions 5.0, 7.0 and MAC)

When you're ready to print, select the area you wish to set as the print area, Then click the Print Area button on the toolbar.

To DELETE, simply click on the RESET button under VIEW/Toolbars. WARNING... this will delete all the buttons that you've created with the customize feature.

NOTE: In Excel 97 - 2003, highlight area, FILE/Print Area/Set Print Area

TIP #7: BREAKING UP IS HARD TO DO (All Versions)

To break up a long cell entry into separate lines, position the cursor where you want a new line to start and press Alt+Enter. Excel will expand the row to accommodate wrapping lines of text. Press Enter to complete the entry.

TIP #8: INSERT AN AUTO SUM (Versions 97 - 2003)

Insert an AutoSum Alt = (equal sign)

Excel will try to guess what cells you want to total up, either the cells immediately above or to the left - but you can change the range of cells to whatever you need.

TIP #9: JUMP AROUND IN PRINT (Versions 7.0 and 97)

When you're in Print Preview and you have zoomed in on the page, use the arrow keys to move around the page.

If you're viewing the entire page when you've zoomed out, you can use the following shortcuts to move between pages:

NOTE: In Excel 2000 & 2002, it's just PageUp and Page Down NOT CTRL

TIP #10: YOU GOTTA HAVE ART (Version 7.0)

Would you like to add a picture to the bars of an Excel chart to set them off?
Try this, create a chart, then click away from the chart

Choose the figure that you think will look best and click OK to insert it into Excel.

Now choose EDIT/Cut, then double-click one of the bars and now choose EDIT/Paste, and
the image will appear in the bar.

NOTE: This does not work in Excel 2000 - 2003. For these versions, you click on the bar to select it and then INSERT/Picture/Clipart (or use clipart button).

TIP #11: AUTOMATIC RANGE NAMES USED IN FORMULAS (Versions 97 - 2003)

Named ranges let you calculate the total for all cells in a range with a formula such as

With Excel 97, you can often get the benefit of named ranges without having to define them.

To see how these natural-language formulas work, enter the name Products for Column C and Price for column D (highlight the column and then type the name in the Range name text area at the left of the fx symbol). Then enter the formula =Products*Price in column E cells. Now, as you fill in numbers in the Products and Price columns, Excel automatically calculates totals in the third column.

TIP #12 - BRING EXCEL DATA INTO ACCESS (Versions 7.0 - 2003)

To bring data from a Microsoft Excel spreadsheet into Access, first open both programs. In Access, open the database and select tables from the list of database objects. Then minimize Access.

Highlight the data in Excel that you want to import into Access. Point to the edge of the highlighted data, then ctrl/drag to the Taskbar (dragging without CTRL key held down will MOVE the data rather than copy) and hold above the Access icon (DO NOT release the mouse button) until Access opens up.

In versions 2002 & 2003, keep the left mouse button down and drag your mouse to the tables list background, then let go.

TIP #13 - GET ATTENTION WITH EXPLODING CHARTS (Version 7.0)

If you want to place widely varying Excel data into a pie, doughnut, or three-dimensional pie chart, you'll find that it can be hard to see the smallest segment. When you insert a chart in a worksheet, you can choose an exploded pie chart if you like.

To insert a chart, select the data and choose INSERT/Chart. Use the mouse to draw the chart's area. Now follow the chart wizard and choose a pie-type chart. When the wizard offers different pie chart styles, choose one of the exploded views. If you choose the view that shows one slice separated from the rest of the pie chart, that slice will be the smallest of the group. This makes the data easier for viewers to see.

Versions 2000 - 2003 allow you to choose from among various chart styles including those that provide "subcharts" of user defined values.

TIP #14 - SHORTCUT KEYS (Versions 2000 and higher)

Shortcut keys abound in Excel. Following are a few that should be useful
for your daily tasks. (Keystrokes are the same in Windows and Mac unless
noted.)

TIP #15 - SOCIAL SECURITY FORMAT (Versions 97 - 2003)

If you've ever used the Social Security format that Excel offers, you may have run into this problem. If the social security number that you're formatting ends with a zero, the format doesn't work.

One common reason the Social Security format removes zeros is if you have Excel's default decimal place setting set to something other than 0. (If you're in finance or accounting, you may have set the default to 2 decimal places so that you could enter financial amounts without having to type a decimal every time.)

TIP #16 - QUICK-CLICK MOVEMENT (Versions 97 - 2003)

Want to find out where a column ends or where the next blank cell is
in the column?

This trick works for rows, too. So, for example, if you want to find
the last filled cell--or the next empty cell--in the current row:

TIP # 17 - ADDING A TREND LINE TO CHARTS (Versions 97 - 2003)

You can add a trend line to a chart (bar, line, column, stock, or another 2D chart) - this is a away that you can focus on someone's attention to the area of the chart that's very important and really makes it easier to see how the data changes.

TIP # 18 - USING 'EXTEND MODE' (Versions 97 - 2003)

Selecting a RANGE of Cells using 'Extend Mode'
You can highlight a range of cells by using 'extend mode' rather than using the mouse or SHIFT/arrows keys to select cell range:

You'll notice that the 'EXT' box in the lower right corner of Status Bar area appears

Note: If you make a mistake, you can simply click somewhere else to identify/highlight the range of cells

TIP #19 - COPYING A 'CHART FORMAT' (Versions 97 - 2003)

You can copy the format of a chart - if you've got a chart with the formatting/colors, etc. set already, you can copy the format to another new chart:

Tip #20 - REMEMBERING YOUR CELL NAMES (Versions 97 - 2003)

When you're doing a formula and can't remember the range names that you need, you can:

Tip #21 - HOW TO WRAP CELLS WITHIN TEXT (Versions 97 - 2003)

You can wrap text in cells - highlight the cells then:

NOTE: If you want to create a 'break' in the text rather than have it automatically word wrap, place your cursor at the point where you want to break the line within the cell, and touch ALT/Enter

TIP #22: 'AUTOCORRECT' (Versions 2000 - 2003)

How about 'AutoCorrect', this is a neat tool to have when you seem to make the same typo errors over and over again. If you've typed a misspelled word, as long as the typo is in the AutoCorrect feature it will automatically correct itself, works also with abbreviations.

TIP #24: 'VIEW' YOUR FORMULAS INSTEAD OF RESULTS IN CELLS (Versions 2000 - 2003)

You can 'see' your formulas instead of the results in a cell by:

TIP #25: 'PREFILL' A CELL (Versions 97 - 2003)

You can prefill a cell with characters like *'s, etc, regardless of the size of the column
In Excel '97 - 2003, you would:

TIP #26: 'AUTOSHOW TOP 10' IN A PIVOT TABLE (Versions 2000 - 2003)

You can Autoshow the Top 10 values for a Row or Column Field in a Pivot Table. Double click on one of the Row or Column Fields in your Pivot Table and then click the Advanced button. Select the “On” option button for Top 10 Auto Show. You can choose the number of the Top to display. For example, you can change the 10 to a 3 to display the top 3.

To turn the 'Autoshow Top 10' off, double click on the Row or Column field again, click the Advanced button and select the Off option button.

TIP #27: USING A CARRIAGE RETURN TO INSERT A LINE BREAK WITHIN A CELL (All Versions)

You can insert line breaks when you want labels or headings on multiple lines in your Excel worksheet, just as you can in WORD.

TIP #28: QUICKLY SEE ALL FORMULAS ON YOUR SPREADSHEET (All Versions)

Touch CTRL/~ (tilde ~ character – upper left of keyboard)

Another method is to select the Tools menu, select Formula Auditing and choose the Formula Auditing Mode.

TIP #29: WHAT DO THOSE FORMULAS ERRORS MEAN IN EXCEL? (All Versions)

When you type a formula in EXCEL, you will be able to tell that you have an error when, rather than seeing your calculated value, you get a strange entry in all uppercase letters beginning with the number sign (#) and ending with an exclamation point (!) or, in one case, a question mark (?) – this entry is known as an ‘error value.’

The error value lets you know that some part of your formula, either in the formula or the cell(s) referenced – is not correct and that Excel can’t perform the calculation. Error values can contaminate other formulas in the worksheet. If a formula returns an error value to a cell and a second formula in another cell refers to the value calculated by the first formula, the second formula returns the same error value and so on down the line.

When you get an error in your formula cell, you’ll need to determine what caused the error and fix it.

#DIV/0!
Appears when the formula calls for division by a cell that either contains the value 0 or, as is more often the case, is empty. Division by zero is a no-no.

#NAME?
Appears when the formula refers to a range name that doesn't exist in the worksheet. This error value appears when you type the wrong range name or fail to enclose in quotation marks some text used in the formula, causing Excel to think that the text refers to a range name.

#NULL!
Appears most often when you insert a space (where you should have used a comma) to separate cell references used as arguments for functions.

#NUM!
Appears when Excel encounters a problem with a number in the formula, such as the wrong type of argument in an Excel function or a calculation that produces a number too large or too small to be represented in the worksheet.

#REF!
Appears when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over the cells referred to in a formula.

#VALUE!
Appears when you use the wrong type of argument or operator in a function, or when you call for a mathematical operation that refers to cells that contain text entries.

TIP #30: MULTIPLE UNDO CAPABILITY IN EXCEL (Versions 97 - 2003)

Excel has the capability to perform multilevel undo’s.

You can reverse actions you’ve performed on your worksheet by undoing what you’ve done.

Example: If you format cells, change data, or accidentally delete data, you can UNDO what you’ve done, even though you may have performed other actions afterward (EDIT/Undo from the menu or the [UNDO] button on the toolbar).

Undoing your actions can only be done sequentially; if you want to undo a particular action, you must also undo all of the actions that you performed AFTER the action that you want to undo. Excel allows you to UNDO 16 operations that you perform.

TIP #31: COLOR CODE SHEET TABS (Version 2002 & 2003)

You can color-code sheet tabs for easier identification or grouping of related sheets. Here's how:

1. Select the sheets you want to color by holding down the CTRL key and clicking the tab(s)
2. Right-click the sheet tab and then click Tab Color.
3. Select the color you want, and then click OK.

TIP #32: ROTATE YOUR 3-D CHARTS IN EXCEL (Versions 97 - 2003)

When you create a 3-D chart (like a 3-D pie chart) in Excel, you may want to rotate the chart so that the data that you want to present is located in a different position.

TIP #33 - HOW DO YOU CHART MISSING DATA IN AN EXCEL CHART? YOU CAN CHOOSE FROM SEVERAL OPTIONS (All Versions)

1. Not Plotted - If you choose not to plot missing data, Excel will leave a gap for each
missing data point.
2. Zero - If you choose zero, Excel will treat the missing data as a zero.
3. Interpolated - If you choose interpolated, Excel will use data on either side of the
missing data point to calculate a value for you.

To select one of the above options for your chart, from the 'Tools' menu, choose 'Options'.
Select the 'Chart' tab and select "Not Plotted", "Zero" or "Interpolated".

Note: The option you choose will apply to the entire chart. If your chart contains
more than one data series, you can NOT set different options for different series in the
same chart.

TIP #34 - USING THE NESTED IF FUNCTION TO ASSIGN LETTER GRADES BASED ON GRADE POINTS EARNED (All Versions)

You can use the following Nested If Function to read grade points (stored in column B in this example) and assign appropriate letter grades based on the grade points earned.

Type =IF(B2<60,”F”,IF(B2<70,”D”,IF(B2<80,”C”,IF(B2<90,”B”,”A”)))).

This function says that if the grade point average is less than 60, assign the letter grade F. If between 60 and 69, assign D. If between 70 and 79, assign C. If between 80 and 89, assign B. Else, assign an A.

TIP #35 - USE AN IMAGE AS THE BATS IN CHARTS (and other chart types) BY USING THE CLIPBOARD AND PASTING INTO CHARTS (All Versions)

When you create a chart, you can create a picture chart using the Clipboard and it isn't necessary to have the image in the file. This works as long as your clipart/image can be copied to the Clipboard.

Find the clipart/image that you want to use, and simply copy it to the Clipboard (CTRL/c or EDIT/Copy). You can also paste it into your Excel spreadsheet first and you can adjust the size, and make other color changes, etc. You can also create an image using the Drawing toolbar if you wish

After you've got the image in the Clipboard by copying, click on your chart to activate it.
Select the data series by clicking on the specific bars if a bar chart is used (the bars in the series should be selected).
Next, simply paste the image into the bars by using the menu selecting EDIT/Paste [or use the Paste toolbar button].
<your chart should now have the image in each bar that was selected>

NOTE: You can also use this to paste the image into only a single point in the data series (bar), rather than the entire series. Simply click to select the specific series you want to have converted to an image. This also works with data markers in line charts, XY (scatter) charts, or bubble charts.

Note: For Versions 2003 & 2003, you can click on the data series and from the Insert menu, select Pictures and then choose Clipart. From the task pane, enter a keyword search term and click Go. Choose the clipart image from the task pane and your data series will change to using this clipart image.

TIP #36 - LOCK CELLS SO CELL CONTENT CAN'T BE CHANGED (All Versions)

To lock cells in an Excel spreadsheet:

1. Unlock the default ‘locked’ cells in a spreadsheet, then lock the cells you want to stop someone from changing

2. Protect your sheet – this is what turns on the ‘lock’ setting and prohibits someone from changing the cells you locked in Step #1

TIP #37 - USING EXCEL FUNCTIONS WITH MORE THAN ONE CONDITION (All Versions)

Here are some examples of Excel Functions that first check to see if more than one condition is met, and if the conditions are met, will SUM, AVERAGE, COUNTIF, etc.:

Sample Worksheet:
Staff         Wage        Department
Yes          350.00        Accounts
No           100.00         Sales
Yes         200.00         Sales
No           300.00        Accounts

=SUM(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5)) Add wages for employees who are both Staff and in Accounts
=SUM(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5)) Add wages for all employees who either work in Accounts or who are Staff
=COUNT(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5)) Count the number of employees who either work in Accounts or who are Staff
=AVERAGE(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5)) Average the Wages for employees who are both Staff and in Accounts
=AVERAGE(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5)) Average the Wages for employees who either work in Accounts or who are Staff
=MAX(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5)) Find the MAX of Wages for employees who are both Staff and in Accounts
=MAX(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5)) Find the MAX of Wages for employees who either work in Accounts or who are Staff
=SUM(IF(((A2:A5="Yes")*(C2:C5="Accounts"))+((A2:A5="No")*(C2:C5="Sales")),B2:B5)) Calculate the Wages for employees who work in Accounts and are Staff, or those who are not Staff and who work in Sales

*Note: Remember that this is an array function, so you must press CTRL+Shift+Enter after you've typed it for it to work.

TIP # 38 - DECIDING WHEN TO USE EXCEL VS. ACCESS

Refer to the Following General Rules When You Need to Decide Between Access or Excel to Store Your Data:

TIP # 39 - ROTATE YOUR 3-D CHARTS (All Versions)

When you create a 3-D chart (like a 3-D pie chart) in Excel, you may want to rotate the chart so that the data that you want to present is located in a different position.

TIP #40 - PRINTING ERROR VALUES IN A WORKSHEET (Version 2002 & 2003)

A new feature in Excel 2002 gives you the option to print error values as they appear on your worksheet or to replace each of the error values with a predefined character in the printed output. Error values include #NUM!, #DIV/0!, #REF!, #N/A, #VALUE!, #NAME?, and #NULL!.

To select how you want error values to appear in your printed output, follow these steps:

Tip #41 - LIST OF SUPPORTED FILE FORMATS (Version 2002)

You can open and save files in many different file formats. A list of these is provided below:

Formats that Save the Entire Workbook
.xls - Excel Workbook versions 97-2002, 5.0/95
.xlt - Excel Template
.xlw - Excel 97 - 2002 Workspace, version 4.0 workbook (saves only worksheets, chart sheets, and macro sheets)
.htm - HTML pages
.mht - Web Archive
.xml - XML Spreadsheet

Formats that Save Only the Active Sheet
.xls, .xlc, .xlm - Excel version 4.0, 3.0 and 2.x sheet formats

Formats for Lotus 1-2-3 File Format
.wk4 - saves the entire workbook
.wk3 - saves only worksheets and chart sheets
.wk1 - saves only the active sheet
.pic (when included in an .all file) - opens but cannot save this format

Text File Formats
.prn - Lotus space-delimited format. Saves only the active sheet
.txt - If you are saving a workbook as a tab-delimited or comma delimited text file
.csv (Windows, MAC, OS2 or MS-DOS) - comma delimited
.dif - saves only the active sheet
.slk - saves only the active sheet

Other Spreadsheet Programs and Database Formats
.dbf (dBase II, III and IV) - open and saves only the active worksheet
.wq1 (Quatro Pro for MS-DOS) - opens and saves only the active worksheet
.wb3 (Quatro Pro version 7.0 for Windows) - opens only and converters required
.wks (MS Works 2.0 and MS Works for MS-DOS) - opens only MS Works 2.0 for Windows and MS Works for MS-DOS spreadsheets

Tip #42 - CORRECTION FOR RELATIVE HYPERLINKS NOT SAVING CORRECTLY (Version 2002 & 2003)

When you insert a hyperlink to an external document into an Excel Spreadsheet, the hyperlinks may not work when you reopen the file. If you open the properties for this hyperlink, you will see and extra "../" at the end of the hyperlink path.

Tip #43 - CHANGE THE DEFAULT FONT SETTINGS FOR WORKSHEETS (Versions 2000 - 2003)

Excel uses a default font and font size for cell entries in your worksheets. You can modify this by choosing the Tools menu, then choose Options and then choose the General tab. The default font that Excel uses will be listed in the Standard Font section. From here, you can change the font listed as well as the font size.

Note: If you want to change the font and/or font size for a single worksheet, do not change the Standard Font options. For a single worksheet or for particular cells (such as Title cells), use the Standard Formatting toolbar to make changes to the font. This will only make changes for the particular cell and/or worksheet.

You may find that you need to refer to a cell in a different worksheet contained in your Excel workbook/file. You can use the following formula: SheetName!CellAddress (the worksheet's name followed by an explanation point and then the cell address). For example, Sheet2!A1. However, if your worksheet name contains a space, you will need to surround the name of the worksheet with single quotation marks. For example, if your worksheet is named Financial Statements and you want to use the value contained in cell A1 in your formula, you would type: 'Financial Statements'!A1

Tip #46 - Permanently Remove Hidden and Collaboration Data (Versions 2002 & 2003)

With Office XP and Office 2003, you can use features such as file tracking, inserting comments, inserting hidden information, and collaborate with others when creating and editing files. However, there may be times when you want to remove this information from your file. A tool is available from Microsoft to remove this hidden/collaboration data. Visit http://www.microsoft.com/downloads/details.aspx?FamilyId=144E54ED-D43E-42CA-BC7B-5446D34E5360&displaylang=en to download this tool and once downloaded to your PC, complete the following installation steps:

Note: If you do not see the Remove Hidden Data command from the File menu, complete the following:

If you store zip codes, phone numbers or social security numbers in Excel, you can use the "Special" number format to appropriately format these numbers. Here's how:

When you type data in formatted cells, the format (for example, phone number ( ) and - ) will appear automatically and all you need to do is simply type the numbers and then move off the cell.

When your cursor is in a particular cell and you press the Enter key, your cursor moves down to the cell underneath the one you were previously on. You can change this and have Excel keep the cursor in the same cell or move the cursor to the cell above, to the left or to the right. Here's how:

When you apply a number format to a cell, this does not change the value but only how the value appears. For example, if a value in a cell is .79888 and you changed for number format to Percentage with no decimal places, the cell's value would appear as 80%. If this cell was used in a formula, the value used for the formula would still be .79888. However, you can set Excel to change worksheet values to match their displayed format (in our example, use 80% as the value for the cell rather than .79888). To do this, select the Tools menu, choose Options, select the Calculations tab and then check the 'Precision as displayed' check box.

Tip #50 - Indent Cell Text (All Versions)

If you want to indent information you have within a cell, you can use the [Increase Indent] button on the Formatting toolbar. On the toolbar, you can find the button to the left of the Borders button. It looks like an arrow with lines to the right of the arrow. Each time you click this button, Excel will indent what you've entered in the cell to the right by one character width of your standard font.

Tip #51 - Use Relative Cell References When Recording a Macro (All Versions)

If you want to create a macro where the references are relative, you can click the [Relative] button on the Excel MACRO toolbar that appears (when you begin to record a macro).

TIP #52 - Changing a Worksheet’s Tab Name (All Versions)

You can rename the default sheet tab names (Sheet1, Sheet2, etc.) of Excel worksheets. Sheet names can be up to 31 characters long.

The quickest shortcut to rename a sheet tab is to:

Doubleclick on the sheet tab
Type a new name
Touch [ENTER] (or click anywhere on your sheet)

Two other methods to rename a sheet tab are:

From the menu, FORMAT/Sheet/Rename
OR
Right-click tab and select Rename

NOTE: Sheet tab names can include spaces but NOT the following characters:
[ ] (brackets)
: (colon)
/ (slash)
\ (backslash)
? (question mark)
* (asterisk)

TIP #53 - Viewing Visual Basic Code While Recording a Macro (All Versions)

You can view the Visual Basic code that is created while you are recording a macro. Before you start recording, open the Excel workbook (or create a new workbook), make sure that Excel is not maximized, press Alt+F11 and you will see the Visual Basic Editor appear. Move and/or resize your Excel and Visual Basic Editor windows so that you can view both programs. In Excel, select the Tools menu, Macro, Record New Macro, and click Ok. Click in the Visual Basic Editor window to activate it and select Module 1 to view the code window. Record your actions in Excel and view the code window to see the code behind your recorded actions.

TIP #54 - Debugging Formula Errors (All Versions)

When your Excel formulas do not work as they should, you need to "debug" the formula to identify and correct the error. Formula errors can involve the following types of errors:

1. Syntax errors - the formula syntax is wrong. As an example, you may have a beginning parenthesis without a matching closing parenthesis or you misspelled a function such as SUMM rather than SUM or AVERAGGE rather than AVERAGE.

2. Logical errors - this may not be an obvious error, but your result is not what you expected. For instance, =SUMIF(A1:A3,">100",A1:A3) would not include a value of 100 in your result since 100 is not greater than 100. Perhaps you would want to change the formula to =SUMIF(A1:A3,">=100",A1:A3) to include an observation of 100 in your sum.

3. Incorrect reference errors - this may occur if you include an incorrect cell reference in your formula. As an example, you may need to modify your range reference if a SUM function is not adding all the observations correctly.

4. Circular references - you will see this error if you type in a formula in a particular cell (i.e. C3) and this same cell is referenced in the formula. For example, suppose in cell C3 you enter =SUM(C1:C3). You will get a circular cell reference error since C3 is included in the range to sum as well as the cell containing the formula.

5. Array formula entry errors - When inserting an array formula (you want Excel to use one cell's value in a formula, then use another cell's value, than another cell's value, etc.), you must press CTRL+Shift+Enter to enter the formula rather than simply pressing the Enter key. CTRL+Shift+Enter informs Excel that this is an Array Formula.

6. Incomplete calculation errors - This type of error is rare and is a bug identified by Microsoft. To ensure that your formulas will calculate completely, press Ctrl+Alt+F9.

TIP #55 - Sorting by More than One Column/Field (All Versions)

If you want to sort your Excel worksheet by more than one column/field, you can select the Data menu, and select Sort. The Sort dialog box will appear. In the Sort By drop down list, select the first field on which you wish to sort and then choose Ascending or Descending. Repeat for the second and third sort fields if appropriate. If you wish to sort by more than three fields, you can first sort by field3, field4, and field5 and once these fields are sorted, resort your worksheet by field1 and field2.

You can use the following shortcut to quickly move from one worksheet to another within an Excel workbook file.

To move to the NEXT worksheet (tab), touch CTRL/PgDn

To move to the PREVIOUS worksheet (tab), touch CTRL/PgUp

When you reach the end of your sheet tabs, it will loop back to the beginning tab

TIP #57 - Configure your Open Dialog box to Point to a Different Folder Instead of My Documents (All Versions)

You can configure your open dialog box to point to a different folder rather than pointing to the My Documents folder. Here's how:

1. Choose the Tools menu and select Options.

2. In the Options dialog box that appears, select the
General tab.

3. In the Default File Location text box, enter the path
to the new default starting folder.

4. Click OK.

TIP #58 - Two Methods to Copy MS Excel Data to MS Word (All Versions)

First Option - One way to copy Excel data into Word is to have both programs open and have MS Word minimized to the Windows task bar. In MS Excel, highlight the cells or chart you want to copy, press and hold the Ctrl key, and drag your mouse over the minimized MS Word program on the WIndows task bar. Word will maximize and drag your cursor and let go within the document of the maximized MS Word program.

Second Option - Another method is to cut and paste the data/chart into a MS Word document. In MS Excel, select the cells and press Ctrl+C or from the Edit menu, choose Copy. Within Word, place your cursor where you want to insert the Excel data and select the Edit menu, and select Paste Special. Select Microsoft Excel Worksheet Object and click Ok. Word will embed the data in the document.

Tip #59 - Quickly Change the Format of a Cell's Value (All Versions)

To quickly change the formatting of a cell, place your cursor in the cell and press Ctrl+1. This will open the Format Cells dialog box.

Tip #60 - "Calculate Your Net Worth" Template (All Versions)

You can visit http://go.microsoft.com/?linkid=2108655 to download Microsoft's "Calculate Your Net Worth" template for Excel.

Tip #61 - Shortcut Key to Quickly Change Font Size (All Versions)

If your Formatting toolbar is displayed and you press Ctrl+Shift+P, the Font Size control of the formatting toolbar will be selected and you can type the font size you desire and press Enter. However, if your Formatting toolbar is not displayed and you press Ctrl+Shift+P, the Format Cells dialog box will display with the Font tab selected.

Tip #62 - Saving as a .CSV File (All Versions)

If you save your Excel file as a .csv file type (comma separated value), you can export the file to other programs that may not be able to read Excel .xls files directly. However, be aware that this file type is a text file and only contains the data. No formulas, formatting or charts included in your original Excel file will be included. However, if you want to save your formulas to a text file, you can...

1. Select the Tools menu and choose Options.

2. Check the Formulas check box to display formulas instead of the formula values in your spreadsheet.

3. Save your spreadsheet as a .csv file.

Tip #63 - Excel Error Checking (Versions 2002 & Later)

The Error Checking feature in Excel will alert you to various types of potential errors in a worksheet: These error types include the following:

Formula Errors - such as #Div/0!, #Value!, #N/A, etc. - See Tip #29 for a complete list of formula errors and the type of error each indicates.

Text Dates - dates formatted as text and entered with two-digit rather than four-digit years.

Numbers - numbers formatted as text.

Inconsistent Formulas - formulas entered within a region of cells that differ from surrounding formulas.

Formulas that Omit Region Cells - formulas entered that do not include all of the cells within the specified range (such as the last cell of a range).

Formulas Containing Unlocked Cells - formulas entered in a protected worksheet and containing unlocked cells.

Formulas with References to Empty Cells - formulas entered and containing references to empty cells.

Tip #64 - Printing Embedded Charts (All Versions)

In Excel, you can create a chart that exists on a separate sheet or exists on the same sheet as the data. If the chart is on the same sheet as the data, this is considered an "embedded" chart. To print the data and chart, you would simply print the sheet. However, if you want to print the chart only, you can follow these steps:

Right click on the border of the chart and select Chart Window from the menu that appears.
Right click on the Chart's title bar and select Print from the menu that appears.
At this point, you can close the Chart Window by simply clicking on the red X appearing in the top, right corner.

Tip #65 - Special Number Formats (All Versions)

You can change your cells' format:

Highlight the cell(s) you want to format. Then, from the menu, select Format/Cells, and click the Number tab.

The Special category contains a Zip Code option, Zip Code + 4, Phone Number (including an area code enclosed in parentheses and a dash separator), and Social Security Number (including dashes).

Tip #66 - "From Existing Workbook" Task Pane Option (Version 2002 and above)

When you want to create a new spreadsheet that is an altered version of an original, you probably open the original and then select the File menu, select Save As, and provide a new file name to create a new file. However, this method makes it very easy to accidentally open the original file, start making changes and then Save the file overwriting the original. Another (and safer) option is to use the "From Existing Workbook" link in the Task Pane. This link will appear in the Excel Task Pane if you select the File menu and choose New. If the Task Pane does not automatically open, select the View menu and choose Task Pane. In the Task Pane, click on the "New from Existing Workbook" link and a dialog box will appear. Browse to the file you wish to modify, select it and click the 'Create New' button. Excel will open a copy of the original file and automatically name this new file the same file name with a 1 appended to the end. If your original file is called MyExcelFile.xls, the new version of this file would be named MyExcelFile1.xls. When you click the Save icon, you will be given the opportunity to keep this new file name or you can change it. This new, modified copy of the original file will be saved ensuring that the original will not be overwritten.

Tip #67 - Defining a Print Area (All Versions)

If you would like to print only a specific part of your spreadsheet, you can configure a Print Area in Excel. Here's how:

Highlight the cell range that you would like to set as your Print Area.
Select the File menu, select Print Area and choose Set Print Area.

When you print the spreadsheet, only this defined area/cell selection will print. If you want to clear the Print Area, you select the File menu, choose Print Area and then choose Clear Print Area. After clearing the Print Area, the entire worksheet will print after selecting Print from the File menu.

Another method to set and clear the Print Area is to select the File menu, choose Page Setup and select the Sheet tab. Place your cursor in the Print Area text box and type the cell range (or select the range by highlighting it in the Excel sheet) and click Ok. To clear the Print Area from this dialog box, select the cell address in the Print Area text box and press the Delete key or Backspace.

Tip #68 - Turning Off the Error Checking Option (Versions 2002 and above)

If Excel locates what it believes might be an error in your worksheet, it will flag the cell with a small, green triangle in the upper-left corner. If you want to turn off this error checking feature, you can follow these steps:

1. Select the Tools menu and choose Options.

2. In the Options dialog box that appears, select the Error Checking tab and uncheck the "Enable Background Error Checking" check box.

3. Click Ok to close the Options dialog box. All existing error indicators will disappear and Excel will stop checking for errors.

Tip #69 - One Line Macro to Delete Hyperlinks (All Versions)

If you have a worksheet and want to remove all hyperlinks, you can create and use this one-line macro:

1. Press Alt+F11 to open the Visual Basic Editor and type the following code:

Sub DeleteHyper( )

ActiveSheet.Hyperlinks.Delete

End Sub

2. Save the Macro and close the Visual Basic Editor.

3. Run the macro and all hyperlinks contained in your worksheet will be removed.

Note: If you wish to remove just one or a few hyperlinks, you can right click on the individual hyperlink(s) and select Remove Hyperlink from the menu that appears.

Tip #70 - Select Cells Containing a Specific Color (Version 2003)

You can quickly select cells formatted with a specific cell color by following these steps:

1. Press Ctrl+F to open the Find and Replace dialog box. The Find tab will be selected and verify that there is nothing in the "Find What" text box.

2. Click the Options button and then click the Format button to open the Find Format dialog box.

3. Select the Patterns tab and from the list of colors, choose the color you want to find and click Ok.

4. Back in the Find and Replace dialog box, click Find All and you will see a list of addresses of cells formatted with the color you specified.

5. Click on one of the cell addresses listed at the bottom of the dialog box and Excel will select the appropriate cell in the worksheet.

6. Press Ctrl+A to select all the addresses in the dialog box.

7. Click Close and all the cells formatted with the specified color will be selected.

Tip #71 - Format a Row Based on the Value in a Cell in the Same Row (Version 2003)

You can conditionally format a row of cells based on the value of a cell within the same row. For example, suppose you have a spreadsheet displaying student names along with test percentage scores as shown below:

Name	Score
Sue	52
Debby	68
Nancy	75
Joe	85
Fred	95

Using this example, you can tell Excel to format score values with a red font color if the student's score is less than 50, with a green font color if between 50 to 59, and with a blue font color if between 60 and 69. Here's how:

1. Highlight cells A2 through B6.

2. From the Format menu, choose Conditional Formatting.

3. Select Formula Is from the Condition 1 drop-down list.

4. Set the condition to =$B2<50, click the Format button and set the font color to red. Click Ok to return to the Conditional Formatting dialog box.

5. Click the Add button, select Formula Is from the Condition 1 drop-down list and enter an additional formula, such as =$B2<60. Click the Format button and set the font color to green. Click Ok to return to the Conditional Formatting dialog box.

6. To add another conditional formula, click the Add button again, select Formulas Is from the Condition 1 drop-down list and enter an additional formula, such as =$B2<70. Click the Format button and set the font color to blue. Click Ok to return to the Conditional Formatting dialog box.

5. Click Ok to close the Conditional Formatting dialog box.

Scores less than 50 will be red, scores between 50 to 59 will be green and scores 60 to 69 will be blue.

Tip #72 - Force Rounding to Nearest Integer (Versions 97 and higher)

You can round to the nearest even integer using the Even function. For example, If the value in cell B2 were 12.1, =EVEN(B2) would return 14.

You can round to the nearest odd integer using the Odd function. For example, If the value in cell B2 were 12.1, =ODD(B2) would return 13.

But suppose you wanted to round to the nearest integer closest to zero? For example, if the value is 12.1, you want it to round to 12. If the value is -12.1, you want it to round to -12. To accomplish this, you need to enter a formula that first divides the value by 2, then rounds that value to an integer, and multiplies by 2. In our example, we would enter the formula =INT(B2/2+0.5)*2. This would round 12.1 to 12 and round -12.1 to -12. In other words, to the nearest integer closest to zero.

Tip #73 - "Spreadsheet" vs. "Worksheet" - Which Term is Correct? (All Versions)

EXCEL -- "Spreadsheets" vs. "Worksheets"

Did you ever wonder if you should refer to a sheet within a MS Excel file as a
"spreadsheet" or as a "worksheet"? You've probably heard and perhaps even used both terms.

MS Excel is considered a Spreadsheet program and sheets within an Excel file are referred to as worksheets. In other words, MS Excel is a spreadsheet program that produces worksheets but not a worksheet program that produces spreadsheets.

Tip #74 - Using Data Entry Forms (All Versions)

Did you know that you can use data entry forms in Excel?

1. Make sure your spreadsheet has descriptive column headings and place your cursor in any column heading cell.

2. From the Data menu, choose Form and click New.

3. You will see text boxes appear using column headings as the labels. Insert new record data into the appropriate fields and press Enter to save the record. You can repeat to add additional records. Click Close when you are finished adding records.

Tip #75 - Protecting Print Settings for Shared Worksheets (All Versions)

If you share your worksheet, other users may modify print settings to address their specific printing needs and/or printer. You can create a macro that will automatically run every time the workbook is opened and reset print settings to your specifications. The following is a sample of the code that could be included in the macro:

Sub Auto_Open

With ActiveSheet.PageSetup

.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(1.5)
.RightMargin = Application.InchesToPoints(1.5)
.TopMargin = Application.InchesToPoints(1.5)
.BottomMargin = Application.InchesToPoints(1.5)
.HeaderMargin = Application.InchesToPoints(1)
.FooterMargin = Application.InchesToPoints(1)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
.PrintArea = "MyPrintArea"
.PrintTitleRows = ""
.PrintTitleColumns = ""

End With

End Sub

Of course, you would modify the code to reflect your workbook's specifications.

Name the macro Auto_Open so that it will automatically run every time the workbook is opened.

Tip #76 - Using the Substitute Function to Replace Characters (Versions '97 and higher)

Suppose you wish to replace one character (such as a dash) with another character (such as a period). Here's the function that would do this:

=SUBSTITUTE (C2, "-",".")

The function above would take the value in cell C2 and replace any dashes with periods. For example, if 999-99-9999 originally appeared in cell C2, running this function would change it to 999.99.9999.

Tip #77 - Exporting Excel Data to AutoCAD (Versions '97 and higher)

If you import from Excel to AutoCAD and see that AutoCAD cuts off some of the worksheet, this may be due to AutoCAD importing a "picture" of the worksheet rather than the actual data. To remedy this, export the Excel data in CSV format (text file format) and then import it into AutoCAD. Or, select the Excel data you wish to copy, press Ctrl+C and then in AutoCAD, select the Edit menu, choose Paste Special and choose AutoCAD Entities and click Ok.

Tip #78 - Dragging to Clear Cells (Versions '97 and higher)

A quick and easy way to clear cell content is to first select the cell range, then use the fill handle (small, black square appearing in the bottom right of your selection) and drag back over the selected cells. The cells will be "grayed-out" and when you let go of the left mouse button, the content of the selected cells will be deleted.

Note: You can press and hold the Ctrl key while dragging to clear any cell formatting along with the content.

Tip #79 - Formatting Subtotal Rows (Versions '97 and higher)

You can create a macro that would shade "Total" and "Grand Total" Rows with a different color, allowing them to stand out more easily. In the following macro code, Total Rows are highlighted yellow and Grand Total rows are highlighted orange. To create this macro, select the Tools menu, choose Macro, and then choose Visual Basic Editor. Type the following in the code window that appears:

Sub FormatTotalRows()

Dim rCell As Range

For Each rCell In Selection

If Right(rCell.Value, 5) = "Total" Then
Rows(rCell.Row).Interior.ColorIndex = 36
End If

If Right(rCell.Value, 11) = "Grand Total" Then
Rows(rCell.Row).Interior.ColorIndex = 44
End If

Next

End Sub

Select the Visual Basic Editor File menu and then select Save personal.xls. This adds the new macro to your personal.xls file. Close the MS Visual Basic Editor by selecting the File menu again and choosing Close and Return to MS Excel.

Back in Excel, drag to select all the data within your worksheet, including the subtotals. With the data selected, select the Tools menu, choose Macro, choose Macros and scroll through your list of macros to find PERSONAL.XLS!FormatTotalRows. Select this macro and click on the Run button. Your macro will run and highlight every "Total" row yellow and every "Grand Total" Row orange.

And since you saved this macro in your Personal.xls file, this "FormatTotalRows" macro will be available for any open worksheet.

Tip #80 - Evaluating Formulas (Versions 2002 and higher)

To step through a formula and see how Excel arrives at the result, follow these steps:

1. Place your cursor in the cell containing the formula.

2. In Excel 2002 and 2003, from the Tools menu, choose Formula Auditing and choose Evaluate Formula. (In Excel 2007, from the Formulas tab, under the Formula Auditing group, select Evaluate Formula

3. In the Evaluate Formula dialog box that appears, you will see the entire formula. Part of this formula will be underlined indicating the section of the formula that Excel will calculate next. Click the Evaluate button, and Excel will calculate this section of the formula and display the result. Excel will then underline the next part of the formula that will be calculated. Click Evaluate again and Excel will display the result. Repeat to allow Excel to calculate subsequent sections of your formula.

4. When you are finished using the Formula Evaluator, click Close to close the Evaluate Formula dialog box.

Tip #81 - Insert a Radical 'Root' Symbol (√) Within a Cell (Versions '97 and higher)

You can format a cell so that the radical root symbol (√) is displayed just to the left of the cell's value. To do this, follow these steps:

1. Place your cursor in the cell for which you wish to display a radical symbol.

2. In Excel 97-2003, from the Tools menu, choose Macro and then choose Visual Basic Editor. (In Excel 2007, from the Developer tab, select Visual Basic, then within the Visual Basic Editor, from the menu, select INSERT/Module

3. In the module window that appears, type the following Visual Basic code:

Sub Radical ()

ActiveCell.NumberFormat = ChrW*8730) & "General"

End Sub

4. Select the cell you want to format, and then run the macro (In Excel 97-2003, select the Tools menu, select Macro, select Macros, highlight the macro named radical and click on the Run button). In Excel 2007, from the Developer tab, select Macros, highlight the Radical macro, and click Run.

The macro will run and display the radical 'root' symbol (√) to the left of the cell's value.

Tip #82 - Format Painter (All Versions)

You can use the Format Painter to copy formatting from one cell to another. Here's how:

Format the cell with the appropriate font, alignment, borders, patterns and color and place your cursor in the formatted cell.
In Excel 97-2003, click on the Format Painter icon from the standard toolbar (in Excel 2007, from the Home tab, select Format Painter). Your mouse cursor will change to a thick, white cross with a paintbrush.
With your left mouse button pressed, drag the paintbrush pointer over all of the cells you want to format and Excel will apply the formatting to the selected cells. Release the mouse button and Excel will apply the format.

Note: You can format multiple cell ranges by double clicking on the format painter icon. When you are finished formatting cells, simply click on the format painter icon again to deselect the tool.

Tip #83 - Keeping Track of Saved Macros (Versions 2000 and higher)

Where you create a macro determines where Excel saves the macro.

If you create the macro while in a particular workbook, the macro will be added to a module in the workbook. To access the macro, make sure the workbook is not hidden by selecting Window menu, choosing Unhide, and selecting the appropriate workbook if it appears in the list. Then select the Tools menu, choose Macro and then choose Macros. A list of macros will appear.

If you create the macro in the Personal Macro Workbook, the macro will be added to the personal.xls workbook. To access the macro, make sure the personal.xls workbook is not hidden by selecting Window menu, choosing Unhide, and selecting the personal.xls workbook if it appears in the list. Then select the Tools menu, choose Macro and then choose Macros. A list of macros will appear.

Note: Macros saved in the personal.xls workbook are available to any open workbook in Excel.

If you create the macro in a new workbook, the macro will reside in a hidden module that is added to the new workbook. To view the macro, select the Tools menu, choose Macro, and then choose Macros. A list of macros within the new workbook will appear and you can select the Macro from the list.

Tip #84- Insert a Picture Into a Chart Area (Versions 97 and higher)

In Excel, you can inert a picture into your chart area or plat area. You can select a graphic file and the picture will become your chart area (or plat area) background. You should be careful that your picture background doesn't interfere with someone reading the chart itself.

Once your chart is created, to insert a picture into the chart area:

1. Right-click inside the chart area on the background, and select 'Format Chart Area'
2. Select the 'Patterns' tab, click the [Fill Effects] button
3. Once the Fill Effects dialog box appears, click the 'Picture' tab
4. Click the [Select Picture] button
5. From the Select Picture dialog box, browse your computer & click to select the picture you want
6. Click [Insert] button
7. Click [OK] twice to exit the dialog boxes ,your picture should appear as background of the chart>

Tip #85- Convert Formulas to Values (Versions 97 and higher)

In Excel, you can convert a formula to its calculated value to prevent future changes to the cell value. To remove a formula and keep only its result, follow these steps:

Select the cell (or range of cells) containing the formula(s) you wish to convert.
From the Edit menu, choose Copy.
Place your cursor where you would like to store the value, and from the Edit menu, select Paste Special.
In the Paste Special dialog box that appears, check the Values option button.
Click Ok to close the dialog box and press Enter to exit "Copy" mode.

Tip #86- Using AutoFill to Complete a Series in Excel (All Versions)

You can use AutoFill to complete a series of incremental values (such as numbering - 1, 2, 3, etc. or creating a list of dates - 1/1/06, 1/2/06, 1/3/06, etc.). For example, suppose you have a list of student names for a particular class. You can number this class list by completing the following.

In a column of cells next to the student names, enter the first two values in the series. In this example, it would be 1 in the cell next to the first student's name and 2 in the cell next to the second student's name.
Select these two numbered cells. Note: you must select both cells for the next step to work.
Click and drag the fill handle (bottom, right) to allow Excel to complete the numbered series. If you drag slowly, you will see the value Excel plans on inserting into a cell in the control tip.

Note: Rather than dragging with the left mouse button pressed, you can drag while pressing the right mouse button and upon release, you will see a list of AutoFill options. Choose Fill Series to fill the series sequentially.

Tip #87- Insert Automatic Decimal Points (All Versions)

If you normally input numbers with fixed decimal places, you can set an option in Excel to save
time setting the format for your cells. Excel would set the decimal places as a default so that you won't have to. You can think of this feature just as a calculator can be set for a number of decimal places.

To set the Excel option to use a fixed decimal place:

1. From the menu, select TOOLS/Options
2. From the Options dialog box, click the 'Edit' tab
3. Check the Fixed Decimal check box, and on the 'Places:' line
set the number of decimal places
4. Click OK to exit the Options dialog box

After you've set the decimal places in the Options dialog box, Excel will automatically set the decimal places for you - for example, if you enter 98765, Excel will interpret the number 987.65 automatically.

If you want to set your Options back, simple go into the Options again and uncheck the Fixed Decimal box.

A reminder: When you change the Fixed Decimal option, it does not alter any values that you already have entered.

Tip #88- Adding Trendlines to Charts (All Versions)

Trendlines can be added to your Excel charts to display a trend which is implied by charted data.

A simple way to add trendlines to your charts:

1. Select your chart
2. From the Excel menu select Chart/Add Trendlines

The 'Add Trendlines dialog box' will open. At this point, you would choose the type of trend in the Trend/Regression Type section and the data series on which to base the trend in the Based on Series list box. [Note: Linear is the default because it is very common.]

Tip #89- Change the Data Series for an Existing Pivot Table (All Versions)

If you create a pivot table and need to change the source range of your list (example: if you added additional rows of data to your sheet that generates the pivot table), you can change the data series to update the pivot table. Refreshing the pivot table does not identify additional rows that you add after you've generated the pivot table.

1. Click inside your generated pivot table
2. From the menu, select DATA/Pivot Table & Pivot Chart Report
3. Click the [BACK] button
4. change the data series that appears on the line when you first created the pivot table
5. Click [FINISH]