Quick Links
Tables are a fundamental part of any Google Sheets spreadsheet. They help you visualize your data, make filtering and sorting simple, ensure consistency, and can be referenced in formulas to aid accuracy and save time.
The 3 Ways to Create a Table in Google Sheets
Whether you’re starting with a blank canvas or already have some data in yourGoogle Sheetsspreadsheet, creating tables is straightforward.
Turning Blank Cells Into a Table
To create a table from scratch in Google Sheets, select two or more adjacent cells, and in the Format tab, click “Convert To Table.”
As you can see in the screenshot below, if the range you select includes cells in row 1, Google Sheets automatically adds a gray banner across the top of the spreadsheet where the table name and other options sit. Then, default column headers are placed in row 1, and you can click these to replace them with more suitable labels.
The banner and column headers are frozen, meaning they remain in view if you scroll down.
Turning Existing Data into a Table
If you already have a dataset in your Google Sheets file that you want to format as a structured table, first, verify each column has an appropriate header. Then, select any cell in the range, and in the Format tab, click “Convert To Table.”
Once the table is created, click the down arrow in any of the column headers to apply filters or sort your data.
Using Pre-Built Google Sheets Tables
A final way to create a table in Google Sheets is to use one of the program’s pre-made templates.
Type@Tablesinto any cell, and press Enter.
Now, in the Tables pane on the right, click the arrow next to the relevant table category, and hover over any of the table types to see a preview. Once you’ve settled on a table that suits your needs, click “Insert.”
If the active spreadsheet already contains some data, the new table is inserted on a new tab. On the other hand, if the active worksheet is blank, the table is added there.
Naming Tables
Now that you’ve created your table, one of the first things you should do is to name it. This makes it easier for collaborators to understand at a glance what the table contains, and descriptive table names are easier to read than direct cell references when used in formulas (we’ll come to this later).
To name a table, click the placeholder name in the contextual tab above the first column, and type the new name.
Although you’re free to type pretty much whatever you want, there are a few restrictions. Table names can’t:
Aside from the above rules, as long as your table name is short yet descriptive, you can’t go wrong.
To jump to any table in a Google Sheets file, regardless of its location, click the down arrow in the name box to the left of the formula bar.
Selecting Column Types
Once you’ve created and named your table and inserted appropriate column headers, it’s time to tell Google Sheets what type of data will go in each column. Taking this step helps the software check that you’re entering the correct types of data into each cell in the table, thereby ensuring data consistency.
In this example, the Shop column contains a whole number, the Manager column contains text, the Revenue columns contain monetary values, and the Trend value should be a percentage.
So, to correct these, click the down arrows in the column headers, hover over “Edit Column Type,” and select the appropriate format.
Notice how data types that require a symbol—like percentages and currencies—are updated automatically, and icons are added to the column header to indicate the types of data each column should contain.
What’s more, if you enter a value into a cell in this column that doesn’t appear to align with the assigned data type, Google Sheets alerts you by adding a red tag to the cell. When you hover over that cell, you’re told that the value doesn’t match the column type.
Enter any integer or decimal.
Number > Percent
When you enter a number, the % symbol is added automatically.
Number > Currency
To change the currency, select all the cells in the column, and click Format > Number > Custom Currency.
Enter any text.
Date > Date
Double-click a cell in a Date column to bring up a calendar, where you can select a date.
Date > Date Time
Double-click a cell in a Date column to bring up a calendar, where you can select a date. Then, override the placeholder time with the correct one.
Enter a time using the HH:MM:SS format.
Drop-down
When you select this column type, the data validation sidebar appears where you can define the drop-down options.
Checkbox
This inserts a checkbox into each cell in the column. you’re able to either check and uncheck these manually or add a Boolean formula to make them work automatically.
Smart chips > People
The people smart chip lets you insert the name of a person in your organization or someone with whom you’ve been in email contact. Then, click the name to reveal more details.
Smart chips > File
The file smart chip lets you add a link to a local Google file.
Smart chips > Finance
The finance smart chip lets you enter the name of a financial organization. Then, click the name of the firm to reveal the latest market trends.
Smart chips > Place
The place smart chip links toGoogle Maps, meaning you’re able to type the name of any location worldwide. Then, click the place to see a link to Google Maps.
Smart chips > Rating
The rating smart chip turns a number from 1 to 5 into a star rating.
None
Choose this option to leave the column open to any data type.
Click “Show Placeholders” at the bottom of the Edit Column Type list to make clear what data type should be entered into each cell.
Adding and Removing Columns and Rows
As soon as you add data or a column header to a blank column to the right of an existing table, Google Sheets expands the table formatting to capture the added information. The same applies to any data you add to the first blank row at the bottom.
To insert a new column to the left of an existing column or a new row above an existing row, right-click a cell in the relevant column or row, and click “Insert 1 Table Column Left” or “Insert 1 Table Row Above.”
To delete a column or row, right-click any cell in the relevant column or row, and select “Delete Table Column” or “Delete Table Row.”
Formatting Tables
To change the appearance of a table in Google Sheets, click any cell in the table, and in the Format tab on the ribbon, click “Table Formatting.”
This brings up the Table Formatting pane on the right of your screen. There, you can (1) choose a different table style, (2) create a custom style with different color headers and rows, and (3) activate several other properties in the Options section.
Check “Show Table Footer” in the Table Formatting pane to add a total row to the bottom of your table.
Referencing Tables and Columns in Formulas
While direct references in Google Sheets link one cell to another using the column and row header labels, table references link a cell to a table and column name.
Table references can be used in non-tabular cells or within tables themselves.
For example, typing:
into a blank cell and pressing Enter adds all the values in the Revenue 2024 column of the Shop_Details table.
Rather than typing the table and column reference manually, use your mouse to select the whole column, and Google Sheets will insert the reference for you.
On the other hand, typing:
into the first cell of the Total Revenue column and pressing Enter sums the values in the two revenue columns of the Shop_Details table. What’s more, since Google Sheets recognizes that you’re likely to want the same formula to be applied to the remaining cells in the column, it offers to autofill it for you.
In both the above scenarios, if data is added to or removed from the referenced columns, the formula will adapt to these changes.
If you need to apply a single formula to a whole column, embed the formula within the ARRAYFORMULA function. For example, typing:
into a blank cell divides the sum of all the values in the Total Revenue column of the Shop_Details table by 24 to produce a monthly average over the two years.
If the table or column names change, any formulas in the same file that reference them will update automatically.
You can also use table and column references with the IMPORTRANGE function to import data from one Google Sheets file to another. For example, typing:
and pressing Enter imports the header and data of the Total Revenue column of the Shop_Details table:
You can also nest the IMPORTRANGE function within another function. Here, typing:
and pressing Enter sums all the values in the Total Revenue column of the Shop_Details table in the linked sheet.
If the table or column names referenced in the IMPORTRANGE formula change, you will need to adjust the second argument in the formula accordingly.
Pro Tip: Grouping by a Table Column
If items in a table column are repeated, you can group all the values with each item in common.
In this example, let’s say you want to aggregate the total sales made by each employee, Laura, Dave, Mike, and Tom.
To do this, click the down arrow in the Employee column, and select “Group By Column.”
Now, the sales linked to each individual are temporarily grouped together, and you can click any of the down arrows to choose how the data is aggregated in each column.
you may either save this aggregated presentation of your table by clicking “Save View” in the top-right corner of the Google Sheets window, or disregard it by clicking “X.”
If you save the view, you’re able to reactivate it anytime by hovering over “Change View” in the Data tab on the ribbon, and selecting the view you saved.
As well as formatted tables, there are many otherfundamental Google Sheets features you should get to knowif you’re new to the program. For example, you cantranslate wordsdirectly in your spreadsheet,automate tedious taskswith macros, and easilyremove duplicate values.
