Creating an Excel Table

Use Excel's Table command to convert a list of data into a named Excel Table. These tables have useful features, like sorting and filtering, to help organize and view data.

What is an Excel Table?

In Excel 2007, and later versions, you can use the Table command to convert a list of data into a formatted Excel Table. Tables have many features, such as sorting and filtering, that will help you organize and view your data.

I recommend that you (almost) always format your lists as named Excel tables, to take advantage of those features, and many other benefits, described below.

Excel Table Benefits

It's simple to format a list as a named Excel table, and there are many benefits, and just a few drawbacks

To see the easy steps in creating an Excel Table, you can watch this short video. The written steps are below.

Benefits

• Drop down lists in the heading cells make it easy to sort and filter the data
• The table range expands and shrinks automatically, when you add or remove rows of data
• Built-in styles make it easy to format the table's appearance, or change to a different look
• Formulas and formatting fill down automatically
• Formulas use structured references to table cells, showing the column name. This makes them easy to understand
• Show or hide the table's built-in Totals row, where formulas show a Sum, Average, Count, or other summary amounts
• An Excel Table makes an excellent source for a Pivot Table. You won't have to readjust the range, if data is added or removed

Drawbacks

There are a few drawbacks to using named Excel tables though, so there might be situations where you prefer not to use them. For example:

• Structured references to table cells don't have an "absolute" setting, so it's a bit trickier to copy them across a column
• Tables won't expand automatically on protected sheets, even if the cells below the table are unlocked
• You can't group and copy or move multiple sheets, if any sheet contains an Excel table
• Custom Views are not allowed in a workbook that has 1 or more Excel tables

Preparing Your Data

Before you create the formatted Excel Table, follow these guidelines for organizing your data.

• The data should be organized in rows and columns, with each row containing information about one record, such as a sales order, or inventory transaction.
• In the first row of the list, each column should contain a short, descriptive and unique heading.
• Each column in the list should contain one type of data, such as dates, currency, or text.
• Each row in the list should contain the details for one record, such as a sales order. If possible, include a unique identifier for each row, such as an order number.
• The list should have no blank rows within it, and no completely blank columns.
• The list should be separated from any other data on the worksheet, with at least one blank row and one blank column between the list and the other data.

  

Create an Excel Table

After your data is organized, as described above, you're ready to create the formatted Table.

1. Select a cell in the list of data that you prepared.
2. On the Ribbon, click the Insert tab.



3. In the Tables group, click the Table command.
4. In the Create Table dialog box, the range for your data should automatically appear, and the My table has headers option is checked. If necessary, you can adjust the range, and check box.
5. Click OK to accept these settings.

Sort and Filter the Data

Your list is now an Excel Table, and is automatically formatted with a default Table Style, which you can change. The heading cells have drop down arrows that you can use to sort or filter the data.

NOTE: In Excel 2013 and later, you can use Excel Table Slicers to quickly filter the table data.

Rename an Excel Table

When it is created, an Excel table is given a default name, such as Table 3. You should change the name to something meaningful, so it will be easier to work with the table later.

To change the table name:

1. Select any cell in the table
2. On the Ribbon, under the Table Tools tab, click the Design tab.

   

3. At the far left of the Ribbon, click in the Table name box, to select the existing name

   

4. Then, type a new name, such as Orders, and press the Enter key

   

Create Excel Table With Specific Style

When you create a table you can apply a specific style from the Table Style options, instead of using the default style. Then, when you are applying that style, click the option to remove any current cell formatting from the data range.

Watch this video to see the steps, and the written instructions are below the video

Create an Excel Table With Specific Style

When you create a table with the Table command on the Ribbon's Insert tab, the table retains any formatting that it currently has, and the default Table Style is applied.

If you want to apply a specific table style when creating an Excel Table:

1. Select a cell in the list of data that you prepared.
2. On the Ribbon, click the Home tab.
3. In the Styles group, click Format as Table
4. Click on the Style that you want to use
   

   

   OR, to apply a Style and remove any existing formatting, right-click on a Style, and click Apply and Clear Formatting

   

5. In the Create Table dialog box, the range for your data should automatically appear, and the My table has headers option is checked. If necessary, you can adjust the range, and check box.
6. Click OK to accept these settings.

A formatted Excel Table is created, with the selected Table Style.

Show Totals in a Table

After you create an Excel table, it's easy to show the total for a column, or for multiple columns, using a built-in Table feature.

To show a total:

1. Select any cell in the table
2. On the Ribbon, under the Table Tools tab, click the Design tab
3. In the Table Style Options group, add a check mark for Total Row

   

4. A Total row will be added at the bottom of the table, and one or more column of numbers might show a total.

Change and Add Totals

In addition to the automatically created totals, you can select totals for other columns.

1. Click in the Total cell for one of the columns.
2. From the drop down list, select the function that you want to use in the current column.

   

A SUBTOTAL formula is added to the cell, and it shows the calculation based on the visible cells in the table's column.

Refer to Table Column in Formula

When a formula refers to part of a named Excel Table, a Structured Reference is created. The Structured Reference will show the table's column name, and might include the table name. For example, this reference is to the Product column in a table named OrderRef -- OrdersRef[Product]

Formula Outside the Table

In this example, a formula will be created outside of the table. The formula will use the COUNTBLANK function to count the blank cells in a table column. The table is named OrderRef and the column is named Product.

1. To start the formula, select a blank cell and type: =COUNTBLANK(
2. Then, click at the very top of the heading cell, for the column that you want to check -- the pointer will change to a down arrow.

   

• Don't click on the column button where the column letter is.

  

• And don't click in the middle of the heading cell

  

3. The structured reference should show the table name and the column name:

=COUNTBLANK(OrdersRef[Product]

4. Then, type a closing bracket, and press Enter, to complete the formula.

   

Add a Counter Field

If you plan to use your Excel table as the source data for a pivot table, add a counter field, that can be used in calculated fields, or summary calculations.

This is very easy to create and maintain in an Excel table, if you use a simple formula, instead of typing the value.

1. On the OrdersTable sheet, add a new heading in first blank column -- Sales
2. In the cell below the heading, type a formula: =1
3. Press Enter, to complete the formula

   

Because the data is in a named Excel table, the formula will automatically fill down to all the rows. It will also be automatically entered when you add new rows.

The 1s will give us a value that can be summed in a pivot table, or used in a Calculated Field, to give correct results.

Print Excel Table Only

When you are working with lists in Excel, use the built-in Table feature, to make it easier to work with the data. Then, if you want to print just the table, without the other items on the worksheet, you can use a built-in command -- Print List.

The command is not on the Ribbon, so you can add it there, or put the command on the Quick Access Toolbar. Watch this short video to see the steps.

Table Doesn't Expand For New Data

By default an Excel table will expand automatically, and fill formulas down to the last row. For example:

• Add new data in the row immediately below a table, or in the column to its immediate right, and the table expands automatically, to include that new data.
• Enter a formula in the first row of a blank column, that formula fills down to all the remaining rows, as soon as you press Enter

If Excel tables are not expanding automtically on your computer, you can adjust the settings either manually, or with VBA programming.

NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.

Watch the video, to see the steps, and read the detailed instructions, below the video.

Turn These Settings On Manually

If Excel tables are not expanding automtically on your computer, check the following settings, in the Excel Options window.

1. At the left end of the Ribbon, click the File tab, then click Options
2. In the Excel Options window, at the left, click Proofing

   

3. In the AutoCorect options section, click AutoCorrect Options

   

4. Click the AutoFormat As You Type tab
5. Add check marks to "Include new rows and columns in table" and "Fill formulas in tables to create calculated columns"

   

6. Click OK, twice, to return to Excel

Now, when you add new data, or create a calculated column, the Excel table should adjust automatically.

Turn These Settings On or Off With VBA

To turn these settings ON with VBA, use the following lines of code:

Sub ListAutoSetOn()

With Application.AutoCorrect
  .AutoExpandListRange = True
  .AutoFillFormulasInLists = True
End With

End Sub

To turn these settings OFF with VBA, use the following lines of code:

Sub ListAutoSetOff()

With Application.AutoCorrect
  .AutoExpandListRange = False
  .AutoFillFormulasInLists = False
End With

End Sub

List All Tables in Workbook

The following code will list all the named Excel Tables in the active workbook. A new worksheet is inserted, and the table name, sheet name, table address, and source type are listed.

• The first macro lists a number for the SourceType property
• The second macro has a text description of the SourceType property

See more info on SourceType on the Microsoft website

Table Info with SourceType Number

Sub ListTables()
Dim ws As Worksheet
Dim Lst As ListObject
Dim wsL As Worksheet
Dim lCount As Long
''Source Type: 0 external; 1 Range;
''  2 XML; 3 Query; 4 PowerPivot Model

lCount = 1

Set wsL = Worksheets.Add
With wsL.Range("A1:E1")
  .Value = Array("ID", "Table", "Location", _
      "Sheet", "Source Type")
  lCount = lCount + 1
End With

For Each ws In ActiveWorkbook.Worksheets
If ws.ListObjects.Count > 0 Then
  For Each Lst In ws.ListObjects
    wsL.Cells(lCount, 1).Value = lCount
    wsL.Cells(lCount, 2).Value = Lst.Name
    wsL.Cells(lCount, 3).Value = Lst.Range.Address
    wsL.Cells(lCount, 4).Value = ws.Name
    wsL.Cells(lCount, 5).Value = Lst.SourceType
    lCount = lCount + 1
  Next Lst
End If
Next ws

End Sub

Table Info with SourceType Description

Sub ListTablesSource()
Dim ws As Worksheet
Dim Lst As ListObject
Dim wsL As Worksheet
Dim lCount As Long
Dim strST As String

lCount = 1

Set wsL = Worksheets.Add
With wsL.Range("A1:E1")
  .Value = Array("ID", "Table", "Location", _
      "Sheet", "Source Type")
  lCount = lCount + 1
End With

For Each ws In ActiveWorkbook.Worksheets
If ws.ListObjects.Count > 0 Then
  For Each Lst In ws.ListObjects
    Select Case Lst.SourceType
      Case 0: strST = "External"
      Case 1: strST = "Range"
      Case 2: strST = "XML"
      Case 3: strST = "Query"
      Case 4: strST = "PowerPivot Model"
      Case Else: strST = "Unknown"
    End Select
    wsL.Cells(lCount, 1).Value = lCount
    wsL.Cells(lCount, 2).Value = Lst.Name
    wsL.Cells(lCount, 3).Value = Lst.Range.Address
    wsL.Cells(lCount, 4).Value = ws.Name
    wsL.Cells(lCount, 5).Value = strST
    lCount = lCount + 1
  Next Lst
End If
Next ws

End Sub

Sheet Name Macros

Thanks to Alex Blakenburg, who shared the followingcode for getting the sheet name for a specific Excel table. Alex needed code to make changes to a named Excel table, and to its worksheet, and there were a few things to consider:

• Macro would be run from a different workbook, not the file with the table
• Table's workbook might not be the active workbook - other workbooks were being created and edited
• Someone might have changed the sheet name (it's unlikely that the table name would be changed)

So, these macros have the table name hard coded (OrderRef), but not the sheet name.

1. Table Info

In the sample file, the TblInfo sheet has a drop down list, where you can choose a table name. That triggers a Change event procedure, which clears a couple of cells, and runs the following macro - ShowTableInfo.

• The macro fills in the Sheet Name and Table Address cells.
• A HYPERLINK formula in cell D2 lets you go to the selected table.

Here is the Change event procedure on the Tble Info sheet:

Private Sub Worksheet_Change _
  (ByVal Target As Range)
If Target.Address = _
    Range("SelTbl").Address Then
  Range("TblSh").ClearContents
  Range("TblAd").ClearContents
  If Target.Value <> "" Then
    ShowTableInfo
  End If
End If
End Sub

And here is the ShowTableInfo macro

Sub ShowTableInfo()
'by Alex Blakenburg
'select table name in SelTable cell
' table's sheet and address
'   added to cells below

Dim wb As Workbook
Dim shTI As Worksheet
Dim shData As Worksheet
Dim SelTable As Range
Dim TblSheet As Range
Dim TblAddr As Range
Dim rngTbl As Range
Dim tblData As ListObject
Dim tblName As String
Dim tblFullName As String

Set wb = ActiveWorkbook
Set shTI = wb.Sheets("TblInfo")
Set SelTable = shTI.Range("SelTbl")
Set TblSheet = shTI.Range("TblSh")
Set TblAddr = shTI.Range("TblAd")

tblName = SelTable.Value
If tblName = "" Then Exit Sub

tblFullName = "'" & wb.Name _
    & "'!" & tblName

Set rngTbl = Range(tblFullName)
Set shData = _
  wb.Sheets(rngTbl.Parent.Name)
Set tblData = _
  shData.ListObjects(tblName)

TblSheet.Value = shData.Name
TblAddr.Value = _
  tblData.Range.Address

End Sub

2. Testing Sheet

The next two macros can be tested with the buttons on the Testing worksheet. They show how the target table and its sheet can be changed, even when their workbook is not active.

TableChanges

This macro makes a change to the table style, after a new workbook has been created.

Sub TableChanges()
'by Alex Blakenburg
'use this technique to work
'  with table on unknown sheet
'table's workbook must be active
'  when tblData variable is set
Dim wb As Workbook
Dim tblData As ListObject
Dim tblName As String
Dim tblFullName As String

Set wb = ActiveWorkbook
tblName = "OrdersRef"

tblFullName = "'" & wb.Name _
    & "'!" & tblName
Set tblData = _
  Range(tblFullName).ListObject
'new workbook created,
'to show table can
' be changed if its
'workbook is not active
Workbooks.Add

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle
'if table is light blue (9)
'change to light green (14)
If tblData.TableStyle = _
    "TableStyleLight9" Then
tblData.TableStyle = _
    "TableStyleLight14"
Else
tblData.TableStyle = _
    "TableStyleLight9"
End If

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle

End Sub

TableSheetChanges

This macro makes a change to the table style, and a worksheet cell, after a new workbook has been created.

Sub TableSheetChanges()
'by Alex Blakenburg
'use this technique to work with
' sheet and table on unknown sheet
'table's wkbk must be active when
' sheet & table variables are set
Dim wb As Workbook
Dim shData As Worksheet
Dim rngTbl As Range
Dim tblName As String
Dim tblFullName As String
Dim tblData As ListObject

Set wb = ActiveWorkbook
tblName = "OrdersRef"

tblFullName = "'" & _
  wb.Name & "'!" & tblName
Set rngTbl = Range(tblFullName)
Set shData = _
  wb.Sheets(rngTbl.Parent.Name)
Set tblData = _
  shData.ListObjects(tblName)

'new workbook created, to show
' table and sheet can be changed
' even if workbook not active
Workbooks.Add

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle
'if table is light blue (9)
'change to light green (14)
If tblData.TableStyle = _
    "TableStyleLight9" Then
tblData.TableStyle = _
    "TableStyleLight14"
Else
tblData.TableStyle = _
    "TableStyleLight9"
End If

MsgBox "Table " & tblName & " style is: " _
  & vbCrLf _
  & tblData.TableStyle

With shData.Range("G1").Interior
  If .ColorIndex = 6 Then
    .ColorIndex = 4
  Else
    .ColorIndex = 6
  End If
End With

End Sub

Download the Sample File

1. Basic: To download the sample file with the tables for this tutorial, click here: Table Sample File. The zipped file is in xlsx format, and does NOT contain macros.
2. Macros: To download the sample file with the tables and macros for this tutorial, click here: Table Files With Macros. The zipped file is in xlsm format, and DOES contain macros

Related Excel Tutorials

Excel Table Slicers

Pivot Table Slicers

Excel Project Task Tracker

Create a Pivot Table

Macro - Copy Excel Table Rows