Format your Google Sheets so they look good GREAT

A collection of formatting tips for tables and titles in your Google Sheets.

Google Sheets table with conditional formatting

Formatting Tables

Let’s start with a simple table, completely devoid of any formatting:

Table no format

Aligning columns

Let’s align those columns, they’re messy!

Table alignment GIF

General rules:

Center column headings, ID numbers, or other standardized entries.

Left align text.

Right align numbers (e.g. financial numbers).

Dates can be center or left aligned, both work, depending on your preference.

Generally, I would leave text left-aligned and numbers right-aligned, but it looks a little wonky in this table, so you’ll notice further on in this post I’ve changed both these columns to be center-aligned too.

Header rows

Go for bold, white text on a dark background cell color. As above, ensure it’s center-aligned and consider making all the text uppercase.

Header row

Formatting data

Choose appropriate formatting options for the data in your tables.

Add thousand separators to big numbers above a thousand. Add currency signs to financial numbers to add context.

Choose an appropriate number of decimal places. For example 2 decimal places if you need that level of detail to show cents on the dollar, but remove decimal places if they’re not needed for large numbers:

Formatting numbers as currency

Dates can be short or long, but be consistent across your spreadsheet:

Date formatting

Table borders

If you add borders, which I’d recommend if you plan to present the sheet with gridlines turned off, then go for light or medium grey so they’re subtle.

Border format

Table row banding with conditional formatting

Sure you can do this manually, but it’s way easier and quicker to do with conditional formatting. Simply highlight your whole table (excluding the header row) and then open up the Conditional Formatting option sidebar. Select Custom Formula and use the following formula to highlight alternate rows:

=isodd(row())

or, if you want to swap the shaded rows, try this formula:

=iseven(row())

Pick a light shade of grey, or a light color to match your color scheme.

Row banding with conditional formatting

For more formatting ideas, including how I set up the red/green indicator arrows in the table at the top of this post, check out 8 and 9 of my 10 Google Sheet dashboard tips article.

Formatting Titles

I’d recommend resisting the urge to use fancy fonts, or cursive fonts designed to mimic handwriting, as they make your work look less professional.

I rarely look past the default Arial, which is fine in most cases, or Verdana as a fine alternative. If I’m looking for something to make my sheet or dashboard stand-out that bit more, then I use the Montserrat font.

Also, I wouldn’t use more than two different fonts in a sheet. Almost always I stick with one.

In this example, I add a title and then merge and center the cells above the table. I increase the font-size to 18pt, make it bold, before trying some different combinations of borders and background shading.

Title formatting basic

In dashboards, I often create “tiles” for headers or metrics. I’ll add the title (or metric), a background color to the cell that matches my color scheme and a thick border to the left or right with a contrasting color. ThenI can change the font and font-size to my needs, e.g.:

Title tile format

17 thoughts on “Format your Google Sheets so they look good GREAT

  1. Your blog is BADASS! Thanks for sharing your Google sheets tips! Really appreciate that you “recorded” gifs for us to see instead of just pasting a trillion screenshots.

  2. For columns with any kind of number, I can’t get anything other than right-alignment to stick. I can set the phone number column to plain text and left-alignment holds, but I need dates and times to remain as dates and times. For those columns, right-alignment seems to be hard wired.

    Any thoughts?

  3. Ben, could you please explain how you created colored arrow signs in right column?
    And additional question – do you know a way to use conditional formatting not only to change colors and fonts in the cell, but also for showing special symbols (for example: smile or up/down arrow)?

    1. Hey Oleg,

      I use CHAR(9650) for the up arrow and CHAR(9660) for the down arrow, and put them inside my IF statement (read more here). Then you can use conditional formatting as follows to color them green or red:

      Conditional Formatting arrows

      Hope that helps!

      Cheers,
      Ben

  4. This is great. Thanks.

    Is there a way to do conditional formatting so that an entire row is adjusted based on the contents of a particular column in that row. So in your example above for example make the entire row background red for any of the rows where revenue was < 40k?

    1. Sure is. The key is to add a $ before the column reference in the custom formula of your conditional formatting.

      Assuming data in say A1:D7, then highlight whole data range, Format > Conditional formatting, then use this formula: =$D1<40000

      like this:
      Conditional formatting

      Cheers,
      Ben

    1. Hey Steve,

      You mean on the Excel sheet right? Not sure why that’s happening, maybe it just doesn’t play well with Drive.

      What if you change the text to white?

      Ben

  5. Can I format a column to fill a different colour if a particular letter is entered in to a cell ie if L is typed in the cell can it be filled blue etc

Leave a Reply

Your email address will not be published. Required fields are marked *