In Excel 2007, and later versions, use the Table command to convert a list of data into a named Excel Table. These tables have many 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.
An Excel Table makes an excellent source for a pivot table, so you should use this feature if you plan to create a Pivot Table from the data.
Before you create the formatted Excel Table, follow these guidelines for organizing your data.
To see the steps in creating an Excel Table, please watch this short video tutorial. The written instructions are below the video.
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.
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.
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:
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.
Sub ListTables() Dim ws As Worksheet Dim Lst As ListObject Dim wsL As Worksheet Dim lCount As Long 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
To download the sample file for this Table tutorial, click here: Table Sample File.
The zipped file is in xlsx format, and does not contain macros.
Pivot Table Tutorial for Excel 2007
Pivot Table Tutorial for Excel 2003 and earlier
Last updated: September 24, 2015 10:39 AM