Macro to Create an Excel Table

These VBA macros turn data into an Excel Table in one click, on the current sheet or every sheet, and convert a table back to a normal range when you need to.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Turn the current data into a table

Click any cell in the data and run:

Sub CreateTable()
    If Not ActiveCell.ListObject Is Nothing Then Exit Sub
    With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveCell.CurrentRegion, , xlYes)
        .TableStyle = "TableStyleMedium2"
    End With
End Sub

CurrentRegion picks up all the data around the active cell, and xlYes tells Excel the first row holds the headers. The macro does nothing if the cell is already in a table.

Create a table on every sheet

Sub CreateTableEverySheet()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.ListObjects.Count = 0 And Not IsEmpty(ws.Range("A1")) Then
            ws.ListObjects.Add xlSrcRange, ws.Range("A1").CurrentRegion, , xlYes
        End If
    Next ws
End Sub

It assumes each sheet’s data starts in A1, and skips sheets that already have a table.

Convert a table back to a range

Sub TableToRange()
    If ActiveCell.ListObject Is Nothing Then Exit Sub
    ActiveCell.ListObject.Unlist
End Sub

The formatting stays; only the table features are removed.

Without a macro

  1. Click any cell in the data.
  2. Press Ctrl + T, check that My table has headers is ticked, and click OK.

Things to know

  • Change TableStyleMedium2 to any style name shown under Table Design → Table Styles, such as TableStyleLight9.
  • Tables can’t contain merged cells. Unmerge them first.
  • Learn why tables are worth using in Excel Tables: How to Create and Use Them.

Related macros