Tables in Excel (Table of Contents)
Tables in excel is very helpful for giving a structure to data sets. Excel tables have very useful features from arranging the data, providing the headers along with applied filters. We can access tables from Insert menu tab or select shortcut key Ctrl + T. For this we just need to select the range of cells that we need to include in the table. We can even change Table styles from the Design tab which will appear once we select the table
Steps need to be done before creating tables in Excel:
- First, remove all blank rows and columns from the data.
- All the column headings should have a unique name.
How to Create Tables in Excel?
It is very simple and easy to create. Let’s understand the working of the tables by some example.
Let’s take a company employee data.
Check the data as it should not have any empty rows or columns. Put the cursor anywhere in the data and press the shortcut keys CTRL+T. It will open a dialog box.
Make sure that checkbox My table has headers, is ticked. It considers the first row as a header. And then Click Ok.
After clicking on OK, it will create a table like the below screenshot.
As we can see that along with the table, it will also open a separate Table tools design window. With the help of this, we can customize our table.
Steps for Customizing Table in Excel
- Table Name
Automatically excel provides a default name. If its first table, it will assign table name as Table1. In our example, Excel gives the table name as Table2.
We can change this name according to the data so that we can use it further.
Go to Table Names field in Design window.
Write the name of the table.
In our example, we are giving the table name as Emp_Data. Refer below screenshot:
- Table Color
We can add the color to the table. Click on the Table Styles section under the Design tab and choose the color accordingly. Refer below screenshot:
So the Output will be:
Benefits of Excel Table:
- If we have more than one Table, we can easily navigate between them. In the Name Box drop-down, it shows all the table name here and we can choose accordingly.
- When new rows or columns are added to the table, It automatically expands with the existing feature.
- It gives an additional feature Total Row. The Total Row option can be easily performed SUM, COUNT etc. operations.
For this facility, click anywhere in the table and press shortcut key CTRL+SHIFT+T. Refer below screenshot:
- Click on Total. It will show a drop down list of various mathematical operations.
Note: In our example, there is no any numeric data, hence it’s showing the total no. of records in the table.
- With the help of an Excel table, we can easily create a Pivot Table. Click anywhere in the table and choose the Summarize with Pivot Table option under Tools section. Refer below screenshot:
- After clicking on this, It will open a dialog box “Create Pivot Table”.
It provides all the facilities of Pivot Table.
Things to Remember About Excel Tables
When assigning the table name, below points should be kept in mind.
- There should be no space in the table name.
- The table name can be the combination of words but only underscore can be used while joining the words.
- Table name should not start with any special character.
- The table name should be unique if there are more than two tables.
- It should start with alphabetic and maximum length should be within 255 characters.
This has been a guide to Tables in Excel. Here we discuss its uses, advantages and how to create Excel Tables along with an example and downloadable excel template. You can also go through our other suggested articles –