The Complete Excel Pivot Tables Course: Beginner to Advanced

Identify and remove exact duplicate rows in your dataset using Excel's remove duplicates tool, selecting all columns and ensuring headers are checked.

Clear formats to remove shading, bold, and italics, restoring your data to plain text, and note that number formatting may also reset during clearing.

Apply appropriate number formatting to columns, converting text, dates, numbers, and currency values; compare currency and accounting formats and use the format cells dialog to improve readability.

Tackle dirty data by using text functions to change case in Excel, with upper, lower, and proper. Use a helper column and paste values to finalize data.

Clean data in Excel by removing erroneous spaces and non-printing characters with trim and clean. Combine formulas to adjust case, remove line breaks, and paste values to finalize a column.

Split a mixed column into separate order date and order ID with flash fill and text to columns, then merge data with the concatenate function to prep pivot-ready files.

Identify numbers stored as text after data imports and convert them to numeric format using the convert to number option or VALUE formula, resolving left-aligned text and green triangle warnings.

Run spell check on all worksheets to ensure consistent data for pivot tables, set the dictionary language correctly, and use suggestions, change all, or add to dictionary to fix errors.

Learn how Excel tables differ from pivot tables and why placing data in an Excel table before creating a pivot table makes updates, filtering, and refreshing effortless.

Learn to create a pivot table from scratch using a table range, choose data sources, place it on a new worksheet, and compare insert and table design methods.

Learn how to use pivot table ribbons and the field list to build and customize pivot table reports. Drag fields into filters, rows, columns, and values to analyze data.

Practice creating a pivot table from the given data, place country and product in rows and gross sales in values, using sum as the calculation on a new worksheet.

Explore aggregation and grouping in pivot tables by summing numeric fields or counting text and dates, and tailor results with value field settings to compute average, max, or min.

Group and ungroup pivot table data to summarize by regions, item types, and dates, including automatic year, quarter, and month breakdowns, and create custom groups like food and drink.

Practice data aggregation in a pivot table by adding gross sales fields and displaying average, minimum, and maximum values, then create Luxe and Premium groups.

apply consistent number formatting in pivot tables by using value field settings or right-click, choosing accounting or currency, and setting two decimal places with a currency symbol.

Learn to handle empty cells in pivot tables by replacing blanks with zero, enabling accurate charts when viewing Albania's profit by item type and sales channel.

Apply accounting formatting with zero decimals and a US dollar symbol to all value columns, then set pivot table options to display zero for empty cells.

Explore how to display and customize subtotals and grand totals in Excel pivot tables, subtitles and totals positions, and how to toggle them on or off via the design tab.

Choose a report layout to tailor pivot table readability by selecting compact form, outline form, or tabular form; compact retains grouping and grid lines.

Insert blank rows from the design ribbon to emphasize groups and improve readability in pivot tables by adding a blank line after each grouped item, throughout the pivot table.

Duplicate the pivot table style to create a custom style, modify header color, borders, font italics, and row stripes, then apply as default or add the gallery for quick access.

Create a custom pivot table style from scratch, applying brand colors and full formatting, and learn how to reuse it since styles stay only in the current workbook.

Apply light blue pivot style dark six and banded rows. Duplicate and modify style, name it with your initials, apply to pivot table, and rename Product two to range.

Filter pivot table data to focus on Americas using region filters. Use value filters with wildcards for profits over 50 million, then clear filters and enable multiple filters per field.

Explore selecting the right chart type for pivot table data, comparing column, bar, line, donut, pie, and map charts to clearly visualize trends, comparisons, and geographical profits.

Create line charts from pivot table data to analyze time series trends using order date, adjust pivot layouts, and apply chart formatting such as titles, legends, axis scales, and markers.

Create a map chart from pivot table data by moving values outside the pivot, then link it back for automatic updates, while formatting totals by region and country.

Apply chart layouts in Excel to quickly add chart elements such as axes, titles, legend, data labels, and grid lines via layouts, including data tables for line and donut charts.

Create a clustered column pivot chart, remove value fields, clear country and range filters, hide field buttons and legend, set title, gap width to 51 percent with purple bars.