How To Create A Table In Excel 2003

Creating an Excel TableUse 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?

Contents

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.Reading: how to create a table in excel 2003I 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 drawbacksTo 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

DrawbacksThere 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.

Select a cell in the list of data that you prepared.

On the Ribbon, click the Insert tab.

In the Tables group, click the Table command. 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. 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:

Select any cell in the table

On the Ribbon, under the Table Tools tab, click the Design tab.

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

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:

Select a cell in the list of data that you prepared.

On the Ribbon, click the Home tab.

In the Styles group, click Format as Table

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

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.

Click OK to accept these settings.

Read more: How to get twists with straight hairA 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:

Select any cell in the table

On the Ribbon, under the Table Tools tab, click the Design tab

In the Table Style Options group, add a check mark for Total Row

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.

Click in the Total cell for one of the columns.

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.

To start the formula, select a blank cell and type: =COUNTBLANK(

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

The structured reference should show the table name and the column name: =COUNTBLANK(OrdersRef[Product] 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.

On the OrdersTable sheet, add a new heading in first blank column – Sales

In the cell below the heading, type a formula: =1

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 automatically 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

Read more: Linear AlgebraIf Excel tables are not expanding automatically on your computer, check the following settings, in the Excel Options window.

At the left end of the Ribbon, click the File tab, then click Options

In the Excel Options window, at the left, click Proofing

In the AutoCorrect options section, click AutoCorrect Options

Click the AutoFormat As You Type tab

Add check marks to “Include new rows and columns in table” and “Fill formulas in tables to create calculated columns”

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 topqa.infoCorrect .AutoExpandListRange = True .AutoFillFormulasInLists = True End With End SubTo turn these settings OFF with VBA, use the following lines of code: Sub ListAutoSetOff() With topqa.infoCorrect .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 websiteTable 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 = topqa.info With topqa.infoe(“A1:E1”) .Value = Array(“ID”, “Table”, “Location”, _ “Sheet”, “Source Type”) lCount = lCount + 1 End With For Each ws In topqa.infosheets If topqa.infot > 0 Then For Each Lst In ws.ListObjects topqa.infos(lCount, 1).Value = lCount topqa.infos(lCount, 2).Value = topqa.info topqa.infos(lCount, 3).Value = topqa.infoess topqa.infos(lCount, 4).Value = ws.Name topqa.infos(lCount, 5).Value = topqa.infoceType lCount = lCount + 1 Next Lst End If Next ws End SubTable 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 = topqa.info With topqa.infoe(“A1:E1”) .Value = Array(“ID”, “Table”, “Location”, _ “Sheet”, “Source Type”) lCount = lCount + 1 End With For Each ws In topqa.infosheets If topqa.infot > 0 Then For Each Lst In ws.ListObjects Select Case topqa.infoceType 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 topqa.infos(lCount, 1).Value = lCount topqa.infos(lCount, 2).Value = topqa.info topqa.infos(lCount, 3).Value = topqa.infoess topqa.infos(lCount, 4).Value = ws.Name topqa.infos(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 following code 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 InfoIn 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 TblInfo sheet: Private Sub Worksheet_Change _ (ByVal Target As Range) If topqa.infoess = _ Range(“SelTbl”).Address Then Range(“TblSh”).ClearContents Range(“TblAd”).ClearContents If topqa.infoe <> “” Then ShowTableInfo End If End If End SubAnd 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 = topqa.infoe(“SelTbl”) Set TblSheet = topqa.infoe(“TblSh”) Set TblAddr = topqa.infoe(“TblAd”) tblName = topqa.infoe If tblName = “” Then Exit Sub tblFullName = “‘” & wb.Name _ & “‘!” & tblName Set rngTbl = Range(tblFullName) Set shData = _ wb.Sheets(rngTbl.Parent.Name) Set tblData = _ topqa.infoObjects(tblName) topqa.infoe = topqa.info topqa.infoe = _ topqa.infoess End Sub2. Testing SheetThe 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.TableChangesThis 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 topqa.info MsgBox “Table ” & tblName & ” style is: ” _ & vbCrLf _ & topqa.infoeStyle ‘if table is light blue (9) ‘change to light green (14) If topqa.infoeStyle = _ “TableStyleLight9” Then topqa.infoeStyle = _ “TableStyleLight14” Else topqa.infoeStyle = _ “TableStyleLight9” End If MsgBox “Table ” & tblName & ” style is: ” _ & vbCrLf _ & topqa.infoeStyle End SubTableSheetChangesThis 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 = _ topqa.infoObjects(tblName) ‘new workbook created, to show ‘ table and sheet can be changed ‘ even if workbook not active topqa.info MsgBox “Table ” & tblName & ” style is: ” _ & vbCrLf _ & topqa.infoeStyle ‘if table is light blue (9) ‘change to light green (14) If topqa.infoeStyle = _ “TableStyleLight9” Then topqa.infoeStyle = _ “TableStyleLight14” Else topqa.infoeStyle = _ “TableStyleLight9” End If MsgBox “Table ” & tblName & ” style is: ” _ & vbCrLf _ & topqa.infoeStyle With topqa.infoe(“G1”).Interior If .ColorIndex = 6 Then .ColorIndex = 4 Else .ColorIndex = 6 End If End With End Sub

Get the Sample Files

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.

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