Excel Advanced 2010 Course

CREATING A PIVOTTABLE IN EXCEL

Creating Excel PivotTables

• Creating A PivotTable

Updating An Excel PivotTable

• Update A PivotTable
• Adding Multiple Fields To The Row / Column Area
• Rearrange PivotTable Fields
• Removing A PivotTable Field
• Filter A PivotTable Field
• Remove A Filter
• Filter The PivotTable Using The Filters Field
• Clearing A Filter
• Setting The Field Calculation
• Format Calculations In A PivotTable
• Managing Subtotals and Grand Totals

More Features In PivotTables

• Filter Records Using Slicers
• Setting Multiple Filter Criteria
• Clearing Filters In The Slicer
• Remove A PivotTable Slicer

ADVANCED FEATURES OF PIVOTTABLES

Working With Calculated Fields In A PivotTable

• Insert A Calculated Field Into A PivotTable
• Moving The Values Field
• Remove A Calculated Field
• Set PivotTable Options
• Show Report Filter Pages For A Field
• Show Detailed Information From A PivotTable Result

PivotCharts

• Create A PivotChart From A PivotTable

DATA VALIDATION, DATA PROTECTION & WORKING WITH TEMPLATES

Setup Data Validation

• Validate Data Entry
• Create A Data Validation Input Message
• Compose An Error Alert Message
• Circle Cells With Invalid Data

Working With Template

• Create A New Workbook Based On A Workbook Template
• Create A New Excel Template
• Create A New Workbook From Your Own Template
• Edit Your Own Template

Protecting Worksheets

• Protecting Data On A Single Worksheet
• Unprotecting A Worksheet For Editing
• Unlock Cells For Editing
• Hide Formulas In A Cell
• Add A Password To Edit A Specific Ranges

Protecting The Workbook Structure

• Protect And Unprotect The Workbook Structure

Password Protect The Workbook

• Setting A Password To Open A Spreadsheet
• Remove A Password From A Spreadsheet
• Setting A Password To Modify A Spreadsheet

FORMULA AUDITING IN EXCEL

Auditing Formulas And Tracing Cell References

• Tracing Precedents
• Tracing Dependents
• Tracing Precedents / Dependents Across Worksheets
• Error Checking
• Trace An Error In A Worksheet
• Setting Error Checking Options
• The Watch Window
• Showing / Hiding Formulas In A Worksheet
• Evaluate Formula Steps

ADVANCED FUNCTIONS

The Payment Function

• PMT Function (Payment)
• Using The ABS Function (Absolute)

Working With Logical Functions

• Introducing A Logical Test
• Setup An IF Function To Return Text
• Setup An IF Function To Return Value
• Nesting IF Functions In A Formula
• Using The OR Function
• Using The AND Function
• Count Cells With Specific Criteria Using The COUNTIF Function

WHAT IF ANALYSIS AND ANALYSING DATA

Goal Seek Tool

• Use The Goal Seek Tool

Data Tables

• Create A One Variable Data Table
• Create A Two Variables Data Table

Scenarios

• Create Scenarios
• Apply A Scenario

Solver Tool

• Load The Solver Add-In
• Setting Up The Solver Tool
• Solver Features

RECORDING MACROS

Recording Macros

Understanding The Two Types of Macros

• Recording A New Macro
• Run A Recorded Macro
• Add A Button To The Quick Access Toolbar
• Showing The Developer Tab
• Embed A Command Button Into A Worksheet
• Editing The Command Button

Editing A Recorded Macro

• Unhide the Personal Macro Workbook
• Edit The VBA Code In A Macro
• Rename A Recorded Macro
• Delete A Recorded Macro
• Hide the Personal Macro Workbook

Relative Reference Macros

• Record A Macro With Relative Cell References
• Run The Relative Reference Macro
• Saving A Workbook With Macros
• Open A Workbook With Macros

INTEGRATING DATA WITH OTHER APPLICATIONS

Importing And Exporting Data

• Using The Text Import Wizard

Import Data From A Database

• Introducing Microsoft Query

Importing Data From Another Source

• Import Information From Other External Sources