This post is 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:
The goal of this article is to show you how to make a table in Google Sheets look great, like this:
How to Format a Table in Google Sheets
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 acting as numbers, i.e. they’re not being used in 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 a thousand separator 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. In a new blank Sheet, put these values in cells A1 to A3:
9650
9660
9644
In the adjacent column B, copy these three CHAR formulas (you can delete them later):
=char(A1)
and
=char(A2)
and
=char(A3)
to give an output:
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.
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.
Thanks! GIFs are quicker for me too 😉
Ben, Can you share how you do those gifs? What a great idea!
Hey Rob! Using a program called LiceCap: https://www.cockos.com/licecap/
this is best its verry help full
Ben, I need a solution.
Today when a filter is added to a table. Alternating Colors stay with the original range. Is there way to have Alternating Colors dynamically match the data when a filter is added removed or the data table grows ?
I started your Blastoff course and thought I had it in Apps Script. I failed any help would be amazing.
Jamie
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?
Hmm, this sounds strange. I’ve never not been able to left, middle or right align different datatypes. I’d suggest posting a question (with a link to a copy of your Google Sheet) to the Google Sheets forum: https://productforums.google.com/forum/#!topicsearchin/docs/category$3Aspreadsheets
Thanks for this, very helpful
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)?
Hey Oleg,
I use
CHAR(9650)
for the up arrow andCHAR(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:Hope that helps!
Cheers,
Ben
Yes, it does.
Thanks a lot! 🙂
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?
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:
Cheers,
Ben
I have a frozen header row. The text is black and the background is black. I have modified it dozens of times to display with a white backround, but every time it is viewed in the shareable link, the background changes back to black. Any thoughts?
https://drive.google.com/file/d/0B3NcZfgejT3mYzFvdFJVQVVyQkk/view
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
P.S. Must have missed this comment originally sorry, just seen the date! 🙁
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
Yes, you can definitely do that by setting up different conditional rules for each of your letter/color combos.
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
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
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.
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
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).
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
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
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.
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.
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!
I would literally pay for a solution to set a default font style and size in Sheets. The feature exists in Docs, but not sheets. I have not been able to find a solution and the Google product forum guide says “Sounds like a good idea, you should submit it”
Do you know any magic workaround?
Hi Ben,
I’ve figured out how to use colours with conditional formatting (eg. if cell A1=Yes then the cell turns green, and if no, it turns red, etc. However, once I input multiple entries with multiple conditions, my spreadsheet looks super messy with different colours for every row. Is there a way where I can group all the same colours together automatically? Thank you!
Hi, Ben. Fantastic tips you got there, they’re real treasures for us dummies =)
When you format as table in excel, and you have some formula attached to the named columns (i.e. =Countif(#All[Clientname]:[@[Clientname]), when you convert the file to google sheets, it gets the row/columns references.
The problem is: In excel, when you insert a line at the end of the file, the formulas autofill to the last respective cells, but I’m having some trouble with the formulas not filling to the last rows on google sheets.
Is there a way to put a “smart filling” of these formulas at the last rows automatically? Or everytime a line is added, the formula must be copied to the last row?
I already got a Vlookup dinamic range, but not the formula to be in the last cell of the column.
Hey Paulo,
Unfortunately Google Sheets doesn’t have that native table feature, but you can create auto-expanding formulas by using array formulas, that will calculate whenever new rows are added. Have a look at this post for an example: https://www.benlcollins.com/formula-examples/array-formulas-forms/
Cheers,
Ben
Hi Ben, thanks for these amazing tips! I just made a table for a food diary in Google Sheets and it’s looking very snazzy.
I was just wondering if there is a way of repeating the table?
Since this is a diary, I’m supposed to fill it out every day. Can I add a formula that copies both the text and the formatting? I got as far as ArrayFormula(A28:K51) but this only copies the bare text.
Kind regards,
Meina
Fantastic – thanks so much, Ben!
Hi Ben-
Lots of great tips! Some I knew and a few that were great additions to my toolbox.
What I’m really struggling with is finding a way to lock (protect) the formatting, so that once I’ve got things formatted things stay that way even when new data is pasted in. I’m still surprised that there isn’t a bigger uproar over the default behavior being to to bite chunks out of the lines and boxes that have been put in place when pasting new data in. I know that it’s possible to “paste content only”, but my whole group shares the spreadsheet and I can’t get the others to reliably do this. Is there some way to lock/protect the formatting but allow for changes to the content?
After a long time, i have visited a page that actually helps to achieve something which i need. Thank you very much.
When I insert or remove a column from the data that my table is referencing, the format does not seem to move with the data…Is there a way to lock the format in the pivot table?
Hi Ben,
Im not super savvy with Google Sheets. I’ve followed the directions on how to “How to make a table in Google Sheets with colors and indicator arrows” however, I’m not sure how to apply it to the % change column. I see that you are supposed to make an IF statement, but I don’t know how to do that or which information to input. Do you have a tutorial anywhere are how to specifically input an IF statement so I can create the percentage change and arrow result you have in you % change column?
Thanks so much!
Hi, great tutorial! It helps a lot! Thanks
I do have a question about a feature! I’ve been a Mac Numbers user for the past few years and so I’m not used to Google spreadsheet anymore! I’m back at it now and a little lost!
In Numbers, there is a feature that I’ve been using but I can not find it in google spreadsheet.
There is an option to add multiple tables on the same sheet. When I tried to convert a “numbers” document into a spreadsheet file. It split my sheet with 3 different tables in 3 different sheets.
My question is, is there an option on spreadsheet to separate table on the same sheet?
Let me know if my question does not make sense, I can attach some pics.
Thank you very much
This was so helpful, just added it in before my meeting with our exec team, thank you!
Congrats Ben, this is amazing and sure is helping so many people, like myself.
All the best!
Joao Pelosi
I am looking for a way to make it so that a tab you where working on previously can be maid to come up when you re open it, we are using it for an accountability tracker with each month with it’s own tab. We want to open it to where the month we are in is what comes up when we open it.
or get excel and do all that with one button
another way that the g-suite is a very, very poor neighbour to the professional office toolset
Bob – strongly and respectfully disagree… having everything online with seamless collaboration between Workspace apps, Google Cloud, and third-party tools, makes my life so much easier than when I worked with other Office tools.
Hello.
How can I dafine the name of table?
Thanks Ben.
Hi Ben,
Is there a way I can highlight an entire row in excel using conditional formatting so that if the sum of Column B+C+D doesn’t equal column A it goes red, and green if it does match?
Is it possible to not show the headings in a Google Sheet? I formatted an Excel sheet without the headings but when imported in to Sheets the heading’s reappeared.
I do not see the option to convert to table under the format option. I did see the section here on How to make a table in Google Sheets with Alternating colors. It does create alternating rows of color. But it isn’t like office 365, where as you click the tab, it will advance to next row. Even trying to create a pivot table, it cannot do because it isn’t a table format. Maybe these options are not available with google sheets. I am trying move form office 365 to google sheets.