Objective 13. Use a Summary Sheet

A summary sheet is frequently used as a place where totals from other worksheets are displayed and charted.

Activity 1.23. Constructing Formulas that Refer to Cells in Another Worksheet

In this activity, you will use formulas in the Summary worksheet to display the total cost of gasoline from the other three worksheets. This technique updates the Summary worksheet whenever changes are made to the other worksheet totals.



Make the Summary worksheet the active sheet. Locate the value in cell B3 and recall that the new total for Austin #1 is 163.08this value is no longer correct.

As currently arranged, cells B3:B5 contain values that must be updated manually each time a change is made to the other worksheets that affect their values. A better method would be to construct formulas in these three cells that refer to the cells in the other worksheets, so that changes are automatic.


Select the range B3:B5 and press to delete the contents of the cells, and notice that the columns in the chart no longer display.

Because the chart is based on data from the cells whose contents you deleted, the chart can no longer display any data. You will replace the values in these three cells with formulas that update when changes are made that affect the totals on the other sheets.


Click cell B3. Type = and then click the Austin #1 sheet tab. On the Austin #1 sheet, click cell E12, and then press to redisplay the Summary worksheet.


Click B3 to select it again. Look at the Formula Bar and notice that instead of a value, the cell contains a formula that is equal to the value in a cell in another worksheet. Compare your screen with Figure 1.77.

Figure 1.77.

The Currency Style format with no decimal places that was applied to the cell is retained, and the chart is revised at the same time. By using a formula of this type, changes in cell E12 of the other worksheet will be automatically updated in this worksheet.


Click cell B4. Type = and then click the Austin #2 sheet tab. Click cell E12 and press . Use the same technique to copy the total from the Dallas sheet to cell B5. Save your changes.

The formulas in cells B3:B5 now display the totals from the other three worksheets. The format that was applied to the Summary worksheet cells before the contents were deleted is applied to the values from the formulas. If changes are made to the three detail worksheets that affect their totals, the new values will display on the Summary sheet and the chart will be updated accordingly. In this manner, the summary worksheet accurately displays the current totals from the other worksheets.

[Page 661 (continued)]

Objective 14 Format Worksheets in a Workbook

Windows XP

Outlook 2003

Internet Explorer

Computer Concepts

Word 2003

Chapter One. Creating Documents with Microsoft Word 2003

Chapter Two. Formatting and Organizing Text

Chapter Three. Using Graphics and Tables

Chapter Four. Using Special Document Formats, Columns, and Mail Merge

Excel 2003

Chapter One. Creating a Worksheet and Charting Data

Chapter Two. Designing Effective Worksheets

Chapter Three. Using Functions and Data Tables

Access 2003

Chapter One. Getting Started with Access Databases and Tables

Chapter Two. Sort, Filter, and Query a Database

Chapter Three. Forms and Reports

Powerpoint 2003

Chapter One. Getting Started with PowerPoint 2003

Chapter Two. Creating a Presentation

Chapter Three. Formatting a Presentation

Integrated Projects

Chapter One. Using Access Data with Other Office Applications

Chapter Two. Using Tables in Word and Excel

Chapter Three. Using Excel as a Data Source in a Mail Merge

Chapter Four. Linking Data in Office Documents

Chapter Five. Creating Presentation Content from Office Documents

Go! With Microsoft Office 2003 Brief
GO! with Microsoft Office 2003 Brief (2nd Edition)
ISBN: 0131878646
EAN: 2147483647
Year: 2004
Pages: 448

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net