How to Convert Range to Table in Excel (4 Methods)

Converting a range to a table in Excel means transforming a selected group of cells, typically containing data, into an Excel Table. In Excel, you can convert a range to a table using Excel’s Table feature, Format as Table option, Pivot Table feature, and VBA macro.

Method 1 – Using the Table Feature of Excel

You can convert a range to a table by creating a table from a selected range using the Table feature of Excel with the following steps:

Select a cell within your data range.
Go to Insert Tab> Tables group > Table. Or, press Ctrl+T.
In the appeared Create Table box:
- Adjust the automatically selected range if needed.
- Tick the box My Table has Headers if the data range contains headers.
- Click OK.

You can see the whole range converted into a table like the image below.

Method 2 – Using Format as Table Feature

To use Excel’s Format as Table feature to convert a range of cells to a table with its own style, follow these steps:

Select the range to convert.
Go to the Home Tab and select Format as Table from the Styles section.
Choose any of the Styles to format the range as a table.
In the Create Table window:
- Check the range.
- Tick the “My table has headers box“.
- Click OK.

The specified range will be converted into a table.

Method 3 – Using Pivot Table Feature

You can also convert a range by inserting a special kind of table, which is a Pivot Table. Follow the steps below:

Select any cell in the data range.
Go to Insert tab > Tables section > Pivot Table > select From Table/Range.
In the PivotTable from table or range box:
- Adjust the automatically selected cell range.
- Choose Existing Worksheet and select a Location to place the table if you do not want it to be placed in a new worksheet. Otherwise, keep New Worksheet option selected.
- Click OK.
In the PivotTable Fields:
- Tick the column entries you want to display.
- Drag the column entries in any fields Filters, Columns, Rows, and Values.

The pivot table will look like the image below.

Note: The PivotTable arranges the data alphabetically and shows the Sum of the values automatically.

Read More: Navigating Excel Table

Method 4 – Using VBA Macro

You can automate the conversion process from range to table using a VBA code with the following steps.

Go to Developer tab > Code group > Visual Basic. Or press ALT+F11
In the VBA window, click on Insert > select Module.
Paste the below VBA macro code inside the Module.
Sub ConvertRangetoTable() ActiveWorkbook.Sheets("VBAMacroCode").ListObjects.Add(xlSrcRange, Range("$B$2:$H$18"), , xlYes).Name = "Product_Sale" End Sub
Press F5 to run the VBA macro code.

If you go back to the worksheet you will see the range converted into a Table like the following picture.

Read More: How to Make Excel Tables Look Good

Download Practice Workbook

Convert to Table.xlsm

Frequently Asked Questions

How do you convert an Excel table back to a regular range?

Click anywhere in the table.
Go to the Table Tools Design tab > Tools section > select Convert to Range.

Are Excel tables compatible with older Excel versions?

Excel tables are available in newer versions (Excel 2007 and later). Some features may not be available in older versions, but basic functionality, such as sorting and filtering, remains compatible.

Can I customize the appearance of an Excel table?

You can customize the appearance of a table by using the various table styles available under the Table Tools Design tab, which includes options for banded rows, columns, and more.