How to Convert Range to Table in Excel (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

overview


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

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

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.

sample dataset

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

click on table option

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

select the range of cells

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.

convert range to table

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.

use of keyboard shortcut

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

convert the range of data to 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.

select format as table option

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

select proper range

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

get the output as table format


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

Steps:

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

click on pivot table option

  • 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 on new worksheet option

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

select pivot table fileds

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

get the the final output


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

Steps:

  • First, hit ALT+F11 altogether. Microsoft Visual Basic window appears.
  • In the Toolbar Menu Click on Insert >Select Module.

Inserting module in microsoft visual basic

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

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

 

 

Tags:

Maruf Islam

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo