Excel table: end-to-end tutorial with examples

Svetlana Cheusheva by , updated on

The tutorial shows how to insert table in Excel and explains the advantages of doing so. You will find a number of nifty features such as calculated columns, total row and structured references. You will also gain understanding of Excel table functions and formulas, learn how to convert table to range or remove table formatting.

Table is one of the most powerful Excel features that is often overlooked or underestimated. You may get along without tables just fine until you stumble upon them. And then you realize you've been missing an awesome tool that could save much of your time and make your life a lot easier.

Converting data to a table can spare you the headache of creating dynamic named ranges, updating formula references, copying formulas across columns, formatting, filtering and sorting your data. Microsoft Excel will take care of all this stuff automatically.

What is a table in Excel?

Excel table is a named object that allows you to manage its contents independently from the rest of the worksheet data. Tables were introduced in Excel 2007 as in improved version of Excel 2003 List feature, and are available in all subsequent versions of Excel 2010 through 365.

Excel tables provide an array of features to effectively analyze and manage data such as calculated columns, total row, auto-filter and sort options, automatic expansion of a table, and more.

Typically, a table contains related data that are entered in a series of rows and columns, though it can consist of a single row and/or column. The screenshot below shows a difference between a usual range and a table: Excel table vs. range

Note. An Excel table should not be confused with a data table, which is part of the What-If Analysis suite that allows calculating multiple results.

How to make a table in Excel

Sometimes, when people enter related data in a worksheet, they refer to that data as a "table", which is technically incorrect. To convert a range of cells into a table, you need to explicitly format it as such. As is often the case in Excel, there is more than one way to do the same thing.

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. Insert an Excel 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. Insert an Excel table with the selected style.
  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. Creating a table in Excel

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! An Excel table is created.

Notes:

  • If you want to manage several independent data sets, you can make more than one table in the same sheet.
  • It is not possible to insert a table in a shared file because the table functionality is not supported in shared workbooks.

10 most useful features of Excel tables

As already mentioned, Excel tables offer a number of advantages over normal data ranges. So, why don't you benefit from the powerful features that are now only a button click away?

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. Sorting and filtering options in an Excel table

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. Show or hide the filter arrows in the table header row.

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. Changing Excel table styles

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: The default Table Style options

Table Styles tips:

  • If the Design tab has disappeared from your workbook, just click any cell within your table and it will show up again.
  • To set a certain style as the default table style in a workbook, right-click that style in the Excel Table Styles gallery and select Set As Default.
  • To remove table formatting, on the Design tab, in the Table Styles group, click the More button More button in the bottom-right corner, and then click Clear underneath the table style thumbnails. For full details, see How to clear table formatting in Excel.

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. A table expands automatically to include new data.

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: Adding the total row

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: The total row is added to the end of the table.

Total Row tips:

  • Excel table functions are not limited to the functions in the drop-down list. You can enter any function you want in any total row cell by clicking More Functions in the dropdown list or entering a formula directly in the cell.
  • Total row inserts the SUBTOTAL function that calculates values only in visible cells and leaves out hidden (filtered out) cells. If you want to total data in visible and invisible rows, enter a corresponding formula manually such as SUM, COUNT, AVERAGE, etc.

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: Creating a calculated column

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: A calculated column is created.

Calculated Column tips:

  • If a calculated column is not created in your table, make sure the Fill formulas in tables to create calculated columns option is turned on in your Excel. To check this, click File > Options, select Proofing in the left pane, click the AutoCorrect Options button, and switch to AutoFormat As You Type tab.
  • Entering a formula in a cell that already contains data does not create a calculated column. In this case, the AutoCorrect Options button appears (like in the screenshot below) and lets you overwrite the data in the entire column so that a calculated column is created.
  • You can quickly undo a calculated column by clicking the Undo Calculated Column in AutoCorrect Options, or clicking the Undo button on the Quick Access toolbar.
Undoing a calculated column

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: Printing only the table

How to manage data in an Excel table

Now that you know how to make a table in Excel and use its main features, I encourage you to invest a couple more minutes and learn a few more useful tips and tricks.

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. Converting a table to a 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. Inserting a new row inside the table

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: Deleting table rows or columns

How to resize an Excel table

To resize a table, i.e. include new rows or columns to the table or exclude some of the existing rows or columns, drag the triangular resize handle at the bottom-right corner of the table upwards, downwards, to the right or to the left: Resizing an Excel table

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.

Selecting a table column or row

Move the mouse point to the top edge of the column header or the left border of the table row until the pointer changes to a black pointing arrow. Clicking that arrow once selects only the data area in the column; clicking it twice includes the column header and total row in the selection like shown in the following screenshot: Selecting a table column or row

Tip. If the whole worksheet column or row gets selected rather than a table column / row, move the mouse pointer on the border of the table column header or table row so that the column letter or row number is not highlighted.

Alternatively, you can use the following shortcuts:

  • To select a table column, click any cell within the column, and press Ctrl+Space once to select only the column data; and twice to select the entire column including the header and total row.
  • To select a table row, click the first cell in the row, and then press Ctrl+Shift+right arrow.

Selecting an entire table

To select the table data area, click the upper-left corner of the table, the mouse pointer will change to a south-east pointing arrow like in the screenshot below. To select the entire table, including the table headers and total row, click the arrow twice. Selecting an entire table

Another way to select the table data is to click any cell within a table, and then press CTRL+A. To select the entire table, including the headers and totals row, press CTRL+A twice.

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.

Tip. To display more than one item, hold down the Ctrl key while picking the items.

Create a slicer to filter table data in the visual way.

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.

To rename an Excel table:

  1. Select any cell within the table.
  2. On the Design tab, in the Properties group, type a new name in the Table Name box.
  3. Press Enter.

That's all there is to it! Changing the table name

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! Removing duplicates from an Excel table

Tip. If you have inadvertently removed the data that should be kept, click the Undo button or press Ctrl+Z to restore the deleted records.

This tutorial is just a quick overview of the main Excel table features. Just give them a try, and you will find new uses of tables in your daily work and discover new fascinating capabilities. I thank you for reading and look forward to seeing you on our blog next week!

Table of contents