Advanced - Pivot Tables

Banner

Your banner contains a hero image with text overlay. You can also add call to action buttons.

Interested in this course? Email us at [email protected]

Icons & text

Icon & text

Use an icon and text to communicate the value proposition and share the specific benefits of your offering. What makes it unique in your market?
Icon & text

Use an icon and text to communicate the value proposition and share the specific benefits of your offering. What makes it unique in your market?
Icon & text

Use an icon and text to communicate the value proposition and share the specific benefits of your offering. What makes it unique in your market?

Course curriculum

1. Downloadable Workbooks
2. New Advanced Pivot Table Videos - Uploaded 02.2020
1. Refresh All
2. Refresh External Data
3. Import from Access database
4. Change Data Source
5. Use a customized style in another workbook
6. Show the Classic Pivot Table Layout
7. Predetermined number formatting
8. Keep Column Widths upon Refresh
9. Automatically Refresh a Pivot Table
1. StdDevp - Standard Deviation Population
2. VarP - Variance Population
3. See all Pivot Items
4. Show a unique or distinct count in a Pivot Table
1. Percentage of Parent Row Total
2. Percentage of Parent Column Total
3. Percentage of Parent Total
4. Rank Smallest to Largest
5. Rank Largest to Smallest
6. Index
7. CASE STUDY: Percentage of Revenue Margins
1. Shortcuts to Grouping
2. Group by Half Years
3. Group by a Date that starts on a Monday
4. Grouping by a custom date
5. Grouping by a Fiscal Year and Quarter
6. Errors when Grouping by Dates
7. Group 2 Pivot Tables in the same Workbook
8. Fixing the problem of counting Grouped sales
9. Display dates that have no data
10. CASE STUDY: Quarterly Comparative Report
11. CASE STUDY: Min and Max Bank Balance Report
1. Override a Custom List sort
2. Sort row from A-Z and sales from Z-A
3. Sort new Items added to your data source
4. Clear a sort
5. Sort Largest to Smallest Grand Totals
1. Shortcuts to filters
2. Keep or hide selected items
3. Filter by Text wildcards
4. Filter by multiple fields
5. Apply multiple filters
6. Filter by multiple values
7. Include new Items in manual filter
8. Clear filters with one click
9. Add a filter for the column items
10. CASE STUDY: Top 5 Expense Report
11. CASE STUDY: Top 25 Percent of Channel Partners
1. Creating a custom Slicer style
2. Copy a custom Slicer style into a new workbook
3. Use one Slicer for two pivot tables
4. Lock the workbook but not the Slicer
5. Interactive employee photos with Slicers!
6. CASE STUDY: Base, Best and Worst Case Slicer Forecast
1. Solve Order for Calculated Items
2. List Calculated Field & Item formulas
3. Remove a Calculated Field temporarily
4. Order of operations
5. CASE STUDY: Creating a Profit & Loss Pivot Table Report
1. Shortcuts to formatting a Pivot Chart
2. Link a Pivot Chart title to a pivot cell
3. Copy a second Pivot Chart
4. Put a Pivot Chart on a separate page with F11
5. Insert Pivot Chart straight from the data source
6. Paste Pivot Chart to your email as a picture
7. Paste a Pivot Chart to PowerPoint & make live updates
8. Chart Do's and Dont's
9. Use Slicers to change Chart types
10. Pivot Chart workaround to creating an interactive Scatter graph
11. CASE STUDY: Profit & Loss Pivot table Report with Graphs
1. Conditionally Format only cells that contain - For Bonuses
2. Conditionally Format only Top or Bottom ranked values - Top 3 sales per year
3. Conditionally Format values that are above or below the average - For Promotions
4. Use a formula to determine which cells to Conditionally Format
5. Use selected cells to Conditionally Format multiple fields
6. All cells showing values to Conditionally Format multiple fields
7. Control Conditional Formatting with Slicers
8. Show Text in the Pivot Table Values area with Conditional Formatting
9. Conditionally Formatting Blank Cells
10. CASE STUDY: Accounts Receivable Ageing Report Matrix
1. Intro to GETPIVOTDATA
2. Create a custom report with GETPIVOTDATA
3. Reference Dates with GETPIVOTDATA
4. Data Validation with GETPIVOTDATA
5. Shortfalls of GETPIVOTDATA
6. Grand Totals to the left of the Pivot Table
7. CASE STUDY: Live forecasting with GETPIVOTDATA
8. CASE STUDY: Channel analysis with GETPIVOTDATA
1. Adding the Developer Tab
2. Record a simple Macro to Refresh a Pivot Table
3. Date Filter Macro
4. Different Pivot Table views Macro
5. Top 10 Macro
6. Add Macro to Quick Access Toolbar
1. Reducing file memory by copying existing Pivot Table
2. Reducing file memory by deleting the data source
3. Reducing file memory by saving file as XLSB
4. Reducing file memory by keeping data in Access
5. Compatibility Issues with Excel 2007 and Excel 2010
6. Sharing a Pivot Table via OneDrive
1. Frequency Distribution with a Pivot Table
2. Interactive Balance Sheet Pivot Table
3. Sales Forecasting with Calculated Fields
4. Consolidate with a Pivot Table
5. Break Even Model with a Pivot Table
6. Several Slicer custom styles for you to use
7. Monthly Sales Manager Performance
8. Reconciling customer payments
1. Cosmetic Changes
2. Recommended Pivot Tables
3. Distinct Count
4. Timeline Slicer
5. Data Model
1. Group Periods
2. Multiselect Slicers
3. PivotChart Expand and Collapse
4. 3D Maps
1. Which Excel version do you have?
2. Default Pivot Table Layout
3. Automatic Relationship Detection
4. Automatic Time Grouping
5. Search in a Pivot Table Field List
1. [Quiz] - Advanced - Pivot Tables
1. Unlock your Certificate of Completion

About this course

Free
125 lessons
5.5 hours of video content

Pricing options

Explain how different pricing options might be valuable to different segments of your audience.

Free

Regular price

Get started now

Call to action

This is where you seal the deal. Sprinkle this section throughout you page to push prospects to purchase!

Get started now

Banner

Icons & text

Icon & text

Icon & text

Icon & text

Course curriculum

Downloadable Workbooks & New Videos Added

Advanced Pivot Table Techniques

Summarize Values By

Show Values As

Grouping Data

Sorting Data

Filter Data

Slicers

Calculated Fields & Items

Pivot Charts

Conditionally Format a Pivot Table

GETPIVOTDATA Formula

Automate a Pivot Table with Macros

Reducing Pivot Table Memory Size

Bonus Pivot Table Reports

New Pivot Table Features in Excel 2013

New Pivot Table Features in Excel 2016

New Pivot Table Features in Excel 2019 & Office 365

Quiz Test

Certificate of Completion

About this course

Pricing options

Free

Call to action