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

Get FREE Advanced Excel Exercises with Solutions!

To convert range to table in Excel:

  1. Select a cell within your data range.
  2. Press Ctrl+T.
  3. In the Create Table box, tick the box “My Table has Headers” if the data contains headers.
  4. Press OK.

Excel will convert the selected range to a table.


Converting a range to a table in Excel means transforming a selected group of cells, typically containing data, into an Excel Table. By converting ranges into tables, you can organize, manage, update, and analyze the entries in your dataset more efficiently.

In this Excel tutorial, you will learn how to convert the range to tables using Excel’s features and VBA macro code.

The following figure shows a dataset of Product Sales of multiple Products for different months, which is converted to a table in Excel using the table feature.

Converting range to table using Table feature


What Are 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 and bottom rightmost cells 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, and sort options as well as a distinct viewing format similar to the image below.

Range vs. Table

Why Convert Range to Table in Excel?

Here are some reasons to convert range to table:

  • Automatic filtering controls in table headers.
  • Quick and efficient sorting of data.
  • Easy data entry for dynamic chart and PivotTable updates.
  • Extends formatting and fills formulas automatically.
  • Automatic formula application across entire columns reduces errors.
  • Easy navigation through large datasets as Table headers remain visible.
  • Table Styles group provides swift and diverse formatting options.
  • Names appear automatically, contributing to a more organized spreadsheet.
  • Total Row enables efficient calculation of totals and summary statistics.

4 Ways to Convert Range to Table in Excel

In Excel, you can convert the range to table using Excel’s Table feature, Format as Table option, Pivot Table feature, and VBA macro.

Here are 4 ways to convert range to table in Excel:

Using Table Feature

You can convert a range to a table by creating a table from a selected range using the Table feature of Excel.

To convert range to table using Table feature, follow the steps below:

  1. Select a cell within your data range.
  2. Go to Insert Tab> Tables group > Table. Or, press Ctrl+T.Selecting table option from Insert tab
  3. 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.

    Create Table dialog box showing data range

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

Range converted to table

Read More: How to Convert Table to List in Excel


Using Format as Table Feature

Excel’s Format as Table feature quickly converts a range of cells to a table with its own style.

Follow the steps below to convert the range using Format as Table feature:

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

    Create Table dialog box showing data range

Finally, the specified range will be converted into a table.

Showing the range in table format

Read More: Types of Tables in Excel: A Complete Overview


Using Pivot Table Feature

You can also convert range by inserting a special kind of table, which is Pivot Table. The Pivot Table is handy for displaying a range in the desired structure. Moreover, you can edit and arrange rows and columns and paste the table into a new worksheet according to your needs using a pivot table.

To use Pivot Table to convert range to table, go through the steps below:

  1. Select any cell in the data range.
  2. Go to Insert tab > Tables section > Pivot Table > select From Table/Range.Selecting pivot table to insert pivot table
  3. 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.

    Showing PivotTable from table or range dialog box to check the input

  4. In the PivotTable Fields:
    • Tick what column entries you want to display.
    • Drag the column entries in any fields Filters, Columns, Rows, and Values.

    Selecting pivot table fields

Finally, the pivot table looks like the image below.

Pivot table converted range to table

Note: You can see that PivotTable arranges the data alphabetically and shows the Sum of the values automatically.

Read More: Navigating Excel Table


Using VBA Macro

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

Follow the steps below to convert the range using the VBA macro:

  1. Go to Developer tab > Code group > Visual Basic. Or press ALT+F11.Selecting Visual Basic Option
  2. In the VBA window, click on Insert > select Module.Inserting module in visual basic editor
  3. 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
  4. Press F5 to run the VBA macro code.

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

VBA Macro converted range to table

Read More: How to Make Excel Tables Look Good


Download Practice Workbook


Conclusion

In the article, you learned 4 different methods to convert a range into a table. You can use Table, Format as Table, PivotTable features,  and VBA macro code to convert range to table according to your dataset. Please ask in the comment section below if you have any suggestions or queries.


Frequently Asked Questions

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

To convert an Excel table to a regular range:

  1. Click anywhere in the table.
  2. 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.


Further Readings


<< Go Back to Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo