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 2 B 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 Table
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 Adding a Company Logo to a Template Using Word Art
Activity 4.3 Adding ClipArt to the Company Logo
Activity 4.4 Grouping Graphic Objects
Activity 4.5 Adding a Formula to a Template
Objective 3: Save a Workbook as a Template
Activity 4.6 Saving a Workbook as a Template
Objective 4: Enter Data in a Customized Template
Activity 4.7 Opening and Using a 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.8 Naming a Range
Activity 4.9 Modifying a Named Range
Activity 4.10 Using Row and Column Titles to Name a Range
Activity 4.11 Deleting a Range Name
Objective 6: Ensure Data Integrity (1.2)
Activity 4.12 Creating a Validation List (1.2.1)
Objective 7: Use Lookup Functions
Activity 4.13 Retrieving Data Using a Named Range in a VLOOKUP Formula
Activity 4.14 Removing Error Codes Using IFERROR Function in a Nested IF Statement
Activity 4.15 Creating an HLOOKUP Table
Activity 4.16 Retrieving Data Using an HLOOKUP Formula
Objective 8: Protect the Worksheet and Save it as a Template
Activity 4.17 Hiding Rows Containing Lookup Tables
Activity 4.18 Unlocking Cells for Data Entry and Protecting a Worksheet
Activity 4.19 Hiding Formulas in the Worksheet
Activity 4.20 Setting a Print Area
Activity 4.21 Saving the Order Form as a Template
Chapter 5 Importing Data and Using Excel as a Database
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 Comma Delimited File
Objective 2: Clean-up Data from External Sources
Activity 5.5 Converting Text to Columns
Activity 5.6 Using TRIM Function to Remove Spaces
Activity 5.7 Consolidating Data
Activity 5.8 Highlight Duplicate Values with Conditional Formatting
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 Creating a Table and Printing the Results
Activity 5.11 Adding Data and Using Custom AutoFilters
Activity 5.12 Creating Criteria Using Advanced Filter Criteria
Activity 5.13 Extracting Data to a New Area on the Worksheet
Activity 5.14 Using Dates in a Calculated Field
Activity 5.15 Subtotaling, 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 DCOOUNTA Function
Activity 5.19 Using DGET Function
Activity 5.20 Controlling Print Options
Chapter 6-Summarizing Data and Making Business Decision Project A: Grocery Sales data by region/store/department
Objective 1: Create and Modify PivotTable Reports
Activity 6.1 Organizing Data to Display in a PivotTable
Activity 6.2 Creating a PivotTable
Activity 6.3 Changing the Value Summary
Activity 6.4 Creating a PivotTable with Subheadings
Activity 6.5 Formatting a PivotTable Report
Objective 2: Create PivotChart Reports
Activity 6.6 Creating a PivotChart Report
Activity 6.7 Modifying and Moving PivotChart
Activity 6.8 Printing PivotTables and PivotCharts
Project B
Objective 3: Compare Dates and Round Numbers
Activity 6.9 Determining the Number of Years Between Two Dates
Activity 6.10 Creating Whole Numbers with ROUND Functions
Objective 5: Apply Business Decisions with Logical Operators
Activity 6.11 Applying a Logical Test to a Cell Value and Using the AND Function
Activity 6.12 Applying the NOT Function
Activity 6.13 Applying the OR Function
Activity 6.14 Writing a Nested IF Statement to Determine the Results
Objective 6: Insert Comments and Document a Workbook
Activity 6.15 Inserting Comments
Activity 6.16 Changing the Workbook Properties
Activity 6.17 Printing Comments
