How to Make a Data Table in Google Sheets (Like In Excel!)

If you’re used to Excel, new to Google Sheets, or have never created a table before, the task may seem daunting. However, don’t worry! In this article, I will show you how to insert a table into google sheets in just a few simple steps.

You will learn the basics of converting data into a table format, as well as some advanced topics on how to extend your table with more options. Let’s get started!

PS. The tutorial will take approximately 5-15 minutes to complete.

5 Things You Need To Know About Google Sheets Table Formatting

This section consists of 5 formatting tips for making a table in Google Sheets. We will start with raw data and make it neater while we go through all the points. This is what we are going to cover:

  1. Creating headings and alternating colors
  2. Make column headings sticky
  3. Aligning columns
  4. Formatting data
  5. Naming your table

Note: If you want to follow along with the tutorial, here’s a link to the raw data I’ll be using (created with Random Data Generator). Simply open it, create a copy and join me in the journey (this step is completely optional though).

After copying the file this is what you should be seeing:

Screenshot of a table that we will be working on. It contains 5 rows: First name,  last name, gender, occupation and salary filled with dummy data

And this is the final result we want to achieve:

The screenshot shows exactly the same data that was mentioned in previous images but is nicely styled, and contains filters and total sum row

Let’s jump straight to the first task.

#1: Creating Headings and Alternating Rows:

Let’s add color to the background of the heading so it stands out, and then we’ll alternate odd/even cells to make them more visually appealing.

Go to Format > Alternating colors:

Screenshot showing where to find Alternating colors in the Options Menu

Now pick your desired theme (you can use a predefined one or customize it) and click on “Done”.

Screenshot shows all predefined variations for styling a table in Google Sheets

#2: Make Column Headings sticky

When you create a table in google sheets with many entries you may want to make the column sticky so you have a point of reference when you scroll down.

Just go to View > Freeze and select “1 row”:

Screenshot shows where to find an option to freeze the row in Options Menu

When you scroll down a tiny bit, it will appear like this:

The screenshot shows how frozen row works

#3: Aligning your data

These are the best practices for aligning the data:

  • The text should be always left aligned
  • All standardized entries (i.e. IDs) and column headings should be center aligned
  • Numbers and dates should be right-aligned

It’s quite easy to align your content. Just go to Format > Alignment and pick the one you need:

The screenshot shows how to access Alignment settings from the Options Menu

For the tutorial purpose let’s align the table headings to the center (the rest of the values is correctly aligned by default):

PS. There are some shortcuts for aligning the values:

  • Press Command + Shift + L for aligning left
  • Press Command + Shift + E for aligning into the center
  • Press Command + Shift + R for aligning right

#4: Formatting table data

Now let’s use the correct format for the values we’re using.

A salary is simply a number, but it should be represented in monetary units.

It shouldn’t include any decimal places (as opposed to product prices, for example).

In various cases, you may also need to switch to another currency. I will cover this as well.

Step 1: Add a currency to your values

Highlight your data (in our case “Salary”) then navigate to Format > Number > Currency rounded to get the value without decimals:

The screenshot shows how to currency format settings from the Options Menu

If for some reason you’d like to use the decimals, you can add them from the toolbar menu:

The screenshot shows how to add/remove decimals from the toolbar

Step 2: Change the currency

Go to Format > Number > Custom currency:

The screenshot shows how use custom currency from the Options Menu

and use the search bar to find the currency you are interested in. Click “Apply” and that’s all.

The screenshot shows how to change the default currency to a custom one. Canadian dollar in this example

#5: Naming your table

You can make your work with tables in Google Sheets more efficient by naming the table. This creates a named range that allows you to reference your google sheets table later in another spreadsheet just by using its name.

Creating a named range is quite simple:

Step 1: Select all the cells that you want to name (in our case the whole table).

Step2: Click on Data > Named ranges.  

The screenshot shows how to access named ranges from the options menu

Step 3: Type the range name that you want.

The screenshot shows a field where name for the named range can be provided

Click Done.

A few tips on naming a range:

  • The range can contain only letters, numbers, and/or underscores (that means no spaces!)
  • The range can’t start with a number or the words ‘true’ or ‘false’.
  • Must be 1–250 characters.

Extending Our Google Sheets Table With Extra Features

In this section, we will go over 4 advanced formatting tips for making tables in Google Sheets more efficient. We will also add some great features to automate some simple processes. Here’s what we are going to cover:

  1. Sorting google sheets table
  2. Adding total row
  3. Filtering google sheets table
  4. Automatic calculations
  5. Conditional formatting

#1: Sorting Google Sheets Table

We’ll sort two columns at a time for the sake of training; this may come in handy in a variety of situations. We’ll start with the “Occupation” column, which will be alphabetized, and then work our way up to the “Salary” column which will be sorted in ascending order (from lowest to greatest value).

Step 1: Highlight the whole table (you can click on the range we created before)

The screenshot shows the named range that we created. It's placed on the right-hand side of the screen

Step 2: Click Data > Sort range > Advanced sorting options

The screenshot shows how to reach Advanced range sorting options from the options menu

Step 3: Make sure to check “Data has header row” as we don’t want our titles to be sorted

The screenshot shows where to find Data has a header row checkbox which is placed directly under the title

Step 4: Set “Sort by” to “Occupation” and A>Z order

The screenshot shows sort by option in the popup menu

Step 5: Click on the “Add another sort column” button

The screenshot shows add another sort column button location which is placed below the sort by option

Step 6: Set “Sort by” to “Salary” and A>Z order

The screenshot shows another row for sorting a range

Step 7: Click the sort button

Screenshot shows the Sort button that is located in bottom right corner

You will see that all the roles are alphabetically sorted and then they are sorted from the lowest value.

#2: Adding a total row

In this section of the tutorial we will cover two things:

  • We will sum up all the company’s spending
  • Then, we will apply a filter to see how much money each department costs.

Step 1: In the first row apply an orange background to two cells (“G” and “H” as we will need the “F” column at a later stage). Put “Total” in the first one.

Step 2: In the second cell we will add all the salaries together.

You’re probably thinking of using a SUM function. It appears to be a good idea, but there is one catch. It will just sum up all the values from the cells even if they are hidden.

So if you want the value to adjust when you apply filters to the table, you should use the SUBTOTAL function instead.

Here’s more information about the SUBTOTAL function from the official docs, it will explain why we used “9” as a first argument. (“9” stands for a SUM in SUBTOTAL aggregation function).

Add this to the cell:


And click Enter

You will see that it appears as a raw number. We need to apply a currency format as we did previously so it looks like that:

The screenshot shows total row that is filled with data

Alright, we have the total value that is paid to all employees as a salary. Let’s add some filters now.

#3: Filtering Google Sheets Table

Now let the fun begin. Let’s add some filtering options so for example we can see how much we are paying to the Florists.

Step 1: Highlight the “Occupation” table column

The screenshot shows highlighted "Occupation" table

Step 2: Click on Data > Create a filter

The screenshot shows how to create new filter using options menu

Step 3: Click on the filter icon

Close up on a place where the filter icon is located

Step 4: Clear all selections and type “Florist” in the search bar, then click the OK button.

The screenshot shows how to filter column data with custom search string

You will see that the table shows only 2 entries and the Total amount has changed as well.

Filtered table results that consist only of people who are hired as florists

That was pretty easy. Let’s jump to the next step then:

#4: Automatic calculations

Let’s assume that every employee gets a 5% rise each year. Obviously, you wouldn’t like to calculate it manually so let’s just use a simple function that will calculate it for us:

Step 1: Let’s unfilter the data first and show all available entries.

Just click on the filter icon again, click on the “Select All” option and confirm with the OK button:

Filtering options submenu setttings

Step 2: In the “F” column add a new heading which will be “Next Year Salary”

Step 3: Let’s calculate the salary after a pay rise for the first entry.

This is just simple math but you can do more advanced calculations if you want to. Just apply the below to the first entry and click Enter:

=(E2 + E2 * 0.05)
How to use a formula in the Next Year Salary column

You will see that the amount was automatically calculated for you.

Step 4: Apply the same logic to all other entries

Click on the cell with value and look for a small blue square in the bottom right corner.

Click on it twice so the values can be populated for all other entries:

Next year's salary column is filled up with new values (old salary increased by 5%)

All done, you know how much each employee will earn in the next year.

#5: Conditional formatting

As our final step let’s apply some conditional formatting to the table.

If a company wants to pay their employees a minimum salary of $55,000 next year, how can they easily identify who those people are?

Step 1: Highlight the “F” column

Step 2: Go to Format > Conditional Formatting

Screenshot shows how to access conditional formatting options from the Options menu

Step 3: Set the Conditional Format Rule

Pick “Less than” and then enter the “55,000” value. You can leave the default formatting style or play with it if you wish

The screenshot shows conditional format rules option in Google Sheets

Boom! You can now see who among your staff is due for a substantial raise next year.

Your table should look like this:

Screenshot shows final version of our data table that looks exactly like one you can create in Excel
The final version of the table

Some Questions You May Have

Do Google Sheets have a “Format as a table” feature?

No, Google Sheets does not have Format as a table as Excel does, but you can convert your data to look and behave as a table using a few simple steps explained in this tutorial.

How do I create a totals row in Google Sheets?

You can easily create a totals row in Google Sheets by using the SUBTOTAL function. The value will be auto-updated when you filter the data or add/remove entries in your data table.

Final thoughts on Making Table in Google Sheets

There you have it. You created a Google Sheet table that looks like the one you can create with Excel. You converted your data to a table so it looks organized and is easy to read.

You can add as many columns, rows, and filters as you wish. You can also use conditional formatting for even more data insights.

Do you use tables when working with data in Google Sheets? Do you have any tips or tricks to share? Let me know in the comments below!

Leave a Reply

Your email address will not be published. Required fields are marked *