Convert Excel table to range and turn data range into 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 undo a table so it changes 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 suit 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.

Excel table vs. range: what's the difference?

Among Excel users, especially beginners, there's a lot of confusion and uncertainty about the terms range and table. When you input some data in a worksheet, can you already call your dataset a table? Many people do so. From Excel's viewpoint, however, it's a range, not a table. So, what's the difference?

Range in Excel means any group of selected cells. It is defined by the reference of the top-left cell and the bottom-right cell. For example, in the left-hand part of the image below, the data is in the range A1:C6.

Excel table is a structured and pre-formatted dynamic range of cells that has its own name. You can think of a table as an advanced version of range with inbuilt functionality such as calculated columns, banded rows, sorting and filtering, etc. We say that a table is dynamic because it expands automatically as you add new data to it.

You can easily identify a table in an Excel worksheet due to these noticeable features: table data is always surrounded by a thin border like shown in the right-hand part of the image below. Auto-filter arrows appear next to the column headers, and the headings remain frozen at the top when you scroll through the data.
Convert the table to a normal range.

For more information, please see Excel table: comprehensive tutorial with examples.

Why would you want to convert a range into an Excel table?

Turning a range of cells into a fully functional Excel table extends its functionality with many advanced features that can take your spreadsheets to a new level:

What does it mean to convert an Excel table to a range?

Microsoft Excel allows quickly transforming a table back to a normal range. This will remove all of the table features but keep table style formatting. Below, you'll find detailed guidance on how to do that.

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.

You can also undo a table in Excel using the ribbon menu:

  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.

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.

Tip. To swiftly transform a selected range into a table, you can also use the Quick Analysis Tables feature.

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!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)