Google Sheets has several built-in ways to format data, and also allows you to customize your own. The same can also be done via the QUERY function! By default, the QUERY function adopts the existing format from the source sheet, but if you need to customize it, you can use the format clause to specify your own. The format clause follows the following format:
format column1 ‘format1’, column2 ‘format2’, column3 ‘format3, ...
In this tutorial, we will learn how to format the queried rows according to your preferred format for the given data type, enhancing the readability of the sheets.
Formatting Date and Time
Google Sheets allows for a wide variety of formats for date and time. Listed below are the common date and time formats and how the date and time will look using them, with August 4, 2020 11:30:22 as the example date and time:
- d-m-yy: 4-8-20
- d-m-yyyy: 4-08-2020
- dd-mm-yyyy: 04-08-2020
- d-mmm-yyyy: 4-Aug-2020
- d-mmmm-yyyy: 4-August-2020
- mmmm d yyyy: August 4 2020
- mmmm d yyyy, dddd: August 4 2020, Tuesday
- h:mm: 14:07
- hh:mm AM/PM: 02:07 PM
- mmmm d yyyy, hh:mm AM/PM: August 4 2020, 02:07 PM
Simply add the given format to the query, enclosed in single quotations. For example, we have the following source sheet, where we want to format the date to include the day of the week:
On a different sheet, we write the following formula:
=query(week32!A1:I, "format A 'mmmm d yyyy, dddd'")
Where the query is
format A 'mmmm d yyyy, dddd'
The result would look like this:
Formatting Currency
Quantities such as price, sales, and profit are special types of numbers that we call currencies. Currencies should include the symbol of currency used and two decimal places to indicate smaller divisions of the currency. If the currency-related data is not formatted in that way, we can do so using the format clause of the QUERY command. Listed below are the common currency formats, applied to the value 24.50:
- $x: $25
- $x.xx: $24.50
- ¥x.xx: ¥24.50
Just like the case of formatting date and time, we enclose the whole clause in double quotes and the format in single quotes inside the clause. As an example, we want to format the price into a proper currency form. We will add the currency format to the existing query that we have above. It will look like this:
=query(week32!A1:H, "format C '$0.00'")
Where the query is (including only the format clauses for the currencies)
format C '$0.00'
The result will look as follows:
Formatting Several Columns at Once
If you need to format several columns at once, the solution is an easy fix. Write the format clause once, and write the individual columns and its corresponding formats together, separated by a comma:
format <column1> ‘<format1>’, <column2> ‘<format2>’, <column3> ‘<format3>, ...</format3></column3></format2></column2></format1></column1>
To finish formatting our example, we will also reformat the other columns into their proper currency format. Our resulting query is:
format A 'mmmm d yyyy, dddd', C '$0.00', E '$0.00', F '$0.00', G '$0.00', H '$0.00'
Putting it into the QUERY formula, the result will look like this:
Further reading
- The specifications for the format clause in Google Sheets can be read here: Query Language Reference (Version 0.7) | Charts
- The specifications for the date and number formats in Google Sheets can be read here: Date and Number Formats | Sheets API
If You Want Something More Sophisticated...
Everybody knows that a well-formatted UI enhances the readability of the data and information on the screen. This includes the font, colors, and the position of the data on the screen. But UI is a complex subject! If you believe google searches, a UI specialist can earn up to six digits per year!
Clearly, UI is a serious business left to professionals. I suggest you consider trying Lido. The platform not only presents the data in a user-friendly format, but it also does the relevant number-crunching behind-the-scenes, so you only have to deal with the important matters such as decision-making for your business.
Recommended articles
More resources
