Home / Tools / Google Sheets Formula Builder

Free tool · 2026

Google Sheets Formula Builder

Pick a function, fill in your ranges and conditions, and copy a formula that works the first time, with a plain-English note on what it does. Covers QUERY, VLOOKUP, XLOOKUP, SUMIFS, COUNTIFS, AVERAGEIFS, FILTER, UNIQUE, SORT, IMPORTRANGE and ARRAYFORMULA.

🔒 Runs entirely in your browser. No AI, nothing is uploaded

Data comes from

Data range (with headers)

Header rows

Columns to show

Use sheet column letters (A, B, C) everywhere. When the data comes from another spreadsheet, the builder converts them to Col1, Col2… for you.

Only rows where

Ignore capitalization

Group and total

Group by column(s)

Calculate

Of column

Header for the total

Sort and limit

Sort by

A column letter, or agg to sort by the total.

Direction

Max rows

Your formula

How to use it

Pick the function from the row of tabs. Not sure which? See which formula to use below.
Fill in your ranges and columns the way they appear in your sheet: Sales!A1:E, column letters like B, values like West, 1,000 or 2026-01-31. Set a condition's type to Cell to compare with a value you keep in a cell, such as H1.
Copy the formula into the top-left cell where the result should go. Formulas that return several rows spill down and to the right, so leave those cells empty.

The builder writes US-style formulas with commas between arguments. If your spreadsheet locale uses a comma as the decimal mark, swap the argument commas for semicolons.

Which formula should you use?

You want to…	Use	Why
Pick columns, filter, group and total, sort, all in one step	QUERY	One formula does what would otherwise take FILTER, SORT, UNIQUE and SUMIFS together
Get the matching rows exactly as they are	FILTER	Simpler than QUERY, case-insensitive, handles mixed data types in a column
Get one number: a total, a count or an average with conditions	SUMIFS, COUNTIFS, AVERAGEIFS	Fast, and the result is a single cell you can reference elsewhere
Bring in one value per row from another table	XLOOKUP (or VLOOKUP)	XLOOKUP can look left and set a not-found value; VLOOKUP only looks right
Make a clean list of distinct values	UNIQUE + SORT	Builds a dropdown source or a summary row label list that updates itself
Use data that lives in another spreadsheet file	IMPORTRANGE	Keeps a live copy in sync; wrap it in QUERY to filter or total it
Run a row formula down the whole column automatically	ARRAYFORMULA	One formula covers new rows as they are added, so nothing needs to be dragged down

Common errors and how to fix them

QUERY

"Unable to parse query string": usually quoting. Text values take single quotes inside the query (where B = 'West'), numbers take none (where D > 1000), and dates need date '2026-01-31'. A stray double quote ends the query string early.
"NO_COLUMN: A": you used letters on data that comes from IMPORTRANGE or a {…} array. Use Col1, Col2… counted from the left edge of the data.
Values missing from the result: QUERY gives each column one type, picked by the majority of its values. Numbers stored as text in a mostly numeric column come back blank. Clean the column, or use FILTER, which has no such rule.
Text match finds nothing: QUERY comparisons are case-sensitive. Tick Ignore capitalization to compare with lower().
"ADD_COL_TO_GROUP_BY_OR_AGG": every selected column must be either in group by or inside sum(), count() and the like. The builder enforces this.

VLOOKUP and XLOOKUP

#N/A for a value you can see: the most common causes are a trailing space (TRIM both sides) or a number stored as text on one side and as a number on the other.
Wrong value returned: VLOOKUP's last argument was left out or set to TRUE, which means approximate match on an unsorted column. Use FALSE for exact.
#REF! or the wrong column after inserting a column: VLOOKUP's column number doesn't move with the table. XLOOKUP's ranges do.
Need a column to the left: VLOOKUP can't. Use XLOOKUP, or INDEX/MATCH.

SUMIFS, COUNTIFS and AVERAGEIFS

#VALUE!: the ranges are different sizes. D2:D100 and B2:B50 can't be paired; give every range the same rows.
Result is 0: an operator criterion must be one quoted string, ">1000", and a cell is joined on with &: ">"&H1. Dates typed as text (">1/1/2026") depend on locale; ">="&DATE(2026, 1, 1) doesn't.
Matches too much: * and ? are wildcards in criteria. To match them literally, put ~ in front, which the builder does for you.
#DIV/0! from AVERAGEIFS: no rows matched. Wrap it in IFERROR(…, 0).

FILTER, UNIQUE and SORT

"No matches are found in FILTER evaluation": nothing met every condition. Wrap it in IFERROR(…, "No matches"), or check an AND that should be an OR.
#REF! "Array result was not expanded": something is in the cells the result needs to spill into. Clear the range below and to the right.
"FILTER has mismatched range sizes": each condition must cover the same rows as the data, so A2:E pairs with C2:C, not C:C.

IMPORTRANGE

#REF! "You need to connect these sheets": click the cell, then Allow access. It's needed once per pair of files.
#REF! "You don't have permission": your account can't open the source file. Ask the owner to share it with you.
Slow or "Result too large": import only the columns and rows you need, or filter in the same step with QUERY(IMPORTRANGE(…), "select Col1, Col3 where Col2 > 100").

ARRAYFORMULA

Only one value comes back: SUM, AND, OR, COUNT and similar functions collapse a column into one value. Use * for AND and + for OR inside IF.
Thousands of zeros or errors at the bottom: open ranges like B2:B run to the last row. Guard with IF(A2:A="", "", …).
#REF! "would overwrite data": a typed value or an old dragged-down formula is in the way. Clear the column below the formula.

When the formula is the hard part

A QUERY with a filter, a group by and a sort is a small program, and every new question means writing another one. If what you actually want is the answer, the Quiriz add-on for Google Sheets lets you ask for it in a cell instead:

=QUIRIZ("total sales by region over $1,000, biggest first", "table")

The table comes back in the sheet. To be clear about what it does and doesn't do:

It answers from your Quiriz datasets: files you upload, a Google Sheet or Drive folder you point a dataset at (refreshed on a schedule), or connected sources such as Shopify, HubSpot and QuickBooks. It doesn't read whatever cells happen to be on screen.
It doesn't write formulas. You get the answer, not a QUERY to paste. For a formula that lives in your sheet and recalculates on its own, use the builder above.
Ask the same question next month and it runs again on the refreshed data, so a report becomes a set of questions instead of a set of formulas to maintain.

Get Quiriz for Google Sheets →

Skip the formula, ask the question

Type a question into a cell with =QUIRIZ() and get a figure, a table or a chart computed from your data. No SELECT, WHERE or GROUP BY to write.

Install for Google Sheets →

Free on the Google Workspace Marketplace. Or try it on sample data first, no signup.

Frequently asked questions

Does this Google Sheets formula generator use AI?

No. It's a guided builder that runs in your browser. You choose the function and fill in the ranges, columns and conditions; the page assembles the formula with fixed rules, so the same fields always give the same formula. Nothing you type is sent anywhere.

When should I use QUERY instead of FILTER or SUMIFS?

Use QUERY when you need several steps at once: pick columns, filter rows, group and total, sort and limit. Use FILTER when you want the matching rows exactly as they are. Use SUMIFS, COUNTIFS or AVERAGEIFS when you need a single number that answers one question.

Why does QUERY use Col1 instead of column letters?

QUERY accepts column letters only when the data is a plain range on a sheet, such as A1:E. When the data comes from another function, such as IMPORTRANGE or an array in curly braces, there are no letters, so columns are numbered Col1, Col2 and so on from the left edge of the data. The builder switches automatically when you choose Another spreadsheet.

How do I write a date in a QUERY formula?

Use the keyword date followed by the date in yyyy-mm-dd format inside single quotes: where C >= date '2026-01-01'. To use a date from a cell, join it in with TEXT: "where C >= date '"&TEXT(H1, "yyyy-mm-dd")&"'". Set the condition type to Cell: date and the builder writes that for you.

Should I use VLOOKUP or XLOOKUP in Google Sheets?

XLOOKUP when you can. It can look left, returns a value you choose when nothing matches, doesn't break when columns are inserted, and can search from the bottom up. VLOOKUP still works everywhere and is fine for a simple exact-match lookup to the right.

Why does my formula show a parse error in my country?

Many locales, including most of Europe and Latin America, separate arguments with semicolons instead of commas. The builder writes US-style formulas with commas. If your spreadsheet locale uses commas as the decimal mark, replace the argument commas with semicolons, or change the locale in File > Settings.

Step-by-step guides

More free tools for spreadsheet chores: remove duplicates, compare two lists, and the rest on the tools page.