Working with Tables in Excel

Content

Introduction

If you prefer watching video, here is a 45 recording of my "Modern Excel webinar" session of January 2020

A lot of the data you use in Excel is laid out as a table. Since Excel 2007 there is handy functionality for this called Format As Table. Contrary to what you might expect from this name however, the format bit is irrelevant or at best a nice side-effect.

This article introduces you into the concepts of working with Tables in Excel and shows you how they may help you in your everyday Excel use.

Creating Your Table

Creating a table in Excel is easy. Of course you already have some data available somewhere on your sheet. Select the cells that contain the data:

Select the table area
Figure 1: Select the table area

Next, on the Home tab of the ribbon, find the group called "Styles". Click on the button that says "Format as Table" (see figure 2):

Format as Table button on the Styles group of the Home tab
Figure 2: "Format as Table" button on the Styles group of the Home tab.

After clicking this button, Excel shows a new user interface element called a gallery, with a number of formatting choices for your table, see figure 3:

Table format gallery
Figure 3: Table format gallery.

Select one of the predetermined formats. After clicking one of the formats, Excel will ask you what range of cells you want to convert to a table (see figure 4). If your table contains a heading row, make sure the checkbox is checked. Click OK to convert the range to a table.

Dialog asking what range of cells has to be converted to a table
Figure 4: Dialog asking what range of cells has to be converted to a table.

After you’ve finished these steps, your table will look like figure 5.

Range of cells, after converting to table
Figure 5: Range of cells, after converting to table

Table Tools add-in

Together with fellow Excel MVP Frederic Le Guen I devised a small add-in to make your table life a little simpler.

The tool adds a tab to the ribbon called Table Tools:

TableTools ribbon tab

And a right-click menu which differs whether you right-click within a table or outside of a table:

  • Within a table it shows the columns names so you can quickly goto a column

    TableTools right-click menu inside a table

  • Outside of a table it shows a list of tables in the active workbook

    TableTools right-click menu outside a table
  • If you use the Table Name editbox on the TableTools ribbon tab, it will not only rename the table, but also automatically update all queries which use this table with the new name
  • If you edit a column name in a table, the tool will update all PowerQuery queries with this new name.
  • Note that the tool has an improved interface to convert a range to a table, which enables you to name the table right when you define the table
    TableTools Create Table dialog
  • In addition, you can add a comment to the table right there, which appears when you type a formula:
     Tooltip showing Comment of a table

Special functionality of a Table

After defining a table, the area gains special functionalities:

1. Integrated autofilter and sort functionality

If your Table has a header row, it will always have filter and sorting dropdowns in place on the header row. See figure 6:

 Table sort and filter drop-down
Figure 6: sorting and filtering dropdowns

2. Easy selecting

Selecting an entire column or row is simple: move your mouse to the top of the table until the pointer changes to a down pointing arrow (figure 7) and click. The data area of that column is selected. Click again to include the header and total rows in the selection.

Mouse pointer indicating selection of a Table column
Figure 7: selecting an entire column of data within your table

You can also select the entire data area or the entire table by clicking near the table’s top-left corner (the mousepointer changes to a south-east pointing arrow, see figure 8).

Mouse pointer indicating selection of the entire Table
Figure 8: selecting all data within your table or the whole table is just one or two clicks away.

3. Header row remains visible whilst scrolling

If your table is larger than fits on a screen and you scroll down, Excel 2007 has a nice new feature: the column letters are temporarily replaced with the table’s column names (but only whilst you’re inside the table!). See figure 9.

Headings of a table show on top of column headers
Figure 9: Table header names on Excel’s column header when scrolling

4. Automatic expansion of table

If you type anything next to a table, Excel assumes you want to expand the table and automatically increases the table size to include your new entry. Of course you can undo this expansion too, or switch off this behavior entirely.

5. Automatic reformatting

When you insert or remove a row (or column) in your table, Excel will automatically adjust the formatting: alternate shading is kept nicely in place.

6. Automatic adjustment of charts and other objects source range

If you add rows to your table, any object that uses your table’s data will automatically include the new data.

Table Options on the Ribbon

Once you have selected any of the cells within the table, you will see a new tab appear on the ribbon, called Table Tools, Design. Figure 10 shows you what the ribbon will look like after you click this tab.

Table options on the ribbon
Figure 10: Ribbon after clicking the Table Tools tab.

Each group on this tab is discussed in the following paragraphs.

Properties group

The properties group (see figure 11 below) enables you to do two things:

Table properties group
Figure 11: properties group on Table Tools tab

1. change the Name of the table

The name of a table is used when you refer to cells within the table in a formula.

2. Change the size of the table

Click this control to change the size of your table.

Tools group

This group (see figure 12) has three controls:

 Table Tools group
Figure 12: Tools group on Table Tools tab

1. Summarize with PivotTable

It is obvious what this control does. After you have created the pivot table, you don’t need to worry about updating the sourcerange of the pivot table anymore. If you add data to your table, Excel automatically expands the source range of the Pivot table to reflect your changes. Of course you still have to refresh the Pivot table to see the results.

2. Remove Duplicates

Another new feature which has been added to Excel 2007. After clicking this control, you are presented with a dialog with which you can select the columns that you want to use to determine whether a row in the table is unique. (see figure 13)

 Remove duplicates dialog
Figure 13: Remove Duplicates dialog

3. Convert to Range

By pressing this button you demote the table back to a normal range. Beware if you do this when you’ve based e.g. a pivot table on the range, the Pivot table’s source range will not be updated and the pivot table cannot be refreshed anymore.

The External Table Data Group

This group (shown in figure 14) is all about the source data of a table and only applies if the data in the table has been imported into Excel using a database- or webquery or a sharepoint list.

 External table data group
Figure 14: External Table Data group on the Table Tools tab of the ribbon

This group has 5 buttons:

1. Export Data

This is in fact a combobutton. If you press it you’re offered two options,

"Export Table to SharePoint List" and "Export Table to Visio PivotDiagram". What these are exactly is beyond the scope of this article.

2. Refresh

Use this combobutton to refresh the external data in your table. If you click the arrow beneath the button, you’re offered a menu which amongst others also includes "Refresh All", with which you can refresh all external data ranges in your file.

3. Data Range Properties

This button can be used to change the properties of the external data you have based your table on.

4. Open in Browser

If your table is a sharepoint list, this button enables you to open a browser window with that list.

5. Unlink

If your table is a sharepoint list, this button disconnects the table from the list.

Table Style Options Group

This group houses the controls which determine how table styles are applied to your table (see figure 15).

 Table Style options group
Figure 15: Table Style Options group on the Table Tools tab of the ribbon

1. Header Row

When this box is unchecked, Excel removes the header row from your table. The cells of the header row are cleared, but Excel does remember the header. If you type anything into any cell in that now empty row, Excel will not overwrite that information when you check the box again. Instead, Excel will insert a new row to show the header. Cells below the table are then moved down.

2. Total Row

Check this box if you want a total row below your table. Excel will automatically add a sum function below the last column in your table.

3. Banded Rows

Check this box to get alternating shading for the rows in your table.

4. First Column

If you check this box, the first column of your table will be formatted differently from the other columns.

5. Last Column

Formats the last column of your table differently from the other columns.

6. Banded Columns

Check this box to get alternating shading for the columns in your table

Table Styles Group

The last group on the Table Tools tab enables you to quickly change the style of your table (see figure 16).

 Table Styles gallery
Figure 16: Table Styles group on the Table Tools tab of the ribbon

Click the dropdown button to the right of the gallery to see all choices available to you. Hover over a particular style to see what your table would look like when you click it. At the bottom of the gallery there are two extra choices:

1. New Table Style

This option enables you to create your own table style.

2. Clear

Use this to remove the table style from your table entirely. Number formats are retained.

Referencing cells in a table (structured referencing)

Excel 2007 introduces a new syntax to refer to cells inside a table. To see how this works, click in a cell to the immediate right of the table, hit the = sign, type SUM( and then click on any cell with data within the table. You’ll get a formula like this one:

Excel 2007: =SUM(Table3[[#This Row];[Discount]])

This syntax has been simplified in later Excel versions (2010 and up):

=SUM(Table3[@Discount])

The new naming convention to refer to the cells in your table works as follows:

Table3: The name of your table

[#This Row] in Excel 2007, @ in Excel 2010 and later: Denotes the data comes from the same row your formula cell is in

[Discount] : The column inside the table

Some other examples:

Description
Excel 2007
Excel 2010 and up
The entire table
=Table1
=Table1
The same row in the table
=Table1[[#This Row][Discount]]
=Table1[@Discount]
Heading of table
=Table1[#Headers]
=Table1[#Headers]
Entire table (2)
=Table1[#All]
=Table1[#All]
Table total row
=Table1[#Totals]
=Table1[#Totals]

Because of this naming convention, you are not allowed to have more than one column inside a table with a specific heading. As soon as you try to type a new heading that duplicates an existing one, Excel will automatically correct the duplication by appending a number to the new column name.

A nice feature of tables is immediately shown as soon as you hit enter: your table is automatically resized to include your formula (Excel has also made up a column heading for you) and the formula is automatically copied down to fill the entire column alongside your data! Both actions may be undone by using the smart tag that appears.

Referring to a table from another workbook

Even though I mentioned that a table is also stored as a range name there is a peculiarity. The range name points only to the data rows of the table. The header row is NOT included. This means that if you want to create a pivot table on data that is in a table in another workbook you need to use a syntax that differs from the old days.

Normally you would refer to a range name "TableName" in workbook "WorkbookName.xls" using: [WorkbookName.xls]!TableName
But although a table is represented by a range name, you should not use the range name syntax as the source. Rather you must use this:

WorkbookName!TableName

This will convince Excel that you are pointing to a table and then includes the header rows.

Other table advantages

Using a table is advantageous on Excel for Android as well. It will enable you to edit records in the table by going into Card view, which makes it a LOT easier to work with the records in the table:

Editing in Excel for Android is a lot easier with a table in Card view

Conclusion

As you have seen, Tables are a great addition to Excel’s features. Most of these features were already part of Excel 2003's List feature. But Excel versions 2007 and up builds upon that feature, significantly improving it. The most important benefits are:

  • Integrated autofilter and sort functionality
  • Easy selecting
  • Header row remains visible whilst scrolling
  • Automatic expansion of table
  • Automatic reformatting
  • Automatic adjustment of charts and other objects source range

If you're interested in VBA, read about Excel Tables and VBA here.

Ron de Bruin has written a nice add-in to ease working with tables.


Comments

Showing last 8 comments of 413 in total (Show All Comments):

 


Comment by: Del Simcox (2-2-2020 22:42:00) deeplink to this comment

There must be a syntax to get the Max or Min values in a table column - but I'm unable to get it to work on Excel (Office 365). Can you help please?


Comment by: Jan Karel Pieterse (4-2-2020 11:21:00) deeplink to this comment

Hi Dell,

You mean using a formula? Just type =MIN( and then select all cells in a single column of the table, Excel should use the correct syntax automatically. Something like

=MAX(Table1[Sales])


Comment by: Hasan (22-2-2021 13:01:00) deeplink to this comment

I prepared an INVENTORY MANAGEMENT SYSTEM in excel but i want that a person cant able to enter more sales than purchases, means
On Feb 20, 2021 Purchase 50 pcs of rose
On Feb 22, 2021 sale 51 pcs of rose (there should be a restriction but i am unable to do it)
So kindly guide me that how can i restrict because if a have 50 roses so it will take time to sold, so sometimes some clients will take 5 or some will take 10 roses. I did use data validation but not any good result to restrict the sales not more than purchases.


Comment by: Jan Karel Pieterse (23-2-2021 10:19:00) deeplink to this comment

Hi Hasan,

I would suggest to look at the Data, Validation options. You can set a maximum value for the sales cell which you can depend on another cell's value (the purchase cell)


Comment by: gerdami (2-8-2021 16:55:00) deeplink to this comment

Hi Jan Karel, I get this error message whenever I want to create a table from a range:

VBA compile error, variable not defined: tbxAddress.
in Private Sub UserForm_Initialize()

Table Tools 1.0 build 008
Excel for Msft 365 MSO (16.0.13...) 64-bit

Best,
gerdami


Comment by: Jan Karel Pieterse (4-8-2021 10:54:00) deeplink to this comment

Hi gerdami,

Which version are you using (please click the About button)?
I do not get that particular error on my system.


Comment by: Kedar Kulkarni (25-8-2021 19:37:00) deeplink to this comment

Great utility as always. I get one runtime error when I just open excel and no open workbook and switch to the table tools tab. (it should be consistently reproducible).

runtime error 1004 - it is in the following method because there is no active workbook. We might want to check for an active workbook before accessing the worksheets.


the row for error is
For Each oSh In Worksheets

You already know the answer but just for others -
we should just add below after Public Function getTableCount() As Long
If ActiveWorkbook Is Nothing Then Exit Function


Comment by: Jan Karel Pieterse (25-8-2021 20:27:00) deeplink to this comment

Hi Kedar,

Thanks for letting me know!


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].