Transcript project_4 - Gonzaga University

Project 4
Formatting, Displaying, Printing, and
Publishing Workbooks
Jason C. H. Chen, Ph.D.
Professor of
Management Information Systems
School of Business Administration
Gonzaga University
Spokane, WA 99258, USA
[email protected]
Seven Steps for Developing
Excel Workbooks
2
Excel Skills









Add 3-D references to a workbook to consolidate
data
Add conditional formats to worksheets
Apply currency formats
Copy formulas that contain 3-D references
Create a data validation message
Create custom formats
Specify data validation criteria
Modify Page Setup options
Use the Report Manager
3
Key Terms

3-D references


Accounting format


Cell references appearing in formulas that refer to other
worksheets in the current workbook, or in other workbooks.
A number format that normally includes a currency symbol
that is left justified in the cell, comma separators, a decimal
point, and two figures to the right of the decimal.
Conditional format

Formatting for one or more cells that vary, depending upon
the values contained in the cell.
4
Key Terms

Consolidate data


A method for organizing or summarizing data in
Excel worksheets. You can consolidate data in four
ways - using 3-D references, by position, by
category, or in a PivotTable report.
Custom format

A custom format for a cell entry that use format
codes to describe how to display a number, date,
time, or text.
5
Key Terms

Data validation


A strategy for minimizing data entry errors by
limiting the values that can be entered into a cell,
and optimally, displaying a data validation
message explaining the acceptable range of
values.
Report Manager

An Excel add-in that helps you generate reports
from views and scenarios.
6
Objectives






Consolidate Data
Apply currency formats to a range of
worksheets
Create custom number formats
Validate data
Apply conditional formats to a workbook
Use the Report Manager create a report
7
Running Case


Selections, Inc. is a national department store
chain with retail stores throughout the United
States and Canada.
He now wants you to complete an Excel
workbook that summarizes the first quarter
revenue and expenses for five Selections, Inc.
stores for the first fiscal quarter of last year.
8
Project 4 Challenge



You will need to include revenue and expense data
for each store, as well as the total revenue and
expenses for the first quarter of last year.
You will need to summarize how the actual expenses
compare to the projected expenses. Since your
workbook will be used to track next year's revenue
and expenses, it must be able to validate data when
it is entered.
The data will be distributed as printed documents
and on the corporate intranet.
9
Solution
10
Features

Add and copy 3-D consolidation formula


Format cells - currency, custom number


Task 3, 4
Data validation - criteria and message


Task 1, 2
Task 5
Conditional formatting - up to three
conditions

Task 6
11
Task 1 (EX-116)
3-D reference formula
=Function(name of 1st worksheet :
name of 2nd worksheet!
Reference Cell)
12
Task 3, 4
13
Task 5: Data Validation Criteria and Message
14
Sample City Worksheet
15
Solution Workbook
16
Report Manager

Step 1: Modify page
setup and add custom
views

Task 8 (EX-130)
17
Report Manager (cont.)

Step 2: Create a report
using the Report Manager

Task 9
18
Web Applications:
Hands-On Exercises

Preview and print worksheets and workbooks


EX-137
Publish Excel data on the Web (Ex-140 )

interactively


Not interactively


must use Internet Explore 4.01 or higher
Netscape is OK
Click on


File --> Save as Web Page …-->
(Excel 97) File --> Save as HTML
19
Publish Excel data on the Web (Ex-140 )
20
Project 4
Formatting, Displaying, Printing, and
Publishing Workbooks