QUERY Function in Excel: What to Use Instead (2026)

If you're porting a model from Sheets to Excel, or a colleague handed you a spec written in QUERY syntax, here's the mapping you need.

What QUERY Does in Sheets (and Why Excel Has No Direct Equivalent)

QUERY bundles filtering, aggregation, sorting, and column selection into a single formula using SQL-like syntax:

=QUERY('Revenue'!A:F,
  "SELECT A, B, SUM(F)
   WHERE C = 'Enterprise' AND D >= 2024
   GROUP BY A, B
   ORDER BY SUM(F) DESC
   LABEL SUM(F) 'ARR'", 1)

That's filtering by segment and year, summing ARR by account and business line, sorting largest-first, and relabeling the output column - all in one cell. Excel has never shipped a function that wraps all of this. Microsoft's approach since 2020 has been dynamic arrays: each operation gets its own function, and you chain them using spill references.

As of July 2026, FILTER, UNIQUE, SORT, SORTBY, CHOOSECOLS, TAKE, and DROP are all available in Excel 365 and Excel 2021. Excel 2019 and earlier get none of them - fall back to INDEX/SEQUENCE for most of this work.

QUERY Clause to Excel Function Mapping

FILTER: Excel's Answer to the QUERY WHERE Clause

FILTER is the direct replacement for QUERY's WHERE clause. According to Microsoft's official FILTER function documentation: "The FILTER function allows you to filter a range of data based on criteria you define."

A QUERY like this:

=QUERY('P&L'!A:E, "SELECT A, B, E WHERE C = 'SaaS' AND E > 500000")

Becomes:

=FILTER('P&L'!A:E, ('P&L'!C:C = "SaaS") * ('P&L'!E:E > 500000))

The * operator is AND; + is OR. That's the detail that trips analysts up most often when first translating QUERY logic.

For a multi-tab model where your Returns Analysis tab needs FCFF for a specific scenario from your Cash Flow tab:

=FILTER(
  CHOOSECOLS('CashFlow'!A:H, 1, 2, 7),        // Period, Entity, FCFF columns only
  ('CashFlow'!D:D = Returns!$B$3) *           // match scenario from assumption dropdown
  ('CashFlow'!C:C >= Assumptions!$C$4)        // year >= projection start
)

On a 50,000-row transaction dataset, FILTER resolves in under 2 seconds. One limitation versus QUERY: FILTER returns rows - it doesn't aggregate them. You can't sum by group inside a FILTER. For that, you need the pattern below.

UNIQUE + SUMIFS: Replacing QUERY GROUP BY in Excel

The GROUP BY + SUM pattern is the most common QUERY use case in FP&A models. A typical Sheets formula:

=QUERY('Transactions'!A:D,
  "SELECT B, SUM(D) WHERE A >= 2024 GROUP BY B ORDER BY SUM(D) DESC")

In Excel, this splits into two formulas. First, UNIQUE to get distinct dimension values:

// Summary!A2 - distinct business units, filtered to 2024+
=UNIQUE(
  FILTER('Transactions'!B:B, 'Transactions'!A:A >= DATE(2024,1,1))
)

Then SUMIFS to aggregate against that spill range:

// Summary!B2 - revenue by business unit
=SUMIFS(
  'Transactions'!D:D,
  'Transactions'!B:B, A2#,
  'Transactions'!A:A, ">=" & DATE(2024,1,1)
)

The A2# is the spill range operator - it expands SUMIFS to run against every row UNIQUE returned. Wrap the output in SORT(..., 2, -1) to order by revenue descending.

For a contribution margin analysis by SKU where sales and COGS live on separate tabs:

// Distinct SKUs with positive margin (Summary!A2)
=UNIQUE(FILTER('Sales'!C:C, 'Margins'!D:D > 0))

// Revenue by SKU (Summary!B2)
=SUMIFS(
  'Sales'!E:E,
  'Sales'!C:C, A2#,
  'Sales'!A:A, ">=" & Assumptions!$B$3
)

// COGS by SKU (Summary!C2)
=SUMIFS(
  'COGS'!D:D,
  'COGS'!C:C, A2#,
  'COGS'!A:A, ">=" & Assumptions!$B$3
)

This pattern covers roughly 80% of what FP&A analysts actually use GROUP BY for.

ORDER BY and LIMIT: SORT, SORTBY, and TAKE

SORT handles single-column ordering. SORTBY handles multi-column:

// Sort by revenue descending, then by entity name ascending
=SORTBY(data_range, revenue_col, -1, entity_col, 1)

LIMIT maps to TAKE. Top 10 rows by revenue:

=TAKE(SORT(data_range, 2, -1), 10)

TAKE was added to Excel 365 in late 2023. On Excel 2021, extract top-n with INDEX(range, SEQUENCE(n)).

A Full QUERY Translation: Board Pack EBITDA Summary

This QUERY builds an entity-by-period EBITDA bridge for a quarterly board pack:

=QUERY('P&L'!A:G,
  "SELECT A, B, SUM(G)
   WHERE C = 'Actuals' AND D >= 2025
   GROUP BY A, B
   ORDER BY A, D", 1)

Here's the Excel equivalent, structured across three formulas in a Summary tab:

// Summary!A2 - unique entity/period combinations
=UNIQUE(
  FILTER(
    CHOOSECOLS('P&L'!A:G, 1, 2),
    ('P&L'!C:C = "Actuals") * ('P&L'!D:D >= Assumptions!$B$3)
  )
)

// Summary!C2 - EBITDA by entity/period
=SUMIFS(
  'P&L'!G:G,
  'P&L'!A:A, Summary!A2#,
  'P&L'!B:B, Summary!B2#,
  'P&L'!C:C, "Actuals",
  'P&L'!D:D, ">=" & Assumptions!$B$3
)

// Summary!A8 - sorted output (entity asc, period asc)
=SORTBY(HSTACK(A2#, B2#, C2#), A2#, 1, B2#, 1)

More formulas than the Sheets version, but every reference is traceable. No SQL string hiding the filter logic from whoever needs to tie out your numbers the night before a board meeting.

When Power Query Beats This Approach

If your dataset exceeds 100,000 rows, or you're joining external sources - say, reconciling GL transactions against Stripe payouts across reporting periods - dynamic array formulas slow down, because Excel recalculates them on every workbook change.

Power Query handles large-dataset aggregation better: it runs transformations outside the grid and caches the output. The trade-off is refresh behavior. According to Microsoft's Power Query documentation, refresh runs on demand or on a scheduled interval - it doesn't respond automatically to source data changes. For a live model that needs to reflect yesterday's close, that's a real constraint.

For datasets that already live in Excel and sit under 100,000 rows, FILTER + UNIQUE + SUMIFS builds faster and audits cleaner than a Power Query transformation.

If you're maintaining the same model in both Sheets and Excel - which happens more often than anyone wants - ModelMonkey handles the Sheets side natively, letting you query and reshape live spreadsheet data through natural language rather than maintaining two sets of formulas by hand. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.