Excel 2016 Pivot Tables Deep Dive

Name: Excel 2016 Pivot Tables Deep Dive
Rating: 4.3 (150 reviews)

Become indispensable at work! Become a power user and earn a huge raise by working Pivot magic in Excel.

Created byOptima Train

Last updated 8/2017

English

English [Auto],

What you'll learn

An understanding of how pivot tables allow you to break down numerical data by categories, something other Excel tools can do, but not nearly so quickly or easily
How to build pivot tables by clicking and dragging
An understanding of the advantages of using Excel tables as a basis for pivot tables
How to base pivot tables from an external source or an Excel data model
How to build pivot charts, which are automatically synchronized with the corresponding pivot tables
How to sort and filter in pivot tables
Methods for grouping categories, especially dates, with many categories in pivot tables
An understanding of the role of pivot caches for extremely fast pivot table calculation
How conditional formatting in pivot tables provides even more insightful reports
How to create calculated items and calculated fields for pivot tables
How to use pivot tables for finding unique values, creating histograms, and classification (learning which variables are related to a Yes/No variable)

Course content

7 sections • 36 lectures • 2h 37m total length

Welcome to Pivot Tables and Pivot Charts Course5:07
Master Excel 2016 pivot tables and pivot charts to analyze data quickly, breaking down revenues by product or store, and leverage Power Pivot, Power Query, Power View, and Power Map.
How to download Exercise Files and Handouts0:22
REMINDER: Turn on HD1:00

What Pivot Tables Do for You7:46
Explore how pivot tables in Microsoft Excel reveal distinct order IDs, products by category, and monthly totals, with accompanying charts for clear data insights.
Life Without Pivot Tables3:56
Explore how pivot tables break down numeric data by genre and MPAA rating, and learn fast non-pivot methods using distinct value lists and averageifs/countifs to replicate results.

Data Structure for Pivot Tables4:29
Structure your data as a rectangular range with records in rows and fields in columns, including numeric and categorical variables and descriptive headers, to power effective pivot tables.
Creating a Pivot Table6:05
Learn to create a pivot table from a 4,000-record dataset, summarize total spent by region and gender by dragging fields into rows, columns, and values.
Pivot Charts5:18
Explore pivot charts in Excel, turning pivot table data into charts, with rows labeling the horizontal axis and columns forming the legend, plus easy sorting and filtering.
Pivot Table Layouts5:11
Learn how to choose and switch between compact, tabular, and outline pivot table layouts in Excel 2016, customize labeling, subtotals, and grand totals for clear data insights.
Excel Tables6:37
Excel tables boost data analysis and serve as a strong foundation for pivot tables, enabling sorting, filtering, summarizing, and automatic chart updates as data expands.
Pivot Tables Based on a Table3:29
Learn to base a pivot table on an Excel table, not a range, so it refreshes automatically as data expands and demonstrates switching totals like total spent to total saved.
Pivot Tables Based on External Data4:56
Base a pivot table on external data by choosing an existing connection or browsing for a new data source, including Access, SQL Server, or text file connections.

Pivot Table Fields Pane Options4:21
Master the Excel 2016 pivot table fields pane: dock or undock, rearrange with the top drop-down, and apply per-field sort, filter, and search to refine pivot tables.
Placing Fields in a Pivot Table4:13
Explore how to place any number of fields in the pivot table's rows, columns, and filters area, rearranging order to create multiple layouts while keeping totals clear.
Multiple Fields in the Values Area3:16
Learn to place multiple numeric fields in the values area of an Excel 2016 pivot table, and explore layouts by moving fields across rows, columns, and totals.
Value Field Settings for a Numeric Field4:30
Modify numeric fields in pivot tables by applying currency formatting, selecting a summarizing function such as average, and using show values as via right-click or the value field settings dialog.
Displaying Counts in a Pivot Table6:17
Excel 2016 pivot tables deep dive: learn to display counts by counting rows, not fields, and use show values as percentages (grand total, column total, row total), with a neutral count label.
Show Values As Options5:05
Demonstrate show values as options in pivot tables, including percentages of grand total, column totals, row totals, and base items, plus running totals and ranks from the online purchases workbook.
Filtering and Collapsing on Row and Column Fields5:18
Learn to filter and collapse pivot table fields in rows and columns by using the active field, drop-downs, and expand-collapse controls to reveal or hide subtotals and grand totals.
Field Settings for a Categorical Field4:22
Explore field settings for categorical fields in Excel 2016 pivot tables, including renaming fields, toggling subtotals, choosing summary functions, and controlling manual filter behavior and layout options.
Sorting in Pivot Tables4:43
Explore sorting options in pivot tables, including label sorting for rows and columns and value sorting for numbers, using the online purchases dataset to demonstrate drag-to-order and multi-field behavior.
Sorting with a Custom List2:44
Explore sorting pivot tables with a custom list to achieve the natural order of morning, afternoon, and evening, and create a stored list in Excel options to auto-sort pivot tables.
More Filters for Row and Column Fields6:14
Explore advanced filtering in Excel pivot tables by applying row and column field filters, including text filters, value filters, and top 10 options, using a customer orders dataset.
Slicers for Filtering6:48
Discover how slicers provide a graphical alternative to filter fields in pivot tables, making filtering transparent and easy, with timeline slicers for date filtering.
Styles for Pivot Tables and Charts3:33
Learn to customize pivot tables and charts with styles, colors, and themes via the design ribbon, hovering to preview, and clear or undo changes while keeping the story intact.

Pivot Cache5:28
Excel creates a pivot table by taking a snapshot of the data into a pivot cache in memory, speeding calculations. Refreshing updates the cache and all related pivot tables.
Converting a Pivot Table to Static Values1:51
Convert a pivot table to a static report by copying as values, removing grand totals and unnecessary labels, and deleting the pivot cache before sharing.
Grouping by Selection4:38
Group by selection in pivot tables creates natural subsets for a long categorical field, such as a to h, i to r, and s to z, using the Analyze ribbon.
Showing Report Filter Pages2:51
show filter report pages to create a separate worksheet for each day of the week from the pivot table, using fields in the filter's area.
Displaying Cells with No Data2:31
Replace blanks in a pivot table with zeros by enabling the four empty cell show option in the layout and format tab; show items with no data in field settings.
PivotTable Options Dialog Box3:02
Explore Excel 2016 pivot tables options dialog box across six tabs to rename pivots, format empty or error values, enable multiple filters per field, and auto refresh on open.
Using GetPivotData for Custom Reports7:43
Learn to use the getpivotdata function with pivot tables to build custom monthly reports, turning hard-coded references into dynamic dates and copyable formulas for future months.
Efficiency Tips for Pivot Tables5:26
Learn how to improve pivot table speed with large datasets using defer layout update. Discover strategies to reduce file size with pivot cache tips and safe distribution.

Finding Unique Values with a Pivot Table3:56
Use a pivot table to extract unique values from a long list, quickly listing distinct industries or names and revealing trailing spaces or data errors.
Creating a Histogram with a Pivot Table2:55
Create a histogram to show the distribution of a numeric variable like salary by binning values and counting observations, using a pivot table and pivot chart in Excel 2016.

Requirements

Microsoft Office 2016

Description

This course provides an in-depth coverage of pivot tables and pivot charts in Excel 2016. These are two of the most powerful, if not the most powerful, data analysis tools in Excel's arsenal, and they should definitely be mastered by anyone who aspires to becoming an Excel “power user." As this course will illustrate with many examples, the tools are surprisingly easy to learn and use—once you know they exist. To learn Excel Pivot Tables & Pivot Charts quickly and effectively, download the companion exercise files so you can follow along with the instructor by performing the same actions he is showing you on the videos.

***** THE MOST RELEVANT CONTENT TO GET YOU UP TO SPEED *****

***** CLEAR AND CRISP VIDEO RESOLUTION *****

***** COURSE UPDATED: February 2016 *****

“The Optima Train two part series on Pivot Tables is pure gold! The material is so amazingly thorough and clear that anyone watching the videos and doing the exercises provided will without a doubt become a true expert at working with Pivot Tables and likely become a hero at work by applying and sharing this new-found knowledge. There is nothing like it on the market." - Phil, FORMER TREASURY OPERATIONS MANAGER - EXXONMOBIL

“When I first looked up this site I was a bit skeptical, but I soon realized how amazing these courses are. They have helped me excel in all my business classes. The PowerPivot course now makes business analytics easy and at my fingertips." - Wilson Xu, STUDENT

90% of Excel users do not use Pivot Tables but those that do save hours of time and become valuable assets in their companies. The hours of time you'll save translates to real money in your pocket of earning potential! Imagine the power you will have as a knowledge worker in your company that people go to when they need help. Upper management will notice your sharp analysis and will not be able to do without you. Pivot Tables has been called the best thing since sliced bread. Once you start using it you'll never stop. Pivot Tables and Pivot Charts are not difficult to learn and it's bewildering that not many people use it – you can take advantage of that short supply of power users in the workforce by becoming a power user yourself and an indispensable resource to your work place. Simplify your work and personal life by learning this extremely useful tool.

This is the most comprehensive Excel Pivot Tables & Pivot Charts course and has 43 short video tutorials. The follow-on course of PowerPivot & Advanced Business Intelligence Tools builds on where this course ends. There is zero fluff and no time wasted in this course. The instructor, Dr. Chris, has decades of experience using Excel in real-world settings solving complex business problems. There is no quicker way to learn Excel than to watch these videos and follow along with the free companion exercise workbooks which are downloadable. If you want to stand out among your colleagues, earn a promotion, further your professional development, save tons of hours every year, and learn Excel Pivot Tables & Pivot Charts in the quickest and simplest manner then this course is for you!

You'll have lifetime online access to watch the videos whenever you like, and there's a Q&A forum right here on Udemy where you can post questions.

Who this course is for:

Any competent Excel users who want to take their Excel data analysis skills to a whole new level

Excel 2016 Pivot Tables Deep Dive

What you'll learn

Explore related topics

Course content

Introduction to Course3 lectures • 6min

Why Pivot Tables?2 lectures • 12min

Creating a Pivot Table7 lectures • 36min

Basic Pivot Table Tools13 lectures • 1hr 1min

Other Pivot Table Tools8 lectures • 34min

Selected Uses of Pivot Tables2 lectures • 7min

Conclusion to Course1 lecture • 3min

Requirements

Description

Who this course is for: