Microsoft 365 Excel Part 2

This course is intended to help all users get up to speed on the different features of Microsoft Excel 365 and to become familiar with its more advanced selection of features. It covers how to create and use advanced formulas, analyze data, organize worksheet data with tables, visualize data with charts, insert graphics, and enhance workbooks.

By the end of this course, users should be comfortable in creating advanced formulas, analyzing data with functions, analyzing data using functions and PivotTables, working with tables, visualizing data with charts, inserting graphics, and enhancing workbooks.

Course Topics:

Lesson 1: Creating Advanced Formulas

TOPIC A: Apply Range Names

• Range Names
• Adding Range Names Using the Name Box
• Adding Range Names Using the New Name Dialog Box
• Using Range Names in Formulas
• Activity 1-1: Using Range Names in Formulas

TOPIC B: Use Specialized Functions

• Function Categories
• Function Syntax
• Finding Excel Functions
• Function Entry Dialog Boxes
• Using Nested Functions
• Automatic Workbook Calculations
• Showing and Hiding Formulas
• Enabling Iterative Calculations
• Activity 1-2: Using Specialized Functions
• Summary
• Review Questions

Lesson 2: Analyzing Data with Logical and Lookup Functions

TOPIC A: Use Text Functions

• Text Functions
• The LEFT and RIGHT Functions
• The MID Function
• The LEN Function
• The TRIM Function
• The UPPER, LOWER, and PROPER Functions
• The TEXTJOIN Function
• The TRANSPOSE Function
• Activity 2-1: Analyzing Data Using Text Functions

TOPIC B: Use Logical Functions

• Logical Functions
• Logical Operators
• The AND Function
• The OR Function
• The IF Function
• The IFS Function
• Activity 2-2: Analyzing Data Using Logical Functions

TOPIC C: Use Lookup Functions

• Lookup Functions
• The LOOKUP Function
• The VLOOKUP Function
• The HLOOKUP Function
• The MATCH Function
• The INDEX Function
• Activity 2-3: Analyzing Data Using Lookup Functions

TOPIC D: Use Date Functions

• The TODAY Function
• The NOW Function
• Serializing Dates and Times with Functions
• Activity 2-4: Analyzing Data Using Date Functions

TOPIC E: Use Financial Functions

• The IPMT Function
• The PPMT Function
• The NPV Function
• The FV Function
• Activity 2-5: Using Financial Functions
• Summary
• Review Questions

Lesson 3: Organizing Worksheet Data with Tables

TOPIC A: Create and Format Tables

• Tables
• Table Components
• The Create Table Dialog Box
• The Table Design Contextual Tab
• Styles and Quick Style Sets
• Customizing Row Display
• Activity 3-1: Creating and Modifying a Table

TOPIC B: Modifying Tables

• Adding Rows and Columns
• Total Row Functions
• Removing Duplicate Values
• Activity 3-2: Modifying Tables

TOPIC C: Table References

• Naming Tables
• Using Structured References
• Database Functions
• Converting to Range
• Activity 3-3: Table References
• Summary
• Review Questions

Lesson 4: Visualizing Data with Charts

TOPIC A: Create Charts

• Charts
• Chart Types
• Chart Insertion Methods
• Resizing and Moving the Chart
• Adding Additional Data
• Switching Between Rows and Columns
• Activity 4-1: Creating Charts

TOPIC B: Modify and Format Charts

• The Difference Between Modifying and Formatting
• Chart Elements
• Minimize Extraneous Chart Elements
• The Chart Contextual Tabs
• Formatting the Chart with a Style
• Adding a Legend to the Chart
• Activity 4-2: Modifying and Formatting Charts

TOPIC C: Create a Trendline

• Trendlines
• Types of Trendlines
• Adding a Trendline
• The Format Trendline Task Pane
• Activity 4-3: Create a Trendline

TOPIC D: Create Advanced Charts

• Combination Charts
• Dual Axis Charts
• Creating Custom Chart Templates
• Activity 4-4: Creating Advanced Charts
• Summary
• Review Questions

Lesson 5: Analyzing Data with PivotTables, Slicers, and PivotCharts

TOPIC A: Create a PivotTable

• PivotTables
• Start with Questions, End with Structure
• The Create PivotTable Dialog Box
• The PivotTable Fields Task Pane
• Summarize Data in a PivotTable
• The “Show Values As” Functionality of a PivotTable
• Format a PivotTable
• External Data
• PowerPivot
• PowerPivot Functions
• Activity 5-1: Creating PivotTables

TOPIC B: Filter Data Using Slicers

• Slicers
• The Insert Slicers Dialog Box
• Activity 5-2: Filtering Data Using Slicers

TOPIC C: Analyze Data with PivotCharts

• PivotCharts
• Creating PivotCharts
• Applying a Style to a PivotChart
• Activity 5-3: Analyzing Data with PivotCharts
• Summary
• Review Questions

Lesson 6: Working with Graphical Objects

TOPIC A: Insert and Modify Graphic Objects

• Graphical Objects
• Inserting Shapes
• Inserting WordArt
• Inserting Text Boxes
• Inserting Images
• The Picture Format Contextual Tab
• The Shape Format Contextual Tab
• The SmartArt Contextual Tabs
• Activity 6-1: Inserting Graphical Objects

TOPIC B: Layer and Group Graphical Objects

• Layering Objects
• Grouping Objects
• Positioning Objects
• Activity 6-2: Layering and Grouping Shapes

TOPIC C: Incorporate SmartArt

• About SmartArt
• The Choose a SmartArt Graphic Dialog Box
• About the Text Pane
• Activity 6-3: Incorporating SmartArt
• Summary
• Review Questions

Lesson 7: Enhancing Workbooks

TOPIC A: Customize Workbooks

• Notes and Comments
• Comments
• Notes
• Watermarks
• Background Pictures
• Activity 7-1: Customizing Workbooks

TOPIC B: Manage Themes

• About Themes
• Customizing Themes
• Activity 7-2: Managing Themes

TOPIC C: Protect Files

• Recovering Lost Data
• The Protect Group
• The Protect Worksheet Option
• The Protect Workbook Option
• Mark Workbooks as Final
• Encrypting a Workbook
• Digitally Signing a Workbook
• Activity 7-3: Protecting a Worksheet and a Workbook

TOPIC D: Preparing a Workbook for Multiple Audiences

• Displaying Data in Multiple International Formats
• Utilize International Symbols
• Adding Alternative Text to Objects
• Activity 7-4: Preparing a Workbook for Multiple Audiences
• Summary
• Review Questions