25 Actionable Tips for Excel Tables

Use Excel Tables to simplify your work! Learn how to create dynamic ranges with easy-to-read formulas using tables. This tutorial will explain how to add new data to a range or 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?

Select the range that contains data.
Use the Insert Table keyboard shortcut, Control + T
Select the ‘My Table has headers’ checkbox if your Table has headers.
Click OK, and Excel will create a new 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 must rename it and add a short, user-friendly name like ‘Sales.’

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.

Select your Table and locate the Table Design Tab to change the default name. Then, jump to the Table Name > Properties section, add a new name, and press Enter.

The Parts of a Table

All Excel tables have two required parts and one optional part.

The Column Header Row is the first row of the Table. It contains identifiers such as ‘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.

Navigating between Tables

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 and 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.

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.

Working with Tables

Moving Excel Tables

A mouse’s drag-and-drop method is the easiest way to move a table to a new location. In the example, we’ve just selected the original Table and moved it to the K4:N13 range.

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

You may frequently want to change the table structure or swap rows or columns. First, select the table row or column. Make sure that the header row is selected. Drag the selected range and drop it to a new area.

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

Keep Table headers always on the top

Are you working with large data sets? No problem! You can scroll down without any trouble, and the table header will stay on top. It is 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

Sometimes, 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.

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 will appear, but the table structure will remain. It’s not necessary to select the range and add a new name for a range again.

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. It’s good to know you don’t have to enter these formulas manually. Great!

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

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 500 rows, for example. To resize a table, select the new data range for your Table. Click the OK button.

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 technique, too. Select the small green triangle while pressing the left mouse button.

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.

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 the ‘Set as default’ option.

Learn more on how to remove table formatting in Excel.

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 in the picture below:

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, 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.

Build dynamic charts using Excel Tables

In our latest article, we discussed dynamic charts. Tables are great for updating data frequently (adding rows or columns). Put your fresh data into the Table, and the chart will refresh using the entered values.

Insert a new slicer into a Table

It is 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:

Select the Table
Locate the Table Design tab on the ribbon
Locate the Tools group and Click the Insert Slicer button.

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

Convert Excel Tables to a normal range back

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

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

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

Final words

Using Excel Tables can transform your data management and analysis tasks, making them more efficient and accurate. With the actionable tips provided in this tutorial, you can harness the full potential of Excel Tables to streamline your workflow. These tips cover various functionalities to enhance productivity, from basic table creation and navigation to advanced formatting and dynamic data analysis.

Additional resources: