by Svetlana Cheusheva, 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.
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:
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.
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.
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:
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!
Notes:
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?
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.
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.
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:
The screenshot below shows the default Table Style Options:
Table Styles tips:
For more information, please see How to use Excel table styles.
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.
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:
Total Row tips:
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:
Calculated Column tips:
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.
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.
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.
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:
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.
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 .
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:
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:
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.
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:
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 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.
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.
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:
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.
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.
That's all there is to it!
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:
Done!
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