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.
