Microsoft Excel 365 Advanced Training Materials

Pivot Tables

• Creating and Using a Pivot Table
• Filtering and Sorting Data within a Pivot Table
• Automatically Grouping Data in a Pivot Table and Renaming Groups
• Manually Grouping Data in a Pivot Table and Renaming Groups

Input Tables

• One-Input Data Tables
• Two-Input Data Tables

Charts

• Creating a Combined Line and Column Chart
• Adding a Secondary Axis to a Chart
• Changing the Chart Type for a Particular Data Series
• Adding a Data Series to a Chart
• Removing a Data Series from a Chart
• Re-Positioning Chart Title
• Re-Positioning the Chart Legend
• Moving and Formatting Chart Data Labels
• Modifying Chart Axis Scales
• Formatting an Axis to Display Using Commas
• Inserting Images into Chart Columns
• Inserting Images to Chart Bars
• Formatting the Chart Plot Area Using a Picture
• Formatting the Chart Area Using a Picture

Hyperlinks

• Inserting a Hyperlink
• Editing a Hyperlink
• Removing a Hyperlink

Linking & Embedding

• What Is Embedding and Linking?
• Linking Data within a Worksheet
• Linking Cells between Worksheets within a Workbook
• Linking Data between Workbooks
• Linking Data from Excel to a Word Document
• Linking an Excel Chart to a Word Document
• Updating, Locking and Breaking Links

Importing Text Files

• What Is a Delimited Text File?
• Importing a Delimited Text File

Sorting and Filtering Data

• Sorting Data by Multiple Columns at the Same Time
• Applying a Pre-Installed Custom Sort
• Creating a Customized List and Performing a Custom Sort
• Removing a Customised List
• Using AutoFilter
• Using AutoFilter to Perform Multiple Queries
• Top 10 AutoFilter
• Removing All AutoFilters from a Worksheet
• Advanced Filter Criteria
• Sub-Totalling
• Removing Subtotals
• Expanding and Collapsing Outline Detail Levels

Tracking and Reviewing Changes

• Enabling or Disabling the ‘Track Changes’ Feature
• Sharing, Comparing and Merging Worksheets

Scenarios

• Scenario Manager
• Scenario Summary Reports

Validating

• Data Validation – Whole Number
• Data Validation – Decimal Number
• Data Validation – List
• Data Validation – Date
• Data Validation – Time
• Data Validation – Text Length
• Customising a Validation Input Message and Error Alert
• Removing Data Validation

Auditing

• Tracing Precedent Cells
• Tracing Dependent Cells
• Cells with Missing Dependents
• Showing All Formulas in a Worksheet
• Inserting and Viewing Comments
• Editing and Deleting Comments
• Showing and Hiding Comments

Macros

• Macro to Change the Page Set-Up
• Macro to Apply a Custom Number Format
• Macro to Format a Cell Range
• Macro to Insert Fields into the Header or Footer
• Assigning a Macro to a Button on the Quick Access Toolbar
• Deleting Macros

Passwords & Security Issues

• Adding ‘Open’ Password Protection to a Workbook
• Adding ‘Modify’ Password Protection to a Workbook
• Removing an ‘Open’ Password from a Workbook
• Removing a ‘Modify’ Password from a Workbook
• Password Protecting Cells and Worksheets
• Hiding Formulas
• Un-Hiding Formulas