How to convert table to a normal range in Excel and turn data range into a table

The article looks at how to quickly turn a cell range into a table using the default or selected table style and how to convert the table back to a normal range.

Table is one of the most useful things in Excel that makes managing, calculating and updating data a lot easier. Under certain circumstances, however, a table format may not suite your needs. Luckily, there is a quick way to turn a table into a normal range without losing your data. And the other way round, you can easily transform a cell range to a fully-functional table whenever you need it.

How to convert a table to a range in Excel

The fastest way to transform the table into a normal range is this: right-click any cell in your table, and then click Table > Convert to Range.
Convert the table to a normal range.

The same operation can also be performed using the ribbon:

  1. Select any cell in the table to activate the Table Design tab.
  2. On the Table Design tab, in the Tools group, click Convert to Range.
    Convert to Range button

Either way, Excel will display a prompt asking you to confirm your intention:
Confirm that you really want to convert the table to a normal range.

Once you click Yes, the table is immediately changed backed to a normal range of cells:
The table is converted to a range.

Note. This method changes a table to a usual range and removes all features of an Excel table such as automatic expansion, structured references, filter buttons, etc. However, it does keep the table style formatting such as fill and font colors, borders, and the like. If you don't want it, remove table formatting before converting the table to a range.

How to convert data range to a table

If you'd like to leverage many wonderful features included with the Excel table format, you can turn your current data into a fully-fledged table by using one of the following approaches.

Convert range to table in Excel

The quickest way to convert a data range to a table is this:

  1. Select any cell in your dataset and press the Ctrl + T keys together.
  2. The Create Table dialog box will pop-up. Here, you just verify if Excel has picked your data range correctly, make sure the My table has headers box is selected (if your dataset does not have headers, then of course unselect it), and click OK.
    Converting a range to a table in Excel

That's it! The selected range is converted to a table right away:
The data range is converted to a table.

Tip. This method creates a table with the default style. You can change it at any moment by picking another style from the Table Styles gallery. For more information, please see How to change table style in Excel.

Another way to turn a range into a table

As is often the case in Excel, a command performed with a keyboard shortcut is also accessible from the ribbon. So, here's one more way to create a table in Excel:

  1. Select your dataset or any single cell within it.
  2. Head over to the Insert tab > Tables group and click Table.
    Insert a table in Excel.

  3. In the Create Table dialog box that pops up, verify the selected range, check or uncheck the My table has headers box, and click OK.
Tip. Like the previous method, this one also creates a table using the default style. To control which style is applied to your table, use the bellow approach.

Convert the cell range to a table using table style

To change a range to a table using the style you like best, these are the steps to perform:

  1. Select any cell in your data set.
  2. On the Home tab, in the Styles group, click Format as Table.
  3. Pick the desired table style in the gallery.
  4. In the Create Table dialog box, make sure the selected range if correct and the My table has headers box is checked, and then click OK.
    Convert the cell range to a table using the preferred table style.

As the result, the selected range gets converted to a table with the style of your choosing:
A range is converted to a table with the selected style.

Tip. If your dataset already has some cell formatting and you do not want it to get messed up with the inbuilt table style, then right-click the style in the gallery and choose Apply and Clear Formatting. This will remove all existing formatting from the range, and then apply the selected table style.

Clear existing formatting and apply the selected table style.

That's how to convert a range to a table in Excel and change it back to a normal cell range. I thank you for reading and look forward to seeing you on our blog next week!

You may also be interested in: