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

Converting a range to a table in Excel means transforming a selected group of cells, typically containing data, into an Excel Table.  In Excel, you can convert a range to a table using Excel’s Table feature, Format as Table option, Pivot Table feature, and VBA macro.

Method 1 – Using the Table Feature of Excel

You can convert a range to a table by creating a table from a selected range using the Table feature of Excel with the following steps:

  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

Method 2 – Using Format as Table Feature

To use Excel’s Format as Table feature to convert a range of cells to a table with its own style, follow these steps:

  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

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

Method 3 – Using Pivot Table Feature

You can also convert a range by inserting a special kind of table, which is a Pivot Table. Follow 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 the column entries you want to display.
    • Drag the column entries in any fields Filters, Columns, Rows, and Values.
  5. Selecting pivot table fields

The pivot table will look like the image below.

Pivot table converted range to table

Note: The PivotTable arranges the data alphabetically and shows the Sum of the values automatically.

Read More: Navigating Excel Table

Method 4 – Using VBA Macro

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

  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.

If you go back to the worksheet 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

Frequently Asked Questions

How do you convert an Excel table back 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

Get FREE Advanced Excel Exercises with Solutions!


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