Convert Range to Table in Excel (5 Easy Methods)

In Excel worksheets, we have to organize, maintain, and analyze data every now and then. In this article, we describe the methods to convert any data range to table in Excel. Excel Table is one of the most powerful tools to organize, manage, update, and analyze entries in a dataset.

We’ll discuss some of the go-to’s in Excel such as Table Feature, Keyboard Shortcut, Table Style and Pivot Table Feature as well as VBA Macro Code to convert a range to a table.

Suppose, we have a dataset of Product Sale of multiple Products for different months.

Dataset-Convert Range to Table in Excel

Dataset for Download

Understanding Range and Table

Range: In Excel, a group of selected cells in a dataset is range. You can select any number of cells in a dataset; the reference of the top leftmost cell and bottom rightmost cell is range. For Example, if we select cells (B4 to D13), then B4:D13 is the range in the dataset.

Range-Convert Range to Table in Excel

Table: Table is a kind of advanced range having inbuilt features. Excel Table works as a pre-structured up-to-date range with various offered features such as Header Rows, Total Rows, Banded Rows, First Column, Last Column, Banded Columns, Filter Button, etc.

It is very easy to distinguish Tables from Ranges. Excel Table has frozen heading, borderlines, auto-filter, sort options as well as a distinct viewing format similar to the image below.

TABLE-Convert Range to Table in Excel

Read more: What is the Difference between a Table and a Range in Excel?

5 Easy Methods to Convert Range to Table in Excel

Method 1: Using Table Feature to Convert Range to Table

Excel Insert tab offers insert table option in Tables Sections.

Step 1: Go to Insert Tab> Select Table (in Tables section).

Table feature-Convert Range to Table in Excel

Step 2: A Create Table command box will appear. Select the range (i.e.B3:H19) you want to convert into a table in Where is your data for your table? field and tick the box saying My Table has Headers.

Table feature command box

In this case, if you don’t have the headers, keep the box Unticked.

Step 3: Click OK. The whole range will convert into a table similar to the image below.

Table feature final result

Method 2: Using Keyboard Shortcuts to Convert Range to Table

We can use keyboard shortcuts for converting a range into a table.

Step 1: Press CTRL+T altogether. A Create Table window appears.

keyboard shortcuts-Convert Range to Table in Excel

Step 2: Select the Range (i.e., B3:H19). If the columns have headers Tick the box saying My table has headers unless keeping it Unticked.

Keyboard shortcuts create table command box

Step 3: Click OK. Then the selected range becomes a structured table.

Keyboard Shortcuts Result

Method 3: Using Format as Table Feature to Convert Range to Table

Another convenient way to convert a range into a table is using the Format as Table feature.

Step 1: Hover to Home Tab > Select Format as Table (inside the Style section). There are lots of table styles that are pre-added. Choose any of the Styles to format the range as a table.

Format as table-Convert Range to Table in Excel

Step 2: The Create Table window pops up similar to the previous methods. Select the Range (i.e., B3:H19) and Tick the My table has headers box as previously instructed.

Format as table create table box

Step 3: Click OK and the specified range converts into a table.

Format as table final result

Further Readings:

Method 4: Using Pivot Table Feature to Convert Range to Table

From the Insert Tab, we can also insert a special kind of table; Pivot Table. Pivot Table is handy to display a range in the desired structure. This feature displays the range as you set what to show in rows or columns.

Step 1: Move to Insert Tab > Select Pivot Table (in Tables section).

Pivot table-Convert Range to Table in Excel

Step 2: Then the Create Pivot Table window pops up. In the Create Pivot Table window, Select the Range (i.e., B3:H19) and New Worksheet or Existing Worksheet (in Choose where you want the PivotTable Report to be placed command box).

Create pivot table command box

Step 3: Click OK, in a moment, a new worksheet will appear. In the PivotTable Fields, Tick what column entries you want to display. You can displace the column entries in any fields Filters, Columns, Rows, and Values afterward the outcomes change accordingly.

Pivot table fields

For easy understanding, we are keeping things simple. We tick all the available fields (dataset columns) and drag them in the Values field. The Sum of the Values will appear in Columns Fields.

Pivot table final result

Method 5: Using VBA Macro Code to Convert Range to Table

We can run a simple VBA Macro Code to convert a range into a table. Let’s say, before running the code the range looks like the picture below.

VBA macro code-Convert Range to Table in Excel

Step 1: Hit ALT+F11 altogether. Microsoft Visual Basic window appears. In the Toolbar Menu Click on Insert >Select Module.

Inserting module in microsoft visual basic

Step 2: 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

vba macro code
Step 3: Tab F5 to run the VBA Macro code. Then back to the worksheet, you will see the Range converted into a Table like the following picture.

VBA Macro code final result


In the article, we convert a range into a table using Excel features, Keyboard Shortcuts, and VBA Macro code. Each of the methods is convenient in different ways. You can use any of them you feel comfortable with. Hope the above-mentioned methods fulfill your quest. Comment, if you have further queries and have anything to add.

Further Readings:


Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply