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.
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.
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.
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:
Before you create the formatted Excel Table, follow these guidelines for organizing your data.
After your data is organized, as described above, you're ready to create the formatted Table.
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.
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.
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
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:
OR, to apply a Style and remove any existing formatting, right-click on a Style, and click Apply and Clear Formatting
A formatted Excel Table is created, with the selected Table Style.
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:
In addition to the automatically created totals, you can select totals for other columns.
A SUBTOTAL formula is added to the cell, and it shows the calculation based on the visible cells in the table's column.
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]
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.
=COUNTBLANK(OrdersRef[Product]
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.
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.
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.
By default an Excel table will expand automatically, and fill formulas down to the last row. For example:
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.
If Excel tables are not expanding automtically on your computer, check the following settings, in the Excel Options window.
Now, when you add new data, or create a calculated column, the Excel table should adjust automatically.
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
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.
See more info on SourceType on the Microsoft website
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
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
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:
So, these macros have the table name hard coded (OrderRef), but not the sheet name.
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.
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
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
Last updated: October 14, 2020 2:38 PM