How to Convert a Table to a Range in Excel (Easily)

Excel table is one of the many useful functionalities of Excel that allows data structuring, managing, updating, and extension in an easier manner.

Once you’ve created a table, you may want to remove it and convert your data into an ordinary cell range without losing any data 🤔

There are two ways how to convert a table to a range in Microsoft Excel and this tutorial, we will look at both these ways. To unveil these methods, download the free practice workbook for this guide here and continue reading with us till the end.

Table of Contents

Table vs. range

In terms of Excel, a table and a range are two very different things.

As soon as you launch an Excel book and type in some data in two or more cells, you can call it a cell range of data. For example, in the below image, we have some data populated in cells A1 to D4. This is what we call a range 👇

Once you create a table out of this range (select this cell range > go to the Insert tab > Tables group > table), it will look like below.

Apart from the difference in formatting, an Excel table is a pre-formatted data set with structured references. This is a dynamic range of cells that you can specifically name. It has banded rows, and an auto-filter applied to each column. The columns are calculated and as you add new rows or columns to this table, the table automatically expands to them.

If your data has headings (or headers), these headers will be frozen by default as you scroll through the sheet 🥶

A table is always surrounded by a thin border and has banded rows and headers so you can identify it from far away. It is not automatically created or there by default in Excel. You have to select a cell range and make it manually.

Similarly, once created, if you subsequently want to remove it, you will have to use either of the two methods explained below to convert it into a range.

Convert a table to range in Excel from the Ribbon

Ribbon in Excel represents the toolbar of options on the top. It has multiple groups starting from the File tab to the home tab to item-specific tabs that will appear only when you include that item in your sheet.

For instance, whenever you include a table in Excel and select it (or any cell from it), you’ll see the following additional tab on the ribbon 🙈

The Table Design tab is a collection of all the table-related commands.

And as soon as you move your cursor away from the table, or deselect it, this tab will vanish from the Ribbon.

Now, the question is, how do you convert a table into a simple range of cells using these tabs on the Ribbon? The answer is simple and lies in the following steps.

Step 1) Select any cell from the formatted table by clicking on it.

As soon as you select any cell from a table, the table-specific tabs will begin to appear on the ribbon.

Step 2) Go to the Table Design tab on the Ribbon > Table Tools group > Convert to Range button.

Excel will present you with a prompt confirming if you want to convert the table to a normal range as doing this will end all the table functionalities ❗

Step 3) Click on the Yes button.

With this Excel will convert the table into a regular range.

The first change you’ll notice is that the filter buttons (small downfaced arrows) are gone from the headers.

Pro Tip!

Converting a table into a range will remove all the table features from the selected cells. But do note that the table formalities will still be there. The table style and cell formatting will not go away (the colors, fonts, and colors. You will have to remove it later separately.

Convert a table to range in Excel from the context menu

The other way how you can convert a table into a cell range in Excel is by using the context menu.

Here’s what you need to do.

Step 1) Right-click anywhere within the table.

This will launch the context menu as shown below 👀

Step 2) Hover your cursor over the option for Table (and the small black arrow towards its right).

Step 3) From the table options that launch consequently, select Convert to Range.

Step 4) From the confirmation dialog box, select Yes.

All the table functionalities will go away.

However, as discussed above, the table formatting will still stay.

Conclusion

There are two quick ways how to convert a table to a range in Excel. You can do it from the table-designated tab on the ribbon. Or, from the contextual menu that launches when you right-click on the table 🎯

Try out both ways to see how they work for you. To learn more about tables in Excel, do not forget to check out the following Excel tutorials by Spreadsheeto.

• How to Remove Table Formatting in Excel – Without Losing Data
• How to Sort a Pivot Table in Excel: Step-by-Step (2024)
• How to Add Calculated Fields in Excel Pivot Tables (2024)