Microsoft Excel logo on a green background

Using a table in Excel allows you to enjoy helpful features for working with your data. But those features may interfere when manipulating your data the way you want. Luckily, you can convert a table to a range or vice versa.

How to Convert a Table to a Range

If you’ve already created a table and decided it’s not working for you, you can easily convert it to a normal range. When you do so, formatting such as alternating row colors and font customizations will remain.

RELATED: How to Apply Shading to Alternate Rows in Excel

Select any cell within the table, and you can then do one of two things. Your first option is to, on the Table Design tab that displays, click “Convert to Range” in the Tools section of the ribbon.

On the Table Design tab, click Convert to Range

The second method is to right-click, move your cursor to Table in the shortcut menu, and select “Convert to Range.”

Select Table, Convert to Range

Advertisement

Using either of the above methods, you’ll receive a pop-up message asking you to confirm. Click “Yes” to continue or “No” to keep your data as a table.

Click Yes to convert to a range

As mentioned, the data range will retain specific table formatting. If you prefer not to keep it, you can go to the Home tab, click the Cell Styles drop-down arrow, and select “Normal” or a custom style if you like. Just be aware this may affect cell formattings such as dates, currency, or custom formats.

RELATED: How to Use and Create Cell Styles in Microsoft Excel

As an alternative, you can copy the formatting from another location in your sheet using Format Painter.

How to Convert a Range to a Table

With tables, you have formatting with banded rows, an easy way to insert a total row, and filter buttons in the column headers. If you’d like to convert a data range to a table, it takes only a few clicks.

RELATED: How to Insert a Total Row in a Table in Microsoft Excel

Select the cell range you want to convert, and you then have two options for proceeding. First, on the Home tab, you can click “Format as Table” in the Styles section of the ribbon. Select the table style you want to use.

On the Home tab, click Format as Table

The second method is to, on the Insert tab, click “Table” in the Tables section of the ribbon.

On the Insert tab, click Table

In either case, you’ll be prompted to confirm the data range conversion to a table. Also, check the box if your data has headers that you want to use. Click “OK.”

Click OK to convert to a table

Advertisement

Using tables in Excel can be convenient but doesn’t work for every situation. Fortunately, you can switch to a simple range easily. So if you’re in the market for alternative ways to analyze your sheet, take a look at how to use the Analyze Data feature in Excel.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.