Spreadsheets

Make a QUERY respond to a dropdown: category and month selectors

Make a Google Sheets QUERY react to dropdown cells for category and month: join cell values into the query string, handle quote rules and an All option.

Editorial illustration for Make a QUERY respond to a dropdown: category and month selectors

To make a QUERY respond to a dropdown, build the query string with ampersands so the selected cell value is joined into the where clause. Text values need single quotes around them, numbers do not, dates need the word date and a year-month-day format, and an All choice simply leaves the filter out.

How do I set up the dropdowns?

  • Data in A to E: Date, Merchant, Amount, Category, Month. In E2 use =ARRAYFORMULA(TEXT(A2:A500,"yyyy-mm")) with the header Month in E1.
  • G1: a dropdown created through Data, Data validation, with the choices All, Food, Travel, Bills.
  • G2: a dropdown with All and your month labels such as 2025-04 and 2025-05. Format G2 as plain text, otherwise the sheet may turn 2025-04 into a date.

What is the formula?

  • =QUERY(A1:E500, "select A, B, C, D where C is not null"&IF(G1="All",""," and D = '"&G1&"'")&IF(G2="All",""," and E = '"&G2&"'")&" order by A", 1)

Read it as a sentence being assembled. The base is select A, B, C, D where C is not null. If G1 says Food, the first IF adds and D = 'Food'. If G2 says 2025-04, the second adds and E = '2025-04'. If either says All, nothing is added for that filter. Note the space at the start of each added piece, otherwise the words run together.

What are the quote rules?

Value typeHow it appears in the queryExample
TextSingle quotesD = 'Food'
NumberNo quotesC > 500
DateThe word date and yyyy-mm-dd in single quotesA >= date '2025-04-01'
Cell number inside the stringJoin with &where C > "&G3

The single quotes belong to the query language. The double quotes belong to the formula text. Mixing them up is the commonest reason these formulas fail.

How do I filter by an amount from a cell?

Say G3 holds 500. Use "where C > "&G3. Joined in, the query reads where C > 500. A text cell containing 500 works too, since the number goes straight into the query. If your spreadsheet locale uses a decimal comma, a value like 1,5 will break the query; replace the comma with SUBSTITUTE(G3,",","."), or enter amounts with a full stop.

How do I filter by a date from a cell?

If G4 holds a real date, join it with TEXT so the query sees the right format: " and A >= date '"&TEXT(G4,"yyyy-mm-dd")&"'". Without TEXT the cell's value would become a serial number and the query would fail.

Common problems

  • A category containing an apostrophe closes the text early and produces a parse error.
  • A trailing space in a dropdown choice returns no rows. Retype the choice cleanly.
  • If the Month dropdown shows dates, not text, the E column and the dropdown values are comparing different types.
  • A query that returns nothing may show an error. Wrap it in IFERROR with a message such as No rows for this choice.

A simple extension

Add a total under the dropdowns: a second query selecting sum(C) with the same joined conditions. If Food in April has 4,200 and 3,100 and 2,700 in it, the total is 4,200 plus 3,100 plus 2,700, which is 10,000. Keeping the same conditions in both formulas means the total always matches the list.

How do I keep the dropdown lists up to date?

Point the dropdown's data validation at a range, not a typed list, so new categories appear automatically. A neat trick is a helper range using the UNIQUE function on the category column, and the dropdown reads that range. Add All at the top by stacking it with curly brackets, for example {"All"; UNIQUE(D2:D500)}.

For a dashboard-style view without writing these, SpendixAI offers filters and a report builder over imported transactions, which can be a gentler path once the sheet becomes complicated.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports