How To Create A Data Table On Excel

Contextures

Create Excel Table & Fix Problems

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.

formatted excel table

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.

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

insert table command

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.

create table dialog box

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.

sort and filter commands

Turn Off Table Filters

In some Excel Tables, you might want to turn off the automatic filter buttons in the table heading row.

Here are a couple of reasons why you might want to hide the filters:

Filter buttons cover some of the text in the column headings
Discourage people from filtering the table, and hiding some of the rows of data

To turn off the filters:

Select a cell in the Excel Table
On the Ribbon's Data tab, click the Filter command, to turn off the filter.

Note: After you turn off the filters, the Excel Table is still recognized as a table, so you can continue to use all its other features, such as Table Styles, and show Banded Rows.

sort and filter commands

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.

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:

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.

Total Row Calculations

When you first add the Total Row, Excel automatically puts a Subtotal formula into the last column of the total row, to show a sum or count of the items in that column.

If the entries are all numbers, the first argument is 109 – SUM.
If there are non-numeric entries, the first argument is 103 – COUNTA

subtotal formula in total row

Totals for Filtered Data

Excel uses the SUBTOTAL function in the total row, because that function only calculates the total for items that are visible after applying a filter.

You can replace the automatic formula in the Total Row, if you prefer, and you can add Total formulas in other columns.

For example, add an AGGREGATE formula in the Total row — it has more functions and options than SUBTOTAL.

aggregate formula in total row

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.

prepare data for excel table

Turn Off the Total Row

After you create a formatted Excel table, it's easy to turn the Total Row on or off.

Select a cell in the Excel table
On the Excel Ribbon, under Table Tools, click the Design tab
In the Table Style Options group, add or remove the check mark for Total Row

show or hide total row

Add Data to Table with Total Row

If the Total Row is showing in an Excel table, you do not need to hide the totals, when you want to add new data.

Use one of the following methods to add data to Excel Table when the Total Row showing. With both methods, the Total row moves down automatically.

Add One New Row

To add a single row of new data:

Select the last cell in the last row of data
Press the Tab key, to start a new row

Add Multiple Rows of New Data

To paste new data at the end of the table

Copy the data that you want to add (Ctrl+C)
Select the first cell in the Title row, even if there's text or a formula there
Then, press Enter, or Ctrl+V, to paste the copied data

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.

Problem: Copy Formulas with Table References

If you create Excel formulas that include table references, and then try to copy those formulas, by dragging to the right, you might get incorrect results.

incorrect total in cell D5

That happens because table references are NOT locked, so the column references adjust automatically, as you drag to the right.

This video shows the problem when copying formulas with table references, and two ways to prevent it. There are notes and a timeline below the video.

Video Timeline

00:00 Introduction
00:46 SUBTOTAL Formula to Check Totals
01:19 SUMIFS Formula with Table References
01:42 Copy the Formula Down One Row
02:13 Copy Across
03:09 Copy With No Problems
03:45 Get More Information

Fix: Copy Formula with Table References

To avoid problems, when you copy formulas with table references, DO NOT DRAG the fill handle to copy across.

Instead, use one of these methods to copy the formulas:

Fill to the right with a keyboard shortcut: Ctrl + R
Copy the formula (Ctrl + C), and paste it into other cells (Ctrl + V)

Both of these methods are shown in the video (above). For step-by-step written details on these methods, go to the Excel Sum Function Examples page.

Table Doesn't Expand For New Data

By default an Excel table should 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

AutoFormat Settings

If Excel tables are not expanding automatically on your computer, the video below shows one of the most common fixes for this problem -- the AutoFormat settings.

Clear Rows Below Table

Another cause for this problem is data on the worksheet, below the Excel table. That can prevent tables from automatically expanding. The data could be space characters, or hidden characters that were copied from a website.

To fix the problem, try deleting or clearing the rows below your Excel Table

Here is a comment from Tully, on my Contextures Blog, explaining this type of problem, and how he fixed it:

I had some unseen data lurking below my table. (Cells with a single space character in them which had been pasted in during some ad hoc testing long ago.)
I assume that Excel sees populated cells below the table and doesn't add them to a new listRow in the table.
Cleared those values and it was working as expected again

Hidden Rows Below Table

Be sure that there aren't any hidden rows on the worksheet, below the Excel table. Those hidden rows could contain data that prevents tables from automatically expanding.

To unhide all the rows:

Click the Select All button, at the top left of the Excel sheet
Right-click on any of the Row buttons
Click the Unhide command

Then, if necessary, clear the rows that were hidden, to remove any data. Or, move the data to a different location in the workbook.

Video: Change Excel Table AutoFormat Settings

you can change the Excel Table AutoFormat settings. Watch the video, to see the steps, and read the detailed instructions, below the video.

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

Turn Table AutoFormat Settings On

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

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

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 change the Excel Table Autoformat settings with a macro, go to the Excel Table Macro page.

The macro code is shown on that page, and you can also download a sample Excel workbook that contains the macro code.

Data Validation Missing in New Rows

In some Excel tables, where there are data validation drop down lists, those lists might not appear when you add new rows at the bottom of the table.

Usually, that happens because the drop down lists have been removed from one or more of the existing rows in the Excel table.

Perhaps someone copied a cell, without a drop down list, from another part of the workbook.
Then, they pasted that cell into the Excel table, overwriting one of the drop down list cells.
The inconsistent formatting confuses Excel, so the new row does not get a drop down list.

Fix the Data Validation

To fix the problem, try these steps:

In the Excel Table, select one of the cells that has a drop down list
Press Ctrl + C, to copy the cell
Select all of the data cells in the column where you copied the cell (do not include the heading cell)
Right-click on one of the selected cells, and click Paste Special
In the Paste Special dialog box, in the Paste section, click Validation
Click OK, to apply the data validation settings to all the selected cells

paste special validation

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.

Excel Table Macros

For more Excel Table macros, go to the Excel Table Macro page.

On that page, you'll find macros to automate the following task:

List All Tables in a Workbook
Get Sheet Name for Specific Excel Table
Change Table AutoFormat Settings

Get the Sample File

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.