October 14, 2020

How to Use SQL Queries to Format Google Sheets

Chart with arrows pointing up and down
SECTIONS
  1. Formatting Date and Time
  2. Formatting Currency
  3. Formatting Several Columns at Once
  4. Further Reading
  5. If You Want Something More Sophisticated...

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
Information in bullet points above in table format
Just in case you like table format instead...


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:

The original sheet with original formatting. The date of entry is formatted as Month-Day while the currency cells are formatted as simple numbers.
Our original sheet with the date of entry is formatted as Month-Day.

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:

The same sheet queried, but with the date column formatted to include the year and the day of the week. For example: August 4, 2020, Tuesday
The same sheet queried, but with the date column formatted to include the year and the day of the week!


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:

The same sheet queried, but with the price-per-item column formatted to include the dollar symbol and the proper number of decimal places to indicate the number of cents.
The same sheet queried, but with the price-per-item column formatted to include the dollar symbol and the proper number of decimal places to indicate the number of cents.


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>, ...

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:

The same sheet queried, now with both the date and all the currency columns properly formatted.
The same sheet queried, now with both the date and all the currency columns properly formatted!


Further reading

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. 

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.