Microsoft Excel 2019/365 – Part 2

Course subjects

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

TOPIC B: Use Specialised 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

Lesson 2: Analysing 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

TOPIC B: Use Logical Functions

• Logical Functions

• Logical Operators

• The AND Function

• The OR Function

• The IF Function

• The IFS Function

TOPIC C: Use Lookup Functions

• Lookup Functions

• The LOOKUP Function

• The VLOOKUP Function

• The HLOOKUP Function

• The MATCH Function

• The INDEX Function

TOPIC D: Use Date Functions

• The TODAY Function

• The NOW Function

• Serialising Dates and Times with Functions

TOPIC E: Use Financial Functions

• The IPMT Function

• The PPMT Function

• The NPV Function

• The FV Function

Lesson 3: Organising 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

• Customising Row Display

TOPIC B: Modifying Tables

• Adding Rows and Columns

• Total Row Functions

• Removing Duplicate Values

TOPIC C: Table References

• Naming Tables

• Using Structured References

• Database Functions

• Converting to Range

Lesson 4: Visualising 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

TOPIC B: Modify and Format Charts

• The Difference Between Modifying and Formatting

• Chart Elements

• Minimise Extraneous Chart Elements

• The Chart Contextual Tabs

• Formatting the Chart with a Style

• Adding a Legend to the Chart

TOPIC C: Create a Trendline

• Trendlines

• Types of Trendlines

• Adding a Trendline

• The Format Trendline Task Pane

TOPIC D: Create Advanced Charts

• Combination Charts

• Dual Axis Charts

• Creating Custom Chart Templates

Lesson 5: Analysing 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

• Summarise Data in a PivotTable

• The “Show Values As” Functionality of a PivotTable

• Format a PivotTable

• External Data

• PowerPivot

• PowerPivot Functions

TOPIC B: Filter Data Using Slicers

• Slicers

• The Insert Slicers Dialog Box

TOPIC C: Analyse Data with PivotCharts

• PivotCharts

• Creating PivotCharts

• Applying a Style to a PivotChart

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

TOPIC B: Layer and Group Graphical Objects

• Layering Objects

• Grouping Objects

• Positioning Objects

TOPIC C: Incorporate SmartArt

• About SmartArt

• The Choose a SmartArt Graphic Dialog Box

• About the Text Pane

Lesson 7: Enhancing Workbooks

TOPIC A: Customise Workbooks

• Notes and Comments

• Comments

• Notes

• Watermarks

• Background Pictures

TOPIC B: Manage Themes

• About Themes

• Customising 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

TOPIC D: Preparing a Workbook for Multiple Audiences

• Displaying Data in Multiple International Formats

• Utilising International Symbols

• Adding Alternative Text to Objects