Format your Google Sheet tables so they look good GREAT

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

Google Sheets formatting for tables

Formatting Tables

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

Table no format

Header rows

Go for bold, center-aligned and wrap the text, so it all shows.

Google Sheets formatting column headings

Aligning columns

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

Table alignment GIF

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

Left align text.

Right align numbers (which they are by default). Really the only exception to this rule is for numbers that are not really numbers, i.e. they’re not being compared against each other or being used in any sort of calculations. For example, the ID numbers above can be center aligned.

Right align dates (which they are by default). However, if you’re working with just a year, as in the example above, you can get away with center aligning, just be consistent.

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:

Google Sheets formatting format as currency

I could stop the article here, as the table is now sufficiently formatted to maximize legibility.

However, for tables that are part of dashboard reports or are being presented to clients, you can add colors to match your branding, or bring additional context to your numbers.

Alternating colors in tables

Sure you can do this manually, but it’s way easier and quicker to do with the Alternating Colors tool under the formatting menu.

Simply highlight your whole table and then open up the Alternating Colors option sidebar. Select the color scheme you want, whether you have a header or footer row, and even choose custom colors if you wish:

Google Sheets formatting alternating rows

Adding additional context with colors and indicator arrows

Hidden in the Custom Number Format menu is a conditional formatting option for setting different formats for numbers greater than 0, equal to 0 or less than zero.

It’s a great tool to apply to tables in your Google Sheets dashboards for example, where the data is changing. By changing the color of a table cell’s text as the data changes, you can bring it to the attention of your user.

Consider the following sales table which has a % change column:

Google Sheets percent table

Now take a look at the same table with colors and arrows added to call out the % change column:

Google Sheets table

It’s significantly easier/quicker to read and absorb that information.

How to add this custom formatting

1. Somewhere in your Sheet, or a new blank Sheet, copy these three CHAR formulas (you can delete them later):

Google Sheets Char formula

=char(A1)
=char(A2)
=char(A3)

Now, copy and paste them as values in your Sheet so they look like column C and are not formulas any longer.

(You copy as values by copying, then right clicking into a cell and select Paste special > Paste values only…)

You’ll need to copy these to your clipboard so you can paste them into the custom number format tool.

2. Highlight the % column and go to the custom number formatting menu:

Google Sheets custom number format menu

3. Change the 0.00% in the Custom number formats input box to this:

[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬

as shown in this image:

Google Sheets custom number format

What you’re doing is specifying a number format for positive numbers first, then negative numbers and then zero values, each separated by a semi-colon.

Copy in the symbols from step 1 (you’ll have to do this separately for each one).

Use the square brackets to specify the color you want e.g. [color50] for green.

Read more about custom number formatting here: Excel custom number formats

(Yes, it’s an Excel article, but the rules are the same.)

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

28 thoughts on “Format your Google Sheet tables 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.

    1. Hi Charlie,

      Thanks for your thoughtful comment. I had hoped to reply sooner but it’s only now I’ve finally found time to update this article. I agree with your comments and have updated the article to reflect that. The original post had a number of significant shortcomings, most noticeably the center aligned numbers, so thank you for calling me out on that!

      As you’ll notice, I’ve still included row banding and shaded headers, as I feel these have a place in spreadsheet reports for marketers, small businesses etc. (although not in more formal academic settings I agree).

      Thanks again,
      Ben

  8. Hi Ben: My issue with Conditional Formatting is keeping Google from taking one rule that had a range of the entire sheet and over a period of time breaking it up into several rules with blocks of ranges.
    I think this happens when rows are added or moved. Is there any way to keep things tidy? Right now I have to periodically go into the conditional formatting rules, delete the additional rules Google created and update the range of the original rules back to the entire sheet.

    BTW I noticed that Google will not accept A1:L in the range, but instead replaces it with A1:L700 (last row).

    1. Hey John,

      Yes, unfortunately I find that happens too. I don’t think there’s anything you can do about it, other than periodically jumping into your conditional formatting rules sidebar and tidying things up! And yes, it also sets any ranges to last row references rather than leaving them open, so you have to keep an eye out for that!

      Cheers,
      Ben

  9. Hi,
    I have a formatted sheet the new information is added daily because it is attached to a google form. What happens, though, say I format the sheet and it looks great, when someone fills out the form and the new data is added to the sheet, it is not formatted. The old data is still formatted but the new stuff isn’t…any thought?
    Thanks,
    Kristy

    1. If you apply your formatting to entire columns and/or rows, the formatting will carry over to new rows/columns.

      In the case of conditional formatting where you don’t want it formatted until there is data in it (like making every other row a color), you could add a secondary condition to only format if there is data in the cell.

  10. Hey Ben,

    I am SO GLAD I found this page! I’ve now been browsing the rest of your website, lots of great stuff, something tells me I’ll be signing up for one of your courses. So THANK YOU!

    I’m trying to format a Pivot Table, the problem is that the formatting I apply is tied to the cell and not the data, so if a new line of info comes or goes, I need to go back and manually reformat the cells.

    For example, if I format the Totals line, but then a line is added to the Pivot Table, all of that formatting goes to the regular line of data where the Totals line used to be.

    Thanks for any input you can share.

  11. Leaving comment to appreciate this entry. So few people pay attention to aesthetics. I usually go even further than you, but some of your tips are new for me so big thank you!

Leave a Reply

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