Filter and Sort Data With QUERY

Excel & Google Sheets

How it works

QUERY is Google Sheets only: it runs a miniature SQL statement over a range, and this one keeps columns A and D, drops every row where D is 1,000 or less, and sorts what's left by D descending. The crucial quirk is that you refer to columns by their spreadsheet letters (A, D), never by their header names. The final 1 tells QUERY the first row is headers, so the headers ride along into the output instead of being treated as data. Excel has no QUERY — the 365 equivalent chains dynamic arrays: FILTER keeps the rows where the amount beats 1,000, CHOOSE({1,2},…) stitches just the customer and amount columns side by side, and SORT(…,2,-1) orders by the second column, descending.

A1:D20

The data to query, including the header row.

"select A, D"

Which columns to return — spreadsheet letters, not header names.

"where D > 1000"

Keeps only rows whose amount is strictly greater than 1,000.

"order by D desc"

Sorts the results by amount, biggest first.

1

One header row — QUERY passes it through instead of treating it as data.

When to use it

Use it for live report views that update as the source changes: top deals over a threshold, unpaid invoices sorted by amount, open orders for one customer — anywhere you'd otherwise filter and sort by hand every week.

Common mistakes

• ✕Using header names instead of column letters: "select Amount".

  ✓QUERY throws a parse error — it only understands spreadsheet letters. Write "select D", even though the column is labeled Amount.

• ✕A column that mixes numbers and text.

  ✓QUERY decides each column's type by majority vote and silently blanks the minority — a 950 stored as text just disappears. Keep each column one type.

• ✕Typing QUERY into Excel.

  ✓Excel shows #NAME? — QUERY doesn't exist there. Use the Excel 365 version: =SORT(FILTER(CHOOSE({1,2},A2:A20,D2:D20),D2:D20>1000),2,-1).