If you work with a lot of data, especially in Google Sheets, chances are you have your data in tabular format. But what if we told you that you could have your tables looking all shades of amazing and fabulous, would you be interested?
In today’s guide, we will show you how to make and format a table in Google Sheets.
Instead of having your data as simple entries in a spreadsheet grid, this guide will show you some amazing ways to convert your data into a well-formatted table. At the end of the day, this would make your data a lot more readable.
Note: If you have used Excel in the past, you might be aware that it has a built-in table feature. However, with Google Sheets, there isn’t any table functionality at the moment. But not to worry, today’s guide will provide you with some exciting tips you can use to make and format a table in Google Sheets.
How to make a table in Google Sheets with formatting
For today’s guide, we will be working with the following sample data set.
While Google Sheets doesn’t currently have a shortcut that lets users format tables, we will show you some amazing steps you can take to convert this bland-looking table into something fabulous.
After we are done formatting, your table should look all shades of amazing. So let’s kick off with some basic table formatting, shall we?
In this tutorial, we will explore some remarkable formatting techniques that will have your table in Google sheets look more professional.
How to make and format a table in Google Sheets: Applying the borders
One thing that will remarkably transform the tabular data you currently have in your Google Sheets is to apply borders. Surprisingly, this is super easy to do. Once you apply the borders, your data should start looking a lot like a table.
Now, the million-dollar question is, how do you insert a table in Google Sheets by applying a border? Read on as we delve deeper.
- Start by selecting the data in your spreadsheets. You can do this manually or use the good old keyboard shortcut, “Control + A” if you use a Windows computer or “Command + A” on Mac.
- Once you have selected the data in your spreadsheet, the next thing you want to do is navigate to the Borders icon in the toolbar.
- From the options that pop up, you want to choose the Border color and apply a border to all your cells (simply choose the “All Border option”)
Once you’re done with this step, your spreadsheet should look something like this:
How to insert a table in Google Sheets: Aligning your data
If you take a look at our sample data, you’ll notice that numbers are aligned to the right while text strings are aligned to the left. This is Google Sheets’ default setting.
While there is nothing wrong with that, in some cases, you’d want your data to look center aligned. This makes it look a lot like you used Google Sheets to insert the table.
Even with your data set, you may sometimes want the columns center-aligned instead of going with Google Sheets default option.
With our sample data, we will align all the headers and the numbers in the scores columns. Read on for all the steps for formatting tables in Google Sheets via center aligning the header text:
- First, select all the cells that have the headers.
- After highlighting all the cells with the headers, you want to navigate to the align icon on the toolbar.
- Finally, click on the Center align icon and watch the magic happen.
If you’re a bit confused about the steps outlined above, you can watch this short video to get a better perspective.
The steps outlined above will center all the text featured in the selected cells.
Having center aligned all the headers, we want to do the same for the numbers in Column D. Like you did for the headers, you want to highlight all the data featured in column D and click on the align option in the toolbar.
Here is a video demonstration showing you exactly how to go about it.
How to create and format a table in Google Sheets: leveraging colored/bold headers
Do you know that by tweaking the colors of the cells that feature your header, you can greatly improve the readability of the data in your table? And guess what? You can even bolden the information in the cells for better clarity.
Here is how to bolden and color format the headers in your spreadsheet.
- Start by selecting all the cells with headers.
- After highlighting the headers, the next thing is to click on the Bold Icon on the toolbar. If this sounds confusing, you can use the keyboard shortcut “Control + B” if you’re using Windows or Command + C for Mac.
- With your headers bolded, you want to proceed to click on the fill color icon located in the Toolbar. Here, you’ll need to choose the color you’d like to apply.
Here is a short video demonstration that makes the entire process easy and hassle-free:
Tips: If you opted for a dark color, we recommend choosing something that is less darker. The reason for this is that you want the text to be visible. From the video above, you’ll notice we opted for a lighter shade of blue.
How to make make and format a table in Google Sheets- Formatting the numbers
From what you can see so far, you’ll agree that our table looks way better than when we first started. But this isn’t enough, as we want to take things up a notch, so our table looks even better.
The next thing we want to do is format the numbers in our table. Doing this will make our data easier to read and meaningful too.
As of right now, the numbers we have in the scores column look a little boring. So let’s switch things up.
To make the numbers in the Scores column better, we can add a thousand separator along with a currency symbol. Let’s do that, shall we?
- Start by highlighting the numbers featured in the Scores column.
- Navigate to the toolbar and click on the “format as a currency option.” This action will add a dollar sign in the front of the numbers. Not only that, but it would also add a thousand separator along with two numbers after the decimal point.
Here is what your spreadsheet should look like after executing these steps:
- From the screenshot above, you’ll notice the numbers under the Scores column have decimal points. You can change this if you don’t want the decimal points. To remove the decimal points, all you need to do is click on the “Decrease decimal places” icon twice.
After doing that, your table should look exactly as depicted below:
If you have followed our guide so far on how to make and format a table in Google Sheets, you’ll agree that the table looks a lot better than when we started.
But we aren’t done yet, as we want our table to look even more professional. To this end, we want to explore some next-level formatting tips that will make your table meaningful and readable to users.
Formatting your table- Applying alternate colors to rows
While there are many exciting things you can do to remarkably improve the readability of your table, we love the idea of applying alternate shades to the rows in the data.
And since Google Sheets is always launching new and exciting features every now and then, we are thrilled to announce that Google Sheets now has a built-in feature that lets users quickly alternate row colors in Google Sheets.
Here is how you can apply alternating colors to your tables in Google Sheets:
- You want to first highlight the entire data set in your spreadsheet. You can either do this manually, which might waste time, or use the keyboard shortcut “Control + A” on Windows or Command + A on Mac for quick results.
- With your data set now highlighted, head over to the Format option in the menu.
- From the option that pops up, choose “Alternating color”
Once your changes have been effected, you’ll instantly notice that the color of the header row has now changed. Also, you’ll notice that alternate rows now have slightly darker shades than the others.
Another thing that is worth mentioning is the Alternate colors editor that pops up on the extreme right of your spreadsheet. This editor lets you customize the colors you want for both your header and alternate rows.
Once you’re done making all the desired changes, your table should look like this:
Make and format a table in Google Sheets- Sorting the columns
Our goal with this guide is not just to beautify the table. While that’s important, we also want to have the table look professional so that the data is easy to understand.
While we have outlined a few ways you can format your table in Google Sheets to look professional, we now want to show you how to sort the data. Doing this will greatly improve the look of your table and help users understand a large data set right from the get-go.
In this Google Sheets tutorial, we will show you how to sort the data so that all the regions within our spreadsheet are presented together. Also, we want to ensure that for each region, we sought the data by scores value.
This may sound a bit confusing, but you’ll get the hang of what we mean when we jump into the practical aspects.
Let’s dive right in, shall we?
- The first thing we want to do is highlight the entire data set. To save you all the hassles of doing it manually, you can use the keyboard shortcut “Control + A” if you use a Windows computer or “Command + A” on Mac.
- With the entire data set highlighted, head over to the Data option in the menu.
- Then navigate down to the “Sort range” option. You should notice three options. Select the “Advanced range sorting” option. This will launch the Sort dialog box in Google Sheets.
- The next thing you want to do is check the “Data has header row” option. After that, click on the Sort by dropdown and select Region.
- Click on the “Add another sort column option.
- From the “then by” menu that pops up, select Scores.
- After choosing Scores under the “then by” menu, you need to set the order for Scores to “Z to A”
- Finally, click on Sort and watch the magic happens.
By executing these steps above, your data will be sorted based on two columns- first by regions and then by the scores in that region. Take a look at our sample sheet for more clarifications.
From the screenshot above, you’ll notice that sorting the columns has allowed us to aggregate data from specific regions as well as show scores in descending order for those regions.
So let’s say you are a manager perusing this report and you’re only interested in the data from Asia; you won’t have to spend hours going through the entire report. You can easily see the cluster for Asia and go over the report for that region. Isn’t that just amazing?
Tips: In case you may need the original data in the future, we recommend creating a backup copy you can access later.
Advanced tricks for formatting tables in Google Sheets- Highlighting high and low values
When dealing with a lot of numbers, one thing that can help users quickly peruse your data would be to highlight the important data points.
And in today’s guide, we will show you some tricks to help you highlight some important numbers in your data set. And just so you know, the entire process only takes a couple of minutes.
For this tutorial, we want to highlight the top three and bottom three values in the Scores column. And we will do this using conditional formatting.
Read on for the steps to execute conditional formatting in Google Sheets:
- Start by selecting the data in the sales column
- With the data in the Scores column highlighted, navigate to the Format tab and click on Conditional Formatting. This action will launch the Conditional Format rules pane on the extreme right of your spreadsheet.
- Head over to the Conditional format rules editor located on the extreme right of your spreadsheet and click on the “Format cells if” dropdown.
- From the numerous options that pop up, we want to select the “Greater than or equal to” option.
- A field should appear below. Now, type in the following formula: =LARGE($D$2:D$14,3)
- Now, choose the color you want the top three values to be highlighted. For this guide, we will choose a lighter shade of blue.
- With that done, you can then click Done.
After executing these steps, you’ll notice that the top three values in our sample spreadsheet have been highlighted. Take a look at the below screenshot to see what we mean.
Since we mentioned earlier that we intend to highlight the top three values, which we have done so far, we also want to repeat the same process to highlight the bottom three values. And just so you know, the steps are exactly the same as the above. The only difference here is that we will use a different formula. Also, we need to choose the “Less than or equal” option instead of the “Greater than or equal to” option we used for the earlier steps.
Here is the formula you want to use to highlight the bottom three values:
Also, to differentiate the result from the top three values we highlighted earlier, we will choose a lighter shade of green.
If you did everything exactly as we explained, your table should look like the below image:
After highlighting the top three values and bottom three values, you can still make some tweaks so your table is more meaningful and readable to users. Here are other exciting ways to make your table more readable:
- Try highlighting all the cells with a negative value. Ensure that the color is different from others.
- Try highlighting rows for specific regions where the scores value is either more or less than a specific value.
Keep in mind that these are some suggestions. At the end of the day, you have the final say as to how you want your table to look. Today’s guide merely shows you some unique ways you can make and format a table in Google Sheets.
In a nutshell
It can be a tad challenging to make and format a table in Google Sheets. Sure, Google Sheets provides you with a gridded spreadsheet where you can easily enter your values across different cells and columns, but when it comes to making a table and formatting it in Google Sheets, you have to be creative and think outside the box.
And as we mentioned right from the onset, Google Sheets doesn’t currently have built-in functionality for making tables in Google Sheets. So to make and format a table in Google Sheets, you need a bit of creativity.
Thankfully, you don’t have to do all the hard work, as we have done all that for you. In today’s tutorial on how to make and format a table in Google Sheets, we showed you several tips and tricks on how you can effortlessly make and format a table in Google Sheets.
From applying borders to sorting the data to leveraging colored and bolded headers to formatting numbers and more, today’s guide explained how anyone, regardless of their Google Sheets experience, can make and format a table in Google Sheets without a hassle.
We even went the extra mile to show you screenshots as well as short videos detailing how to execute the steps outlined in today’s guide. If, for any reason, you find it challenging to execute some steps, feel free to check the numerous screenshots and videos embedded within this post for guidance.
We know that we covered several tips and tricks for making and highlighting tables in Google Sheets. However, you can always choose which tips and tricks you want to use for your table.
For other exciting Google Sheets tutorials, feel free to check our blog, as we have many exciting Google Sheets tutorials you’ll find helpful.
Frequently asked questions
Is there a dedicated feature for creating a table in Google Sheets?
Even though Google Sheets is known to roll out new features every now and then, the Google Sheets team is yet to launch a feature that lets users create tables in Google Sheets. To make and format a table in Google Sheets right now, you’d have to be creative and think outside the box.
In today’s guide, we showed you a few ways to make and format a table in Google Sheets. So in case you need to create one, feel free to explore the different tips and tricks highlighted in this tutorial.
How can I format a table in Google Sheets?
Formatting a table in Google Sheets has to be one of the easiest things to do, and that’s because Google Sheets provides you with different formatting options. From formatting numbers to applying borders and more, there are plenty of simple changes you can execute to make your tables in Google Sheets look professional.
Can I make my table look Good in Google Sheets?
Google Sheets provides you with several options you can use to make your table in Google Sheets look good. To start with, you can add alternating colors to the rows. Not just that, you can also use conditional formatting. This will highlight specific parts of your tables.
We covered both of these methods in today’s guide, so feel free to check that out if you’re looking to make your table in Google Sheets look good.
Disclosure: This page may contain a few affiliate links, which means if you buy something through them, we may get a commission (without any extra cost to you).