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
- Editing a Range Name and Deleting a Range Name
- Using Range Names in Formulas
TOPIC B: Use Specialised Functions
- Function Categories
- The Excel Function Reference
- Function Syntax
- 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 CONCATENATE Function
- The TRANSPOSE Function
TOPIC B: Use Logical Functions
- Logical Functions
- Logical Operators
- The AND Function
- The OR Function
- The IF Function
TOPIC C: Use Lookup Functions
- Lookup Functions
- The LOOKUP Function
- The VLOOKUP Function
- The HLOOKUP 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 Modify Tables
- Tables
- Table Components
- The Create Table Dialog Box
- The Table Tools – Design Contextual Tab
- Styles and Quick Style Sets
- Customising Row Display
- Table Modification Options
TOPIC B: Sort and Filter Data
- The Difference Between Sorting and Filtering
- Sorting Data
- Advanced Filtering
- Filter Operators
- Removing Duplicate Values
TOPIC C: Use Subtotal and Database Functions to Calculate Data
- SUBTOTAL Functions
- The Subtotal Dialog Box
- Summary Functions in Tables
- Database Functions
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 Tools 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
- Dual Axis Charts
- Creating Custom Chart Templates
- Viewing Chart Animations
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 Pane
- Summarise Data in a PivotTable
- The “Show Values As” Functionality of a PivotTable
- External Data
- PowerPivot
- PowerPivot Functions
TOPIC B: Filter Data by 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: Inserting Graphics
TOPIC A: Insert and Modify Graphic Objects
- Graphical Objects
- Inserting Shapes
- Inserting WordArt
- Inserting Text Boxes
- Inserting Images
- The Picture Tools – Format Contextual Tab
- The Drawing Tools – Format Contextual Tab
- The SmartArt Tools Contextual Tabs
TOPIC B: Layer and Group Graphic 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
- Comments
- Hyperlinks
- Watermarks
- Background Pictures
TOPIC B: Manage Themes
- About Themes
- Customising Themes
TOPIC C: Create and Use Templates
- Templates
- Template Types
- Creating a Template
- Modifying a Template
TOPIC D: Protect Files
- Recovering Lost Data
- The Changes Group
- Worksheet and Workbook Protection
- The Protect Worksheet Option
- The Protect Workbook Option
TOPIC E: Preparing a Workbook for Multiple Audiences
- Displaying Data in Multiple International Formats
- Utilise International Symbols
- Modifying Worksheets Using the Accessibility Checker
- Managing Fonts
