A table in Excel is a structured range of data that comes with built-in features like filtering, automatic formatting, and structured references. These features make it easier to organize, analyze, and manage information. But in some cases, you might not need those features anymore and prefer to work with a simple range instead.
For example, if you have a sales dataset formatted as a table but want to use basic formulas without table references, converting it to a range makes it simpler. It also improves performance in large workbooks and allows you to customize formatting without being tied to table styles.
In this article, you’ll learn how to convert a table to a normal range in Excel using different methods.
Here’s how to convert a table to a range in Excel:
➤ Select the entire table.
➤ Press Ctrl + C to copy it.
➤ Go to the Home tab, click the drop-down of the Paste tab.
➤ Choose Values & Source Formatting from Paste Values.
➤ This removes the table structure completely and you’ll get a range with formatting and style.
Use the Table Design Tab to Convert to Range
In the following dataset, we have monthly sales data formatted as a table. Column A lists the Order ID, Column B shows the Customer Name , Column C contains the Product, and Column D shows the Total Sales. We’ll use this table to demonstrate different ways to convert a table into a range.
We’ll use this table to show several ways to convert it into a regular range.
The most common and direct way to convert a table into a range is by using the Table Design tab. This method is quick and preserves your data and formatting.
Here’s how to do it:
➤ Open your dataset in Excel.
➤ Click on any cell inside the table.
➤ Go to the Table Design tab in the Excel ribbon or Design tab in older versions.
➤ Click Convert to Range in the Tools group.
➤ Excel will ask, Do you want to convert the table to a normal range?
➤ Click Yes.
➤ Your table is now a regular range. The data and formatting remain the same, but the special table features are removed.
Select Convert to Range Option from the Table Context Menu
If you prefer a faster way, you can convert a table to a range directly from the right-click menu.
Here’s how to do it:
➤ Right-click anywhere inside the table.
➤ From the context menu, select Table.
➤ Then click Convert to Range.
➤ Confirm by clicking Yes.
➤ This will immediately change the table back into a normal range without using the ribbon.
Copy and Paste as Values to Break Table Formatting
Another simple way to remove table functionality is to copy and paste the data as values. This converts it into a normal range without the table structure.
Here’s how to do it:
➤ Select the entire table.
➤ Press Ctrl + C to copy it.
➤ Go to the Home tab, click the drop-down of the Paste tab.
➤ Choose Values & Source Formatting from Paste Values.
➤ This removes the table structure completely and you’ll get a range with formatting and style.
Using VBA to Convert a Table to a Range
If you work with multiple tables or want to automate the process, you can use a simple VBA macro to convert them into ranges.
Here’s how to apply this method:
➤ Press Alt + F11 to open the VBA editor.
➤ Click Insert >> Module and paste the following code:
Sub ConvertTableToRange()
Dim tbl As ListObject
For Each tbl In ActiveSheet.ListObjects
tbl.Unlist
Next tbl
End Sub 
➤ Press F5 to run the macro.
➤ All tables in the active sheet will now be converted to regular ranges automatically.
Remove Filters and Formatting to Return to a Range
Another way to convert a table into a normal range is by manually removing its built-in features. When you clear the filters and table formatting, Excel treats the data as a regular range again.
Here’s how to do it:
➤ Click on any cell inside your table.
➤ Go to the Data tab and click on Filter under the Sort & Filter group. This removes the table filter arrows and turns your column headers into standard text headers.
➤ Next, select the entire table.
➤ Go to the Home tab and click on the Clear dropdown in the Editing group.
➤ Choose Clear Formats to remove the built-in table style and formatting.
➤ Once the filters and formatting are gone, Excel will no longer treat your data as a table. It becomes a standard range with plain rows and columns.
Frequently Asked Questions
How do you apply a table style to a range in Excel?
To apply a table style to a normal range, first select the range of cells you want to format. Then go to the Home tab and click Format as Table in the Styles group. Choose a style from the gallery, and Excel will convert the range into a table with the selected design.
How do you get the range of a table in Excel?
Click anywhere inside the table, then go to the Table Design tab. In the Properties group, you’ll see the table name and range in the Table Name box. You can also use the keyboard shortcut Ctrl + A inside the table to highlight the entire range.
How to remove a table in Excel but keep data?
Select any cell in the table, go to the Table Design tab, and click Convert to Range. When prompted, click Yes. This removes the table structure but keeps all the data and formatting. Alternatively, you can copy the table and use Paste Values to paste it as a normal range.
Wrapping Up
Converting a table to a range in Excel is a simple process that gives you more flexibility when working with your data. It removes structured references, automatic formatting, and filtering, but all your data stays exactly the same.
You can do this using the Convert to Range option, the right-click menu, or by copying and pasting values. VBA can help if you want to automate the process, and also removing filters and formatting works.
Once the table is converted, you can use standard formulas, customize formatting, and manage your data without the extra features that tables include.
