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.
Suppose, we have a dataset of Product Sale of multiple Products for different months.
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.
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).
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.
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.
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.
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.
Step 3: 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.
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.
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.
Step 3: Click OK and the specified range converts into a table.
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).
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).
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.
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.
Step 1: Hit ALT+F11 altogether. Microsoft Visual Basic window appears. In the Toolbar Menu Click on Insert >Select Module.
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
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.
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.