Your Office

&>



Module 1, Workshop 1: Understanding and Manipulating Microsoft Excel

Prepare Case: Red Bluff Golf Club Golf Cart Purchase Analysis

Excel Worksheets-What if Data and Information could speak?

Understanding Spreadsheet terminology and components

Starting Excel

What is a Workbook?

Opening a Workbook

Saving and renaming a Workbook

Cells, Rows, and Columns

Worksheet Navigation

Scrolling

Keyboard Navigation

Go To

Navigating among Worksheets

Documentation

Failing to plan is planning to fail

Enter and Edit Data

Text, Numbers, Dates, and Times

How Excel really stores Date and Time Data

Text Wrapping and Hard Returns

Manipulate Cells and Ranges

Selecting Cells and Cell Ranges

Drag and Drop

Cut/Copy and Paste

Series (AutoFill)

Modifying Cell Information

Inserting Cells

Deleting Cells

Merge & Center vs. Center Across

Manipulate Columns and Rows

Select Contiguous and Noncontiguous Columns and Rows

Rows

Inserting Columns and Rows

Column Width and Row Height

Changing Column Widths using AutoFit

Delete vs. Clear

Inserting Columns that contain Data

Printing Worksheets and Manipulating Workbooks

Preview and Print Worksheets

Worksheet Views

Print Preview, Printer Selection, and Print

Print Titles

Print Headers/Footers

Page Margins

Page Orientation

Scaling

Manipulate Worksheets and Workbooks

Name a Worksheet

Insert or Delete a Worksheet

Move a Worksheet

Deleting a Worksheet

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2



Module 1, Workshop 2: Formatting, Formulas, and Functions

Prepare Case: Red Bluff Golf Club Golf Pro Shop Sales Analysis

Worksheet Formatting

Format Cells, Cell Ranges, and Worksheets

Copying a document from one Workbook to another

Numbers

Formats

Negative Values and Color

Dates and Times

Cell Alignment

Content Orientation

Fill Color

Cell Borders

Copying Formats

Paste Options/ Paste Special

Built-in Cell Styles

Table Styles

Workbook themes

Creating Information for Decision Making

What is a Function?

AutoSum Functions

SUM()

COUNT()

AVERAGE()

Min() and Max()

What is a Formula?

Operators

Order of Operations

Hiding Information in a Worksheet

Show Functions and Formulas

Decision Making

Conditional Formatting

Removing Conditional Formatting

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2

Module 1 Capstone

More Practice

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Problem Solve Case 3

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Career

How Others Perform



Module 2, Workshop 3: Conducting Excel Analysis through Functions and Charts

Prepare Case: Massage Table Analysis

Cell References and Formula Basics

Reference Cells within Formulas or Functions

Absolute Cell Reference

Mixed Cell Reference

Working with Named Ranges

Creating Named Ranges in the Name Box

Creating Named Ranges from Selections

Modifying Named Ranges

Using Named Ranges

Creating Formulas Using Named Ranges

Functions

Understanding the Syntax of a Function

Function Structure

Function Construction with Function Arguments Dialog Box

Function Construction with the Formula Bar

Common Functions

Date and Time Functions

Math and Trig Functions

Statistical Functions

COUNTA

Financial Functions

Logical Functions

Adding an IF Function to an existing Formula

Editing and Troubleshooting Functions

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2



Module 2, Workshop 4: Using Charts

Prepare Case: Turquoise Oasis Spa Sales Report

Designing a Chart

Exploring Chart Characteristics-Types, Layouts, and Styles

Navigating a Chart

Chart Locations

Creating Charts in an existing Worksheet

Modifying a Chart's Position

Placing Charts on a Chart Sheet

Chart Types

Pie Charts

Line Charts

Column Charts

Bar Charts

Scatter Charts

Area Charts

Chart Layout, Styles, and Data

Laying out a Chart

Inserting Objects

Working with Labels

Titles for the Chart and Axes

Working with the Legend and Labeling the Data

Modifying Axes

Changing Gridlines

Analysis with Trendlines

Editing and Formatting Charts

Colorizing Objects

Working with Text

Exploding Pie Charts

3-D Charts and Rotation of Charts

Effectively Using Charts

Strategic Statements with Charts

Emphasizing Data

Sparklines

Data Bars, Color Scales, and Icon Sets

Recognizing and Correcting Confusing Charts

Correcting a Line Chart

Changing the Chart Type and Legend

Preparing to Print and Copy

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2

Module 2 Capstone

More Practice

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Problem Solve Case 3

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Career

How Others Perform



Module 3, Workshop 5: Integrating Complex Functions into Excel Analysis

Prepare Case: Red Bluff Pro Shop Sales Analysis

Integrating Logical Functions

Using Nested IF Functions

Using Conjunction Functions in IF Functions

Using Conditional Aggregate Functions

Using Conditional Statistical Functions

The COUNTIF and COUNTIFS Functions

The AVERAGEIF and AVERAGEIFS Functions

Using Conditional Math Functions

The SUMIF Function

The SUMIFS Function

The DSUM Function

Retrieving Data Using LOOKUP and Reference Functions

Using LOOKUP Functions

Retrieving Data with MATCH, INDEX, and INDIRECT

The MATCH function

The Index Function

The INDIRECT Function

Handling Errors with the IFERROR Function

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2



Module 3, Workshop 6: Analyzing Data Using Data

Prepare Case: Golf Course Marketing Strategies

Organizing Data with Tables

Working with Tables of Data

Organizing Data Sets

Organizing a Data Set within a Table

Filtering Data Sets

Using the Advanced Filter Feature

Using the SUBTOTAL Function and Filters with Tables

Organizing and Analyzing with PivotTables and PivotCharts

Developing PivotTables

Creating PivotTables

PivotTable Options

Adding a Slicer to the PivotTable

Design options within PivotTables

Updating and Sharing Data in PivotTables

Creating PivotCharts

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2

Module 3 Capstone

More Practice

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Problem Solve Case 3

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Career

How Others Perform



Module 4, Workshop 7: Working with Multiples and Templates

Prepare Case: Turquoise Oasis Spa Therapist Sales and Service Analysis

Working with Multiple Worksheets

Grouping (and Ungrouping) Worksheets

Data Entry

Formula and Function Entry

Cell Formatting

Filling Contents and Formats across Worksheets

Contents

Formats

Contents and formats

Referencing Data Between and Among Worksheets

3-D References

3-D Named Ranges

Consolidating Data across Worksheets

Consolidate Data by Position

Consolidate Data by Category

Creating Links to Source Data in Consolidation

Accessing Data in Multiple Workbooks

Working with Multiple Workbooks

Create and Excel Workspace

Cell References between Workbooks-Linked Workbooks

Collaborating using Multiple Workbooks

Using Templates

Using Built-in Templates

Using Templates on the Web (Office.com)

Creating your own Template(s)

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2



Module 4, Workshop 8: Refining Your Excel Application

Prepare Case: Turquoise Oasis Spa Invoice Generation

Controlling Data Entry

Controlling Data Entry with Data Validation

Validation Criteria

Input Messages and Error Alert

List Validation

Decimal Validation

Date Validation

Time Validation

Whole Number Validation

Custom Data Validation

Any Value Validation

Text Length Validation

Formulas Minimize Data Entry

Validation with Text-to-Speech

Auditing your Formulas

Auditing your Formulas with Trace Precedents and Trace Dependents

Trace Dependents

Evaluating a Formula

Using Circular Reference

Using Watch Window

Creating a Polished Excel Application

Controlling Workbook Navigation

Navigating with Hyperlinks

Hiding Worksheets

Hiding Worksheet Tabs

Hiding Scroll bars

Hiding Row and Column Headings

Hiding Gridlines

Automating with Macros

Creating a Trusted Location

Adding the Developer Tab to the Ribbon

Recording a Macro

Running a Macro

Modifying a Macro

Protecting your Worksheet(s)

Lock/Unlock Cells

Worksheet Protection and Macros

Hiding Formulas

Protecting your Workbook

Encrypt with Password

Protect Current Sheet

Protect Workbook Structure

Mark as Final

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2

Module 4 Capstone

More Practice

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Problem Solve Case 3

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Career

How Others Perform



Module 5, Workshop 9: Manipulating Data Sets for Business Analytics

Prepare Case: Red Bluff Golf Club Pro Shop Data Analytics

Working with Data Sets

Understanding External Data Sets

Using a Web Query

Editing a Web Query

Saving a Web Query

Using Web Queries to Retrieve Stock Quotes

Working with XML Data and Text Files

Importing XML Data

Importing Text Files

Interacting with Access

Connecting to an Access Database

Using Microsoft Query to Query an Access Database

Making Data Useful

Cleaning Imported Data

Using Text Functions to Clean Data

Using Text Functions

Using Text Functions to Separate String Data

Concatenating Data

Using the LEN and FIND Functions to Separate Data

Using the MID Function to Extract Data

Using Wizards for Separating Data

Removing Duplicates

Removing Partial Duplicates

Using Conditional Formatting to Remove Duplicates

Cleaning Date-Related Data

Reconstructing Dates

Combining Dates

Extracting Partial Dates

More Date Functions

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2



Module 5, Workshop 10: Dashboards and Visual Basic for Applications

Prepare Case: The Red Bluff Golf Club Pro Shop Digital Dashboard

Importance of Business Intelligence and Dashboards

Understanding the basics of Dashboard Design

Keep it Simple

Make sure it is well defined

Know your users

Define Crucial KPIs

Strategic Placement

White Space

Building the Analysis Layer

Connecting to an External Source

Creating PivotCharts for Analysis

Creating PivotTables with Sparklines

Creating and Excel table for Analysis

Enhancing Analysis with Conditional Formatting

Creating the Presentation Layer

Creating the Dashboard

Using the Camera Tool

Enhancing Dashboard with Slicers

Enhancing the Dashboard with Form Controls

Enhancing the Dashboard with a Spin Button

Creating a Lookup table to use with Form Controls

Enhancing the Dashboard with a Scroll Bar

Creating Dynamic Labels

Leveraging the Power of Visual Basic for Applications (VBA) in Excel

Understanding the Components of VBA

The Visual Basic Editor

Renaming Worksheets using VBA

Improving Readability of VBA with Formatting and Structure

Enhancing Functionality and Usability with VBA

Using VBA to Clear Filters

Providing End Users with and easy way to run VBA code

Dimensioning a Variable

Creating an Input Box

Incorporating Conditional Statements

Troubleshooting VBA

Preparing a Dashboard for Production

Protecting Excel Worksheets

Hiding unnecessary screen elements

Adding additional security to protect the Dashboard

Password Protect VBA Code to prevent unauthorized access

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2

Module 5 Capstone

More Practice

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Problem Solve Case 3

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Career

How Others Perform



Module 6, Workshop 11: Making Financial Decisions with Help from Excel

Prepare Case: The Turquoise Oasis Spa Financial Analysis

Constructing a Financial Analysis

Constructing a Loan Analysis with PMT, IPMT, and PPMT

Using the PMT Function

Using the IPMT Function

Using the PPMT Function

Using RATE and NPER to find the missing pieces

Using the RATE Function

Using the NPER Function

Using Amortization Tables and Analyzing Loans

Creating an Amortization Table

Calculating Cumulative Interest and Principal Using CUMIPMT and CUMPRINC

Using the CUMIPMT Function

Using the CUMPRINC Function

Alternatives to Monthly Payments

Predicting Future Values and Handling Depreciation

Analyzing Annuities and Investments with the PV,FV, NPV, and XNPV Functions

Understanding Annuity Functions

Using the PV Function

Using the FV Function

Using the NPV Function

Using the XNPV Function

Analyzing Rates of Return on Investments with the IRR and XIRR functions

Using the IRR Function

Using the XIRR Function

Calculating the Depreciation of Assets using the SLN, DB, and DDB Functions

Using the SLN Function

Using the DB Function

Using the DDB Function

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2



Module 6, Workshop 12: Decision Making with Excel

Prepare Case: The Turquoise Oasis Spa Business Planning Analysis

Examining Cost-Volume-Profit Relationships

Performing Break-Even Analysis

Using the Scroll Bar to Perform Break-Even Analysis

Using Conditional Formatting

Using Data Tables

One-Variable Data Table

Two-Variable Data Table

Using Goal Seek

Using Scenario Manager

Creating Scenarios

Adding, Deleting, and Editing Scenarios

Viewing Scenarios

Generating a Scenario Summary Report

Generating a Scenario PivotTable Report

Using Solver

Loading the Solver Add-in

Solving Complex Problems and Finding Optimal Solutions

Setting the Objective Cell and Variable Cells

Defining the Constraints

Selecting the Solving Method

Generating and Interpreting Solver Answer Reports

Generating a Solver Answer Report

Interpreting a Solver Answer Report

Saving and Restoring a Solver Model

Concept Check

Key Terms

Visual Summary

Practice

Practice Case 1

Practice Case 2

Module 6 Capstone

More Practice

Problem Solve

Problem Solve Case 1

Problem Solve Case 2

Problem Solve Case 3

Perform

Perform in Your Life

Perform in Your Career

Perform in Your Career

How Others Perform



Glossary

Index