How to 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.

In the following figure, we convert the range to a table in Excel with the aid of the table feature.

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.

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.

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

5 Easy Methods to Convert Range to Table in Excel

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

In this section, we will demonstrate 5 easy methods to convert the range to table in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Method 1: Using Table Feature to Convert Range to Table

Excel Insert tab offers insert table option in Tables Sections.

Steps:

• First, go to Insert Tab> Select Table (in the Tables section).

• Therefore, a Create Table command box will appear.
• Then, select the range (i.e.B4:H16) 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.

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

• Finally, click OK. The whole range will convert into a table similar to the image below.

Read more: How to Convert Table to List in Excel

Method 2: Using Keyboard Shortcuts to Convert Range to Table

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

Steps:

• First, press CTRL+T altogether.
• Consequently, a Create Table window appears.

• Then, select the Range (i.e., B4:H16). If the columns have headers Tick the box saying My table has headers unless keeping it Unticked.
• Next, click OK.
• Then the selected range becomes a structured table.

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.

Steps:

• First, 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.

• Consequently, the Create Table window pops up similar to the previous methods.
• Then, select the Range (i.e., B4:H16) and Tick the My table has headers box as previously instructed.

• Next, click OK and the specified range converts into a table.

Similar Readings:

• How to Make a Table in Excel (With Customization)
• Does TABLE Function Exist in Excel?
• How to Use Slicers to Filter a Table in Excel 2013
• Use Formula in an Excel Table Effectively (With 4 Examples)

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.

Steps:

• First, move to Insert Tab > Select Pivot Table (in Tables section).

• Then the Create Pivot Table window pops up.
• Next, in the Create Pivot Table window, Select the Range (i.e., B4:H16) and New Worksheet or Existing Worksheet (in Choose where you want the PivotTable Report to be placed command box).

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

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

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.

Steps:

• First, hit ALT+F11 altogether. Microsoft Visual Basic window appears.
• In the Toolbar Menu 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

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

Read more: How to Use an Excel Table with VBA

Conclusion

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

• How to Lookup a Table and Return Values in Excel (3 Simple Ways)
• Get a Count in Excel Pivot Table Calculated Field
• How to Edit a Pivot Table in Excel (5 Methods)
• Refresh All Pivot Tables in Excel (3 Ways)