Tables in excel | Step by Step Guide to Creating an Excel Table

In excel we have an options for table which is present in the insert tab in the tables section, tables are basically a range with data in rows and columns but the unique feature is that they expand when a new data is inserted in the range in any new row or column in the table, to use a table click on table and select the range of data we want.

What are Excel Tables?

How do you generally submit your data to the management or end-user? Obviously, through report excel table format isn’t it?

Excel Tables can save tons of time in your workplace. In the excel table the headings will hold the reference to that particular column. Tables are an extremely powerful tool when you are working with sets of data. Tables are powerful but at the same time it is tricky too and that is why a lot of people still use normal data range instead of tables.

Difference Between Normal Data and Excel Table Format

Before understanding excel table format we need to understand the difference between the normal table and excel table format.

There is a lot of difference between the normal data and excel table format. Below are the two differences

The first difference is the way the data looks. In the normal range, we do not see any color formatting unless you make an attempt to change the color of the data set.
In normal data range, any formula cell referenced by cells. In excel table format, each cell is named after the heading of the particular column.

How to Create Tables in Excel?

Creating a table in excel is as easy as a walk in the park. I would say we can create an excel table in less than 5 seconds.

We need to remove all blank rows and column from our original data to create an excel table and all the column headings should not have any duplicate heading values. Follow below steps to create first ever table.

You can download this Excel Tables Template here – Excel Tables Template

Step 1: Create data as shown in the below image. My data does not have any empty rows and empty columns.

Step 2: Place your cursor anywhere within the data range and press Ctrl + T and it will open up the below additional dialogue box.

Step 3: Now make sure My table has headers box is checked. What it will do is it will treat the first row in your data as your table headers.

Step 4: Click on OK and your table is ready to rock and it will look like the below one.

Customize Your Tables

Once the table is created we can do a whole lot of changing ranging from changing the table color, give the table a name etc…

#1 – Change the Name of the Table.

Every excel table has its own name. Initially, when the table is created, the default name will be Table1 if it is your first table in the workbook.

But in the long run, when you are working with many tables you will get confused which table you are referring to so it is always a good practice to give a name to each and every table.

Step 1: Select the table. Once the table is selected in the Ribbon tab one extra tab will pop up named after Design.

Step 2: In the Design tab, select Table Name: section and write the name for your table.

Note: Few things to consider when you are naming the table

You cannot give any space in between words while naming the table
You cannot give any special character except underscore (_) but the name should not begin with an underscore.
You need to give a unique name to each table i.e. there should not be any duplication of the table name.
The name should begin with an alphabetic character.
Name of the table should be within 255 characters.

#2 – Change the Color of the Table.

Like how we can change the name of the table, we can change the color too. Follow below steps to change the color of the table.

Step 1: Select the table. Once the table is selected in the Ribbon tab one extra tab will pop up named after Design.

Step 2: Just below the Design tab we have various color options. Choose the color according to your wish.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

10 Advantages of Tables

Now we will look at the 10 advantages of the tables.

#1 – Add data and not to worry about the data changes

When we add additional data to our original data set our formulas will not work in the normal data range. By creating a table, we need to worry about the data range. Since the formula taking, the column header as the entire column reference we need not worry about our formula.

Especially in Excel pivot tables, we need not worry about our data range.

#2 – Insert Pivot Table in Excel Just Selecting One Cell in the Table

We need to select the entire data set to insert our pivot table in excel. But if the table is created we need not select the entire data rather select the one cell and insert the pivot table in excel. In the below image I just placed the curser on one of the cells in the table and inserted excel pivot table.

In the Range section of pivot table name of the table is appearing.

Do not worry about cell reference because we have structured references

We can write understandable formulas using these structured references. Humanly readable formulas always self-explanatory.

#3 – Auto Dragging of the formula to remaining cells

In normal data range, we apply the formula in the first cell and drag but in tables, it auto drags the formula once you enter the formula.

#4 – Visible Table Header when scrolling down

When we are scrolling down table headers always visible to us.

#5 – All kinds of calculations at the bottom cell

Tables can give us Average, SUM, Subtotal, Count, Max, Min, etc….

#6 – Convert the table to the normal range

If need be we can convert the table back to the normal range just by clicking a button.

#7 – Add Slicer to Table

We have an auto filter for Tables but we can add our customized slicer option too. In order to add slicer, we need to go to Design > Tools > Slicers.

Note: Slicer is available in 2013 or later versions.

#8 – Connect Multiple Tables

We connect many tables to each other based on the headers. It is very useful to use tables for Power BI.

#9 – AutoFill of Formulas & Formatting

Once the data increases you need not to worry about the above cells formatting and formula. The table can capture the above cells formatting and formula automatically.

#10 – Turn off Structured Reference in Settings

We can turn off structured formula reference in the settings.

First, go to File

Select Options

Click on Formulas and turn off structured formula reference.