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
