The Ultimate Guide to Switching Between Tables and Ranges in Microsoft Excel

Quick Links

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.

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.

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

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 here

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.

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.

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.

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

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.”

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 .

Win Dash

The Ultimate Guide to Switching Between Tables and Ranges in Microsoft Excel