25 Actionable Tips for Excel Tables

Use Excel Tables to simplify your work! Learn how to create dynamic ranges, easy-to-read formulas, and structured references using a table. In this tutorial, we’ll explain how to add new data to a range or how to manage Tables. Keep your formulas ready for auditing!

This guide provides useful tricks and examples.

Fundamentals of Excel Tables

In this section, we’ll start with the basics.

Creating an Excel Table is Easy

How to create an Excel table?

  1. Select the range which contains data.
  2. Use the keyboard shortcut, Control + T
  3. If your table has headers, select the ‘My Table has headers’ checkbox.
  4. Click OK, and Excel will create a new table.
how to create an Excel Table

If you are in a hurry, Excel Tables is yours! On the right side of the picture, you’ll see banded rows.

Rename a Table

When you create a new Excel Table, Excel will assign a custom name, like Table1. You have to rename it and add a short, user-friendly name like ‘Sales.’

Important: Each table must have a unique name that contains letters, numbers, or the underscore character (for example, using spaces or special characters between words is not allowed.

To change the default name, select your table and locate the Table Design Tab. Jump to the Table Name > Properties section. Add a new name and press Enter.

add a new name

The Parts of a Table

All Excel Tables have two required and one optional part.

The Column Header Row is the first row of the table. It contains identifiers, for example, ‘Product,’ ‘Order Date,’ and so on.

The body is the main data set.

Optional: you can switch an additional (Totals) row using the ‘Ctrl + Shift + T‘ shortcut.

what are table parts

If you want to find your table quickly, locate the name box and use the drop-down menu in the top-left area of your workbook. Choose the table name, what you want to activate. The range will be highlighted.

What if your table is located in a different tab? Excel will jump to the selected table.

navigating between tables

If you have tabular data, you can easily convert it to an Excel Table.

If you select a simple cell in the table, you can select the actual row using the ‘Shift + Space’ command. To select rows, use the ‘Control + Space’ keyboard shortcut.

keyboard shortcuts for tables

Working with Tables

Moving Excel Tables

The easiest way to move a table to a new location is using a drag and drop method with a mouse.

In the example, we’ve just selected the original table then moved it to the K4:N13 range.

drag and drop a table

Tip: This is the fastest way to move tables to a new location. The method works only on the same sheet!

Swapping rows or columns in the table

It can frequently happen that you want to change the table structure or swap rows or columns. As first, select the table row or a table column. Make sure that the header row is selected. Drag the selected range and drop to a new area.

Excel will swap the given rows or columns. The method is safe because Excel will not overwrite the existing data.

swapping rows and columns

Keep Table headers always on the top

Excel Tables work simply. Are you working with large data sets? No problem! You can scroll down without any trouble. The table header will stay on the top. It’s not necessary to use the good old Freeze Top Rows function.

You can find it under the Freeze Panes group.

keep your table headers always on the top

Expand your Table easily

In some cases, you need to insert rows or columns into an Excel Table. We’ll show you how to do that. The Excel Table is a dynamic range; it will resize even if you add or delete rows or columns.

insert a new row or column

To add a new row, select the bottom-right cell in the range, in this example, cell F11. Press the Tab button; a new row appears, and the table structure remains.

It’s not necessary to select the range and add a new name for a range again.

insert a new row using tab shortcut

Show Totals (Sum, Count, Min, Max) without using formulas

With the help of tables, it’s easy to show dynamic Totals. Use your preferred formulas, like SUM, COUNT, AVERAGE, MIN, and MAX. Good to know that you don’t have to enter these formulas manually. Great!

You have a filtered Excel table on the left side and a regular table on the right side in the picture below. Use the ‘Control + Shift + T‘ shortcut to display the additional row under the table.

how to show totals

Resize an Excel Table

In the example below, you’ll learn how to change the table size using the ‘Resize Table’ command. This function is yours if you need to prepare a table for – for example – 500 rows.

To resize a table, select the new data range for your table. Click the OK button.

resize a table

Note: The table headers must remain in the same row, and the resulting table range must overlap the original table range.

Or you can use the classic resize on the fly technic too. Select the small green triangle keeping the left mouse button pressed.

resizing with mouse

Excel Tables and Formulas

Tables and Calculated Columns

Managing formulas has never been easier! The calculated columns function makes your work more accurate. Let’s see an example:

Try to enter a simple formula in a column. Now press Enter, and the Excel will populate the table automatically. It’s great for auditing purposes; you don’t have to copy the formula manually.

The result is error-free.

calculated columns example

Auto-update after changing Formulas

Let us assume that you need to change the formula in the calculated column. What will happen?

Update the formula in an arbitrary cell in the calculated column. Change in any row is propagated to all banded rows.
In the example below, the price has been changed to 40% in one step.

Auto-update after changing Formulas

Build easy to read Formulas

Are you in trouble with the spreadsheet audit? Sometimes, an audit can be painful when you are working with complex formulas. Structured reference is a great Table feature; it uses easy formula syntax to point to a part of a table. Let us see an example!

We want to calculate the total sales. If you are working with normal ranges, the formula will be:

=SUM(B3:E10)

If you have a table, work with structured references! The formula looks like this:

=SUM(SalesData)

build-easy-to-read-formulas

Benefits of using dynamic ranges

As we stated above, the biggest advantage of using tables is that we can automatically expand the table with new records. So, the Excel table is a dynamic range!

OK, it’s time to learn how to use our formulas with a dynamic range. In the example, locate the table named ‘SalesData.’

You can use your formulas, as usual, to add the table name as a parameter. If you add new records, the formula will refresh the range and modify the result.

table based formulas

Enter structured references using autocomplete function

If you want to speed up the data entry, use the Excel auto-complete function. Try to start typing the table names. After entering the first character, a floating window appear. Select the correspondent table name from a drop-down list.

structured references using autocomplete function

To move the list up or down, use the arrow keys. Select and confirm the table name using the Tab button. The trick works fine with the Table columns too. In the example below, we’ve just entered the Table name.

To refer to a column, open a bracket by typing a ‘[‘ sign. The trick as mentioned above will work fine.

how to refer a column

Build formulas using on thy fly selection with the mouse

What is on the fly formula editing with the mouse? It’s an easy way to select the given parameter of the formula. To add an argument to a function, select the range of the table using the mouse.

Excel has a built-in autocomplete function; please check the result in the picture below. In the example, select the Price column and click Enter.

select a function argument using mouse

Table Formatting

Change table formatting quickly

When you create a new table, it has a default formatting style. To override the displayed theme select a cell in the range. Choose the Table design tab on the ribbon and locate the Table Styles Group.

Click your preferred style, and the new theme will appear with a single click.

change formatting style

If you want to use the classic style without formatting, select the table and choose the default style by clicking on the top-left corner of the table style previews. The name of the first style is ‘None.’

Choose your Table style

You can choose a default table style. What is the default style? Start Excel and create your first table. Excel assigns a built-in style to the table.

Select the style that you want to apply to all new tables in the future. Right-click on the Table preview and click on the ‘Set as default’ option.

set default style

Tables and Data Sources

Quick Analysis works with Excel Tables

Everyone knows that the Quick Analysis Tool is a great function in Excel. Create your table first. After that, click Control + Q to create a floating row like on the picture below:

quick analysis for tables - totals

Apply various methods to analyze your data. Use conditional formatting, create chars, or calculate Totals, among others. If you need more power, use conditional formatting.

Use a Table as a pivot table source

Is it possible to use an Excel table as a pivot table source? Absolutely.

As we mentioned before, the Pivot table will track the changes in the table. If you add new data to the range, the pivot table will refresh automatically.

Go to the Insert tab. Insert a new pivot table.

A new window appears. Select the data that you want to analyze.

Finally, click OK.

create a pivot table from a range

Build dynamic charts using Excel Tables

In our latest article, we’ve just talked about dynamic charts.

If you update your data frequently (adding rows or columns), tables are great for these purposes. Put your fresh data into the table, and the chart will refresh using the entered values.

build dynamic charts

Insert a new slicer into a table

Good to know that all Excel Tables are slicer-ready? What does that mean in practice? Slicers are special filters. With its help, you can filter data quickly in various ways.

To add a new slicer:

  1. Select the table
  2. Locate the Table Design tab on the ribbon
  3. Locate the Tools group and Click the Insert Slicer button.
insert a new slicer

In the example below, now we have a slicer for Products:

working with table slicers

Convert Excel Tables to a normal range back

Tables are useful, but sometimes you need to work with normal ranges. To convert an Excel Table into a range back, do the following steps.

  1. Select the Table
  2. Remove the formatting style (choose the ‘None’ table style)
  3. Click on the Table Tools Tab
  4. Apply the ‘Convert to Range’ commands.

Note: Step 2 is necessary; otherwise, the formatting styles remain.

convert table to a normal range

Conclusion

Excel Tables are versatile tools. With its help, you can speed up your daily tasks.

You May Also Like the Following Excel Tutorials: