Excel: Intermediate 2003

Excel Intermediate

Table of Contents

Sorting

Sorting With the Sort Buttons

Sorting With the Data Menu

Using AutoFilter

Working with Subtotals

Removing Subtotals

AutoCalculate

Formatting

Formatting Cells Using the Toolbar

Formatting Cells Using the Menu

Number Formatting

Custom Format Example – Rounding Millions

Alignment Formatting

Formatting Fonts

Formatting Borders

Drawing Borders

Formatting Shading or Patterns

Protecting a Worksheet - Formatting Protection

Making Changes to a Protected Worksheet

Unprotecting a Worksheet

Using AutoFormat

Using the Format Painter

Other Formatting Tips

Numbers as Text

Entering Fractions

Formatting Multiple Worksheets

Worksheet Tab Colors

Quick Data Entry

Financial Functions

Excel’s Financial Terminology

PMT Function

IPMT & PPMT

Absolute an d Relative Cell References

Absolute References

Negative or Positive Perspective

FV – Future Value

Rate

NPER

Using Logical Functions – IF Statements

Creating a Simple IF Statement

Creating a Nested IF Statement

Working with Comments

Adding Comments

Viewing Comments

Displaying an Individual Comment

Displaying or Hiding All Comments on a Worksheet

Moving a Comment Box

Hiding an Individual Comment

Editing a Comment

Deleting a Comment

Changing the Comment Identifier Name

Printing Comments

Hyperlinks

Drawing Objects

Turning on the Drawing Toolbar

Drawing a Text Box or AutoShape

Formatting the Object

Re-sizing an Object

Moving an Object

Auditing

Auditing Toolbar

Viewing the Auditing Toolbar

Precedents and Dependents

Viewing Cells From Other Worksheets or Workbooks

Troubleshooting Formulas

Copying a Formula to a New Cell While Retaining the Original Formula

Copying the Current Value of a Formula to a New Cell

Displaying Formula Results in Another Cell

Displaying and Printing All Formulas

Locating and Highlighting Formulas

Circular References

Consolidating Worksheets

Consolidating by Position

Consolidating by Category

Appendix

Conditional Formatting

Templates

Creating a Template

Using a Template

Changing a Template

Changing Excel’s Default Settings

Changing the Default Font

Changing the Default Folder/Directory

Changing the Number of Files Displayed at the Bottom of the File Menu

Changing the Number of Sheets in New Workbooks

Displaying Page Breaks Automatically on Screen

Stopping Excel from Automatically Advancing the Active Cell After You Press Enter

Using Custom Lists

Using the Fill Handle

Using the Fill Handle to Copy One Cell

Using the Fill Handle with a Range as the Pattern

Excel’s Default Custom Lists

Creating a New List as an Option for the Fill Handle

Using a Range of Existing Entries to Create a New List for the Fill Handle

Course Practice File

Copyright © 2004, Carlson School of Management, University of Minnesota

Excel: Intermediate 2003

Course Objectives – Excel 2003 Intermediate

At the end of this course, participants will be able to:

  • Sort data
  • Use AutoFilter
  • Work with subtotals
  • Format worksheets effectively
  • Format multiple worksheets
  • Use the function wizard to perform additional functions
  • Payment
  • IPMT
  • PPMT
  • PV
  • FV
  • Rate
  • NPER
  • IF and Nested IF statements
  • Work with comments
  • Create and use hyperlinks
  • Audit worksheets
  • Use Consolidation
  • Appendix
  • Conditional Formatting
  • Create Templates
  • Customize Excel’s Default Settings
  • Custom Lists

Copyright © 2004, Carlson School of Management, University of Minnesota

Excel Intermediate 2003

Sorting

Excel has powerful database capabilities. One of the most important features of databases is sorting in a meaningful order. You can use buttons on the Formatting toolbar to perform a sort on one column in a worksheet. Use the menu to perform a more advanced sort.

Sorting With the Sort Buttons

Follow these steps to use the sorting buttons.

  1. Click anywhere in the column by which you want to sort.
  2. Click the appropriate Sort button or . The entire worksheet sorts by that column.

Note: If you do not like the results of your sort, click the Undo button to return to the previous sort order.

Sorting With the Data Menu

Columns are used to designate the fields by which you want to sort. You can designate the columns by using column letters. If your worksheet has a row of text entries that contain column headings in the row immediately above the data you want to sort, Excel reads those entries as a header row when sorting. Follow these steps to perform a sort by more than one column.

  1. Click anywhere in the list you want to sort.
  2. Select Data, Sort. The Sort dialog box displays.
  1. In the Sort by field, indicate the first column by which you want to sort.
  2. In the Then by field, indicate the second column by which you want to sort.

  1. In the Then by field, indicate the third column by which you want to sort.
  2. In the My list has area, indicate whether your list has a header row.
  3. Click OK. The list sorts according to your specifications.

Using AutoFilter

You can also filter your data to see only certain items. Perhaps you want to see only students from a certain state or city. The data sheet needs to have some common records in fields, e.g., state, product, region, city, etc. so that you can filter by specific fields. The exception is the Top 10 filter.

Follow these steps to use AutoFilter. You can apply multiple filters.

  1. Click anywhere in the list you want to sort.
  2. Select, Data, Filter, AutoFilter. Each column heading displays with next to it.
  1. Click to select by which classification in that column you want to filter your data.

Note: If you filter a numerical column, you can select to sort by Top 10 (or another number you specify).

Note: Use Custom filter to filter by more than one option or by a numerical range.

  1. To turn off AutoFilter, select Data, Filter, AutoFilter.

Working with Subtotals

Excel can also create subtotals based upon your column headings. This can be a great way to quickly analyze your data. The data sheet needs to have some common fields, e.g., state, product, region, city, etc. so that you can subtotal by a specific field.

  1. Sort your data first by the main column you want to use (e.g., Region).
  2. Click anywhere in the list you want to subtotal.
  3. Select, Data, Subtotals. The Subtotal dialog box displays.
  1. Select the desired options. Note: To use multiple subtotals for different columns, deselect “Replace current subtotals.”
  2. Click OK. A screen similar to the following displays.
  1. If desired, click the buttons to expand or collapse the information. (You can also click + or – to expand or collapse a single category of information.) If you used multiple subtotals, more outline options display.

Note: You can print this view, if desired.

Removing Subtotals

To remove subtotals, select, Data, Subtotals. The Subtotal dialog box displays. Click .

AutoCalculate

You can have Excel perform a quick calculation for you. This does not enter a result in a cell, but quickly displays a result for you on the Status bar.

  1. Select the range of cells you want to autocalculate.
  2. Right-click on the Status bar (see the illustration below) and select the appropriate option. The answer displays on the Status bar.

Formatting

You can format your worksheets to more effectively communicate your information. Formatting can be done on individual cells or ranges of cells. Excel now also has the ability to conditionally format cells – make cells that meet certain conditions display in a different format. You can even format cells to only accept certain kinds of data.

Formatting Cells Using the Toolbar

You can do a lot of standard formatting using the Formatting toolbar. As you rest your mouse pointer over each button, it displays its purpose. Select the cell(s) you want to format, then click the appropriate tool.

Formatting Cells Using the Menu

There are more formatting options available from the Format menu. Select the cells you want to format, then do one of the following:

  • Select Format, Cells
  • Press Ctrl + 1
  • Right-click. Select Format cells.

The Format Cells dialog box displays. Activate the appropriate tab, make your formatting selections, then click OK. Some of the formatting options are described on the following pages.

Number Formatting

Activate the Number tab. Choose the appropriate option from the Category: field. An example of the selected option displays in the Sample area of the dialog box. A description of the format also displays.

Number – Allows you to specify the number of decimal places, whether to add commas to separate groups of 3 digits, and the way to display negative numbers.

Currency vs. Accounting – These are very similar. Currency is just like Number, except a currency symbol like $ can be added. Accounting adds the symbol in a slightly different position and automatically displays negative numbers with parentheses.

Special – Special formats include social security numbers, phone numbers, and zip codes.

Custom – Custom allows the user to build a format. Use the following placeholders.

#holds the place for a digit that is to appear only if necessary

0holds the place for a digit that will display 0 if no other number is there

Custom Format Example – Rounding Millions

In the example below, cells A1, A2, and A3 all have the same digits entered. However, cell A2 and A3 have custom formatting. One comma at the end of the format is used to display it in thousands. Two commas tell Excel to round to the nearest million, etc. To create a custom format, you can type your format as described below. Consult Help for other custom formats.

  1. Select Format, Cells.
  2. Scroll down to Custom (left side).
  3. In the Type field, enter the custom format as shown, then click OK.
  4. To round to thousands (remove hundreds from the display in the cell), type #,###, (one comma at the end)
  5. To round to millions (remove hundreds and thousands from the display in the cell), type #,###,###,, (two commas at the end)

Alignment Formatting

You can format the alignment of the cell contents. In addition to standard horizontal alignments, you can also align text at an angle or vertically. The horizontal alignment is initially set to General. This aligns text to the left, numbers and dates to the right. Follow these steps to change the alignment.

  1. Select the cell(s) you want to format.
  2. Select Format, Cells. The Format Cells dialog box displays.
  3. Activate the Alignment tab.

  1. To change horizontal formatting, in the Horizontal: field, select the appropriate options.
  • To indent text that is left aligned, select Left (Indent) from the drop down list. Then increase the number in the Indent: field. For each number increase, the text moves by how many Xs using the current font could fit in the cell to the left of the entry.
  • To fill a cell with a pattern of characters across the width of a cell (e.g. all x’s) select Fill.
  • To center text across several cells, first select the cell you want to center and all other cells in this row over which you want the text centered. Then select Center Across Selection.
  1. To change the orientation or angle of the text, in the Orientation area, click and drag the red diamond to the desired degree of angle.
    or
    Click to display the text vertically (one letter underneath the other).

  1. To change the vertical alignment (where the text displays vertically within the cell height), in the Vertical: field, select the appropriate option.
  2. To change text control, select the appropriate option.
  • Wrap text displays text on several rows in the cell while leaving the column width the same.
  • Shrink to fit makes the font size smaller to fit in the cell.
  • Merge cells merges the contents of one cell over several selected cells.
  1. When finished selecting the appropriate options, click OK.

Formatting Fonts

You can format fonts from the buttons on the Formatting toolbar. To do more custom formatting, use the Format menu.

  1. Select the cell(s) to which you want to apply a border.
  2. Select Format, Cells. The Format Cells dialog box displays.
  3. Activate the Font tab.

  1. Select the appropriate options. Your selections display in the Preview area.
  2. Click OK.

Formatting Borders

You can format basic borders from the Border button on the Formatting toolbar. To do more custom border formatting, use the Format menu.

  1. Select the cell(s) to which you want to apply a border.
  2. Select Format, Cells. The Format Cells dialog box displays.
  3. Activate the Border tab.

  1. In the Line area, select the appropriate Style and Color.
  2. In the Presets area, select the appropriate option
    or
    In the Border area, indicate the type of border you want. Verify your selections in the display area of the dialog box.
  3. Click OK.

Drawing Borders

Excel 2003 now has a feature for drawing borders. Click the drop-down arrow from Borders button on the Formatting toolbar to display the palette below. Click Draw Borders to draw around the desired cells. Select the drawing options from the Borders toolbar before drawing.

Formatting Shading or Patterns

You can also format the background color of selected cells. You can add patterns, if desired, however, patterns make text difficult to read.

  1. Select the cell(s) to which you want to apply a border.
  2. Select Format, Cells. The Format Cells dialog box displays.
  3. Activate the Border tab.

  1. In the Cell shading area, select the appropriate color.
  2. To use a pattern, in the Pattern: field, select the appropriate pattern style from the drop down list. (NOT recommended for content.)
  3. Click OK.

Protecting a Worksheet - Formatting Protection

Use the Protection option to prevent valuable formulas from being overwritten. If an entire workbook is protected, no one is able to enter any data. If you want others to be able to add or edit information in specific cells or ranges, when protecting a worksheet, you must select all cells where data should be unprotected, and “unlock” protection on those cells. Follow these steps to protect “selected” cells of a worksheet.

  1. Select the cells you do NOT want to protect.
  2. Select Format, Cells. The Format Cells dialog box displays.
  3. Activate the Protection tab.
  1. De-select Locked to indicate that the selected cells can be changed. All cells not currently selected cannot be changed once the sheet is protected.

Note: You can also hide formulas. You can use the “Hidden” option for this purpose. Users will only see the value displayed in the cell–the formula will not display on the Formula bar when the cell is selected.

  1. Click OK.

  1. To protect the worksheet, select Tools, Protection, Protect Sheet. The Protect Sheet dialog box displays.
  1. Verify that the desired options are selected. Excel 2003 offers many more options than previous versions.
  2. If desired, enter a password.
  3. Click OK.

Making Changes to a Protected Worksheet

You can make changes to any unlocked cells. However, if you try to change a locked cell, the following prompt displays.

Click OK to return to the workbook.

Unprotecting a Worksheet

If you need to make changes to the locked cells, follow these steps.

  1. Select Tools, Protection, Unprotect Sheet.
  2. If prompted, enter a password, then click OK.

Using AutoFormat

You can use Excel’s auto-formatting feature to apply shading and fonts in predetermined formats.

  1. Open the worksheet you want to format. (Note: if you have blank rows in the worksheet, you need to select the cells you want to format.)
  2. From the menu, select Format, AutoFormat. The AutoFormat dialog box displays.
  1. Scroll through the options available and select the desired format.
  2. If desired, select Options to exclude some of the formats.
  1. Click OK. The selected format applies to the active worksheet or the selected cells.

Using the Format Painter

A great way to copy formatting from one location is by using the Format Painter button on the toolbar.

  1. Select the cell(s) that have the format you want to copy.
  2. Click the Format Painter button on the Standard toolbar.
  3. Click and drag over the cells to which you want to copy the formatting.

Note: To use the Format Painter for multiple locations in the worksheet, double-click the Format Painter button on the Standard toolbar. Click and drag over the cells you want to format. To deactivate the Format Painter, click the button again.

Other Formatting Tips

Numbers as Text

To enter numbers as text, e.g., part numbers that will not be calculated or zip codes, etc., type an apostrophe before the number. ’22 displays left aligned and would not be used in calculations. The apostrophe only displays in the Formula bar – not in the worksheet. Excel now displays green triangle in the upper-left corner of the cell to alert you of a potential formatting problem. Selecting the cell displays a button to assist you in the changing the format, if necessary. You can click the button to change to numbers if desired.