Tips and Tricks for Excel 2000

Analyzing and Managing Data

Make a Copy That Excludes Hidden Cells in Excel 2000

When you use outlining or subtotals to create a small summary of a large set of data, you can make a copy of just the displayed cells in the summary, excluding the detail. First display only the summary rows or columns, and then select all of the summary data. On the Edit menu, click Go To, click Special, and then click Visible cells only. Now click the Copy button, click a cell on a blank worksheet, and click the Paste button. The copy includes only the summary data. For more information about subtotals and outlines, type inserting subtotals and creating outlines in the Office Assistant or on the Answer Wizard tab in the Excel Help window.

Customizing Excel

Set High-Contrast Selection in Excel 2000

When you select cells, do you have trouble seeing what's selected? If you need a higher-contrast selection display to accommodate low vision, you can set Excel 97-style selection shading. Use the Registry Editor to add a registry subkey HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options\Options6 as a DWORD value, and set this subkey equal to 16 decimal.

Drawing Tools

Insert a Drawing Object Multiple Times in Office 2000

Have you ever wanted to insert the same drawing object several times in a row in your document, worksheet, or presentation? It's easy. Instead of clicking the drawing object button every time you want to insert the object, you can take advantage of the drawing object button's "sticky" feature.

1. To view the Drawing toolbar, right-click any toolbar and select Drawing.
2. To insert a drawing object several times, double-click the drawing object button (such as Rectangle) on the Drawing toolbar. The button stays selected or "sticky."
3. Insert your drawing object several times by clicking in the appropriate locations.
4. When you're finished inserting the objects, click the drawing object button again, or press ESC.

You can also turn any of the drawing objects available from the AutoShapes button on the Drawing toolbar into "sticky" drawing object buttons. Simply click the AutoShapes button, point to a category (such as Flowchart), and a submenu of shapes opens up. Drag the move handle at the top of the submenu to create a floating toolbar. Then double-click the drawing object button (such as Flowchart: Process). The button now stays "sticky."

More Information

For more information about drawing, see Use a Workflow Diagram in Your PowerPoint 2000 Presentation and Draw Flowcharts with Word 2000 and PowerPoint 2000.

Entering and Formatting Data

Apply Text and Graphics Formatting Multiple Times in Office 2000

Have you ever wanted to make several non-sequential words stand out by using a special font in your document? Or have you ever wanted to change certain solid lines to dotted lines in graphics created with the drawing tools? If you've ever wanted to apply the same format to items in different locations in a file, you might not realize how easy it is. Instead of clicking the Format Painter button on the Standard toolbar every time that you want to apply the new format, you can take advantage of the button's "sticky" feature.

1. Select the item whose format you would like to copy.
2. To copy the selected format to several items, double-click the Format Painter button. The button stays selected, or "sticky."
3. Select the text or graphic where you want to apply the new format.
4. When you're finished applying the format, click the Format Painter button again or press ESC.

Note  Applying text and graphics formatting multiple times does not work between programs. For example, you cannot apply formatting from Word to PowerPoint®.

More Information

For more information, type format painter or copy formats in the Office Assistant or on the Answer Wizard tab in the program's Help window, and then click Search. For related information, see Quickly Copy Formatting with the Format Painter in Office 2000 and Insert a Drawing Object Multiple Times in Office 2000.

Enter More Than One Line in an Excel 2000 Cell

Excel provides two ways for you to display multiple lines of data in a cell.

Type a line break   Press ALT+ENTER to start a new line while you're typing or editing data.

Wrap text automatically   Click the cell, click Cells on the Format menu, click the Alignment tab, and then select the Wrap text check box. Data in the cell will then wrap to fit the column width. You can make the column wider or narrower to adjust the width of the data. For more information, type change column width in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Enter URLs As Text in Excel 2000

When you type an Internet address in a cell, such as www.example.microsoft.com, Excel automatically turns the address into a hyperlink. To store the address as regular text instead, type an apostrophe (') before the address. For instance, if you type 'www.example.microsoft.com, you'll see the address text in the cell, without the apostrophe, and the text won't be a hyperlink. If Excel has already turned an address into a hyperlink, you can deactivate it: Right-click the cell, point to Hyperlink on the shortcut menu, and then click Remove Hyperlink.

Switch Rows of Cells to Columns or Columns to Rows in Excel 2000

Have you ever had a column of text that you wanted to move into a row so that you could use the text as column labels in a table? Or, have you ever had a row of data that you wanted to move into a table in which the data is organized in columns? It would be tedious to move the data manually, one item at a time. Instead, you can use the Paste Special command to transpose a column of data to a row of data, and vice versa.

Transpose a column of data...

Dairy

Meat

Beverages

Produce

...into a row of data.

Dairy

Meat

Beverages

Produce

Follow these steps to transpose a row of data into a column or data, or vice versa:

1. Select the cells that you want to switch.
2. Click Copy on the Edit menu.
3. Select the upper-left cell of the paste area. The paste area must be outside the copy area.
4. On the Edit menu, click Paste Special.
5. Select the Transpose check box.

Data from the top row of the copy area appears in the left column of the paste area, and data from the left column appears in the top row.

For more information on options in the Paste Special dialog box, click the ? in the upper-right corner of the dialog box, and then click the option you want to learn more about.

Formulas and Calculations

Calculate the Amount of Time Between Two Dates in Excel 97/2000

To calculate the number of days between two dates, you can simply subtract the two dates. For example, if cell A1 contains the date 6/8/2000 and A2 contains 6/20/2000, the formula =A2-A1 calculates the number of days between these dates (12).

Be sure to use number format for the cell where you enter this formula (click Cells on the Format menu, click the Number tab, and then click Number under Category). If you don't format the cell with number format, Excel assumes the result has the same format as the cells used in the calculation, and displays the result as a date instead of a number.

Create a Formula to Keep a Running Total in Excel 97/2000

In Microsoft Excel, you can calculate a running (or cumulative) total in a column or row of cells by using a combination of absolute and relative references in a formula that uses the SUM function.

	A	B
1	100	100
2	200	300
3	300	600
4	400	1000
5	500	1500

For example, to keep a running total of cells A1 through A5 in column B (for example, B1 contains the value from A1, B2 contains A1+A2, B3 contains A1+A2+A3, etc.), set up the worksheet as follows:

1. Enter the formula
    =SUM($A$1:A1)
   into cell B1, as shown here.

   	A	B
   1	100	=SUM($A$1:A1)
   2	200
   3	300
   4	400
   5	500

2. Select cells B1 through B10.
3. On the Edit menu, click Fill Down.
   
   The $A$1 (absolute reference) will be constant in each cell, while the A1 (relative reference) will be updated in each successive cell to refer to the adjacent cell in column A, as shown here:

   	A	B
   1	100	=SUM($A$1:A1)
   2	200	=SUM($A$1:A2)
   3	300	=SUM($A$1:A3)
   4	400	=SUM($A$1:A4)
   5	500	=SUM($A$1:A5)

   For more information on using absolute and relative references in Excel, type cell and range references in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. For more information on the SUM function, type SUM worksheet function in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Finding Worksheet Functions in Excel 2000

Can't remember the name of that Excel worksheet function you used last year to determine your mortgage payments? You can use the Paste Function dialog box and the Office Assistant to help you locate the function you want.

1. On the worksheet, select the cell you want to insert the function into.
2. On the Insert menu, click Function.
3. If the Office Assistant does not appear with a balloon asking if you want help, click the Office Assistant button in the lower-left corner of the Paste Function dialog box.
4. In the Assistant balloon, click Help with this feature or Yes, please provide help.
5. In the Assistant balloon, enter a brief description of what you want to do, such as amortize a loan, and then click Search.

For a list of functions to help you begin your search, see the Recommended category in the Function Category list in the Paste Function dialog box.

Forgot a Function's Arguments While Entering It in Excel 2000?

Press CTRL+A on your keyboard, and the formula palette is displayed to show you a description of the function, a description of each of the arguments for the function, and even the result of your calculation.

Hide Results When Formulas Calculate Error Values in Excel 2000

You may not want the results of a formula to be displayed in your worksheet when the formula calculation results in an error value. In Excel, you can hide error values by using conditional formatting or conditional formulas.

In the following example, the error value #DIV/0! is the result of a formula in cell D1 that tries to divide the value in cell C1 by the value in cell B1, which is empty:

You could delete the formula from the cell, of course. But, you want to keep it so that it calculates and displays valid results when a value is entered in B1. You can do this with conditional formatting in Excel 2000. Just follow these steps:

1. Select the cell or cells that contain formulas that may calculate error results that you don't want to display.
2. On the Format menu, click Conditional Formatting.
3. In the Conditional Formatting dialog box, click the Condition 1 list and click Formula Is.
4. In the box to the right of the Condition 1 list, enter the following formula:
   
   =ISERROR(cell_reference)
   
   where cell_reference is the relative reference of the active cell in the selection. In the example shown, the active cell in the selection is D1. When you select a range of cells to conditionally format, the formula must evaluate each cell in the range. However, when you enter only the relative reference of the active cell in the selection, Excel adjusts the references to the other cells relative to the active cell.
5. Click the Format button. In the Format Cells dialog box, click the white color in the Color list.
6. Click OK in the Format Cells dialog box, and then click OK in the Conditional Formatting dialog box.

In versions of Excel prior to Excel 97, you could not create a custom number format to hide error values returned to the cell by the cell formula. However, you can change the formula itself to automatically hide error results by using the ISERROR function in a conditional formula. So, instead of

=C1/B1

as in the example shown, you would use the formula:

=IF(ISERROR(C1/B1),"",C1/B1)

which returns an empty string ("") to the cell if an error value is calculated.

Keyboard Shortcuts

Useful Shortcut Keys in Excel 2000

Do you find that using the keyboard is sometimes quicker than using your mouse? Shortcut keys can help you bypass menus and carry out commands directly. You can use shortcut keys in many ways with Excel, from accessing commands and toolbar buttons to outlining and editing information. Shortcut keys are sometimes listed next to the command name on menus. For example, on the Edit menu, the Copy command shows the shortcut CTRL+C.

For a comprehensive list of shortcuts, ask the Office Assistant for help. In Excel 2000 or any of the other Office 2000 applications, press F1 to display the Assistant, and then type shortcut keys in the text box. Here are some of the most useful Excel shortcut keys:

Activity	Shortcut Keys
Select the current column	CTRL+SPACEBAR
Select the current row	SHIFT+SPACEBAR
Move to the beginning of the worksheet	CTRL+HOME
Move to the last cell on the worksheet, which is the cell at the intersection of the rightmost used column and the bottommost used row (in the lower-right corner), or the cell opposite the home cell, which is typically A1	CTRL+END
Paste a function into a formula	SHIFT+F3
When you enter a formula, display the Formula Palette after you type a function name	CTRL+A
Select all (when you are not entering or editing a formula)	CTRL+A
Alternate between displaying cell values and displaying cell formulas	CTRL+` (single left quotation mark)
Calculate all sheets in all open workbooks	F9
Calculate the active worksheet	SHIFT+F9
Create a chart that uses the current range	F11 or ALT+F1
Enter the date	CTRL+; (semicolon)
Enter the time	CTRL+: (colon)
Fill the selected cell range with the current entry	CTRL+ENTER
Display the Go To dialog box	F5
Display the Format Cells dialog box	CTRL+1
Copy	CTRL+C
Paste	CTRL+V
Undo	CTRL+Z
Save	CTRL+S
Print	CTRL+P
Open	CTRL+O

Additional Resources

For more information about keyboard shortcuts in Excel 2000, see the following:

• The One Excel 2000 Keyboard Shortcut Everyone Should Know
• The Best Keyboard Shortcuts for Selecting Data
• The Best Keyboard Shortcuts for Moving in a Workbook
• The Best Keyboard Shortcuts to Use with Functions and Formulas
• The Best Keyboard Shortcuts to Speed Your Day

In addition, you can download A List of Useful Office 2000 Shortcut Keys.

PivotTable Reports

Creating Excel 2000 PivotTable Reports Is Much Easier Now

Click a cell in your source data, click PivotTable and PivotChart Report on the Data menu, and then click Finish. Now you can drag the fields you want from the PivotTable toolbar to the outlined areas of the worksheet, and watch your PivotTable® report take shape.

Printing

Make Your Excel 97/2000 Printouts Fill the Page Width

When you print a worksheet, do you want it to use the full width of the paper, and take as many sheets of paper as required to print all the data? When you click Page Setup on the File menu, click the Page tab under Scaling, click Fit to, and select 1 page wide. In the second Fit to box for how tall you want the data, delete the number so the box is blank.