Spreadsheets

QUERY label and format: rename headers and show rupees in results

Use the QUERY label and format clauses in Google Sheets to rename headers like Total spend and show rupee values, plus the quote mistakes that break the query.

Editorial illustration for QUERY label and format: rename headers and show rupees in results

In Google Sheets QUERY, the label clause renames a column header and the format clause sets how its numbers display, and both go near the end of the query string. A category-total query can return a header of Total spend and amounts with a rupee sign, as long as you use single quotes inside the query and put the clauses in the right order.

What does the full query look like?

Assume dates in A, category in B and amount in C, with headers in row 1 and data down to row 500:

  • =QUERY(A1:C500, "select B, sum(C) where B is not null group by B order by sum(C) desc label B 'Category', sum(C) 'Total spend' format sum(C) '₹#,##0'", 1)

Reading it: pick category and the sum of amount, ignore blank categories, group by category, sort the biggest first, then rename the two columns and format the number. The final 1 tells QUERY the range has one header row.

How does label work?

  • Syntax: label column 'New header', column2 'Another header'.
  • Use the same expression you used in select. If you selected sum(C), the label is for sum(C), not for C on its own.
  • The new text goes in single quotes. Because the whole query is already inside double quotes, using double quotes for the label would end the query string early and cause an error.
  • To hide a header, label it with empty single quotes: label sum(C) ''.

How does format work?

The format clause takes a pattern in single quotes. Common ones:

PatternShows 1234567.5 asUse for
#,##01,234,568Whole rupees
#,##0.001,234,567.50Paise shown
₹#,##0₹1,234,568Rupee sign, whole rupees
0.0%0.254 as 25.4%A column holding a fraction

The pattern groups digits in threes, so a lakh appears as 1,234,568, not 12,34,568. For Indian lakh grouping, leave QUERY's format alone and apply the Format menu's number settings to the result column, or check your spreadsheet's locale setting. Also test the rupee sign in your own sheet; if it does not display, format the column from the menu.

Which quote mistakes break it?

  1. Double quotes inside the query: label sum(C) "Total spend" fails. Use single quotes.
  2. A missing quote at the end of a label: label sum(C) 'Total spend gives a parse error.
  3. Labelling C when you selected sum(C), so the label is ignored or errors.
  4. Clause order wrong. The order is select, where, group by, order by, limit, label, format. Putting label before group by fails.
  5. Straight versus curly quotes. If you copy a formula from a chat or document, retype the quotes.

What about a header that comes from a cell?

If you want the header to follow a cell, such as a financial year written in F1, build the label with an ampersand: label sum(C) 'Spend "&F1&"'. Watch the quotes: the single quote opens the label, then the double quote closes the string so the cell value can be joined, then another double quote reopens it. Keep a copy of the working version before experimenting.

A tidy checklist

  • Select expressions first, then label them using the same expressions.
  • Single quotes inside, double quotes outside.
  • Format patterns in single quotes too.
  • Header row count set to 1.

What does the result look like?

With hypothetical totals, the query returns a small table whose first row is the renamed header and whose later rows are the biggest categories first:

CategoryTotal spend
Groceries₹12,400
Fuel₹6,850
Eating out₹4,100

If your headers still read sum, check that the label text matches the expression in select exactly.

If the sheet becomes a maze of formulas, SpendixAI can take the statements you upload and show category totals in its reports, with no labels to write.

SPENDIXAI JOURNAL

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

Explore your reports