Excel table: end-to-end tutorial with examples

3 ways to create a table in Excel

To insert a table in Excel, organize your data in rows and columns, click any single cell within your data set, and do any of the following:

1. On the Insert tab, in the Tables group, click Table. This will insert a table with the default style.
2. On the Home tab, in the Styles group, click Format as Table, and select one of the predefined table styles.
3. If you prefer working from the keyboard rather than using a mouse, the fastest way to create a table is pressing the Excel Table shortcut: Ctrl+T

Whatever method you choose, Microsoft Excel automatically selects the entire block of cells. You verify if the range is selected correctly, check or uncheck the My table has headers option, and click OK.

As the result, a nicely formatted table is created in your worksheet. At first sight, it may look like a normal range with the filter buttons in the header row, but there is much more to it!

1. Integrated sorting and filtering options

Usually it takes a few steps to sort and filter data in a worksheet. In tables, filter arrows are automatically added in the header row and enable you to use various text and number filters, sort in ascending or descending order, by color, or create a custom sort order.

If you don't plan to filter or sort your data, you can easily hide the filter arrows by going to the Design tab > Table Style Options group, and unchecking the Filter Button box.

Or, you can toggle between hiding and showing the filter arrows with the Shift+Ctrl+L shortcut.

Additionally, in Excel 2013 and higher, you can create a slicer to filter the table data quickly and easily.

2. Column headings are visible while scrolling

When you are working with a large table that does not fit on a screen, the header row always remains visible when you scroll down. If this doesn't work for you, just be sure to select any cell inside the table before scrolling.

3. Easy formatting (Excel table styles)

A newly created table is already formatted with banded rows, borders, shading, and so on. If you don't like the default table format, you can easily change it by selecting from 50+ predefined styles available in the Table Styles gallery on the Design tab.

Apart from changing table styles, the Design tab lets you turn the following table elements on or off:

• Header row - displays column headers that remain visible when you scroll the table data.
• Total row - adds the totals row at the end of the table with a number of predefined functions to choose form.
• Banded rows and banded columns - display alternate row or column colors.
• First column and last column - display special formatting for the first and last column of the table.
• Filter button - shows or hides filter arrows in the header row.

The screenshot below shows the default Table Style Options:

For more information, please see How to use Excel table styles.

4. Automatic table expansion to include new data

Usually, adding more rows or columns to a worksheet means more formatting and reformatting. Not if you've organized your data in a table! When you type anything next to a table, Excel assumes you want to add a new entry to it and expands the table to include that entry.

As you can see in the screenshot above, the table formatting is adjusted for the newly added row and column, and alternate row shading (banded rows) is kept in place. But it's not just the table formatting that is extended, the table functions and formulas are applied to the new data too!

In other words, whenever you draw a table in Excel, it is a "dynamic table" by nature, and like a dynamic named range it expands automatically to accommodate new values.

To undo the table expansion, click the Undo button on the Quick Access Toolbar, or press Ctrl+Z like you usually do to revert the latest changes.

5. Quick totals (total row)

To quickly total the data in your table, display the totals row at the end of the table, and then select the required function from the drop-down list.

To add a total row to your table, right click any cell within the table, point to Table, and click Totals Row.

Or, go to the Design tab > Table Style Options group, and select the Total Row box:

Either way, the total row appears at the end of your table. You choose the desired function for each total row cell, and a corresponding formula is entered in the cell automatically:

6. Calculating table data with ease (calculated columns)

Another great benefit of an Excel table is that it lets you calculate the entire column by entering a formula in a single cell.

For example, to create a calculated column in our sample table, enter an Average formula in cell E2:

As soon as you click Enter, the formula is immediately copied to other cells in the column and properly adjusted for each row in the table:

7. Easy-to-understand table formulas (structured references)

An indisputable advantage of tables is the ability to create dynamic and easy-to-read formulas with structured references, which use table and column names instead of regular cell addresses.

For example, this formula finds an average of all the values in columns Jan through Mar in the Sales_table:

=AVERAGE(Sales_table[@[Jan]:[Mar]])

The beauty of structured references is that, firstly, there are created automatically by Excel without you having to learn their special syntax, and secondly, they adjust automatically when data is added or removed from a table, so you don't have to worry about updating the references manually.

For more information, please see Structured reference in Excel tables.

8. One-click data selection

You can select cells and ranges in a table with the mouse like you normally do. You can also select table rows and columns in a click.

9. Dynamic charts

When you create a chart based on a table, the chart updates automatically as you edit the table data. Once a new row or column is added to the table, the graph dynamically expands to take the new data in. When you delete some data in the table, Excel removes it from the chart straight away. Automatic adjustment of a chart source range is an extremely useful feature when working with data sets that frequently expand or contract.

10. Printing only the table

If you want to print just the table and leave out other stuff on the worksheet, select any sell within your table and press Ctrl+P or click File > Print. The Print Selected Table option will get selected automatically without you having to adjust any print settings:

How to convert a table to a range

If you want to remove a table without losing the table data or table formatting, go to the Design tab > Tools group, and click Convert to Range.

Or, right-click anywhere within the table, and select Table > Convert to Range.

This will delete a table but keep all data and formats intact. Excel will also take care of the table formulas and change the structured references to normal cell references.

For more information, please see How to convert Excel table to normal range .

How to add or remove table rows and columns

As you already know, the easiest way to add a new row or column to a table is type any value in any cell that is directly below the table, or type something in any cell to the right of the table.

If the Totals row is turned off, you can add a new row by selecting the bottom right cell in the table and pressing the Tab key (like you would do when working with Microsoft Word tables).

To insert a new row or column inside a table, use the Insert options on the Home tab > Cells group. Or, right-click a cell above which you want to insert a row, and then click Insert > Table Rows Above; to insert a new column, click Table Columns to the Left.

To delete rows or columns, right-click any cell in the row or column you want to remove, select Delete, and then choose either Table Rows or Table Columns. Or, click the arrow next to Delete on the Home tab, in the Cells group, and select the required option:

How to select rows and columns in a table

Generally, you can select data in your Excel table in the usual way using the mouse. In addition, you can use the following one-click selection tips.

Insert a slicer to filter table data in the visual way

In Excel 2010, it is possible to create slicers for pivot tables only. In newer versions, slicers can also be used for filtering table data.

To add a slicer for your Excel table, just do the following:

• Go to the Design tab > Tools group, and click the Insert Slicer button.
• In the Insert Slicers dialog box, check the boxes for the columns that you want to create slicers for.
• Click OK.

As the result, one or more slicers will appear in your worksheet, and you simply click the items you want to show in your table.

How to name a table in Excel

When you create a table in Excel, it is given a default name such as Table 1, Table 2, etc. In many situations, the default names are fine, but sometimes you may want to give your table a more meaningful name, for example, to make the table formulas easier to understand. Changing the table tame is as easy as it can possibly be.

How to remove duplicates from a table

This is another awesome feature of Excel tables that many people are totally unaware of. To delete duplicate rows in your table, just do the following:

1. Got to the Design tab > Tools group, and click Remove Duplicates.
2. In the Remove Duplicates dialog box, select the columns that may contain duplicates.
3. Click OK.

Done!