Office Fundamentals - PivotTables in Microsoft Excel

Explore pivot tables in Excel to analyze a large orders list, with fields like product, category, quantity, cost, revenue, dates, and status, and download the example data file.

Structure source data with a single header row, contiguous entries, and clear headings; keep only categorical and numerical fields, avoid empty cells, and keep formatting minimal.

Create and insert pivot tables in Excel by selecting the data range and placing the pivot on a new worksheet, using all data or a selected subset with filters.

Explore how to use the pivot table field list to organize data by rows, columns, filters, and values, including numeric fields like revenue and cost across locations and products.

Explore pivot table ribbons in Excel: PivotTable Analyze tab offers actions like inserting slices, timelines, refreshing data, and changing data sources, while the Design tab controls layout and styles.

Learn to use pivot table actions to clear filters, select entire pivot tables, and move a pivot table to a new worksheet, enabling independent analyses and side-by-side comparisons.

Understand static versus dynamic data in pivot tables and learn how to refresh data to reflect changes, or change the data source to include new rows or columns.

Learn to handle growing data in pivot tables by using either change data source or convert to a named table, then refresh to update new rows and columns.

Discover how the pivot table cache works, how refreshing data and changing the data source affect the cache, and how deleting source data can dramatically reduce file size.

Pro tip: generate a source data snippet from a filtered pivot table by double-clicking, extracting detailed orders for a person, category, and locations, then delete the sheet when done.

Format pivot tables to control layouts, customize headings, apply number formats, and add conditional formatting as visual aids, preparing you for more advanced analysis.

Format pivot table values as currency to improve readability, using the number format options to apply currency, decimals, and thousand separators. Maintain formatting when rearranging fields.

Explore the design tab to customize pivot tables using compact, outline, and tabular layouts, adjust subtotals and subtitles, and apply pivot table styles.

Switch the pivot table to tabular form, remove subtotals and blank rows, and repeat labels to produce a clean, exportable data set for use in other applications.

Create and apply a custom pivot table style in Excel, customizing the header, first column, and stripe formatting with colors and fonts.

Edit pivot table header labels by selecting the label, typing a new name, and pressing enter; use a trailing space to avoid conflicts with exact field names.

Learn to apply conditional formatting in Excel to highlight revenue values over a million, distinguishing total revenue by location and enabling quick insights from PivotTables.

Learn to highlight top and bottom locations by applying conditional formatting in pivot tables, using top/bottom rules, percent, and above/below average to reveal strongest and weakest revenue drivers.

Apply data bars in conditional formatting to visualize location revenue in pivot tables, customize max values, hide numbers with formats, and display multiple revenue instances for clear distribution.

Visualize revenue by location in a pivot table using conditional formatting with icon sets and color scales, hiding raw values and highlighting top and bottom performance.

Learn to apply conditional formatting to pivot tables, including single-cell and field-wide rules for sums of revenue by location or responsible, and manage rules as fields move.

Learn to prevent pivot tables from resizing on update by disabling all columns on update in pivot table options, and use formatting tricks like data bars to display values.

Master sorting, filtering, and grouping in pivot tables to analyze data more effectively in Microsoft Excel, using dropdown menus and right-click options for ascending, descending, and value filters.

Learn to sort data in Excel pivot tables using basic and advanced options, including manual reordering and field-based ascending or descending sorts, with outline form for clarity.

Create and apply custom lists in Excel to sort a pivot table's locations by a predefined order, enriching auto sort and autocomplete with a tailored city sequence.

Learn to filter pivot table data with manual selections, search, and label filters, and apply text and numeric conditions such as contains, begins with, equals, and between.

Discover a faster manual selection filter technique for pivot tables in Excel, using keep only selected items or hide selected items to quickly refine large data sets.

Use wildcard filtering in pivot tables to filter labor internal codes, apply begins with, question mark, and asterisk, then refresh the pivot and refine results.

Master value filters in pivot tables to filter fields like cost and revenue, using equals, greater than, less than, between, and top or bottom, including percent and top x options.

Discover how to combine label and value filters in pivot tables by enabling 'allow multiple filters' in pivot table options (totals and filters) and applying both criteria.

Learn to create and name custom groups in a pivot table by selecting items, grouping 1–4 and 5–9, and viewing subtotals for cost and revenue.

Discover how pivot tables automatically group date values into years, quarters, and months, customize or remove these groups, and collapse or expand date levels for clearer analysis.

Master visual filtering with slicers in a pivot table to filter by location, compare cities, and see how cost and revenue change across selected areas.

Master visual date filtering with timelines in Excel by inserting a timeline, selecting a date field for pivot tables, filtering by year to day, and managing report connections.

Learn how to use report filter pages in pivot tables to generate separate worksheets for each location, saving manual work in Excel.

Explore how pivot tables analyze values, display revenue totals, and apply options for averages, max values, and flexible totals to gain deeper insights from your data.

Learn how to use different aggregation methods in a pivot table, switching between sum, count, average, max, and more for cost and revenue by product.

Learn how to use show values as options to display pivot table values as percent of column totals, row totals, raw totals, and grand totals for clear revenue analysis.

Learn to display pivot table values as a percent of a parent total, using year breakdowns under each product to show yearly shares.

Learn to use pivot tables to display year-over-year revenue differences, show values as difference from previous year, and as percent differences.

Explore creating running totals in pivot tables in Microsoft Excel using absolute revenue figures and percent running totals over four years, with a base field and automatic first-year base.

Use pivot tables to rank values by revenue across years, ranking from largest to smallest to identify best and worst years. Place rankings beside values for clarity.

Enhance large pivot tables by inserting blank rows for clarity and applying conditional formatting, color scales, and ranking rules to reveal year-over-year changes and top performers.

Explore how to show values as index in a pivot table to compare relative revenue by game category and advertising channel, including constructing index values and interpreting percent changes.

Explore using calculated fields in pivot tables to create new metrics like profit from turnover and costs, ensuring aggregated results stay correct and avoiding errors from separate calculations.

Create calculated fields in a pivot table to compute profit (revenue minus cost) and return (revenue divided by cost). Name the fields, enter formulas, and format as percentage.

Discover how to use calculated fields in pivot tables to compute average profit by dividing profit by a count metric, and use a helper column to enable accurate counts.

Explore pivot charts and dashboards in Excel, linking charts to pivot data to visualize fields, filters, and sorting. Build a dashboard with slices and timelines to present analysis.

Create and synchronize pivot charts with pivot tables to visualize revenue by product using clustered column charts, while applying sorting and filters and keeping visuals simple for clear insights.

Explore creating pivot charts in Excel, visualize top products with pivot tables, add category pie charts, and organize multiple pivot charts on separate worksheets for clarity.

Enhance pivot charts with the design tab by adjusting colors, layouts, and chart styles. Add data labels and format numbers as thousands or millions with custom formats.

Learn to build multi-dimensional pivot charts by combining products and locations in a two-dimensional pivot table, then switch to stacked area charts to reveal revenue distribution across locations.

Filter pivot charts and tables with slicers and timelines to explore revenue by product and responsible persons, adjust date ranges, and reset filters for dashboards.

Create a dashboard by moving pivot charts to one worksheet, and connect a slicer and timeline via report connections to all pivot tables so filters update every chart.

Improve dashboard visuals in Excel by applying a blue monochromatic color scheme to pivot charts, adding clear chart titles, removing legend, aligning elements, and turning off gridlines.

Learn how to print a pivot dashboard by selecting the print area, adjusting margins and orientation, and using fit to one page to produce a clean, printable summary.