Excel Tables

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.

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 tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. read more 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 ExcelSUM In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more, Subtotal in ExcelSubtotal In ExcelThe SUBTOTAL excel function performs different arithmetic operations like average, product, sum, standard deviation, variance etc., on a defined 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 –

• Delete Pivot Table Excel
• Refresh Pivot Table in Excel
• Data Table in Excel
• Excel Merge Tables