If you work with the data in Google Sheets, it would be safe for me to assume that you have it in a tabular format (i.e., have the data in a table).
While you can always have the data as simple entries in the spreadsheet grid in Google Sheets, if you can convert it into a well-formatted table, your data would be a lot more readable.
In this tutorial, I will show you how to make a table in Google Sheets by converting your tabular data. I will also cover some formatting tips that will help you make your table look a lot better.
Note: If you have used Excel, you might know that there is an Excel table feature in it. At the time of writing this article, there is no such functionality in Google Sheets. But this guide will make it easy. You may want to consider doing a Google Sheets course to learn even more. Check out our new one by following the link below.
Table of Contents
How to Create a Table in Google Sheets
- Open a blank spreadsheet in Google Sheets
- Add the text for the header row.
- Add in your data.
- Format the table
Formatting Your Table in Google Sheets
For the purpose of this tutorial, let’s consider the below table in Google Sheets
While there’s no direct shortcut for Google Sheets to format as a table, I will show you all the steps you can take to quickly convert this bland-looking table into a great-looking table in Google Sheets.
Once I’m done with the formatting, the table will look a lot better, as shown below.
So let’s get started with some basics for how to format a table in google sheets!l.
Making a Table in Google Sheets by Applying a Border
One thing that would instantly make a huge difference in our tabular data would be to apply borders.
As soon as you apply the borders, it starts looking like a table.
Here’s how to insert table into Google Sheet by applying a border
- Select the data in the sheet, and click on the Borders icon in the toolbar.
- From the options that show up, select the Border color and apply a border to all the cells (using the All Border option).
Once done, your data will look something as shown below:
How to Insert Table in Google Sheets by Aligning the Data
By default, numbers are aligned to the right in Google Sheets and text strings are aligned to the left.
While this works well, in most cases you would want your headers to be center aligned. This makes it look more like you used Google Sheets to insert a table.
Even in your data set, you may want some columns to be center-aligned instead of the default alignment.
In our data set, I will center align all the headers and the numbers in the sales column.
Below are the steps for making tables in Google Sheets by center aligning the header text:
- Select all the cells that have the headers
- Click on the align text icon in the toolbar
- Click on the Center align icon
The above steps would center align all the text in the selected cells.
Follow the same steps to center align the numbers in Column D by first selecting the data and then clicking on the center align option in the toolbar.
Once done, your data set would look something as shown above.
How to Create a Table in Google Sheets With Colored/Bold Headers
By changing the color of the cells that have the header and making the text of those cells bold, you will be able to drastically improve the readability of your data.
Since the headers are highlighted, that’s usually the first thing that a reader would focus on. this would help them understand what the table is all about and what all data does it contain.
Below are the steps for creating a table in Google Sheets with header color formatting:
- Select the header cells
- In the toolbar, click on the bold icon (you can also use the keyboard shortcut Control + B for Windows or Command + B for Mac)
- With the header cells selected, click on the fill color icon in the toolbar
- Select the color that you want to apply to the header cells
- [Optional] In case you have chosen a dark color, change the font color to white by clicking on the text color icon in the toolbar and selecting the white color.
Once done, your table should look something as shown below:
How to Make Tables on Google Sheets – The Numbers
As you can see, your table in Google Sheets already looks way better than what it was when we started.
One last thing in basic formatting that you can do is to format the numbers to make them easier to read and more meaningful.
As of now, we just have numbers in the sales column.
To make it better, we can add a thousand-separator and add the currency symbol.
Below are the steps to do this:
- Select the numbers in the sales column
- In the toolbar, click on the ‘Format as currency’ option. this would add a dollar sign in front of the numbers, add thousand separator and two numbers after a decimal point
- [Optional] If you do not want to show the two decimal values, click on the ‘Decrease decimal places’ icon two times.
Once done, your table should look something as shown below:
If you followed all the steps above to make a Google spreadsheet table, I’m sure you can see that the table looks a lot better than just numbers and text values in cells.
Now let’s have a look at some advanced formatting that you can do with the data to make it more meaningful and readable to the users.
Google Sheets Table Format – Applying Alternate Colors to Rows
One thing you can do to dramatically improve the readability of your table is to apply alternate shades to the rows in the data.
And thanks to Google Sheets rolling out new features every few days, there is now an inbuilt method to quickly alternate row colors in Google Sheets.
Here is how to make tables in Google Sheets with alterating color rows:
- Select the entire data set.
- Click the Format option to open the format menu
- In the options that show up, click on ‘Alternating color’
As soon as you do this, you notice that two things happen – the color of the header row changes and alternate rows get a slightly darker shade than the remaining ones.
At the same time, you’ll see add alternating colors pain that opens up on the right side of the Google Sheets worksheet.
Here, you can specify the custom colors that you want for the column headers and the color you want for the alternating rows.
Once done, your data should look something as shown below:
You can also choose color options and whether or not you want to include headers or footers in your table in the options.
Google Sheets Table Formatting – Sort the Columns
Our aim with all the formatting is not just to beautify it but also to make it easier for the user to understand the data.
And one of the simple things that can really help the user go through large datasets would be to sort the data using one column or multiple columns.
In our example, I would show you how to sort the data so that all the regions are presented together and within each region then sought the data by sales value.
Let’s see how to do this:
- Select the entire data set
- Click the Data option in the menu
- Click on the ‘Sort range’ option. This will open the Sort dialog box in Google Sheets
- Check the ‘Data has header’ rows option
- In the Sort by drop-down, select Region
- Click on the ‘Add another sort column’ option
- In the then by drop-down, select Sales
- Set the order to Z to A for Sales
- Click on Sort
The above steps would sort your data based on two columns – first by the region and then buy the sales in that region.
This helps us get all the data for one specific region as a group and show the sales in descending order for that region.
So if your manager is going through this report and she’s only interested in the data for Europe, she doesn’t have to scour through the entire report. she can easily spot the cluster for Europe and go through the data for it.
Pro Tip: In case there is a possibility that you may need the original data set back, it’s a good idea to create a backup copy of the data.
Advanced Google Sheets Format as Table Tricks – Highlight High/Low Values
When working with numbers, one thing that can help a user quickly go through your data would be to highlight important data points.
In our example, it would help the user if we the top and the bottom value, or multiple top/bottom values.
Let me show you how to highlight the top three and the bottom three values in the sales column using conditional formatting.
Below are the steps to highlight the top 3 values:
- Select the sales data (where you want to highlight the cells)
- Click the Format tab
- Click on Conditional Formatting. This will open the Conditional format rules pane on the right
- Click on the ‘Format cells if’ dropdown
- In the options that show up, click on the ‘Greater than or equal to’ option
- In the field below, enter the following formula: =LARGE($D$2:$D$14,3)
- Select the color in which you want to highlight the top three values. in this case, I’ll go with the default green
- Click on Done
The above steps would highlight the top three sales values in our table in Google Sheets.
Now, if you also want to highlight the bottom three values, all of the same steps above, and use this formula instead (and choose the ‘Less than or equal to’ option in step 5):
Also, to differentiate it from the top three values, make sure to choose a different color (maybe red).
Once done, your table would look something as shown below:
Just like we have highlighted the top three in the bottom 3 values in the table, you can also try the following to make your table more readable:
- Highlighting all the cells with a negative value in a red color
- Highlight rows for a specific region or records where the sales value is more than or less than a specific values
How to Make a Table in Google Sheets With Colors and Indicator Arrows
If you’re using your table for financial details, you can add indicators to the percentage changes. Here’s how:
- Highlight the column you wish to add the indicators to and head to Format > Number > Custom number format
- Enter the following into the custom number formatting box 0.00%▲;0.00%▼;0.00%
This will make the figures show an arrow depending on whether the percentage is a positive or negative number.
Then, you can use two conditional formatting rules to make the text green or red. Follow the steps from the previous examples and simply make the rules, greater than 0 and less than 0 and set the colors to green and red.
Remove Formatting from Table in Google Sheets
In case you do not like any of the formattings that you have applied to the table in Google Sheets, you can easily remove them as well.
In most cases, the way to remove a format would be to simply go to that same format option. Most probably, there would be a way to remove the format.
And in case you want to remove all the formatting from the table and start over, follow ṭhe below steps:
- Click the Format option in the menu
- Click on the Clear Format option (or use the Keyboard shortcut Control + )
The above steps would remove all the formatting in a table in Google Sheets.
Related Reading: Build a Table of Contents in Google Sheets
How to Name Your Table and Access It via Its Name
To name the table, you simply have to highlight the range, go to Data > Named ranges, name the range, and then click Done.
You can then reference the table by using the same instead of typing the range into formulas. For example:
=AVERAGE(QUERY(A Named Table,”SELECT C”))
How to Make a Filtered Table in Google Sheets
You can also make filtered table that will allow you to display specific data based on a criterion.
- Select a header row in the table.
- Go to Data > Create a Filter
- Click on the Filter sign in the header row.
With the filter table, you can choose the data you want to filter out or any other actions like sorting.
Filtering helps you focus on the specific parts of your data and makes it easier to do calculations for them. A more advanced version of this would be a pivot table.
How to Make a Searchable Table in Google Sheets
Searchable tables are very convenient, especially when working with formulas. You can search for specific data in the table and use it in the formula instead of having to manually look for and select the range.
To make a searchable table, you’ll need to use named ranges. Here’s how:
- Select the cells in your table.
- Go to Data > Named ranges.
- Type in the name of your table.
- Click Done.
The table now appears in the “Named ranges” sidebar.
Next, we can name a specific range in the table, like a column.
- Select the range.
- Click Add a range.
- Name the range.
- Click Done.
Now if you want to use the range in a formula, for example, finding the sum, you can type the formula and, in the brackets type the name of the range.
This will bring up the named ranges, and you can choose the range you want to work with.
Frequently Asked Questions
Which Button Do You Press to Create a Table in Google Sheets?
Unfortunately, no button does it all, you have to mess with the formatting of the cells to create a defined google sheets table.
How Do I Format a Table in Google Sheets?
You can use a number of different formatting tricks to format a table in Google Sheets, but the simplest is to just change the outline of the cells with the borders shortcut in the toolbar.
How Do I Make a Table Look Good in Google Sheets?
You can add alternating colors for the rows and use conditional formatting to highlight certain parts of your table. Both of these methods are explained in the article above.
How Do I Make a Google Spreadsheet Pretty?
You should format each of your tables. One easy way is to use alternate formatting. You can:
- Highlight the date you wish to format
- Navigate to Format > Alterternating colors
- Choose the colors to use
You could consider building pivot tables from larger data sets.
Its also not a bad idea to add charts to make your data more accesible.
How Do I Create Multiple Tables in Google Sheets?
A spreadsheet essentially is a giant table. You can break it up into several by selecting the data you want in one table and changing the formatting around
So these are some of the things that you can do to convert your bland-looking data into a nice-looking table in Google Sheets.
You don’t need to use all these methods that I have covered in this tutorial. feel free to choose the ones that suit your need.
And of course, as you work more with Google sheets, you can come up with your own formatting style to make a table in Google Sheets.
I hope you found this tutorial on how to make a table in Google Sheets useful.
Other Google Sheets tutorial you may also like: