Tuesday, January 18, 2022

How to Convert a Table to a Range and Vice Versa in Microsoft Excel

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

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

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.


ALSO READ  How to Get Started With Google TV

Leave a Reply

This website uses cookies. By continuing to use this site, you accept our use of cookies.