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

21 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

  6. Hi Ben. My wife uses Google Sheets in her business of helping people with budgeting and personal finance. Her biggest issue is with copying and pasting a range of cells from Sheets into email communications. Usually something goes “wonky” and the appearance of the grid in email is not attractive – fonts don’t match, or cells are sized wrong, or grid lines are sometimes present, sometimes absent. She is copying into Apple Mail in OS X. Thanks for any help and suggestions!

    David

    1. Hey David,

      Hmm, not sure of anything that can help here. Generally I find tables copy across pretty well (I’ve just tested a few into Apple Mail too). Simply formatted tables will probably copy across better into email, and ultimately display better for the end user too. For example, fancy fonts available in your Sheet may not be available to the end user in their email software. So your wife could always try creating a simple format style for copying into emails.

      Cheers,
      Ben

  7. Making a table look ‘attractive’ is subjective, and if that’s your aim then there’s a great deal of flexibility in how you achieve it. If your focus is on people understanding the data, then you may be interested in some feedback based on best practise for academic literature on statistics:

    As an overarching principle, the purpose of table formatting is to aid the comprehension of the data. Formatting should not be used unless it serves this aim. Formatting creates noise and draws attention away from the data. Any added formatting therefore needs to be strongly justified.

    You note that good practise is for numbers to be right-aligned, then disregard that. The purpose of right-aligning is to make it easy for someone to look down the column and compare different numbers. By centring the numbers you’re creating extra work for the brain to shift the numbers into a position where they can be compared. Your example, which shows just a handful of simple numbers, is not the most egregious – but it still makes the table less easy to understand.

    Colour bands are additional noise that add little to the understanding of the data. It’s unnecessary to have multi-coloured rows. Occasionally a colour band may be used to identify a particular row/column which is of specific relevance.

    Indeed, best practise is to remove gridlines as well – the data itself provides a table structure that is clear and easy to follow, as long as it’s properly aligned. I wonder whether our current obsession with gridlines is to some extent a product of our computer software, where they serve a different purpose: in word processing the lines are used as default to make it easier to drag tables around a document, and in spreadsheets they’re necessary because we start with blank sheets that doesn’t have the data there to provide structure.

    There’s no need to change the header colour either – simply bolding the column headers is enough to clearly differentiate them.

    I’m not suggesting that everyone must always follow these guides (although centre aligning everything really doesn’t help!), as clearly there are times when you need to present data in a way that is engaging for the viewer as well as being easily understood, which isn’t necessary in an academic context. Just thought it may be of interest.

Leave a Reply

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