Microsoft Excel Pivot Tables - Beginner to Advanced

Understand why Excel pivot tables quickly summarize large data sets and reveal insights, and learn to merge data sources with Power Query to build effective pivot reports.

Identify pivot table source data from single or multiple datasets, including Excel lists and Power Query connected sources, to build reports from orders and customers data.

Create a pivot table from the orders data to summarize spending by shipping method across years. Use the sample pivot table as a guide and explore year-by-year totals.

Use the pivot table fields list to drag fields into rows, columns, and values, and summarize data like total freight by shipper and count of customers.

Modify a pivot table layout by dragging fields into rows, columns, and values, then group ship date into years, quarters, and months to reveal trends.

Refresh pivot tables to reflect updated source data in shared workbooks by clicking the refresh button, which pulls changes from the original data or external sources.

Learn to convert a data list into an Excel table and name it, then set the pivot table source to that table so updates and new rows refresh automatically.

Ensure your original source data is properly laid out with clear headers and formatting, and learn the do's and don'ts of source data design for accurate pivot tables.

Learn the do's and don'ts of proper Excel list design, including headers, table formatting, clear column names, no empty rows, no merged cells, and consistent formatting to support pivot tables.

Spot and fix common list design issues in the orders data, such as merged headers and empty rows, to enable accurate pivot tables.

Clean and standardize Excel headers for a pivot table. Merge order date across two columns, remove the extra column, add spaces, rename ID to employee ID, and remove dollar signs.

Clean the data by removing empty rows and filling blank cells to improve pivot table accuracy, using go to special to find blanks and delete empty rows with ctrl minus.

Create and format a pivot table, using the proper() function to standardize ship via values and refresh the pivot to reflect proper case.

Clean up pivot table data by applying the trim function to remove leading, trailing, and extra inner spaces; then paste values and refresh to reveal three unique values.

Learn to use the value function to convert text numbers in the freight column, fix data types, and ensure pivot tables sum rather than count, then refresh when data updates.

Learn how to convert text to uppercase in Excel using the upper function, including creating, copying, and pasting values with paste special for IDs.

Apply data validation to a list to ensure integrity and prevent misspellings by providing a dropdown of shipping methods (Shipping, Speed Express, United package) in Excel, aiding clean pivot tables.

Identify and correct invalid list data in Excel by circling values that violate data validation, using a dropdown of valid shipping methods. Clean misspellings to ensure proper pivot table groupings.

Identify and remove duplicate records in Excel before creating pivot tables by using conditional formatting to spot duplicates and the remove duplicates tool on the order ID column.

Format the list as a table for pivot table sourcing by name, keep headers, adjust the design, and rename the table to a name with no spaces for seamless refreshing.

Learn how the pivot table value area summarizes data by default: numeric values are summed, text values are counted, and dates are counted. Then adjust these calculations for custom summaries.

Learn to manage pivot table value field settings to switch from sum to average, min, or max. Break out the data by ship via and customize the freight value name.

Discover how to use show values as settings in Excel pivot tables to switch between sum, average, min, and max, and display percentages of the grand total for freight data.

Format pivot table values to display percentages and currency cleanly, learn to apply number formats in value field settings so formatting persists as data changes.

Explore how to group pivot table dates using default and manual grouping, fix text values that aren't dates, and refresh data to summarize freight by year, quarter, and month.

Modify the default date grouping in your pivot table by using the group field in the analyze tab, choosing years, quarters, or months to display.

Create manual groups in pivot tables by selecting employees (employee ID) and naming groups like 18 and B team; then collapse and view group totals by year.

Create multi-level pivot table row groupings by dragging fields into the row section, order them to form a hierarchy—ship via, year, and employee ID—and observe the effect on summary data.

Sort and filter data in an Excel pivot table to analyze sales by buyer. Learn to sort by row labels and by summed items to rank top buyers.

Create and apply custom lists to sort pivot table data in a non alphabetical order. Learn to edit and use these lists in Excel options for hierarchical sorting.

Sort pivot table column headers to rearrange monthly data by applying a descending a-to-z sort on a header. Drop months into the column section and test different sort orders.

Learn to filter pivot table data to show only the records you need, using classic filters and slicers to drill by year, month, and ship via filters.

Explore how to replace standard pivot filters with slicers in Excel to create interactive, dashboard-like filtering of pivot tables, including multi-select and clear-filter options.

Master removing a pivot table slicer in Excel by clearing filters, then deleting the slicer to reset pivot results, with examples using the order and timeline slicers.

Use a timeline filter to filter pivot tables by dates, via insert timeline in the pivot table analyze tab. Show years or quarters and adjust date range for dashboard insights.

Learn to apply pivot table styles for predefined formatting in Excel, using the design tab to choose a light, medium, or dark style and apply bold headers and totals.

Master managing subtotals in pivot tables by toggling show or hide, and choosing top or bottom placement, while grouping by year and supplier to reveal category and grand totals.

Control grand totals in pivot tables by turning them on or off and choosing whether they appear for rows, columns, or both, using the design tab's grand totals options.

Insert and format pivot table slicers to filter by category and order date, then adjust styles, columns, and size for a clean, accessible dashboard.

Create a pivot chart by linking it to your pivot table, then insert a pie chart and customize labels, legend, and formatting like a regular chart.

Create an interactive excel dashboard by combining pivot tables with pivot charts and slicers to filter summarized data and reveal visual insights.

See how a slicer connects pivot table and pivot chart, so filtering updates both in real time, and switch to a clustered column chart to show categories and shipping methods.

Format a pivot chart using built-in chart styles from the design tab, previewing data labels and visuals. Choose a style that adds context without clutter, such as borders and colors.

Enhance charts by adding data labels and grid lines, exploring quick layouts and adding chart elements, adjust legend position, and even add a data table for clearer context.

Explore how Excel Power Query connects to internal and external data, transforms and combines it, loads it for pivot tables and charts, and refreshes from the original source.

Learn to connect to external and internal data sources with Excel's power query. Transform and consolidate data sources, then load the results into an Excel workbook.

Create a new Power Query connection to an internal data source using from table/range, then open the Power Query Editor and refresh data for pivot tables.

Connect to Excel data in the Power Query editor, transform it without disturbing the source, filter by country like South America, and load to a worksheet for pivot table report.

Load the Power Query results into an Excel worksheet by choosing close and load two, load as a table in a new worksheet, and maintain a live connection for updates.

Refresh the Power Query connection to pull updates from the original source data after edits. Add a record and refresh the table design to show the latest data.

Create a pivot table from a power query data source, count customers by country, and refresh the power query and pivot table when the source updates.

Explore how Power Query merges and appends multiple data sources to build a master list by linking primary and related tables, such as customers and orders, for pivot tables.

Merge two data sources in power query using a left outer join on customer ID, combining customers and orders, then load to sheet to build pivot tables.

Learn to create a pivot table from multiple datasets by appending and merging tables with Power Query, establishing relationships, and summarizing across data in Excel.

Complete the Microsoft Excel Pivot Tables course and enjoy lifetime access to updates, engage with the Q&A board, and teach others how to use pivot tables.