Table of Contents:
Chapter 1–Creating a Worksheet and Charting Data       Project 1A–Create a Worksheet and Chart Data         Objective 1: Create, Save, and Navigate an Excel Workbook             Activity 1.1 Starting Excel and Naming and Saving a Workbook             Activity 1.2 Navigating a Worksheet and a Workbook             Activity 1.3 Selecting Parts of a Worksheet         Objective 2: Enter and Edit Date in a Worksheet             Activity 1.4 Entering Text, Using AutoComplete, Filling a Series with AutoFill,             and Using Spelling Checker and Undo to Correct Typing Errors             Activity 1.5 Aligning Text and Adjusting the Size of Columns and Rows             Activity 1.6 Entering Numbers             Activity 1.7 Inserting and Deleting Rows and Columns and Using the Insert             Options Button         Objective 3: Construct and Copy Formulas, Use the Sum Function, and Edit Cells             Activity 1.8 Constructing a Formula, Using the Sum Function and Editing             Numbers in Cells             Activity 1.9 Copying a Formula by Using the Fill Handle         Objective 4: Format Data, Cells, and Worksheets             Activity 1.10 Formatting Financial Numbers, Using Column AutoFit, and Using             Format Painter             Activity 1.11 Formatting Text and Using Merge and Center         Objective 5: Close and Reopen a Workbook             Activity 1.12 Closing and Reopening an Existing Workbook         Objective 6: Chart Data             Activity 1.13 Charting Data         Objective 7: Use Page Layout View, Prepare a Worksheet for Printing and Close Excel             Activity 1.14 Changing Views, Creating a Footer and Using Print Preview             Activity 1.15 Deleting Unused Sheets in a Workbook             Activity 1.16 Printing a Worksheet             Activity 1.17 Displaying, Printing, and Hiding Formulas       Project 1B–Perform Calculations and Make Comparisons by Using a Pie Chart         Objective 8: Design a Worksheet             Activity 1.18 Setting Column Widths and Creating Row and Column Titles             Activity 1.19 Entering Data by Range         Objective 9: Construct Formulas for Mathematical Operations             Activity 1.20 Using Arithmetic Operators             Activity 1.21 Copying Formulas Containing Absolute Cell References         Objective 10: Format Percentages and Move Formulas             Activity 1.22 Formatting Cells with the Percent Style Button             Activity 1.23 Inserting Rows in a Worksheet Containing Formulas and Wrapping             Text in a Cell         Objective 11: Create a Pie Chart and Chart Sheet             Activity 1.24 Creating a Pie Chart and a Chart Sheet         Objective 12: Use the Excel Help System             Activity 1.25 Using the Excel Help System   Chapter 2–Managing Workbooks and Analyzing Data       Project 2A Create a Summary Sheet from Multiple Worksheets         Objective 1: Create and Save a Workbook from an Existing Workbook             Activity 2.1 Creating and Saving a Workbook from an Existing Workbook         Objective 2: Navigate a Workbook and Rename Worksheets             Activity 2.2 Navigating Among Worksheets, Renaming Worksheets, and             Changing the Tab Color of a Worksheet         Objective 3: Enter Dates, Clear Contents, and Clear Formats             Activity 2.3 Entering and Formatting Dates             Activity 2.4 Clearing Cell Contents and Formats         Objective 4: Move, Copy, and Paste Cell Contents             Activity 2.5 Copying, Pasting, and Moving Cell Contents         Objective 5: Edit and Format Multiple Worksheets at the Same Time             Activity 2.6 Wrapping Text in a Cell in Several Worksheets at the Same Time             Activity 2.7 Entering Data and Constructing Formulas on Multiple Worksheets         Objective 6: Create a Summary Sheet             Activity 2.8 Constructing Formulas that Refer to Cells in Another Worksheet         Objective 7: Format and Print Multiple Worksheets in a Workbook             Activity 2.9 Moving and Formatting Worksheets in a Workbook             Activity 2.10 Printing All the Worksheets in a Workbook   Project 2B Growth Projection     Objective 8: Design a Worksheet for What-If Analysis             Activity 2.11 Using Parentheses in a Formula             Activity 2.12 Formatting as You Type             Activity 2.13 Calculating a Value After an Increase         Objective 9: Perform What-If Analysis             Activity 2.14 Performing What-If Analysis and Using Paste Special         Objective 10: Compare Data with a Line Chart             Activity 2.15 Creating a Line Chart   Chapter 3–Using Functions and Tables       Project 3A–Track Inventory by Using Math, Logical, and Statistical Functions and     by Creating an Excel Tabl                 Objective 1: Use SUM, AVERAGE, MEDIAN, MIN, and MAX Functions             Activity 3.1 Using the SUM, AVERAGE, and MEDIAN Functions             Activity 3.2 Using the MIN and MAX Functions             Activity 3.3 Moving Data, Adding Borders, and Rotating Text         Objective 2: Use COUNTIF and IF Functions, and Apply Conditional Formatting             Activity 3.4 Using the COUNTIF Functions             Activity 3.5 Using the IF Function and Applying Conditional Formatting             Activity 3.6 Applying Conditional Formatting Using Custom Formats and Data             Bars             Activity 3.7 Using Find and Replace         Objective 3: Use a Date Function             Activity 3.8 Using the NOW Function         Objective 4: Freeze Panes and Create an Excel Table             Activity 3.9 Freezing and Unfreezing Panes             Activity 3.10 Sorting and Filtering in an Excel Table             Activity 3.11 Inserting a Second Table in a Worksheet             Activity 3.12 Converting a Table to a Range of Data         Objective 5: Format and Print a Large Worksheet             Activity 3.13 Printing Large Worksheets       Project 3B–Make Financial Decisions by Using Financial Functions and What-If     Analysis         Objective 6: Use Financial Functions             Activity 3.14 Designing a Loan Worksheet             Activity 3.15 Inserting the PMT Financial Function         Objective 7: Use Goal Seek             Activity 3.16 Using Goal Seek to Produce the Desired Result             Activity 3.17 Using Goal Seek to Find an Increase Period         Objective 8: Create a Data Table             Activity 3.18 Designing a Two-Variable Data Table             Activity 3.19 Using a Data Table to Calculate Options   Chapter 4 Using Templates Range Names and Lookup Functions       Project 4A: Using Excel Templates         Objective 1: Open an Excel Template             Activity 4.1 Opening an Excel Template         Objective 2: Customize a Template             Activity 4.2 Using Word Art to Add a Company Logo to a Template             Activity 4.3 Adding ClipArt to the Company Logo             Activity 4.4 Grouping Graphic Objects             Activity 4.5 Adding a Formula to a Template             Activity 4.6 Adding Comments to a Cell         Objective 3: Save a Workbook as a Template             Activity 4.7 Saving the Workbook as a Template         Objective 4: Use a Customized Template             Activity 4.8 Opening and Completing a Customized Template       Project 4B: Create a Protected Order Form Template Using Range Names and     Lookup Functions         Objective 5: Create Range Names (3.1.3)             Activity 4.9 Naming a Range             Activity 4.10 Modifying a Named Range             Activity 4.11 Using Row and Column Labels to Name a Range             Activity 4.12 Deleting a Range Name         Objective 6: Ensure Data Integrity (1.2)             Activity 4.13 Creating a Validation List (1.2.1)         Objective 7: Use Lookup Functions             Activity 4.14 Using a Named Range in a VLOOKUP Formula to Retrieve Data             Activity 4.15 Using IFERROR Function in a Nested IF Statement to Remove Error             Codes             Activity 4.16 Creating an HLOOKUP Table             Activity 4.17 Using an HLOOKUP Formula to Retrieve Data         Objective 8: Protect the Worksheet and Save it as a Template             Activity 4.18 Hiding Rows Containing Lookup Tables             Activity 4.19 Unlocking Cells for Data Entry and Protecting a Worksheet             Activity 4.20 Hiding Formulas in the Worksheet             Activity 4.21 Setting a Print Area             Activity 4.23 Saving the Order Form as a Template   Chapter 5 Importing Data and Using Database Tools to Extract Information       Project A: Real Estate Company — Import list of customers, and massage list to     send Holiday letters, notices of new houses on the market etc.         Objective 1: Import Data into Excel             Activity 5.1 Importing Data from a Word Table             Activity 5.2 Importing Data from a TXT File             Activity 5.3 Importing Data from Access             Activity 5.4 Importing Data from a CSV File     Objective 2: Clean-up Data from External Sources             Activity 5.5 Consolidate Data             Activity 5.6 Converting Text to Columns             Activity 5.7 Using TRIM Function to Remove Spaces             Activity 5.8 Use Conditional Format to Highlight Duplicate Values             Activity 5.9 Removing Duplicate Records       Project B Table of Real Estate Sales Listings         Objective 3: Analyze Data with Excel Database Tools             Activity 5.10 Using Custom Filters             Activity 5.12 Creating Criteria Using Advanced Filter Criteria             Activity 5.12 Extracting Data to a New Area on the Worksheet             Activity 5.13 Using Subtotals             Activity 5.14 Adding a Calculated Field to a Filtered List             Activity 5.15 Grouping and Outlining Data         Objective 4: Use Database Functions (3.3)             Activity 5.16 Using the DAVERAGE Database Function             Activity 5.17 Using the DSUM Database Function             Activity 5.18 Using DCOOUNT             Activity 5.19 Using DGET   Chapter 6–Summarizing Data with Pivot Tables and Conditional Summary Formulas       Project A: Grocery Sales data by region/store/department         Objective 1: Create Pivot Table Report             Activity 6.1 Organizing Data to Display in a PivotTable             Activity 6.2 Naming the Table Range             Activity 6.3 Using the PivotTable and PivotChart Wizard             Activity 6.4 Creating a PivotTable from Data in the Existing Table             Activity 6.5 Limiting PivotTable Data         Objective 2: Modify a Pivot Table Report             Activity 6.6 Rearrange a PivotTable Report             Activity 6.7 Displaying Pivot Table Details in a New Workbook             Activity 6.8 Displaying PivotTable Data on Separate Pages             Activity 6.9 Changing Calculations in a PivotTable Report             Activity 6.10 Formatting a PivotTable             Activity 6.11 Updating PivotTable Report Data         Objective 3: Create a Pivot Chart Report             Activity 6.12 Creating a PivotChart Report from a PivotTable Report             Activity 6.13 Modifying a PivotChart Report       Project B         Objective 4: Use Logical Operators to Make Decisions             Activity 6.14 Using the And Logical Operator             Activity 6.15 Using the OR Logical Operator             Activity 6.16 Using the NOT Logical Operator             Activity 6.17 Creating a Nested Statement Using Logical Operators         Objective 5: Use the Auditing Toolbar to Analyze a Worksheet             Activity 6.18 Using the Audit Toolbar to Analyze a Worksheet         Objective 6: Set Workbook Properties             Activity 6.19 Setting Workbook Properties