Comprehensive Microsoft Excel 2016 – Become a Power User

#text-block-10 { margin-bottom:0px; text-align:left; }

Creating a Microsoft Excel Workbook

Starting Microsoft Excel

Creating a Workbook

Saving a Workbook

The Status Bar

Adding and Deleting Worksheets

Copying and Moving Worksheets

Changing the Order of Worksheets

Splitting the Worksheet Window

Closing a Workbook

Exercise: Creating a Microsoft Excel Workbook

The Ribbon

Tabs

Groups

Commands

Exercise: Exploring the Ribbon

The Backstage View (The File Menu)

Introduction to the Backstage View

Opening a Workbook

Exercise: Open a Workbook

New Workbooks and Excel Templates

Exercise: Select, Open and Save a Template Agenda

Printing Worksheets

Exercise: Print a Worksheet

Adding Your Name to Microsoft Excel

Managing Workbook Versions

The Quick Access Toolbar

Adding Common Commands

Adding Additional Commands with the Customize Dialog Box

Adding Ribbon Commands or Groups

Placement

Exercise: Customize the Quick Access Toolbar

Entering Data in Microsoft Excel Worksheets

Entering Text

Expand Data across Columns

Adding and Deleting Cells

Adding an Outline

Adding a Hyperlink

Add WordArt to a Worksheet

Using AutoComplete

Exercise: Entering Text and Using AutoComplete

Entering Numbers and Dates

Using the Fill Handle

Exercise: Entering Numbers and Dates

Formatting Microsoft Excel Worksheets

Selecting Ranges of Cells

Hiding Worksheets

Adding Color to Worksheet Tabs

Adding Themes to Workbooks

Exercise: Customize a Workbook Using Tab Colors and Themes

Adding a Watermark

The Font Group

Exercise: Working with Font Group Commands

The Alignment Group

Exercise: Working with Alignment Group Commands

The Number Group

Exercise: Working with Number Group Commands

Using Formulas in Microsoft Excel

Math Operators and the Order of Operations

Entering Formulas

AutoSum (and Other Common Auto-Formulas)

Copying Formulas and Functions

Displaying Formulas

Relative, Absolute, and Mixed Cell References

Exercise: Working with Formulas

Working with Rows and Columns

Inserting Rows and Columns

Deleting Rows and Columns

Transposing Rows and Columns

Setting Row Height and Column Width

Hiding and Unhiding Rows and Columns

Freezing Panes

Exercise: Working with Rows and Columns

Editing Worksheets

Find

Find and Replace

Exercise: Using Find and Replace

Using the Clipboard

Exercise: Using the Clipboard

Managing Comments

Adding Comments

Working with Comments

Finalizing Microsoft Excel Worksheets

Setting Margins

Setting Page Orientation

Setting the Print Area

Print Scaling (Fit Sheet on One Page)

Printing Headings on Each Page/Repeating Headers and Footers

Headers and Footers

Exercise: Preparing to Print

Microsoft Excel Features that Were New in 2013

One Workbook Per Window Feature

Using Flash Fill

Exercise: Using Flash Fill

#text-block-11 { margin-bottom:0px; text-align:left; }

Features New in 2016

Tell Me

Smart Lookup

Advanced Formulas

Using Named Ranges in Formulas

Naming a Single Cell

Naming a Range of Cells

Naming Multiple Single Cells Quickly

Exercise: Using Named Ranges in Formulas

Using Formulas That Span Multiple Worksheets

Exercise: Entering a Formula Using Data in Multiple Worksheets

Using the IF Function

Using AND/OR Functions

Using the SUMIF, AVERAGEIF, and COUNTIF Functions

Exercise: Using the IF Function

Using the PMT Function

Exercise: Using the PMT Function

Using the LOOKUP Function

Using the VLOOKUP Function

Exercise: Using the VLOOKUP Function

Using the HLOOKUP Function

Using the CONCATENATE Function

Exercise: Using the CONCATENATE Function

Using the TRANSPOSE Function

Using the PROPER, UPPER, and LOWER Functions

The UPPER Function

The LOWER function

The TRIM Function

Exercise: Using the PROPER Function

Using the LEFT, RIGHT, and MID Functions

The MID Function

Exercise: Using the LEFT and RIGHT Functions

Using Date Functions

Using the NOW and TODAY Functions

Exercise: Using the YEAR, MONTH, and DAY Functions

Creating Scenarios

Utilize the Watch Window

Consolidate Data

Enable Iterative Calculations

What-If Analyses

Use the Scenario Manager

Use Financial Functions

Working with Lists

Converting a List to a Table

Exercise: Converting a List to a Table

Removing Duplicates from a List

Exercise: Removing Duplicates from a List

Sorting Data in a List

Exercise: Sorting Data in a List

Filtering Data in a List

Exercise: Filtering Data in a List

Adding Subtotals to a List

Grouping and Ungrouping Data in a List

Exercise: Adding Subtotals to a List

Working with Illustrations

Working with Clip Art

Exercise: Working with Clip Art

Using Shapes

Exercise: Adding Shapes

Working with SmartArt

Visualizing Your Data

Creating a Custom Chart Template

Inserting Charts

Exercise: Inserting Charts

Exercise: Editing Charts

Add and Format Objects

Insert a Text Box

Create a Custom Chart Template

Working with Tables

Format Data as a Table

Move between Tables and Ranges

Modify Tables

Add and Remove Cells within a Table

Define Titles

Band Rows and Columns

Remove Styles from Tables

Exercise: Creating and Modifying a Table in Excel

Advanced Formatting

Applying Conditional Formatting

Exercise: Using Conditional Formatting

Working with Styles

Applying Styles to Tables

Applying Styles to Cells

Exercise: Working with Styles

Creating and Modifying Templates

Modify a Custom Template

Microsoft Excel Features that Were New in 2013

New Functions in Excel 2013

Exercise: Using the New Excel Functions

Using New Chart Tools

Exercise: Using the New Excel 2013 Chart Tools

Using the Quick Analysis Tool

Exercise: Using the Quick Analysis Tool

Using the Chart Recommendation Feature

Features New in 2016

New Charts

Treemap

Sunburst

Histogram

Waterfall

Box and Whisker

Pareto

Using Pivot Tables

Creating Pivot Tables

Preparing Your Data

Inserting a Pivot Table

Inserting Slicers

Working with Pivot Tables

Grouping Data

Using PowerPivot

Managing Relationships

Inserting Pivot Charts

More Pivot Table Functionality

Exercise: Working with Pivot Tables

Auditing Worksheets

Tracing Precedents

Exercise: Tracing Precedents

Tracing Dependents

Exercise: Tracing Dependents

Showing Formulas

Data Tools

Converting Text to Columns

Exercise: Converting Text to Columns

Linking to External Data

Controlling Calculation Options

Data Validation

Exercise: Using Data Validation

Consolidating Data

Exercise: Consolidating Data

Goal Seek

Exercise: Using Goal Seek

Working with Others

Protecting Worksheets and Workbooks

Password Protecting a Workbook

Removing Workbook Metadata

Exercise: Password Protecting a Workbook

Password Protecting a Worksheet

Exercise: Password Protecting a Worksheet

Password Protecting Ranges in a Worksheet

Exercise: Password Protecting Ranges in a Worksheet

Tracking Changes

Accepting or Rejecting Changes

Setting Tracking Options

Merging Multiple Workbooks

Marking a Workbook as Final

Exercise: Tracking Changes

Recording and Using Macros

Recording Macros

Copy a Macro from Workbook to Workbook

Exercise: Recording a Macro

Running Macros

Editing Macros

Adding Macros to the Quick Access Toolbar

Managing Macro Security

Exercise: Adding a Macro to the Quick Access Toolbar

Random Useful Items

Sparklines

Inserting Sparklines

Customizing Sparklines

Exercise: Inserting and Customizing Sparklines

Preparing a Workbook for Internationalization and Accessibility

Display Data in Multiple International Formats

Modify Worksheets for Use with Accessibility Tools

Use International Symbols

Manage Multiple Options for +Body and +Heading Fonts

Importing and Exporting Files

Importing Text Files

Exercise: Importing Text Files

Exporting Worksheet Data to Microsoft Word

Exercise: Copying Data from Excel to Word

Exporting Excel Charts to Microsoft Word

Exercise: Copying Charts from Excel to Word

Microsoft Excel Features that Were New in 2013

Using Slicers to Filter Data

Exercise: Filtering Data with Slicers

Creating a PivotTable Timeline

Exercise: Creating a Timeline

Creating a Standalone PivotChart

Workspaces in Excel 2013

Features New in 2016

PivotTable Updates

Ink Equations

Multi-Select Option in Slicers

Quick Shape Formatting

Sharing with SharePoint or OneDrive