SELECT
Advanced Excel 2000
Published on 7. April 2000
Book
Hardback
369 pages
978-0-13-088544-9 (ISBN)
Description
The goal of this text is to introduce learners to intermediate and advanced spreadsheet management concepts, and using spreadsheet technology to manage business data. Through the projects in this text, learners build upon a basic understanding of Excel by creating more complex three-dimensional workbook solutions, using a full range of Excel's functions and tools. Utilizing a comprehensive approach to creating spreadsheet solutions, this text emphasizes the features in Microsoft Excel for integrating Microsoft Access data, and building web-based solutions for analyzing pivot table data. This text is MOUS certified at the Expert level.
More details
Language
English
Place of publication
United States
Publishing group
Pearson Education (US)
Target group
Professional and scholarly
Dimensions
Height: 216 mm
Width: 276 mm
Thickness: 15 mm
Weight
807 gr
ISBN-13
978-0-13-088544-9 (9780130885449)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Person
DR. PHILIP A. KONEMAN
has over ten years of experience in computers and education. He is an Associate Professor of Computer Information Systems and Advanced Computer Applications at Colorado Christian University. His teaching responsibilities include Introduction to Computer Applications, where students learn the primary applications in Microsoft Office 2000 Premium Edition.
Dr. Koneman has a broad professional portfolio that includes a variety of activities outside the university. He assisted Nivo International in defining the MOUS Certification objectives for Office 2000. In addition to being an author on numerous Select modules; he is also the Series Consulting Editor for the entire Select series. Dr. Koneman is President of Instructional Design Consultants, Inc., a company that develops educational multimedia and provides consulting for curriculum design and educational measurement.
Dr. Koneman received his Ph.D. from the University of Colorado at Denver, School of Education, Division of Instructional Technology. His research interests include computer-based learning, educational testing and measurement, and ethical issues in computers and technology. He is a certified Microsoft Office User Specialist for Office 2000.
has over ten years of experience in computers and education. He is an Associate Professor of Computer Information Systems and Advanced Computer Applications at Colorado Christian University. His teaching responsibilities include Introduction to Computer Applications, where students learn the primary applications in Microsoft Office 2000 Premium Edition.
Dr. Koneman has a broad professional portfolio that includes a variety of activities outside the university. He assisted Nivo International in defining the MOUS Certification objectives for Office 2000. In addition to being an author on numerous Select modules; he is also the Series Consulting Editor for the entire Select series. Dr. Koneman is President of Instructional Design Consultants, Inc., a company that develops educational multimedia and provides consulting for curriculum design and educational measurement.
Dr. Koneman received his Ph.D. from the University of Colorado at Denver, School of Education, Division of Instructional Technology. His research interests include computer-based learning, educational testing and measurement, and ethical issues in computers and technology. He is a certified Microsoft Office User Specialist for Office 2000.
Content
INTRODUCING EXCEL 2000.
Introduction to Microsoft Excel and Electronic Spreadsheets. Worksheets and Workbooks. Where Data Resides and What Kind of Data Worksheets Contain. Designing Excel Workbooks. Launching Excel 2000. Beyond the Basics: Exploring the Power of Microsoft Excel 2000.
I: DESIGNING COMPLEX WORKBOOKS.
1. Creating a Three-Dimensional Workspace.
Applying the Seven Steps for Designing Workbooks to the Selections, Inc. Prototype Workbook. Determining the Worksheet's Purpose. Entering Text Labels. Constructing Formulas. Creating a List of Products. Designing a Worksheet Template. Editing a Template. Creating the Selections Prototype Workbook and Adding Worksheets Based Upon the Selections Store Template. Completing the Structure of the Sales Summary Worksheet by Adding Text Labels. Adding Linking Formulas to Display the Product Information in the Sales Summary Worksheet. Saving the Selections Prototype and Products Workbooks as a Workspace.
2. Completing a Three-Dimensional Workspace Using Logical and Lookup Functions.
Displaying Product Descriptions in the Selections Prototype Workbook. Entering Sales Data for Each Selections Store. Creating Named Ranges. Sorting Data Ranges. Constructing Formulas in the Sales Summary Worksheet Using the Lookup Function. Using the Sum Function to Total the Sales Figures Returned by the Lookup Function. Using the IF Function to Determine the Store Region with the Greatest Number of Sales for Each Product.
3. Sorting, Grouping, and Filtering Sales Data.
Applying Number Formats to the Sales Figures. Creating a Macro for Automating Redundant Tasks. Hiding Worksheet Columns. Filtering Sales Data Using Autofilter. Running a Macro. Sorting the Sales Summary Data Using Multiple Criteria. Adding Subtotals to the Sales Summary Worksheet. Creating Custom Views. Showing Custom Views.
4. Formatting, Displaying, Printing, and Publishing Workbooks.
Consolidating Data Using 3-D References. Copying 3-D References. Applying Cell Formats. Creating a Custom Number Format. Using Data Validation to Minimize Data Input Errors. Using Conditional Formatting. Using the Report Manager.
II. ANALYZING DATA WITH EXCEL.
5. Creating Interactive PivotTable Reports.
Importing External Data into Microsoft Excel. Setting Up Data for a PivotTable. Using PivotTable Reports to Analyze Data Interactively. Grouping Data in the PivotTable Report. Organizing a PivotTable Report Using a Page Field. Modifying Fields in a PivotTable Report. Displaying PivotTable Reports on the Web.
6. Using the Analysis ToolPak to Analyze Sales Transactions.
Importing ASCII Data into Excel. Adding Additional Formulas to the Worksheet. Recoding Text Data as Numeric Data. Creating Data Sets Containing the Variables to Be Tested. Calculating Descriptive Statistics Using the Data Analysis Tool. Conducting an Analysis of Variance Comparing Means for Two Groups. Conducting an Analysis of Variance. Comparing Means for Three Groups. Displaying Sales by State on a Map Using Microsoft Map.
7. Forecasting Values with What-If Analysis: Using Data Tables, Scenarios, Goal Seek, and Solver.
Forecasting Future Values with a One-Variable Data Table. Forecasting Future Values with a Two-Variable Data Table. Using Goal Seek to Constrain Vales in a What If Analysis. Using the Trend Worksheet Function to Forecast Future Sales. Working with Scenarios. Using Solver to Determine Optimum Values.
III: INTEGRATING DATA, COLLABORATING WITH OTHERS, AND CUSTOMIZING EXCEL 2000.
8. Using Excel's Auditing and Collaboration Features.
Modifying a Workbook for Collaboration. Using Excel's Auditing Features. Protecting Worksheet Data by Locking Cells. Setting Up a Shared Workbook. Tracking Changes in a Shared Workbook.
9. Using Visual Basic for Applications (VBA) to Customize and Automate Excel.
Using Visual Basic for Applications and the Macro Recorder. Viewing the Contents of a Macro Using the Visual Basic Editor. Objects, Properties, and Methods. Using Macros to Automate Complex Tasks. Adding Statements to a Macro in the Visual Basic Window. Assigning a Macro to a Button Control. Running a Macro from a Button Control.
10. Sharing Excel 2000 Data with Other Microsoft Office 2000 Applications.
Using Microsoft Query. Changing File Properties. Embedding an Excel Workbook in a Microsoft Word 2000 Document. Using an Excel List as a Microsoft Word 2000 Mail Merge Data Source. Linking an Excel Worksheet to a Microsoft PowerPoint 2000 Slide. Creating and Updating Web Queries.
Appendix A: Working with Excel.
Navigating Worksheets and Workbooks. Entering Text, Numbers and Formulas. Using the Office Assistant.
Appendix B: Using Excel's Financial Functions to Amortize a Loan.
Introduction to Microsoft Excel and Electronic Spreadsheets. Worksheets and Workbooks. Where Data Resides and What Kind of Data Worksheets Contain. Designing Excel Workbooks. Launching Excel 2000. Beyond the Basics: Exploring the Power of Microsoft Excel 2000.
I: DESIGNING COMPLEX WORKBOOKS.
1. Creating a Three-Dimensional Workspace.
Applying the Seven Steps for Designing Workbooks to the Selections, Inc. Prototype Workbook. Determining the Worksheet's Purpose. Entering Text Labels. Constructing Formulas. Creating a List of Products. Designing a Worksheet Template. Editing a Template. Creating the Selections Prototype Workbook and Adding Worksheets Based Upon the Selections Store Template. Completing the Structure of the Sales Summary Worksheet by Adding Text Labels. Adding Linking Formulas to Display the Product Information in the Sales Summary Worksheet. Saving the Selections Prototype and Products Workbooks as a Workspace.
2. Completing a Three-Dimensional Workspace Using Logical and Lookup Functions.
Displaying Product Descriptions in the Selections Prototype Workbook. Entering Sales Data for Each Selections Store. Creating Named Ranges. Sorting Data Ranges. Constructing Formulas in the Sales Summary Worksheet Using the Lookup Function. Using the Sum Function to Total the Sales Figures Returned by the Lookup Function. Using the IF Function to Determine the Store Region with the Greatest Number of Sales for Each Product.
3. Sorting, Grouping, and Filtering Sales Data.
Applying Number Formats to the Sales Figures. Creating a Macro for Automating Redundant Tasks. Hiding Worksheet Columns. Filtering Sales Data Using Autofilter. Running a Macro. Sorting the Sales Summary Data Using Multiple Criteria. Adding Subtotals to the Sales Summary Worksheet. Creating Custom Views. Showing Custom Views.
4. Formatting, Displaying, Printing, and Publishing Workbooks.
Consolidating Data Using 3-D References. Copying 3-D References. Applying Cell Formats. Creating a Custom Number Format. Using Data Validation to Minimize Data Input Errors. Using Conditional Formatting. Using the Report Manager.
II. ANALYZING DATA WITH EXCEL.
5. Creating Interactive PivotTable Reports.
Importing External Data into Microsoft Excel. Setting Up Data for a PivotTable. Using PivotTable Reports to Analyze Data Interactively. Grouping Data in the PivotTable Report. Organizing a PivotTable Report Using a Page Field. Modifying Fields in a PivotTable Report. Displaying PivotTable Reports on the Web.
6. Using the Analysis ToolPak to Analyze Sales Transactions.
Importing ASCII Data into Excel. Adding Additional Formulas to the Worksheet. Recoding Text Data as Numeric Data. Creating Data Sets Containing the Variables to Be Tested. Calculating Descriptive Statistics Using the Data Analysis Tool. Conducting an Analysis of Variance Comparing Means for Two Groups. Conducting an Analysis of Variance. Comparing Means for Three Groups. Displaying Sales by State on a Map Using Microsoft Map.
7. Forecasting Values with What-If Analysis: Using Data Tables, Scenarios, Goal Seek, and Solver.
Forecasting Future Values with a One-Variable Data Table. Forecasting Future Values with a Two-Variable Data Table. Using Goal Seek to Constrain Vales in a What If Analysis. Using the Trend Worksheet Function to Forecast Future Sales. Working with Scenarios. Using Solver to Determine Optimum Values.
III: INTEGRATING DATA, COLLABORATING WITH OTHERS, AND CUSTOMIZING EXCEL 2000.
8. Using Excel's Auditing and Collaboration Features.
Modifying a Workbook for Collaboration. Using Excel's Auditing Features. Protecting Worksheet Data by Locking Cells. Setting Up a Shared Workbook. Tracking Changes in a Shared Workbook.
9. Using Visual Basic for Applications (VBA) to Customize and Automate Excel.
Using Visual Basic for Applications and the Macro Recorder. Viewing the Contents of a Macro Using the Visual Basic Editor. Objects, Properties, and Methods. Using Macros to Automate Complex Tasks. Adding Statements to a Macro in the Visual Basic Window. Assigning a Macro to a Button Control. Running a Macro from a Button Control.
10. Sharing Excel 2000 Data with Other Microsoft Office 2000 Applications.
Using Microsoft Query. Changing File Properties. Embedding an Excel Workbook in a Microsoft Word 2000 Document. Using an Excel List as a Microsoft Word 2000 Mail Merge Data Source. Linking an Excel Worksheet to a Microsoft PowerPoint 2000 Slide. Creating and Updating Web Queries.
Appendix A: Working with Excel.
Navigating Worksheets and Workbooks. Entering Text, Numbers and Formulas. Using the Office Assistant.
Appendix B: Using Excel's Financial Functions to Amortize a Loan.