Pivot-style summaries (formula approach)
Group-by, distinct count, running totals, crosstabs — every pivot-style summary built with formulas, without ever touching the pivot dialog.
11 pages · 1 reference, 10 how-to
Reference
How-to guides
How to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
Excel ≠ SheetsHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.
How to group data by month using formulas instead of a pivot
Group and sum transaction data by month using SUMIFS with EOMONTH or TEXT in Excel and Google Sheets — no pivot table needed.
How to group data by week using formulas instead of a pivot
Sum transaction data by week number or ISO week using SUMIFS and WEEKNUM in Excel and Google Sheets — a formula-only alternative to pivot date grouping.
How to find the top N per group with formulas
Rank and filter the top N rows within each category group using LARGE, FILTER, and SUMPRODUCT in Excel and Google Sheets — no pivot table needed.
How to count distinct values without a pivot table
Count unique values in a column using SUMPRODUCT(1/COUNTIF) in Excel or COUNTUNIQUE in Google Sheets — no pivot table required.
Excel ≠ SheetsHow to build a cross-tabulation with formulas
Replicate a pivot table cross-tab layout using SUMIFS in a row-column grid in Excel and Google Sheets — fully dynamic with no pivot refresh needed.