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 your Google Sheets spreadsheet, 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.
Related
How to Freeze or Hide Columns and Rows in Google Sheets
Focus on your most important data.
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, make sure 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 @Tables into 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:
- Be “TRUE” or “FALSE,”
- Be the same as an existing table name or cell reference,
- Start with a number,
- Contain more than 255 characters,
- Contain special characters (other than underscores), or
- Contain spaces (these are changed to underscores by default).
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.
Here are the column type options:
Column Type |
Details |
|---|---|
Number > Number | 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. |
Text | 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. |
Date > Time | 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 can 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 to Google Maps, meaning you can 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.
Related
This One Keyboard Shortcut in Google Sheets Will Change How You Edit Formulas
Change a reference type in an instant.
Table references can be used in non-tabular cells or within tables themselves.
For example, typing:
=SUM(Shop_Details(Revenue 2024))
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:
=SUM(Shop_Details(Revenue 2023)+Shop_Details(Revenue 2024))
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:
=ARRAYFORMULA(SUM(Shop_Details(Total Revenue))/24)
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:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d...","Shop_Details((#All),(Total Revenue))")and pressing Enter imports the header and data of the Total Revenue column of the Shop_Details table:
- The first argument is the URL of the file containing the table, enclosed in quotation marks.
- The second argument, also in quotation marks, references the Total Revenue column of the Shop_Details table, with (#ALL) telling Google Sheets to also include the column header and total row in the import.
Related
How to Use the IMPORTRANGE Function to Import Data From One Google Sheets File Into Another
This powerful function makes duplicating data simple.
You can also nest the IMPORTRANGE function within another function. Here, typing:
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d...","Shop_Details(Total Revenue)"))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 can 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 can 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 other fundamental Google Sheets features you should get to know if you’re new to the program. For example, you can translate words directly in your spreadsheet, automate tedious tasks with macros, and easily remove duplicate values.