Excel 2016 Pivot Table
9:00am - 12:00pm
Advances in technology have made it possible to store ever increasing amounts of data. Along with this, the need to analyze that data and gain actionable insight is greater than ever. You already have experience working with Excel and creating basic PivotTables to summarize data. But, Excel is capable of doing much more. You will not only be able to summarize data for you to analyze, but also organize the data in a way that can be meaningfully presented to others. This leads to data-driven business decisions that have a better chance for success for everyone involved.
AUDIENCE AND PREREQUISITES
- Upon successful completion of this course, you will be able to use Excel 2016 advanced PivotTable functionality to analyze your raw data.
- Prepare data for PivotTable reporting and create PivotTables from various data sources.
- Analyze Data Using PivotTables.
- Work with PivotCharts.
Students taking this course are experienced Excel users who are seeking to advance their data analysis capabilities by using PivotTables.
PREREQUISITE COURSESFOLLOW UP COURSES
To ensure your success in this course, you should have experience working with Excel® 2016 and PivotTables. You should already understand spreadsheet concepts and be comfortable creating basic PivotTables.
*Course cost listed does not include the cost of courseware or exam . Please contact us at email@example.com or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to a minimum enrollment to run. Course may run as a virtual instructor led class if the minimum enrollment is not met. Students can connect virtual into a class if travel is an issue. If students connect virtually, VTEC is not responsible for the internet connection. Please verify that you have a good internet connection.
Pivot Table Fundamentals Defining a Pivot Table When to Use a Pivot Table Anatomy of a Pivot Table Pivot Tables Behind the Scenes Pivot Table Backward Compatibility Next StepsLesson 2: Creating a Basic Pivot Table Preparing Data for Pivot Table Reporting How to Create a Basic Pivot Table Understanding the Recommended Pivot Table Feature Using Slicers Keeping Up with Changes in the Data Source Sharing the Pivot Cache Saving Time with New Pivot Table ToolsLesson 3: Customizing a Pivot Table Making Common Cosmetic Changes Making Report Layout Changes Customizing a Pivot Table’s Appearance with Styles and Themes Changing Summary Calculations Adding and Removing Subtotals Changing the Calculation in a Value FieldLesson 4: Grouping, Sorting, and Filtering Pivot Data Automatically Grouping Dates Using the PivotTable Fields List Sorting in a Pivot Table Filtering a Pivot Table: An Overview Using Filters for Row and Column Fields Filtering Using the Filters AreaLesson 5: Performing Calculations in Pivot Tables Introducing Calculated Fields and Calculated Items Creating a Calculated Field Creating a Calculated Item Understanding the Rules and Shortcomings of Pivot Table Calculations Managing and Maintaining Pivot Table CalculationsLesson 6: Using Pivot Charts and Other Visualizations What Is a Pivot Chart...Really? Creating a Pivot Char Keeping Pivot Chart Rules in Mind Examining Alternatives to Using Pivot Charts Using Conditional Formatting with Pivot Tables Creating Custom Conditional Formatting RulesLesson 7: Analyzing Disparate Data Sources with Pivot Tables Using the Internal Data Model Building a Pivot Table Using External Data Sources Leveraging Power Query to Extract and Transform DataLesson 8 Sharing Pivot Tables with Others Designing a Workbook as an Interactive Web Page Sharing a Link to a Web Workbook Sharing with Power BILesson 9: Working with and Analyzing OLAP Data Introduction to OLAP Connecting to an OLAP Cube Understanding the Structure of an OLAP Cube Understanding the Limitations of OLAP Pivot Tables Creating an Offline Cube Breaking Out of the Pivot Table Mold with Cube Functions Adding Calculations to OLAP Pivot TablesLesson 10: Mashing Up Data with Power Pivot Understanding the Benefits and Drawbacks of Power Pivot and the Data Model Joining Multiple Tables Using the Data Model in Regular Excel 2016 Using the Power Pivot Add-in Excel 2016 Pro Plus Understanding Differences Between Power Pivot and Regular Pivot Tables Using DAX CalculationsLesson 11: Dashboarding with Power View and 3D Map Preparing Data for Power View Creating a Power View Dashboard Replicating Charts Using Multiples Showing Data on a Map Using Images Changing a Calculation Animating a Scatter Chart over Time Some Closing Tips on Power View Analyzing Geographic Data with 3D MapLesson 12:` Enhancing Pivot Table Reports with Macros Why Use Macros with Pivot Table Reports Recording a Macro Creating a User Interface with Form Controls Altering a Recorded Macro to Add FunctionalityLesson 13: Using VBA to Create Pivot Tables Enabling VBA in Your Copy of Exce Using a File Format That Enables Macros Visual Basic Editor Visual Basic Tools The Macro Recorder Understanding Object-Oriented Code Learning Tricks of the Trade Understanding Versions Building a Pivot Table in Excel VBADealing with Limitations of Pivot Tables Pivot Table 201: Creating a Report Showing Revenue by Category Calculating with a Pivot Table Using Advanced Pivot Table Techniques14 Advanced Pivot Table Tips and Techniques Tip 1: Force Pivot Tables to Refresh Automatically Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending Tip 4: Turn Pivot Tables into Hard Data Tip 5: Fill the Empty Cells Left by Row Fields Tip 6: Add a Rank Number Field to a Pivot Table Tip 7: Reduce the Size of Pivot Table Reports Tip 8: Create an Automatically Expanding Data Range Tip 9: Compare Tables Using a Pivot Table Tip 10: AutoFilter a Pivot Table Tip 11: Force Two Number Formats in a Pivot Table Tip 12: Create a Frequency Distribution with a Pivot TableLesson 15: Dr. Jekyll and Mr. GetPivotData Avoiding the Evil GetPivotData Problem Using GetPivotData to Solve Pivot Table Annoyances