Practical Excel 2013 Pivot Tables and Pivot Charts

Explore how pivot tables in Excel 2013 transform large datasets into actionable insights, from orders and products to customer, category, and month level analyses with charts.

Pivot tables quickly break down 2011 movie gross by genre and MPAA rating into averages and counts, while showing how manual Excel formulas and duplicate removal achieve similar results.

Learn how to structure a dataset for pivot tables by arranging numeric and categorical variables in adjacent columns within a rectangular range free of blank rows or columns.

Create your first pivot table from a 4000-record online retail dataset, placing categorical fields in rows, columns, or filters and numeric fields in values.

Explore how Excel tables enable sorting, filtering, and summarizing for data analysis, with automatic expansion as data is added. pivot tables and charts use these tables for dynamic updates.

Learn to base a pivot table on an Excel data range or Excel table, rename the table, and refresh after data expansion for new fields such as total saved.

Navigate the pivot table field list to undock, resize, and reattach the fields pane, and explore layout options, including alphabetical tables in Excel 2013, with per-field sort and filter arrows.

Place fields in rows, columns, or filters to create diverse pivot table layouts, arrange multiple fields, and filter by region, gender, or purchase day for weekend insights.

Explore adding multiple numeric fields to the values area in pivot tables, using the summation sign layout to compare sums and averages by region and gender.

Compare the compact, tabular, and outline pivot table layouts, including the default compact layout, and learn to switch among them in the design ribbon.

Apply currency formatting with no decimals and adjust the summarizing function for numeric fields in a pivot table, using show values as options via right-click or values field settings dialog.

Discover that when you summarize by count, any field works in the values area; use show values as options to express counts as percentages.

Explore how show values as options in pivot tables work, including percentages of grand total, column totals, row totals, and base items.

Filter fields in the rows or columns area of a pivot table using the active field to drive dropdown options, then collapse or expand inner fields to reveal details.

Explore field settings for categorical fields in pivot table rows or columns, customize names and subtotals, and manage manual filters and layout options for regional, purchase time, and item data.

Explore pivot table sort options, including sorting labels in rows and columns and sorting numbers in values, including dragging time of day for custom order and grand totals by region.

Explore sorting pivot tables with native options and a custom list morning, afternoon, evening to achieve natural order and automatic sorting across future pivot tables via Excel options dialog.

Explore advanced filtering for pivot table row and column fields, including label, value, and top‑n filters, with practical examples using a customer orders dataset.

Explore slicers as a graphical alternative to filters. Insert slicers for fields, filter by selecting items, clear with red X, and connect to multiple pivot tables and timeline slicers.

Pivot charts stay in sync with pivot tables and convert data into line charts for total spent by month.

Learn to style pivot tables and pivot charts with numerous colors and themes, using the design and format ribbons, previewing changes, and undoing as needed.

Group date and numeric fields in pivot tables to reveal time trends and categorical patterns. Use monthly and yearly groupings and pivot charts to compare trends and product adoption.

Explore how the pivot cache stores a snapshot of data to speed up pivot table calculations and how sharing this cache updates multiple pivot tables by default.

Convert a pivot table to a static, shareable version by copying the pivot, using paste special to paste values and number formatting, and removing the pivot cache.

Learn to group dates in pivot tables by month and year, build a pivot line chart to show trends, and explore week-by-week grouping using day intervals.

Explore grouping by selection in Excel 2013 pivot tables to organize many categories into natural subsets, create a–h and i–r groups, and filter or move them for clearer reports.

Explore how to create report filter pages from a pivot table, generating a page per day or per credit card type, while preserving the original pivot table.

Learn to replace blanks with zeros in a pivot table by adjusting options in the layout and format tab, and enable show items with no data where needed.

Explore the pivot table options dialog box in Excel, with six tabs covering renaming, formatting, empty or error cells, totals and filters, the display tab, and data refresh settings.

Select the entire pivot table, copy or move it, and clear or delete it using the pivot table tools. Understand slicers and cache effects on copies.

Refresh pivot tables whenever the source data changes to update the pivot cache and display the newest values, including automatic refresh on workbook open for external data.

Explore how pivot tables share a pivot cache by default, its memory impact, and how to avoid sharing using the old pivot table dialog (alt-d-p) to refresh separately.

Apply dynamic conditional formatting in pivot tables using data bars, icons, and percentile rules to highlight trends and top values in the customer order data.

Explore calculated fields in pivot tables to create metrics like original value and spent per item, and learn why ratios based on sums may mislead without a proper dataset column.

Explore calculated items in excel pivot tables, learn to add, modify, or delete new categories from existing fields like weekday and weekend, and follow excel rules to avoid meaningless results.

Discover how to use get pivot data formulas to reference pivot table cells, such as total spend, purchase time, and gender, and how to toggle to familiar cell references.

Learn to use the getpivotdata function to build custom reports from pivot tables, transforming hard-coded values into month and year references, and creating copyable formulas that update with each refresh.

Discover efficiency tips for pivot tables to speed up performance and reduce workbook size, including defer layout update and pivot cache concepts.

Use a pivot table to find unique values in a long list, such as industries. It reveals duplicates caused by trailing spaces and can uncover data errors quickly.

Pivot tables and pivot charts quickly create a histogram of salaries by grouping values into bins and counting observations, updating automatically when regrouping.

Use pivot tables to reveal relationships in the classification problem, showing how the live alone field, mall trips, age, and income relate to have tried the product.

Complete the practical Excel 2013 pivot tables and pivot charts course by mastering creation, manipulation, and reporting. Advance with a follow-up on external data, Power Pivot, and self-service BI.