Project 4 Formatting, Displaying, Printing, and Publishing Workbooks

1. Jason C. H. Chen, Ph.D. Professor of Management Information Systems School of Business Administration Gonzaga University Spokane, WA 99258, USA Chen@gonzaga.edu Project 4 Formatting, Displaying, Printing, and Publishing Workbooks

2. Seven Steps for Developing Excel Workbooks

3. 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 Excel Skills

4. Key Terms • 3-D references • Cell references appearing in formulas that refer to other worksheets in the current workbook, or in other workbooks. • Accounting format • 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.

5. 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.

6. 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.

7. 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

8. 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.

9. 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.

10. Solution

11. Features • Add and copy 3-D consolidation formula • Task 1, 2 • Format cells - currency, custom number • Task 3, 4 • Data validation - criteria and message • Task 5 • Conditional formatting - up to three conditions • Task 6

12. Task 1 (EX-116) 3-D reference formula =Function(name of 1st worksheet : name of 2nd worksheet! Reference Cell)

13. Task 3, 4

14. Task 5: Data Validation Criteria and Message

15. Sample City Worksheet

16. Solution Workbook

17. Step 1: Modify page setup and add custom views Task 8 (EX-130) Report Manager

18. Step 2: Create a report using the Report Manager Task 9 Report Manager (cont.)

19. Web Applications:Hands-On Exercises • Preview and print worksheets and workbooks • EX-137 • Publish Excel data on the Web (Ex-140 ) • interactively • must use Internet Explore 4.01 or higher • Not interactively • Netscape is OK • Click on • File --> Save as Web Page …--> • (Excel 97) File --> Save as HTML

20. Publish Excel data on the Web (Ex-140 )

21. Project 4 Formatting, Displaying, Printing, and Publishing Workbooks