Tables in Google Sheets turn a plain range into a structured, named table with typed columns, built-in filters, group-by views and formulas that refer to columns by name. If you have used tables in Microsoft Excel, the idea is the same, and it makes everyday data much easier to keep tidy.
This tutorial shows how to insert a ready-made table, set column types, convert your own range into a table, change its colours, group and save views, rename it and use table references in formulas. The examples use a small Sales_Data table with Name, Date, Sales, Target, Target Status and Check Box columns, and a free practice file is included.
🎥 Watch the Video Tutorial
What Tables in Google Sheets Give You
| Feature | Plain range | Table |
|---|---|---|
| Name | Referred to by cell addresses, e.g. C2:C100 | Has its own name, e.g. Sales_Data |
| Column types | Any value can go in any cell | Each column can be Number, Text, Date, Dropdown, Checkbox or Smart chips |
| Formatting | Applied by hand | Header and alternating row colours applied automatically |
| Filtering and grouping | Needs a separate filter | Filter, sort and group from each column’s menu, with savable views |
| Formulas | =SUM(C2:C100) |
=SUM(Sales_Data[Sales]), which grows with the table |
Insert a Ready-Made Table
When you open a new spreadsheet, a Tables pane may appear on the right with ready-made layouts. The Featured list includes options such as Event tasks, Project tasks and Content tracker. Hover over one and click Insert.
The Project tasks template, for example, creates columns for Task, Priority, Owner, Status, Start date and End date. Priority is a drop-down from P0 to P3, and Status offers Not started, In progress, Blocked and Completed.
If the pane does not appear, right-click any cell and choose the tables option, or use Insert > Tables to open it.
Change a Column’s Data Type
Column types stop bad data from creeping in, for example text in a number column.
- Click the small down-arrow next to the column name.
- Choose Edit column type.
- Pick Number, Text, Date, Dropdown, Checkbox, Smart chips or None.
The same menu has Sort column, Filter column, Group by column, Insert 1 table column left/right and Delete table column, so most table tasks start from this one arrow.
Convert Your Own Data Into a Table
- Select your data range, including the header row.
- Go to Format > Convert to table (or press Ctrl + Alt + T; on a Mac, Cmd + Option + T).
- Alternatively, right-click the selection and choose Convert to table.
Sheets uses the first row as headers, and you can set or change each column’s type afterwards. In the example, Sales and Target are shown as currency and Check Box is a checkbox column (note the checkbox icon in its header).
Change the Table Colours
- Click anywhere inside the table.
- Go to Format > Alternating colors.
- In the pane, pick one of the Table default styles or build your own under Custom styles by setting the Header, Color 1 and Color 2 shades. You can also switch a Footer row on.
Group Data by a Column
- Click the down-arrow next to a column name, for example Name.
- Choose Group by column.
- The table is split into sections such as Name: PK, Name: Raj and Name: William, with a bar at the top reading Temporary group by Name.
A temporary grouping disappears when you close it. To keep it, click Save view at the top right, accept or change the suggested name (for example Group by Name) and click Save.
Group views do not change the layout for other collaborators, which makes them ideal for checking one person’s rows on a shared sheet.
Create a Filter View From the Table
- Click the table menu icon next to the table name.
- Choose Create filter view (or Create group by view).
- Filter or sort any column, for example show only rows where Target Status is Not Met.
- Click Save view and give it a name.
The saved view is available by name whenever you need it again. For the full picture of how filter views work, see our guide to Filter and Filter View in Google Sheets.
Rename the Table
Click the table name on the tab above the header (the default looks like Table1), type a new name such as Sales_Data and press Enter. Use a short name without spaces, because you will type it in formulas.
Using Table References in Formulas
Instead of cell ranges, refer to a column by TableName[Column name]:
=SUM(Sales_Data[Sales])Worked example: if Sales_Data held only the four 1/1/2024 rows from the screenshot (PK $68, Raj $47, William $35, Jack $25), this formula returns $175.00. Add a fifth row inside the table and the total updates without editing the formula, because the reference grows with the table.
A few more patterns that work with the same table:
Count rows that met target: =COUNTIF(Sales_Data[Target Status],"Met")
Total sales for one person: =SUMIF(Sales_Data[Name],"PK",Sales_Data[Sales])
Average target: =AVERAGE(Sales_Data[Target])
Ticked check boxes: =COUNTIF(Sales_Data[Check Box],TRUE)In the first ten rows of the sample, five rows are Met and five are Not Met, a quick way to test the COUNTIF version. Column names with spaces, such as Target Status, work inside the brackets as they are. The Target Status column follows the rule “Met when Sales is at least Target”, which you can build with IF; see checking sales targets with the IF function.
Common Problems and Fixes
- A value shows an error flag. It does not match the column type (for example text in a Number column). Correct the value or change the column type.
- Convert to table is greyed out. The selection overlaps an existing table or includes merged cells. Unmerge and select a clean range.
- The formula returns an error. The table or column name inside the brackets does not match exactly. Check the spelling against the header text, including spaces.
- Someone’s grouping changed my view. They used a normal filter. Use group-by or filter views so each person keeps their own layout.
Tips for Working With Tables
- Give every table a meaningful name before writing formulas against it.
- Use Dropdown columns for fixed lists such as status or priority; our tips on faster dropdowns in Google Sheets apply here too.
- Use a Checkbox column for done/not-done tracking, as in this to-do list with checkboxes in Google Sheets.
- When you need totals by category rather than a list, summarise the table with a pivot table in Google Sheets.
If you are newer to spreadsheets and want a structured route from tables to formulas and reports, the learning paths on NextGenTemplates Academy lay out the steps in order.
Frequently Asked Questions
How do I create a table in Google Sheets?
Select your data including headers and choose Format > Convert to table, or press Ctrl + Alt + T. You can also insert a ready-made table from the Tables pane.
Are Google Sheets tables the same as Excel tables?
They are similar: both have names, styled headers, structured references such as Sales_Data[Sales] and automatic expansion. Google Sheets adds column types and savable group-by views.
Can I use table names in formulas?
Yes. Write TableName[Column], for example =SUM(Sales_Data[Sales]). The reference includes new rows added to the table.
How do I convert a table back to a normal range?
Open the table menu next to the table name and choose the option to revert or convert the table back to a range. Your data and formatting values stay in place.
Do group-by views affect other people using the sheet?
No. Group-by and filter views change only your own view, so collaborators keep seeing the full table.
Which column types can a table use?
Number, Text, Date, Dropdown, Checkbox, Smart chips, or None for no type, as shown in the Edit column type menu.
Wrapping Up
Tables in Google Sheets give a plain range structure: a name, typed columns, consistent colours, savable group and filter views, and formulas that read like sentences. Convert one of your existing lists today, rename it, and try =SUM(TableName[Column]). The practice file below has the Sales_Data table ready to experiment with.
📅 Last updated: October 2026
