Excel 2013: Creating and Using Pivot Tables (1/2 Day)



COURSE TIMES: 9:00am - 12:00pm

Printable version of this course
Register for this course



Use Excel 2013 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control!


Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. In just the first seven chapters, you learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then, you go even further, discovering how to build a comprehensive, dynamic pivot table reporting system for any business task or function.

This course is intended for those familiar with Microsoft Excel. Completion of Excel 2013 Level 1 Introduction to Excel or equivalent knowledge is required.


*Course cost listed does not include the cost of courseware (required) or lunch (optional). Please contact us at learn@vtec.org or 207-775-0244 for additional pricing information, or if you have any questions.
Course subject to minimum enrollment.


After completing this course, you'll be able to:
Create, customize, and change your pivot tables and pivot charts
Transform gigantic data sets into crystal-clear summary reports
Summarize and analyze data even faster with new Excel 2013 recommended pivot tables
Instantly highlight your most (and least) profitable customers, products, or regions
Quickly filter pivot tables using slicers
Use dynamic dashboards using Power View to see exactly where your business stands right now
Revamp analyses on the fly by simply dragging and dropping fields
Build dynamic self-service reporting systems your entire team can use
Use PowerPivot or the Data Model to create pivot tables from multiple data sources and worksheets
Work with and analyze OLAP data, and much more


Pivot Table Fundamentals
What is a Pivot Table?
Why Should You Use a Pivot Table?
When Should You Use a Pivot Table?
The Anatomy of a Pivot Table
Values Area
Row Area
Columns Area
Filters Area
Pivot Tables Behind the Scenes
Limitations of Pivot Table Reports

Creating a Basic Pivot Table
Preparing Your Data for Pivot Table Reporting
Ensure Your Data Is in a Tublar Layout
Avoid Storing Data in Section Headings
Avoid Repeating Groups as Columns
Creating a Basic Pivot Table
Adding Fields to the Report
Adding Layers to Your Pivot Table
Rearranging Your Pivot Table
Creating a Report Filter
Understanding the Recommended Pivot Tables Feature

Customizing a Pivot Table
Making Common Cosmetic Changes
Making Report Layout Changes
Customizing the Pivot Table Appearance with Styles and Themes
Changing Summary Calculations
Adding and Removing Subtotals
Changing the Calculation in a Value Field

Grouping, Sorting, and Filtering Pivot Data
Grouping Pivot Fields
Using the PivotTableFields List
Sorting in a Pivot Table
Filtering the Pivot Table
Using Filters for Row and Column Fields
Filtering Using the Filters Area
Filtering Using Slicers and Timelines

Performing Calculations Within Your Pivot Tables
Introducing Calculated Fields and Calculated Items
Method 1 Manually Add the Calucalted Filed to Your Data Source
Method 2 Use a Formula Outside Your Pivot Table to Create the Calculated Field
Method 3 Insert a Calculated Field Directly into Your Pivot Table
Creating Your First Calculated Field
Creating Your First Calculated Item
Understanding the Rules and Shortcomings of Pivot Table Calculations
Managing and Maintaining Your Pivot Table Calculations

Using Pivot Chars and Other Visualizations
What is a Pivot Chart?
Creating Your First Pivot Chart
Keeping Pivot Chart Rules in Mind
Examining Alternatives to Using Pivot Charts
Using Conditional Formating with Pivot Tables
Creating Custom Conditional Formatting Rules

Analyzing Disparate Data Sources with Pivot Tables
Using Multiple Consolidation Ranges
Using the Internal Data Model
Building a Pivot Table Using External Data Sources

Sharing Pivot Tables with Others
Designing a Workbook as an Interactive Web Page
Sharing Pivot Tables with Other Versions of Office

Working with and Analyzing OLAP Data
What is OLAP?
Connecting to an OLAP Cube
Understanding the Structure of an OLAP Cube
Understanding the Limitations of OLAP Pivot Tables
Creating Offline Cubes
Breaking Out of the Pivot Table Mold with Cube Functions
Adding Calculations to Your OLAP Pivot Tables

Mashing Up Data with PowerPivot
Understanding the Benefits and Drawbacks of PowerPivot and the Data Model
Merge Data from Multiple Tables Without Using VLOOKUP
Import 100 Million Rows into Your Workbook
Create Better Calculations Using the DAX Formula Language
Joining Multiple Tables Using the Data Model in Regular Excel 2013

Dashboarding with Power View
Preparing Your Data for Power View
Creating a Power View Dashboard
Replicating Charts Using Multiples
Showing Data on a Map
Using Table or Card View with Images
Changing the Calculation
Animating a Scatter Chart Over Time
Some Closing Tips on Power View

Enhancing Your Pivot Table Reports with Macros
Why Use Macros with Your Pivot Table Reports?
Recording Your First Macro
Creating a User Interface with Form Controls
Altering a Recorded Macro to Add Functionality

Using VBA to Create Pivot Tables
Enabling VBA in Your Copy of Excel
Using a File Format That Enables Macros
Visual Basic Editor
Visual Basic Tools
The Macro Recorder
Learning Tricks of the Trade
Understanding Object-Oriented Code
Building a Pivot Table in Excel VBA
Pivot-Tabel Creating a Report Showing Revenue by Category
Calculating with a Pivot Table