The Complete Excel Pivot Tables Course: Beginner to Advanced

Clean data before analyzing with a pivot table in Excel. Tidy and format datasets to ensure accurate pivot results, addressing empty rows, blanks, duplicates, and inconsistencies from external sources.

Clean your data by removing blank rows before analyzing in a pivot table, using go to special to locate blanks and delete the rows.

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.

Use the find and replace utility to shorten country names in a column for cleaner chart axes. See how case-insensitive matching and the substitute formula offer quick alternatives.

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.

Clean a dataset in Excel by removing blanks and duplicates, standardizing text with proper case, applying currency formats, using flash fill to split month and year, and correcting spelling.

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.

Format your data as an Excel table using format as table or Ctrl+T, apply a style, toggle headers and totals, name the table (sales_data), and prepare for pivot table analysis.

Practice formatting a clean data set as an Excel table and name it sales_data. Convert column M from text to numbers to ensure Excel recognizes them as numeric.

Explore how to use Excel's recommended pivot tables to quickly summarize data, choosing from prebuilt layouts that show regions, the item type, and key metrics like revenue, cost, and profit.

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.

Explore building pivot tables by dragging fields into rows, columns, and values to summarize data such as total profit by region and by item type, with filters and collapsible sections.

Add multiple dimensions in pivot tables by dragging fields into rows, columns, or filters to analyze data across region, item type, sales channel, and order priority, yielding multi-dimensional insights.

Delete fields to simplify the pivot table and lock the report layout. Turn off auto fit column widths on update to prevent pivot chart overlaps as you rearrange fields.

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.

Combine multiple aggregation methods in a pivot table to extend analysis beyond the source data by duplicating fields and applying sum, average, max, and min calculations, with renaming for clarity.

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.

Practice pivot table layout techniques by turning off grand totals, moving subtotals to the bottom, switching to tabular form, repeating item labels, and inserting a blank line after each group.

Apply and customize pivot table styles using the design ribbon, preview styles in the gallery, and adjust options like row headers, column headers, and banded rows to improve readability.

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.

Sort pivot table data to organize profit by region, item type, and sales channel for clearer analysis. Apply ascending, descending, manual sorts, and custom lists, then refresh as needed.

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.

Apply the top 10 filter in Excel pivot tables to spotlight the top five countries by profit, reducing clutter before charting.

Practice sorting and filtering pivot tables by removing the product field, sorting by country in descending order, filtering the top three countries by gross sales, and isolating the Luxe range.

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.

Explore creating and formatting pivot charts in Excel, using clustered column and bar charts to show top five countries by units sold, with data labels and formatting.

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.

Learn how to create and format pie and donut charts from pivot tables, including grouping data into active versus cancelled orders, adding data labels, and refining titles and colors.

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.