JMWINCDevelopers

Tutorials & Guides

Build a PivotTable in four clicks

A PivotTable turns a thousand rows of transactions into a clean summary — totals by location, by month, by category — without a single formula. It takes about four clicks.

The four clicks

  1. Click any cell inside your data (don't select the whole range — Excel finds it).
  2. Insert → PivotTable → OK.
  3. In the field list, drag one field to Rows (say, Location) and another to Values (say, Amount).
  4. If it summed when you wanted a count (or vice versa), right-click a value → Summarize Values By and pick Sum, Count, or Average.

You now have a live summary. Drag a second field into Rows to break it down further (Location, then Month), or into Columns to pivot it sideways.

Group dates into months

Raw dates make a messy pivot with one row per day. Fix it in one move: right-click any date in the pivot → Group → choose Months (and Years if the data spans more than one). Daily rows collapse into clean monthly buckets.

Keep it current

A PivotTable does not update when the source data changes. After you add or edit rows, click inside the pivot and press Alt + F5 (Refresh). If your source grows often, turn it into a Table first (Ctrl + T) and point the pivot at the Table — then the range expands automatically and a refresh picks up new rows.

Where this earns its keep

Any "total by location and period" question — revenue by site by month, expenses by category by quarter, counts of exceptions by cause — is a PivotTable, not a stack of SUMIFS. Build it once, refresh it monthly.

When the "source data" is deposits across multiple locations that you're re-keying every month, that's the work IMS Made Simple and TrueBalance take off your plate. For conditional totals without a pivot, see SUMIFS and COUNTIFS.