Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find the top N per group with formulas in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=LARGE(IF(A2:A7="East",B2:B7),1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Place group labels in A2:A7 and numeric values in B2:B7.
- 2To get the single largest value for 'East', enter as an array formula: =LARGE(IF(A2:A7="East",B2:B7),1). In Excel 365 this spills automatically; in older Excel press Ctrl+Shift+Enter.
- 3Change the last argument from 1 to 2 for second-largest, 3 for third-largest, and so on.
- 4For multiple groups, use FILTER to isolate each group's rows first: =LARGE(FILTER(B2:B7,A2:A7="East"),1).
- 5To return the full top-N rows (with all columns), use =FILTER(A2:B7,RANK(B2:B7,B2:B7,0)<=2) — adjust 2 to your N.
Tips
- LARGE with IF returns #NUM! if fewer than N values exist in the group — wrap in IFERROR(...,"") to suppress.
- In Google Sheets, FILTER is available natively and avoids the Ctrl+Shift+Enter requirement.
- To get top-N rows across all groups separately, combine FILTER with SORT and SEQUENCE for a fully dynamic output in Excel 365.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I get the top N rows with all their columns, not just the value?
Use =FILTER(A2:B7,(A2:A7="East")*RANK(B2:B7,B2:B7)<=2) — but RANK doesn't handle group isolation natively, so it is cleaner to pre-filter with FILTER then wrap in SORT and INDEX.
What if there are ties at position N?
LARGE returns the Nth largest value regardless of ties. Both tied values would be included if you filter on value >= LARGE(...,N).
Can I get top N per group for multiple groups at once?
In Excel 365, BYROW and MAKEARRAY can automate this. In Google Sheets, QUERY with ORDER BY and LIMIT is often the cleanest approach.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow 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-toHow 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.
How-toHow 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-toHow 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-toHow 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.