Excel Tables is one of the powerful options that help to handle and organize large sets of data effectively. Organizing/ Formatting the data in a structured format always makes the user’s job much easier to manage the data. By converting raw data into tables you can use multiple options which makes work easier and saves a lot of time. In this post we are going to see how to create a table in excel and explore the different features/ options available with Tables in Excel.
Lists feature in Excel 2003 was succeeded by the Table Feature. The concept of Tables in Excel is available from Excel 2007 version.
What is an Excel Table?
Excel Table is a data set organized in series of rows and columns. Interesting thing is you can also add data to the existing table without worrying about the customizations made by you i.e., formulas, references, formatting remains the same and are also applied to newly added data. Also, Excel Tables helps in creating pivots easily as the data is already organized and formatted.
To Do’s before Creating Tables in Excel
Please make a note of the below points before creating the table.
- Headings : Make sure that in the 1st row, each column contains Unique Heading regarding the data present in the column. If there is any column without headings Excel automatically names it as column 1, column 2 e.t.c. This will make identifying the columns difficult while analyzing the data.
- Data Types : Organize the data in such a way that same data type is present in the same column.
- Blank Rows and Columns : There should not be any row or column which is completely bank in the data. Excel treats the data after blank row or column as separate data and will not be a part of your table. (In case you have different sets of data in the same spreadsheet you can use a blank row/ column to separate them).
How to Create a Table in Excel from Data?
To create a table from an existing data,
- Select any cell in your data.
- Go to Insert tab in the Ribbon and click on the TABLE option.
- You’ll now see a Create Table Dialog Box with the complete data range selected (make sure the data range is correct). By default “My Table has Headers” is checked (In case if it is unchecked check it).
- Click OK and you can see your data is formatted into a table.
See the below image on creating a table in excel from a data range.
Key Board Shortcut – Alternatively you can use the Key Board Shortcut “CONTROL + T” for Creating a Table.
Now you can see
- Header with a different colour .
- Banded columns and rows.
- Data Filters are available in the top row.
- The header remains on the top if the table stretches beyond the visible screen and incase if you need to scroll down (works like freezing the top row).
- Upon clicking any cell in the table a new tab with name “Design” is available on the ribbon.
Customizing Excel Tables – Design Tab
Excel provides various options to customize the excel table in the way you want to. See the below image to understand about the options and we will discuss them in detail.
Naming the Tables in Excel
In theDesign Tab, in the properties group (at the extreme left) you can see an option to change the name of the table. By default it will be “Table 1”. Delete the “Table 1” and type the name which you want to give the table.
Table Styling Options
In theDesign Tab, in the Table Style Options group there are different options available with check boxes against each option.
- Header Row: To show/hide the Header.
- First Column and Last Column: To Bold the 1st and last columns.
- Banded Rows and Columns: Helps to differentiate between alternate rows/columns with a different pattern. You don’t need any Conditional Formatting for this.
- Filter Button: To hide/show filter on the top row.
- Styles: There are different styles inbuilt in excel available to change the color and appearance of tables. To do this,
- Go to the “Home Tab” and Select the option “Format as Table”. Choose the style which you like.
- Alternatively, Click on any cell in the table and choose the style which you need from the “Design Tab”.
Summarize with Pivot Table
To create a pivot table from the table data.
Remove Duplicates
To identify and remove the duplicate items from the table.
Convert to Range
To change the table to normal range.
Insert Slicer
To create a slicer to represent the data in an interactive way. Available from Excel 2013
Total the values in Table without writing Formulae
One of the best features of Excel Tables is that you don’t need to struggle or fiddle with formulae to show the total for a column/ multiple columns. Tables has a built in feature to directly update your totals in the columns.
To see the totals in table
- Select any cell in the table.
- Go to the Design Tab and in the Table Styling Options group put a check mark for the “Total Row” option.
- At the bottom of the table (after the last row of data) you can see a new row inserted with name “Total” and one or more columns might show totals.
The interesting feature of the “Total Row” is, you can change the summary type from “Sum” to other options like “Average”/ ”count”. Click on any cell on the “Total Row” and you can see the drop down with options count, max, min etc. Select the desired option from the drop down. You can also see SubTotal formula in the cell which shows the calculation based on the visible cells of the table.
Using Structured References in Tables in Excel
Structured References makes your job much easier. Instead of using cell references, you can use structured references that reference table names in a formula. As and when a formula refers to a part of a named excel table a structured reference is created. There is specific format to refer to the data in a table using structured references. The structured reference will show the name of the column in between “[ ]”and might include the table name before “[ ]”.
If you already have a Name to the Table and Column then use the same instead of Table Name and Column1 in the below formula.
For Example “=Table Name[Column1]”
If you are working with multiple sheets, you need not go back to the master sheet to refer the data in the table. Just type the name of the table and excel will provide you the option of using the data from the table. See the below image
Similarly, if you want to refer to the data in a specific column from a table2 (from the above image), type the name of the entire table and put a “square bracket” after the table name and excel will show you the list of columns in the table to use. See the image below.
You can also use Structured references directly in the formulae as well.
Limitation: Please note structured references will not work with conditional formatting
Calculated Columns in Excel Tables
Inserting or using a calculated column is easy with excel tables and structured references. When you write a formula in one cell, excel fills the formula in the rest of the cells in the column making your job much easier.
Table do not adjust on adding/removing data
Tables are dynamic in nature. The range expands as and when new data (rows/ columns) is added to the table. In case if you see that the table range is not expanding please follow the below steps.
- Go to File tab and click on Options.
- In the Options window select Proofing. Click Auto Correct Options.
- In the Auto Correct Window, Go to “Auto Format as you type” tab (2nd tab).
- Add a check mark to the 2nd and 3rd options. i.e. “Include New Rows and Columns in Table” and “Fill Formulas in Table to create calculated columns”.
- Click on “OK” twice.
- Now you can see the table range adjusting automatically upon adding/ removing data.
Suggested Reading : We have seen multiple details in the above post. To understand more about them read the below articles.
What are your thoughts on Excel Tables?
I feel they are powerful and easy to use without much complexity. They can be of great use in your day to day work. Please do share your thoughts on working with excel tables in the comments.
Do not Forget to Share this post
Pingback: Filter Data with a Single Click – Excel Efficiency Tip - Excel Bits
Pingback: How to Use VLOOKUP Function in Excel - Excel Bits