Spreadsheets

QUERY returns the wrong header rows: the headers argument explained

Google Sheets QUERY showing an extra header row or missing one? Learn what the third headers argument does and how to set it or trim the range for statements.

Editorial illustration for QUERY returns the wrong header rows: the headers argument explained

The third argument of QUERY tells Sheets how many rows at the top of your range are headers. If it is left blank Sheets guesses, and for bank statements with several lines of account details above the real column titles the guess is often wrong. Set it yourself, usually to 1, and start your range on the real header row.

What does the headers argument do?

  • 1 means the first row of the range holds the column titles and is not treated as data. This is what you want for most clean tables.
  • 0 means there are no header rows, so every row counts as data.
  • 2 or more means that many header rows, which Sheets then joins into a single label for each column.
  • Left out, or set to minus 1, Sheets guesses from the contents of the range.

Example: =QUERY(A1:D500, "select A, B, C", 1) says treat A1:D1 as the header.

Why does a statement confuse the guess?

A statement pasted from a bank export often has a few lines above the table: the account holder, an account number, a period such as 1 April to 30 April, and then the column titles. If your range starts at row 1, QUERY sees text lines in the first rows, and the real header appears further down as an ordinary data row. The guess may then treat the wrong row as the header, so your output has odd titles and an extra title-looking row in the middle of the data.

SymptomLikely causeFix
Real titles show as a data rowHeaders argument is too small, or the range starts above the real headerStart the range at the header row and set 1
First transaction missingHeaders argument is too large, so a data row is treated as a headerLower the number, or set 1
Titles joined like Date NarrationArgument is 2 or more with a one-row headerSet 1
Titles change when data changesArgument left blank, Sheets guessingSet the number explicitly

How do I trim the range so the output starts correctly?

Say the real header row is row 8 and your data runs to row 500. Start the range there:

  • =QUERY(A8:F500, "select A, B, C", 1)

Now the first row of the range is the header, and everything above it is ignored. Row counting: if the titles sit in row 8 and the first transaction is in row 9, the range A8:F500 contains the titles plus 492 data rows at most (rows 9 to 500).

What if my statement has two header rows?

Some exports put a group label on one row, such as Debit and Credit, and a sub-label below it. Set the argument to 2 and Sheets merges them. It is often tidier to delete or fix one row in a cleaned copy of the sheet, so each column has one clear title.

What happens when I stack ranges?

When you combine tabs with curly brackets, the second range should start below its own header, otherwise the title appears again in the middle of the block as an ordinary row. Start the second range one row lower, or filter it out with a where clause on the title text.

Safer habits for statement data

  • Keep the raw statement on one tab and a cleaned table starting at A1 on another.
  • Use one header row, no merged cells and no blank rows inside the data.
  • Set the headers argument in every QUERY, even when the guess seems to work today.
  • Keep dates as real dates and amounts as numbers, since mixed types in a column can confuse the header detection.

If cleaning statement tops by hand is a monthly chore, SpendixAI lets you upload the statement and review rows before saving, so you are not trimming ranges; do check that your particular layout reads properly, as not every layout will parse perfectly.

SPENDIXAI JOURNAL

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

Explore your reports