MS Excel Pivot tables and Macros ver 2007/2010 – Course Outline
Course Description
This course focuses on more advanced features of Pivot tables and macros. The course does not cover Visual Basic for applications, but the delegates will be introduced to simple modifications in VBA..
The course covers ISETT Unit Standard: 116940
Duration: 7 hours (1 days) (Pivot tables and macros)
3.5 hours ½ day (Pivot tables only)
Target Group: This course is intended for delegates who has a good knowledge of Excel and would like to analyse data with Pivot tables.
Prerequisites: The delegates must have a good knowledge of Excel.
Method of training: Instructor led, hands-on exercises using a computer. Groups not more than 12 delegates.
Course Content:
REVISION OF DATABASE CONCEPTS
IMPORTING TEXT FILES (DELIMITED, FIXED WITH),
REVISING FUNCTIONS , i.e. substitute, Trim, Value, text,left,right,mid,vlookup, find,search, more advanced IF statements,
PIVOT TABLES
Adding The Pivot table And Pivot chart Wizard To The Quick Access Toolbar,
Using the Options For a Report Filter On A Pivot Table,
Use a Report Filter To Filter Only Certain Records,
Use a Report Filter To Display The Different Entries In The Filter Over Sheets,
Use the Format Report Option To Format A Pivot Table,
Hiding Subtotals in a Pivot Table,
Formatting Fields in a Pivot Table,
Sorting data in a Pivot table
Showing the Top Ten in a Pivot Table
Change Heading Names in a Pivot Table
Inserting Calculations In A Pivot Table
Showing Values As % Of Total
Grouping Data In A Pivot Table
Using Multiple Consolidation Ranges In A Pivot Table
Displaying Details Of Pivot Table Summaries
Using Slices
Pivot Charts
MACROS
Setting Security Options For Macros
To Record A Macro
Create a Button On The Quick Access Toolbar To Run A Macro,
Adding a Command Button On A Worksheet For A Macro
Edit a Macro To Go To The Very Last Row Of Information
Edit a Macro To Reference Specific Cells,
Running a Macro Using Workbook Events,
Running a Macro Using Sheet Events
Macro Project