by Whatever data you work with in Google Sheets, most likely it's initially a set of plain-looking text values, numbers, maybe dates, etc. Wouldn't you agree that putting all these records into a well-built table would help you analyze and communicate the data more effectively?
In this article, I share all tips on how to make a table in Google Sheets. I'll go through native formatting tools and review one special add-on that you can use as a shortcut to paste styles to your sheets.
Shortcut to make a table in Google Sheets
If you switch to spreadsheets from MS Excel, you're probably looking for a special shortcut (like Ctrl+T) that converts selected cells to a table. If Excel has it, it must be here as well, right?
Unfortunately, you won't find this magic key combination in spreadsheets. You have to use other means in Google Sheets to format your data as table.
Formatting tools to make a table in Google Sheets
Here's a short dataset I've put together. You can see some fruits, their types, suppliers and the prices along with the discounts they offer:
All records are displayed with the default settings: the default Arial font of size 10. All text values are aligned to the left, numbers — to the right. Nothing to make it look visually appealing. Let's see how you can format that as a table in Google Sheets.
Google Sheets table formatting — changing the font and its size
The first thing you may want to change in your table is the font and its size. You'll find these options shoulder to shoulder on the Google Sheets toolbar. Just pick at the fonts available and you'll see lots of easier-to-read options. For instance Calibri, 12:
If existing fonts are not enough, click More fonts of that menu and browse through literally hundreds more fonts:
Google Sheets table — Number format
The next thing to put in order is the Number format. It directly affects the way you'll see records in cells.
Take the last column in my table for example — E2:E14. It's called Discount and shows number fractions.
To make a better table in Google Sheets, I will select this column, go to Format > Number, and pick Percent:
The displayed values turn to percentages making the whole column and table easier to read and understand:
By the way, look at column C in the screenshot above. It contains prices that used to look like simple numbers. So I also turn them to currency: Format > Number > Currency.
Format as table in Google Sheets with horizontal & vertical alignment
Based on the Number format, the values are always aligned to different parts of spreadsheet cells: text — to the left, and numbers — to the right.
You are free to adjust this as needed with the corresponding tools: horizontal and vertical alignment.
I don't use vertical alignment much, though it's no doubt useful if you merge cells or import data:
Horizontal alignment in its turn will make your table in Google Sheets look good:
Choose the setting that'll help records from the adjacent columns (text and numbers) avoid sticking together:
Make a table in Google Sheets with a distinct header row
The header row contains labels for each column. And it's a widespread practice to make it stand out to improve your table readability. Here's what you can do:
- Apply Bold to add weight:
- Highlight cells in your header row with some fill color:
You can pick one of the standard colors, suggested theme colors, or add a custom color as I did.
- Set the horizontal alignment for the labels. I prefer when they're centered no matter their Number format:
- To make your Google Sheets table format more convincing, separate your header row visually from the rest of the data by adding the bottom border:
You can pick any color and border style from the corresponding setting:
Here's how I've formatted my data as a table in Google Sheets so far:
Google Sheets table — format footer row
If the last row of your data sums up the info from one or several columns, you can format that row as well making a table in Google Sheets with both header and footer rows.
Of course, you can just select your header row and copy its formatting using the format painter in the Google Sheets toolbar:
But this may be not the best solution if there are bottom borders in the header row (who wants just the bottom border in the total row, right?) or if you'd just like the rows to look unique. In this case, you need to format cells in the Google Sheets total row over again.
I will go for another fill color & text color, add a thin top border and align everything to the center:
Insert borders to make a table in Google Sheets
One of the distinctive features of the table is the borders. Borders outside the table, borders inside (between columns and rows).
Spreadsheets let you turn on all of them at once with one setting (All borders) or select only certain borders (Inner, Vertical, Top, etc.):
Before going for Google Sheets borders, don't forget to tweak their colors and styles:
Tip. You can set up a different color and style for each border (inner, vertical, top, etc.) individually.
Create a table in Google Sheets with alternating colors
Another useful setting that exists specifically for the Google Sheets table format is Alternating colors. The Alternating colors tool fills every other row with a color different from the first row.
You will find this setting under the Fill color:
Pick one of the suggested styles for your table or pick hues for the header, footer, 1st and 2nd rows:
Your Google Sheets table formatted in this way will help you scan the data with ease:
Tip. If you'd like to make the coloring depend on some values, set up conditional formatting rules by following the steps from this tutorial.
Make a scrolling table in Google Sheets
As you can see, I have a very small dataset that fits one screen. Yours will most likely take a few dozen or even hundred rows. And when you start to scroll it, the header row will disappear leaving you in the dark on the meaning behind this or that column.
Scrolling back and forth is not an option, right? Luckily, you can change this unfortunate behaviour :)
Go to View > Freeze in the menu and you'll be able to lock one header row, two rows and even more:
Tip. To lock more than 2 rows, select any cell on the last row that you need to lock (5th, for instance) and you'll see the option to freeze everything up to that row.
I locked the 1st row and it will always remain visible no matter how many rows I scroll down:
You can actually do the same with columns if the leftmost ones contain names, IDs or other labels that have lots of additional information in multiple other columns. The way is the same: View > Freeze, and then pick the option for the required amount of columns:
Format data as table in Google Sheets using the named range
Named ranges in Google Sheets let you give names to different groups of cells in your spreadsheet.
If I am to use my table in some formulas or calculations, normally I'd refer to it by its location, like A1:E15. But with the named range, I can give any name to this group of cells and refer to them by that name in formulas.
I select my Google Sheets table and go to Data > Named ranges:
Since my table contains data on fruits, I'll call it Fruits and click Done:
Tip. Use short simple names to ease the job for later when you'll need to refer to this table in formulas:
Create a table with filters in Google Sheets
One of the fancy things to do with data formatted as table in Google Sheets is filter and sort it in different ways.
Select your table and click Create a filter on the spreadsheet toolbar:
We described different ways to filter your table in previous articles:
- To sort or filter your Google Sheets table by data, read this blog post.
- To filter by color, hop to this article.
- To filter by condition, follow the steps from this tutorial.
Remove formatting from Google Sheets table
Since there are lots of options to style different parts of your data to make it look like a table in Google Sheets, there must be a way to remove all the formatting.
To remove only certain formatting, like frozen rows or alternating colors, you need to open those settings again and reset directly from there (Freeze > No rows or Remove alternating colors):
Some options work like a button: press to turn on, press to turn off (Bold, Italic).
Others can't be removed completely. You can only change them, like font style & color, alignment.
Yet, there's one option available that resets all the styling to its default state. It's called Clear formatting and you'll find it under the Format tab:
Tip. Google Sheets offers a shortcut to remove all style formatting from your table. For that, press Ctrl+\ (or Cmd+\ on Mac) on your keyboard.
It removes all fill colors and borders, returns the default font (black Arial 10) and alignment, removes all bold or italic settings. In other words, it returns your formatted Google Sheets table to its default initial state:
This may be particularly useful for when it's easier to remove and reset all the formatting and start making your Google Sheets table look cool but different anew.
The easiest way to make a table in Google Sheets — Table Styles add-on
As you can see, there's no Google Sheets shortcut to paste style. All standard ways to format your data as table are scattered all over Google Sheets. You have to jump from one setting to another to apply the formatting, then back to it to remove it.
There's also no way to save everything as a style pattern and reuse it later.
Luckily, there's this add-on: Table Styles. Consider it your style vault where you can create, edit & keep all your table formatting sets. Believe me, you don't want to miss having this tool, and here's why.
Format Google Sheets with 50+ ready-made styles
The add-on comes with a starter kit of 50+ pre-defined style patterns:
They are grouped based on the color patterns. You can apply any set to your data, or play around with its formatting & adjust to your needs.
Make your own table styles for Google Sheets
The tool will become your magic wand as it's the only way to create and save your own table styles in Google Sheets.
You can opt for one, all or any combination of different table parts: header row, footer row, alternating colors, left column, right column:
The formatting tools let you fine-tune each table part in its own unique way & preview the result on the go — all in one window:
Tip. If several table parts should share the styles, the Multiple selection option will help. When it's enabled, you can select several parts and set the same color, font, etc. to them in one go:
Google Sheets shortcut to paste style from the add-on
Once you save your desired pattern, Table Styles for Google Sheets works as a shortcut to paste style. Just click that green Style button at the bottom.
Or use the filter icon next to it to pick and apply only certain formatting:
Whatever you choose, it will make a good-looking table in Google Sheets in a matter of a few mouse clicks:
Video: How to format Google Sheets using Table Styles add-on
The following 2-minute video demonstrates Table Styles in action:
I encourage you to install the add-on from the Google Sheets Marketplace and start creating stunning professional tables with ease.
Let me know in the comments if you know other ways to make a table in Google Sheets :)
