Microsoft Excel Courses - Advanced

Data Analysis with PivotTables

Essentials

• Preparing data for a Pivot Table
• Creating & Editing a Pivot Table
• Using the column, row, page/filter and value positions
• Formatting a Pivot Table
• Adding & editing a Pivot Table Chart
• Using multiple fields
• Filtering on multiple fields
• Using the Slicer - from Excel 2010

Dates, Custom Groups, Multiple Tables

• Working with dates and grouping dates
• Creating custom groups
• Creating multiple Pivot Tables

• Using Sum, Average, Max, Min
• Adding a field more than once to the values area
• Changing the calculation of the data
• Calculating a % of column, row and table
• Calculating a running total and % of base record
• Comparing two years figures
• Adding a calculated field
• Creating a Pivot Table from multiple data sources

Data Validation

• Setting up Data Validation to enable:
• Pick from List
• Specific numbers to be typed in cells
• Controlling the error messages and input alerts

Sharing Workbooks

• Setting User Information
• Making a workbook shareable
• Updating a shared workbook
• Updates by multiple users at the same time
• Resolving and viewing conflicts
• Viewing the change history
• Protecting a shared workbook

Macros

• Recording and running a Macro
• Macro storage
• Absolute and Relative referencing
• Assigning a Macro to a button, keystroke and menu
• The Personal Macro Workbook