Excel Tutorials
Build the same PivotTable in Excel and Google Sheets, add a linked PivotChart, and avoid the single most common reason a pivot table looks wrong: forgetting to refresh it. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
A PivotTable takes a large, detailed dataset and summarizes it into a compact table you can rearrange just by dragging fields around, group by category, sum a column, count entries, all without writing a single formula or touching the original data. Both Excel and Google Sheets have their own version, built through slightly different menus but working on the same underlying idea.
The tradeoff for that power is that a PivotTable isn't automatically live the way a formula is, it has to be manually refreshed after the source data changes, which is where most of the confusion around PivotTables actually comes from.
Step-by-step, matching the video above.
Click anywhere inside your dataset, or select the full range including headers.
Click Insert on the ribbon, then PivotTable.
Pick New Worksheet to place it on a fresh tab, or Existing Worksheet to pin it to a specific location, then click OK.
In the PivotTable Fields pane, drag category-type fields into Rows, and numeric fields you want totaled into Values.
Drag another field into Columns to break the summary out further sideways, or into Filters to add a dropdown that narrows the whole table.
Click the dropdown on any field in the Values area and choose Value Field Settings to switch between Sum, Count, Average and more.
Right-click inside the PivotTable and choose Refresh, or use Refresh All on the Data tab, PivotTables never update automatically on their own.
| A: Category | B: Cost |
|---|---|
| Venue | 2,500 |
| Catering | 1,800 |
| Venue | 600 |
| Decor | 450 |
| Catering | 300 |
Drag Category into Rows and Cost into Values, and the PivotTable collapses these five raw rows into three: Venue (3,100), Catering (2,100), Decor (450), summed automatically. Add another field into Columns, like Month, and the same data breaks out sideways by month too, no formulas written anywhere.
Same underlying idea, a different menu, and one extra panel worth knowing.
Select your range, then go to Insert > Pivot table.
Pick New sheet or Existing sheet, then click Create.
On the right side, click Add next to Rows, Columns, Values or Filters, and choose the field you want in each.
Click the Summarize by dropdown next to a field in Values to switch between SUM, COUNTA, AVERAGE and other options.
Google Sheets pivot tables update automatically as the source data changes, no manual refresh step required, the one meaningful difference from Excel's behavior.
A chart that stays linked to your PivotTable, filtering and updating together.
Click any cell inside the PivotTable, go to Insert > PivotChart, and choose a chart type. The chart reflects whatever the PivotTable currently shows.
With your pivot table built, go to Insert > Chart, Sheets automatically detects the pivot table as the data source.
A PivotChart stays connected to the PivotTable's filters, changing a filter or rearranging fields updates the chart automatically along with the table.
The single most common source of "my PivotTable is wrong" confusion.
Changing a value already inside your original data range requires a manual Refresh in Excel before the PivotTable reflects it.
If your source was a plain cell range, new rows added past the bottom aren't included until you manually update the PivotTable's source range via PivotTable Analyze > Change Data Source.
If your original data is an Excel Table (Ctrl+T) instead of a plain range, the PivotTable's source automatically expands to include new rows, refresh still required, but no range to manually update.
Raw daily dates or a wide spread of numbers rarely make a useful PivotTable row on their own, grouping collapses them into meaningful buckets.
Right-click any date already in the Rows or Columns area and choose Group, then select Months, Quarters or Years. A field of 365 individual daily dates collapses into just 12 month rows.
Right-click a numeric field in Rows and choose Group, then set a starting value, ending value, and interval, like grouping every expense into 0-100, 100-200, 200-300 buckets automatically.
Select several row labels while holding Ctrl, right-click, and choose Group to combine them under one custom label, useful for merging similar categories that were entered inconsistently.
Google Sheets groups dates automatically once a date field is added to Rows, offering Year, Quarter, Month and Day breakdowns directly in the field's dropdown, no separate Group command needed.
| Month | Sum of Cost |
|---|---|
| June | 1,850 |
| July | 2,400 |
| August | 1,100 |
The same event-planning data from the earlier example, but with individual Event Date values grouped into months instead of listed one by one. What would have been dozens of daily rows becomes three readable totals, all from right-clicking one date field and choosing Group.
A Values field defaults to showing its raw Sum or Count, but it can display something more useful with one dropdown change.
Right-click a Values field, choose Show Values As > % of Grand Total, and every number becomes a percentage of the overall total instead of a raw figure, instantly showing which category dominates the budget.
Useful when comparing categories within each column separately, like seeing what percentage of each month's spending went to Venue, rather than a percentage of everything combined.
Shows each row as a cumulative total building on the one before it, useful for tracking spending building up toward a budget cap over time.
Compares each value against a specific reference row or column you choose, like comparing every month's spending against a baseline month.
A more visual alternative to the Filters area dropdown, Excel-specific.
Click inside the PivotTable, go to PivotTable Analyze > Insert Slicer, and choose a field. A floating panel of clickable buttons appears, one per unique value, filtering the table with a single click instead of a dropdown menu.
For a date field specifically, PivotTable Analyze > Insert Timeline adds a draggable date-range slider, letting you filter to a custom date window visually.
Right-click a Slicer, choose Report Connections, and check any other PivotTables built from the same source, one Slicer can then filter several PivotTables at once.
When none of the built-in summary options do what you actually need, a Calculated Field adds your own formula directly into the PivotTable.
Click inside the PivotTable, go to PivotTable Analyze > Fields, Items & Sets > Calculated Field, name it, and write a formula referencing other fields, like Cost divided by a Budget field to calculate a percentage used.
A Calculated Field appears in the field list just like any other and can be dragged into Values, its result recalculates automatically as the PivotTable updates.
Calculated Fields operate on the summarized totals, not the original row-by-row data, which can produce unexpected results with anything more complex than basic arithmetic between two summed fields.
In Excel specifically, a PivotTable shows stale numbers until you right-click it and choose Refresh, it never updates on its own.
New rows added past a plain range's original boundary won't be included until the data source is manually updated. An Excel Table avoids this entirely.
Dragging a unique ID or name field into Values instead of Rows produces a giant, unsummarized mess. Category-type fields belong in Rows or Columns, numbers you want totaled belong in Values.
"Catering" and "catering " (with a trailing space) will show up as two separate rows in the PivotTable instead of combining into one. Clean up the source data first.
Grouping by Month when you actually needed Quarter (or vice versa) means regrouping from scratch. Right-click the group and choose Ungroup, then group again with the correct interval.
Calculated Fields operate on already-summarized totals, not the original detailed rows, a formula that works fine on raw data can produce a misleading result once it's calculated on aggregated numbers instead.
A summary shouldn't need rebuilding and refreshing every time.
Free 14-day trial. No credit card required.
A PivotTable summarizes a large dataset into a compact, interactive table, letting you group, count, sum, or average your data by dragging fields around, without changing the original data at all.
Select your data, go to Insert > PivotTable, choose where to place it, click OK, then drag fields into the Rows, Columns, Values and Filters areas in the PivotTable Fields pane.
Select your data, go to Insert > Pivot table, choose a destination, then use the Pivot table editor panel on the right to add Rows, Columns, Values and Filters.
PivotTables don't update automatically. Right-click anywhere inside it and choose Refresh, or use Refresh All on the Data tab, after editing the source data.
These are different ways to summarize the values in a field. Sum adds them together, Count counts how many entries there are, and Average finds the mean, changeable anytime through Value Field Settings.
Yes, select any cell inside the PivotTable and go to Insert > PivotChart in Excel, or Insert > Chart in Google Sheets once your pivot table is built. The chart stays linked to the PivotTable's data.
Drag a field into the Filters area of the PivotTable Fields pane, a dropdown appears above the table letting you narrow results to specific values.
Yes, you can build as many PivotTables as you want from the same source range, each one showing a different summary or breakdown.
Yes, if your source is a proper Excel Table, the PivotTable's range automatically expands to include new rows whenever you add them, no manual range updating needed.
Yes, select the PivotTable, copy it, then Paste Special > Values Only over the same or a new location to convert it into a static table with no live formulas.
Right-click any date in the Rows or Columns area, choose Group, and select Months, Quarters or Years to collapse individual dates into that time period automatically.
Right-click a field in the Values area and choose Show Values As, then pick % of Grand Total, % of Column Total, or another percentage-based option.
A Slicer is a floating panel of clickable buttons, one per unique value in a field, that filters a PivotTable visually instead of using a dropdown menu. Insert one from PivotTable Analyze > Insert Slicer.
Yes, using a Calculated Field, found under PivotTable Analyze > Fields, Items & Sets > Calculated Field, which lets you write a formula referencing other fields in the PivotTable.
Every business starts with a spreadsheet. Updoot is where you scale past it, no pivot table to build or refresh.
Start Your Free Trial →