PIVOTs

Understand how DAX SUMX and AVERAGEX work in Power Pivot with simple, practical examples. This tutorial explains iterator functions, row-by-row calculations, calculated totals, weighted averages, and when to use SUMX or AVERAGEX instead of regular SUM or AVERAGE. Ideal for Excel users, analysts, finance teams, and Power Pivot learners who want to build more accurate and flexible DAX measures.

Understand how the DAX RANKX function works in Power Pivot with practical ranking examples. This tutorial explains how to rank sales, products, employees, customers, or categories, handle ties, use ascending or descending order, and build dynamic ranking formulas based on filter context. Ideal for Excel users, analysts, finance teams, and Power Pivot learners who want to create accurate ranking calculations in DAX.

Understand how the DAX ALL and ALLEXCEPT functions work in Power Pivot with simple examples. This tutorial explains how to remove filters, keep specific filters, calculate grand totals, create percentage-of-total formulas, and control filter context in Excel Power Pivot. Ideal for Excel users, analysts, finance teams, and Power Pivot learners who want to build more flexible and accurate DAX calculations.

Understand how the DAX EARLIER function works in Power Pivot with simple row-by-row calculation examples. This tutorial explains row context, calculated columns, nested calculations, ranking logic, and common EARLIER use cases in Excel Power Pivot. Ideal for Excel users, analysts, finance teams, and Power Pivot learners who want to build stronger DAX formulas and understand how calculations work across rows.

A KPI in Power Pivot is more than a number — it is a comparison with a visual verdict. You define a base measure (Actual Revenue), a target (Budget Revenue), and two threshold values that divide performance into bad (red), neutral (yellow), and good (green). Power Pivot calculates the ratio automatically and displays a traffic light, arrow, or star icon in the PivotTable alongside the value and the goal. No conditional formatting setup is needed — the logic lives in the data model and updates on every refresh. This guide covers the full five-step creation workflow, the three KPI components, how to handle reversed thresholds for metrics where lower is better (defect rate, churn), Year-over-Year growth KPIs with a zero baseline, Sales Attainment with star rating icons, and the correct way to display Value, Goal, and Status fields together in a polished PivotTable layout.

“Microsoft Corp.” in one table. “Microsoft Corporation” in the other. A standard merge returns zero matches. Fuzzy matching solves this by joining rows based on similarity rather than exact equality — you set a threshold between 0 and 1, and any pair scoring above it is treated as a match. The Transformation Table feature takes this further, letting you pre-map known abbreviations (Ltd → Limited, Corp. → Corporation) so they match exactly before the similarity calculation even runs. This guide covers the five-step fuzzy merge workflow, the similarity threshold selector, when to use the Transformation Table, a self-merge technique for finding near-duplicate names within a single table, product name normalisation, performance strategies for large datasets, and a frank assessment of when cleaning the data first with Trim, Text.Lower, and exact replacements is a faster and more reliable alternative to fuzzy matching.

A regular chart reads a fixed range and stays fixed. A PivotChart is linked directly to a PivotTable — every filter, slicer click, and field expansion instantly redraws the chart with no manual editing required. Change the Region slicer from All to North and East, and the bars update in real time. Expand a Year node and the quarterly breakdown appears automatically. This guide explains exactly how PivotCharts work and how they differ from regular charts, with a clear decision rule for choosing between them. Six practical examples cover: a filtered column chart that updates with PivotTable filters, a slicer-connected chart shared across multiple PivotTables, a drill-down hierarchy chart from year to quarter to month, a dynamic trend line driven by a Timeline slicer, a Combo chart pairing revenue bars with a margin percentage line on a secondary axis, and a polished dashboard card with field buttons hidden and formatting locked. It also explains why Scatter and Bubble charts are not supported in PivotCharts and how to work around this using GETPIVOTDATA.

Stop manually searching through PivotTables for individual numbers. This practical GETPIVOTDATA tutorial shows you how to extract specific values from a PivotTable using formulas that reference fields, items, and filters. Learn the GETPIVOTDATA syntax, how to build dynamic references, retrieve values based on multiple criteria, and troubleshoot common formula errors. You’ll also see how GETPIVOTDATA can make financial reports, KPI dashboards, management summaries, and recurring Excel analysis more reliable and easier to maintain. Ideal for analysts, accountants, finance professionals, auditors, and advanced Excel users who work with PivotTables.

A PivotTable showing 200 rows of revenue data tells you everything and nothing at once. Conditional formatting changes that — it applies color scales, icon sets, data bars, and threshold rules automatically, so outliers and top performers become visible at a glance. The key is choosing the right scope. Using “Selected cells only” creates a static rule that breaks the moment you filter or refresh the table. Using “All cells showing [field] values” creates a dynamic rule that follows the data everywhere it moves. This guide covers all three PivotTable scope options, with six practical examples: a green-yellow-red heat map across all revenue cells, a traffic light icon set with exact percentage thresholds, a Top 10 auto-highlight that recalculates on filter, data bars for in-cell visual comparison, a positive/negative variance rule pair, and a formula-based row highlight with a warning about its limitations.

Your PivotTable shows Q1, Q2, Q3, and Q4. You want a row for H1 (Q1+Q2) and another for H2 (Q3+Q4) — without adding columns to the source data. Calculated Items make this possible. Each one inserts a virtual row or column. Its value comes from a formula referencing other items in the same field. The result appears in the PivotTable as if it were real data, but it lives entirely inside the PivotTable itself.

This guide explains what calculated items are and how they differ from calculated fields. It also covers creation, editing, and deletion. You will learn when to use them, what their key limitations are, and six practical examples. Examples include grouping quarters into halves, computing market share, and creating a budget‑versus‑actual variance row.

Master Pivot Tables in Excel with this complete beginner-to-advanced tutorial. Learn how to summarize, analyze, and transform raw data into meaningful insights using simple, practical steps. Whether you’re starting out or aiming to become a data analyst, this guide helps you unlock the full power of Pivot Tables for smarter decision-making.