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 quickly convert any tabular data into a table in Google Sheets. I will also cover some formatting tips that will help you make your table look a lot better.
Formatting a Table in Google Sheets
For the purpose of this tutorial, let’s consider the below table in Google Sheets.
I will show you all the steps that 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 would look a lot better as shown below.
So let’s get started with some basic table formatting!
Let’s start with some basic formatting techniques that will make your table in Google Sheets look a lot more professional.
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.
To do this, 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:
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.
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 to center align the header text:
- Select all the cells that have the headers
- Click on the align 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 below:
Color/Bold the 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 to change the header color and the 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:
Formatting 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, 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.
Advanced – 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 do this:
- Select the entire data set
- Click the format option in the 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 color that you want for the header and the color you want for the alternating rows.
Once done, your data should look something as shown below:
Advanced – Sort the Columns in the Table
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.
Advanced – 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
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.
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 useful.
Other Google Sheets tutorial you may also like: