How to make a good-looking table in Google Sheets

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: Google Sheets table — default formatting.

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: Style fonts in Google Sheets.

If existing fonts are not enough, click More fonts of that menu and browse through literally hundreds more fonts: Add more fonts to Google Sheets.

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: Display numbers as percent via the spreadsheet menu.

The displayed values turn to percentages making the whole column and table easier to read and understand: Google Sheets table format: percent.

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: Vertical align options: top, middle, bottom.

Horizontal alignment in its turn will make your table in Google Sheets look good: Horizontal align options: left, center, right.

Choose the setting that'll help records from the adjacent columns (text and numbers) avoid sticking together: Center records so they don't stick together in the neighbouring columns.

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:

  1. Apply Bold to add weight: Bold header row.
  2. Highlight cells in your header row with some fill color: Add fill color to your header row.

    You can pick one of the standard colors, suggested theme colors, or add a custom color as I did.

  3. Set the horizontal alignment for the labels. I prefer when they're centered no matter their Number format: Center records of your labels in the header row.
  4. 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: Apply a bottom border under your header row.

    You can pick any color and border style from the corresponding setting: Change the color and the style of your borders.

Here's how I've formatted my data as a table in Google Sheets so far: A nice header 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: How to copy formatting in Google Sheets.

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: Make a table in Google Sheets with header and footer rows.

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.): Bottom and other borders.

Before going for Google Sheets borders, don't forget to tweak their colors and styles: Tweak border colors and styles.

Tip. You can set up a different color and style for each border (inner, vertical, top, etc.) individually. Format inner borders in your Google Sheets table.

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: Alternating colors option.

Pick one of the suggested styles for your table or pick hues for the header, footer, 1st and 2nd rows: Use one of the default alternating styles or customise it.

Your Google Sheets table formatted in this way will help you scan the data with ease: Format Google Sheets data as table.

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: Lock rows in Google Sheets.

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:
Make a scrolling table in Google Sheets.

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: Scrolling table with the locked row and column in Google Sheets.

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: Named ranges in Google Sheets.

Since my table contains data on fruits, I'll call it Fruits and click Done: Give a name to your table.

Tip. Use short simple names to ease the job for later when you'll need to refer to this table in formulas: Refer to your Google Sheets table as to the named range.

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: Create a filtering table in Google Sheets.

We described different ways to filter your table in previous articles:

  • To filter your Google Sheets table by values, colors and other conditions, read this blog post.
  • To filter dynamically using functions, this article will help.

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): Unfreeze all rows (or columns).

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: Clear all formatting from your Google Sheets table.

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: Reset all the formatting to its default view.

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.

Google Workspace Marketplace badge

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: Make use of the existing style patterns for Google Sheets.

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: Select all parts that should appear in your Google Sheets table.

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: Format data as table using Google Sheets add-on.

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: Format multiple table parts in Google Sheets at once.

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: Paste the whole style or only its parts to your data.

Whatever you choose, it will make a good-looking table in Google Sheets in a matter of a few mouse clicks: Google Sheets table format.

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.

Google Workspace Marketplace badge

Let me know in the comments if you know other ways to make a table in Google Sheets :)

You may also be interested in

Table of contents

Post a comment

Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)