Converting a Range to a Table and Vice Versa in Google Sheets

Google Sheets has recently introduced several features, with one of the latest being the ability to insert tables. You can now insert custom tables and also convert a range to a table in Google Sheets. This tutorial will guide you through the process of converting a range to a table and vice versa.

Until now, we’ve been relying on a workaround (you can find it here: How to Create Self-Formatting Tables in Google Sheets (With a Simple Initial Setup)), which has several limitations. However, we no longer depend on such workarounds.

Before you begin, ensure that your data meets the following three basic requirements to convert a table to a range in Google Sheets:

  1. There should not be any formulas in the top row of the range.
  2. If there isn’t a header row with field labels, insert a blank row at the top of the range.
  3. There should not be merged cells in the range.

Once you meet these requirements, you can proceed to convert a range to a table in Google Sheets.

Converting a Range to a Table in Google Sheets

Example #1: Range with Header Row

I have the following sample data in the range A1:E6 where A1:E1 contains the field labels “Product”, “Q1 Sales”, “Q2 Sales”, “Q3 Sales”, and “Q4 Sales”.

Sample Data Set: Product and Q1-Q4 Sales

Here are the step-by-step instructions to convert this range to a table:

  1. Select the range A1:E6.
  2. To open the context menu, right-click any cell within the selected range.
  3. Select “Convert to table”.
Context Menu Option to Convert a Range to Table in Google Sheets

It’s as simple as that.

Example #2: Range without a Header Row

Let’s consider the same sample data as above but without the header row. In other words, the data is in A2:E6, and A1:E1 is empty.

You can follow the same steps as above: select A1:E6, then right-click any cell within the selected range, and choose “Convert to table”.

Google Sheets will add field labels in the top row like “Column 1”, “Column 2”, … “Column 5”.

A table with default field labels automatically generated

Converting a Table to a Range in Google Sheets

To convert a table back to a range, follow these steps:

  1. Open the “Table menu” by clicking on the dropdown next to the table name at the top left corner of the table.
  2. Then select “Revert to unformatted data”.
Convert Table to Range Option in Google Sheets Context Menu

Optionally, Go to the View menu and click on Freeze > No rows.

Conclusion

Unlike a data range, a table offers several advantages, particularly in data formatting. You can assign data types to columns by clicking the dropdown in the header of each column and selecting “Edit column type”. This feature is very useful when multiple users update the table with content, as it helps avoid mixed data type issues in columns.

Another notable feature is the use of table names in formulas. So, when the table grows, the formula will automatically adjust to use the correct range.

Example formulas:

=SUM(Table1[Q1 Sales])

Where ‘Table1’ represents the table name and ‘Q1 Sales’ represents the field label of the second column (as per example #1).

=QUERY(Table1[#ALL], "Select A, sum(C) group by A")

Where ‘Table1’ represents the table name and #ALL represents all fields in the table (as per example #1).

I hope you enjoy using this new Google Sheets feature and find it helpful when converting your ranges to tables and vice versa in Google Sheets.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.