Â鶹¹û¶³´«Ã½

Pivot tables are arguably Excel's most powerful feature, and most likely to impress your boss. At first glance, pivot tables seem quite mysterious: with a quick drag-and-drop, your data somehow reorganizes itself into interesting groupings and useful totals. There are more than a few organizations whose critical business decisions rely on carefully designed pivot tables. Unfortunately, because they accomplish so much behind the scenes" it's hard to figure out how pivot tables actually work — and scary to try to modify existing ones. That's where our trainers come in: they love nothing more than demystifying tricky features like pivot tables. After this class, you'll be the one creating pivot tables to capture insights about your company's data. Here's what you'll learn:

• Cleanup. The data in your spreadsheet needs to meet certain criteria before it can be turned into a pivot table. Â鶹¹û¶³´«Ã½'ll show you how to clean the data, designate the cells you want to become a pivot table, place it exactly where you want, and format it nicely in color.
• Manipulation. The advantage of a pivot table is being able to entirely rearrange your view of the data with nothing more than a few mouse clicks. For example, which is more useful: orders by city, revenue by customer, or orders by salesperson? Being able to easily switch these parameters — within the same worksheet — is what makes pivot tables so useful.
• Aggregate Functions. Seeing different views of the data is only useful if you can summarize (aggregate) information about that data: what were the totals, averages, medians and counts for each different view of the data? Â鶹¹û¶³´«Ã½'ll show you how to apply these functions and filter the results to find the true insights.
• Pivot Charts. As with normal tables of data, it's helpful to create charts that illustrate the pivoted views of the data. Â鶹¹û¶³´«Ã½'ll show you how to present the data from pivot tables in colorful visualizations (unless you prefer black and white).
• Big Data and "Power" Features. Excel has a limit of only one million rows, and if you've ever tried using them all, your computer probably slowed to a crawl. Enter the family of "Power" features: Power Query, Power Pivot, Power View, Power Map, and Power BI. These features allow you to import hundreds of millions of rows of data — even from multiple databases simultaneously — and use them in a spreadsheet. Â鶹¹û¶³´«Ã½'ll show you how to get this so-called "big data" using Power Query and then apply calculations using Power Pivot. Ultimately, you'll be able to turn them into pivot tables, pivot charts and other data visualizations.

Module 1:

Building Strong Foundations with Pivot Tables

·ÌýÌýÌýÌýÌýÌýIntro to Data Mining Techniques

·ÌýÌýÌýÌýÌýÌýFlash Fill

·ÌýÌýÌýÌýÌýÌýAdding Related Data with XLOOKUP

·ÌýÌýÌýÌýÌýÌýManaging Your Pivot Tables

·ÌýÌýÌýÌýÌýÌýNaming Conventions for Easy Reference

·ÌýÌýÌýÌýÌýÌýRefreshing and Updating Data Sources

·ÌýÌýÌýÌýÌýÌýLayering Fields for Dynamic Views

·ÌýÌýÌýÌýÌýÌýFormatting with Functions & Number Styles

·ÌýÌýÌýÌýÌýÌýRevealing Details Behind the Summary

·ÌýÌýÌýÌýÌýÌýVisualizing with Pivot Charts

·ÌýÌýÌýÌýÌýÌýChart Formatting Basics

·ÌýÌýÌýÌýÌýÌýAdvanced Filtering & Slicing for Pivot Charts

Module 2:

Advanced Strategies & Power Pivot Essentials

·ÌýÌýÌýÌýÌýÌýCustomizing Report Layouts

·ÌýÌýÌýÌýÌýÌýDesigning and Applying Table Styles

·ÌýÌýÌýÌýÌýÌýWorking with Subtotals & Grand Totals

·ÌýÌýÌýÌýÌýÌýExploring Pivot Table Options

·ÌýÌýÌýÌýÌýÌýCreating Calculated Fields & Items

·ÌýÌýÌýÌýÌýÌýIntroduction to Power Pivot & Data Modeling

·ÌýÌýÌýÌýÌýÌýAdding Data to the Model

·ÌýÌýÌýÌýÌýÌýBuilding Relationships Across Tables

·ÌýÌýÌýÌýÌýÌýDesigning Custom Measures

·ÌýÌýÌýÌýÌýÌýCreating Custom Columns

·ÌýÌýÌýÌýÌýÌýGetting Started with DAX Expressions

·ÌýÌýÌýÌýÌýÌýBuilding Advanced Power Pivot Tables

·ÌýÌýÌýÌýÌýÌýFrom Pivot Tables to Power Pivot: The Next Step

·ÌýÌýÌýÌýÌýÌýSneak Peek: Power Query & Power BI