A collection of formatting tips for tables in your Google Sheets.
Formatting Tables
Let’s start with a simple table, completely devoid of any formatting:
Header rows
Go for bold, center-aligned and wrap the text, so it all shows.
Aligning columns
Let’s align those columns, they’re messy!
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:
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:
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:
Now take a look at the same table with colors and arrows added to call out the % change column:
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):
=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:
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:
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.:
“I will definitely be using the statistical functions to test whether the conclusions of my analysis are statistically significant or not.
“I was really excited by the idea of using an application I was familiar with, Google Sheets, to complete data analysis that had seemed so challenging and foreign before.
“I wanted to learn more about what I can do with Google Sheets to get an idea about how my agency can benefit. My SEO company uses Google almost entirely for all our project management, etc.
