A collection of formatting tips for how to make a table in Google Sheets.
Let’s start with a simple table, completely devoid of any formatting:
How to Format a Table in Google Sheets
The goal with this article is to show you how to make a table in Google Sheets look great, like this:
Go for bold, center-aligned and wrap the text, so it all shows.
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.
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.
How to make a table in Google Sheets with Alternating colors
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:
How to make a table in Google Sheets 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):
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 Google Sheets 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.
Looking for a Google Sheets expert to help with your next project? Schedule a consult today with a Ben-approved Google Sheets expert.