Excel 365 Part 15 – Pivot Tables

PivotTables are used to summarize a large database of data into a more manageable visible layout. This will open your eyes to the true purpose of PivotTables because you will see the layout in a new light. Why should you use a PivotTable? It will allow you to identify areas of concern, reduce human error, and improve efficiency. The best time to use a PivotTable is when you need to find relationships between data. A PivotTable can also be useful if you are looking to layout your data in a unique way, you notice changing trends, or just want to organize your data for better understanding.Show more

Video Series Part 15 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth.
Higher Quality Video: www.elearnlogic.com/media/excel365-2-ch6.mp4

- Click + Rumble if the video was helpful.
- Click Subscribe so you won’t miss out on any new videos.
- Click the Share button so you can help others expand their skills.

Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video.

Courseware: Excel 365 - Formulas, Charts, And PivotTables
The courseware is available on Amazon at: https://www.amazon.com/dp/1660221943

Chapter 6 - PivotTable And PivotChart
PivotTables are used to summarize a large database of data into a more manageable visible layout. This will open your eyes to the true purpose of PivotTables because you will see the layout in a new light. Why should you use a PivotTable? It will allow you to identify areas of concern, reduce human error, and improve efficiency. The best time to use a PivotTable is when you need to find relationships between data. A PivotTable can also be useful if you are looking to layout your data in a unique way, you notice changing trends, or just want to organize your data for better understanding.

Section 1: Understanding PivotTables
In this section, we will answer the basic question of "Why do you want to use a PivotTable?" Once you understand "why," you will have a deeper appreciation for its capabilities. We will take you through the process to create a Pivot Table by creating several different types. The following concepts will be covered: Internal Data Source, Row Drop Zone, Column Drop Zone, ∑ Values Drop Zone, Report Filter Drop Zone, Add Multiple Fields, Multiple PivotTables, Excel Table, External Datasource, Dedicated PivotTable Cache, Shared PivotTable Cache, Multiple Consolidation Of Ranges, Default Pivot, and Table Layout.

Section 2: Filter Feature
Filtering is the core part of a PivoTable which is used to adjust the amount of data within a PivotTable. This section will cover the standard filter feature and Slicer capability. The following concepts will be covered: Filter, Filter Options, Sort A to Z, More Sort Options, Clear Filter, Clear Filters, Clear All, Label Filters, Value Filters, Search, Check, Boxes, Field List Filter, Slicer, Slicer Color Codes, Multi-Select Slicer, Delete Slicer, Slicer Settings, Slicer Formatting, Slicer Report, Analytics Dashboard, Timeline, Fields Settings, Filter Section, and Filter On Worksheets.

Section 3: PivotTable Formatting
Once a PivotTable is created, it can be formatted using many of the standard Excel formatting tools including Conditional Formatting capabilities. The following concepts will be covered: PivotTable Styles, Style Options, Format Cells, Select, Conditional Formatting, Number Format, Merge And Center Cells, Autofit Columns, Preserve Formatting, Blank Values In Database, Empty Cells In The PivotTable, Blank Rows And Columns, Display Features, Custom Name, Convert to Values, Copy And Paste, PivotTable, and Themes.

Section 4: PivotChart
PivotCharts are directly tied to PivotTables and any changes made to a PivotTable will directly affect the PivotChart. Once created, the graphical layout can be formatted and adjusted using standard Chart features.

Section 5: PivotTable Adjustments And Layout
Once a PivotTable is created, labels can be adjusted or grouped, subtotals/total fields can be added and moved to an entirely New Sheet, PivotTable values can be copied, and DrillDowns can be added which allow you to see the details of any field.
The following concepts will be covered: Grouping and Ungrouping, Collapsing And Expanding, DrillDown, Move Heading, Move PivotTable, Manual Date Grouping, Autodetect Date, Convert to Values, Subtotals, SubTotal Controls, Grand Totals, Report Layout, Blank Rows, and Compare Two Tables,

Section 6: Formulas And Value Field Settings
Here, both internal and external Formulas to the Pivot Table will be covered. The Value Field Setting will also be changed in order to modify the way numbers are calculated so you can explore ways to compare values. The following concepts will be covered: Order Of Operations, Calculated Item, External Formulas, Refresh Data, Table Object, Internal Formulas, Solve Order, List Formulas, Custom Name, Summarize Values By, Show Value As, Running Total, Defer Update, and GetPivotData.

This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019.

If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com