Excel Advanced Course 2016

CREATING A PIVOTTABLE

Creating A PivotTable

• Create A PivotTable

Updating A PivotTable

• Update The Information In A PivotTable
• Adding Multiple Fields To The Row Area
• Adding Multiple Fields To The Column Area
• Arrange PivotTable Information
• Remove A PivotTable Field
• Filter A PivotTable
• Remove A Filter
• Filter A PivotTable Using A Filters Field
• Clear the Filter
• Change The Values Field Calculation
• Format Data In A PivotTable
• Managing Totals

PivotTables in Excel

• Inserting A Recommended PivotTable
• Filter Records Using Slicers
• Selection Multiple Filter Criteria
• Clear The Slicer Filter
• Remove A Slicer

ADVANCED PIVOTTABLE FEATURES

Working With Calculated Fields In A PivotTable

• Insert A Calculated Field
• Working With The Values Field
• Delete Calculated Fields
• Changing PivotTable Options
• Show Report Filter Pages
• Show Detailed Data From A PivotTable Value
• Show Different Calculations In PivotTable Value Fields

Value Field Calculation Options

Creating A Chart From A PivotTable

• Create A PivotChart From A PivotTable

DATA VALIDATION AND PROTECTING DATA

Using Data Validation And Tracing Invalid Data

• Validate Data Input
• Compose A Data Input Message
• Create An Error Alert Message
• Circle Invalid Data

Protecting Worksheet Styles, Contents And Elements

• Protecting A Worksheet
• Unprotecting A Worksheet
• Unlock Cells For Editing In A Protected Sheet
• Hide A Cell Formula
• Set A Password To Edit A Specific Range

Protecting Workbook Contents

• Protect And Unprotect The Workbook Structure

Protecting Your Workbook From Unauthorised Users Access

• Set A Password To Open A Workbook
• Remove A Password From A Workbook
• Set A Password To Modify A Workbook

WORKING WITH TEMPLATES

Creating A New Workbook Based On A Template

• Create A New Workbook Based On A Template
• Create A New Template
• Create A New Workbook Based On Your Own Template
• Editing A Template

FORMULA AUDITING

Auditing Worksheets And Tracing Cell References

• Tracing Precedent Cells
• Tracing Dependent Cells
• Trace Cells Across Worksheets
• Error Checking A Worksheet
• Tracing Errors
• Set Error Checking Options
• Using The Watch Window
• Showing and Hiding Formulas
• Evaluate A Formula

USING ADVANCED FUNCTIONS

Using Financial Functions

• The PMT Function
• Nesting the PMT and ABS Functions

Editing Nested Functions

• Applying Named Ranges To An Existing Formula

Using Logical Functions

• Use A Logical Test
• Use The IF Function To Return A Text Message
• Use The IF Function To Return A Value
• Nesting IF Functions
• The OR Function
• The AND Function
• The COUNTIF Function

ANALYSING DATA

The Goal Seek Tool

• Use The Goal Seek Tool

Exploring What-If Tables

• Create A One Variable What-If Table
• Create A Two Variables What-If Table

Using Scenarios

• Create Scenarios
• Add a Scenario
• Displaying the Various Scenarios

Solver Tool

• Load The Solver Tool Add-In
• Use The Solver Tool

AUTOMATING ROUTINE TASKS WITH MACROS

Understanding Macros

Types of Macros

• Show The Developer Tab
• Record A New Macro
• Run A Macro
• Create A Run Macro Button On The Quick Access Toolbar
• Create A Macro Command Button On A Worksheet
• Edit The Command Button

Editing A Macro

• Edit Macro Coding
• Rename A Macro
• Delete A Macro

Using A Relative Reference Macro

• Record A Relative Reference Macro
• Run A Relative Reference Macro
• Saving a Macro Workbook
• Open a Macro Workbook