PivotTables in Excel Course for Beginners

Learn how to clean data before analyzing with pivot tables by tidying formats, removing blanks and duplicates, and ensuring consistent data imports for accurate pivot analysis.

Clean data by removing blank rows before analyzing in a pivot table, using go to special to select blanks and delete sheet rows, then refresh the pivot table.

Learn how to identify and remove exact duplicate rows in Excel to prepare clean data for pivot tables, using the remove duplicates tool, checking all columns with headers enabled.

Tidy up a worksheet by removing formatting to restore plain text and values, using Excel's clear formats option from the Home tab.

Apply appropriate number formatting in Excel for pivot tables by setting text, date, currency, or accounting formats via the Format Cells dialog (Ctrl+1), selecting short or long dates and decimals.

Split a column into separate order date and order ID with text to columns or flash fill, then merge data using the concatenate (concat) function for pivot table ready results.

Learn how to use Excel's find and replace to shorten country names (DRC, USA, UK), preparing data for pivot tables and charts, with a quick substitute formula alternative.

Learn to run spell check in Excel to clean data for pivot tables, selecting the correct language and dictionary, and using F7 with change, ignore, or add-to-dictionary.

Learn the difference between Excel tables and pivot tables, and why putting data into an Excel table before creating a pivot table streamlines updates, expansion, and analysis.

Format the data set as an Excel table, name it sales_data, and convert column M from text to numbers, using the Home tab or Ctrl+T.

Explore how to use Excel's recommended pivot tables to quickly summarize clean data by region or item type, then customize and build a blank pivot table from scratch.

Learn to create a pivot table from scratch by selecting data, choosing table range or external data source, and placing it on a worksheet with the pivot table field list.

Explore pivot table ribbons and the field list to manage, style, and arrange data, then drag fields into filters, columns, rows, and values to analyze regions and units sold.

Build pivot tables by dragging fields into rows, columns, and values to sum totals like total profit by region, then use filters and collapsible items to refine analysis.

Drag fields to create multi dimensional pivot tables, analyzing data by region, sales channel, and order priority, and remove fields to revert to region view with sum of total profit.

Delete fields to tidy a pivot table and lock the report layout, then control column widths by turning off auto fit on update in PivotTable options.

practice creating a pivot table from excel table data, placing country and product in rows and gross sales in values with a sum calculation on a new worksheet.

Explore aggregation methods in pivot tables, learning when to sum or count based on numeric or text fields, and how to apply average, max, and min via value field settings.

Practice extending a pivot table by adding gross sales fields to show average, minimum, and maximum, renaming headers, and grouping items into Luxe and Premium.

Learn to handle empty cells in pivot tables by replacing blanks with zero to keep analyses complete. Filter Albania, then view profit by item type and sales channel.

Apply accounting formatting with zero decimal places and a U.S. dollar symbol to all pivot table value columns, then configure the pivot table to show zeros for empty cells.

Explore how to manage subtotals and grand totals in pivot tables, control their display with the design tab layout options, and choose where they appear for rows or columns.

Explains how to choose between compact, outline, and tabular report layouts in pivot tables, adjusting subtitles, repeating item labels, and grid lines for readability.

Apply and customize pivot table styles in the design ribbon, with live previews of light to dark presets, and adjust options like row headers, column headers, and banded rows.

Learn to modify pivot table styles in Excel by duplicating styles to create custom options, then format borders, header colors, fonts, and row or column stripes.

Learn to create a custom pivot table style from scratch, tailor every element with your branding colors, save it as a default, and copy the style across workbooks.

Sort pivot table data to organize regions and the sum of profit. Explore sorting options, custom lists, and manual sorts, then refresh the pivot table.

Master filtering pivot table data in Excel by region and item type, using label and value filters, wildcards, and multi-filter options to refine profit insights.

Apply the top 10 filter in pivot tables to show the top five countries by the sum of profit, improving pivot chart readability.

Practice sorting and filtering a pivot table by removing the product field, sorting country in descending order, filtering the top three by gross sales, and showing the luxe range.

Master selecting the right chart type for pivot table data to visualize insights clearly. Explore column, bar, line, donut, pie, and map charts and learn when to use each.

Learn to create clustered column charts from pivot tables in Excel, filter top five countries by units sold, and convert to bar charts with basic formatting and data labels.

Create line charts from pivot table data by duplicating a worksheet, configuring a date field on rows, filtering to the last four years, and applying chart formatting and legend options.

Learn to create and format pie and donut charts from pivot tables, including grouping data into canceled and active orders and applying data labels and colors for clear visualization.

Explore applying chart layouts in Excel to add multiple elements at once, including axes, titles, legend, data labels, and data tables, with quick layouts previewed across chart types.

Create a clustered column pivot chart, remove fields, clear filters, hide legend and field buttons, format title 'gross sales by range and country', set 51% gap, and apply purple fill.