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 the 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 columns from our original data to create an excel table, and all the column headings should not have any duplicate heading values. Follow the below steps to create the first-ever table.
Below are the steps for creating table in excel –
- Create data, as shown in the below image. My data does not have any empty rows and empty columns.
- Place your cursor anywhere within the data range and press Ctrl + T, and it will open up the below additional dialogue box.
- 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.
- 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 changes, 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 about 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 in excel, 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.
- The 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 the 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.
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 is 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 an excel pivot table.
In the Range section of the pivot table name of the table is appearing.
Do not worry about cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more because we have structured references.
We can write understandable formulas using these structured references in excelStructured References In ExcelIn Excel, structured references define data sets in columns by giving names instead of cell address. You may use the column name instead of the cell address in the formula; this makes work easier.read more. Humanly readable formulas are 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 in Excel, Subtotal in ExcelSubtotal In ExcelThe Excel Subtotal function is a built-in function that returns the subtotal of a given data table or data sets. This formula requires two inputs, the first of which is the function number and the second of which is the range.read more, Count, Max in ExcelMax In ExcelThe MAX Formula in Excel is used to calculate the maximum value from a set of data/array. It counts numbers but ignores empty cells, text, the logical values TRUE and FALSE, and text values.read more, 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 worry about the above cells formatting and formula. The table can capture the formatting and formula of the above cell automatically.
#10 – Turn off Structured Reference in Settings
We can turn off structured formula references in the settings.
First, go to File
Select Options
Click on Formulas and turn off structured formula reference.
Recommended Articles
This has been a Guide to Tables in Excel. Here we discuss its uses and how to create Excel Tables, advantages of Excel tables along with an example, and downloadable templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
45.6K subscribers