Course Code: 1077

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

Class Dates:
1/4/2019
2/8/2019
3/8/2019
Length:
1/2 Days
Cost:
$$175
Class Time:
Technology:
Office
Delivery:

Overview

  • Course Overview
  • CRUNCH ANY DATA, FROM ANY SOURCE, QUICKLY AND EASILY, WITH EXCEL 2013 PIVOT TABLES!

    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!

  • Audience
  • 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.

Prerequisites

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

  • Recommended Courses:

Course Details

  • 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
  • Introduction
  • 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