What are Excel Tables?
Tables in Excel helps group related data into one or more rows and/or columns. Once a table is created, Excel assigns a unique name to the columns and the table itself. Such names are used as structured references, which make it easy to apply Excel formulas. Therefore, tables eliminate the need to create named ranges in Excel.
For example, the following image shows a usual data range on the left side and an Excel table on the right side. Notice the alternately colored excel rowsAlternately Colored Excel RowsThe two different methods to add colour to alternative rows are adding alternative row colour using Excel table styles or highlighting alternative rows using the Excel conditional formatting option. and structured references (in cell J2) of the latter.
The purpose of creating tables is to make the data manageable, organized, and fit for analysis. Apart from structured references, tables offer several benefits like quick sorting and filtering, automatic expansion, easy formatting, readable formulas, and so on. Tables are extremely helpful when working with large databases.
Table of contents
- What are Excel Tables?
- How to Create Tables in Excel?
- How to Customize Tables in Excel?
- What are the Advantages of Creating Tables in Excel?
- #1–Creates Structured References Automatically
- #2–Is Dynamic in Nature
- #3–Makes it Easy to Work With PivotTables
- #4–Freezes Header Row Automatically
- #5–Helps add a Total Row Containing Functions
- #6–Eases Restoring to Usual Data Range Without Losing the Table Style
- #7–Helps add Slicers for Filtering Data
- #8–Assists in Creating Reports in Power BI
- How to Turn off Structured References in Excel?
- Frequently Asked Questions
- Recommended Articles
How to Create Tables in Excel?
The steps to create a table in Excel are listed as follows:
- Ensure that the raw data does not contain any empty rows and/or columns. Further, each column should have a unique heading. If any two columns have the same headers, Excel automatically changes one of these headers once a table is created.
The raw data is shown in the following image.
Note: A column header should not contain any Excel formulas.
- Select any cell of the raw data and press the shortcut “Ctrl+T.” Both keys of the shortcut should be pressed together.
Note: Alternatively, after selecting a cell of the raw data, click “table” from the Insert tab of Excel. This option is in the “tables” group of the Excel ribbon.
- The “create table” dialog box opens, as shown in the following image. Excel automatically selects the range for the table. Check whether this range is correct or not. If not, it can be edited.
- Select or deselect the checkbox of “my table has headers.” If selected, Excel will treat the content of the first row (row 1) as column headers.
In the preceding table, row 1 does contain column headers. So, we have selected the option “my table has headers.” Next, click “Ok.”
- An Excel table has been created. It is shown in the following image. Notice that the banded rows and filters (to the right of each column header) appear automatically.
Note: In Excel, banded rows are those in which color or shading is applied alternately.
How to Customize Tables in Excel?
A table can be customized by changing its name and/or the default color (or table style). Let us learn how this is done.
#1–Change the Name of the Table
When a table is created, Excel assigns a default name like “table1,” “table2,” etc., depending on whether it is the first or the second table of the current workbook.
Working with such default names may become confusing, especially when there are lots of tables. So, the solution is to assign unique names to each table.
The steps for changing the name of the Excel table are listed as follows:
Step 1: Select the entire table. Once selected, the Design tab appears on the Excel ribbonExcel RibbonRibbons in Excel 2016 are designed to help you easily locate the command you want to use. Ribbons are organized into logical groups called Tabs, each of which has its own set of functions.. This tab is shown within a red box in the following image.
Note: In place of the entire table, even if a single cell is selected, the Design tab will become visible.
Step 2: In the Design tab, perform the following tasks:
- Click inside the box under “table name.” This box is displayed in the “properties” group at the leftmost side of the ribbon.
- Enter the desired name in this box.
- Press the “Enter” key.
Our table has been named “Sales_Table.” This is shown in the following image.
Rules Governing the Table Names
While naming a table, the following points should be considered:
- The name should always begin with a letter, a backslash () or an underscore (_).
- The name cannot be the single letter “r” (or “R”) or “c” (or “C”).
- The name should not consist of any spaces. Instead, one can use an underscore (_) or a period (.) to separate the words.
- The name can contain numbers, but cell references should be avoided.
- The name can contain a maximum of 255 characters.
- The name of the table should be unique. In case of a duplicate name, a message will be displayed saying that a unique name should be chosen.
Note: A name is said to be duplicate if two tables within the same workbook have the same names. Further, Excel does not distinguish between the uppercase and lowercase letters in the name of a table. So, the names “abc” or “ABC” are considered the same by Excel.
#2–Change the Color of the Table
When a table is created, Excel assigns it a default color. To change the color of the table, its style needs to be changed. Excel displays a list of different table styles from which one can choose the desired option.
The steps to change the color (or style) of a table are listed as follows:
Step 1: Select either a cell of the table or the entire table. We have selected the latter. The Design tab becomes visible, as shown in the following image.
Step 2: Choose the desired color (or style) from the “table styles” group of the Design tab. To expand the list of table styles, click the “more” button displayed at the bottom right side. One can also click “new table style” to view more styles.
The following image shows the expanded list of table styles. Once the desired style is chosen, it will be applied to the entire table.
Note: The default table style of the workbook can be changed by the user. For making this change, perform the listed steps:
- Select the desired table style from the list of table styles.
- Right-click the selected table style and choose “set as default” from the context menu.
What are the Advantages of Creating Tables in Excel?
Let us discuss eight advantages of creating tables in Excel.
#1–Creates Structured References Automatically
An Excel table uses structured referencesStructured 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. for referencing one or more cells. This is in contrast with the direct cell addresses used in a normal data range. A structured reference is a combination of table and column names that are used in Excel formulasExcel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions..
The major benefits of using structured references are listed as follows:
- They automatically update when new entries are added or deleted from the table.
- They are automatically created when one or more cells of the table are selected.
- They can be used both within and outside the table, thus making it easy to locate tables in a workbook.
- They are used to autofill the calculated columns. A calculated column is an additional column added to the existing Excel table. It uses a single formula that expands on its own in the remaining part of the column. This implies that when a formula is entered in one cell of a calculated column, it need not be dragged to the remaining cells of the column. Rather, the formula is automatically entered in all cells (called autofill), unlike a usual data range which requires one to drag the fill handleFill HandleThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications..
Example of point “d”: The range A2:B7 is an Excel table containing random numbers. To create column C as a calculated column, enter the SUM formula in cell C3, which adds the numbers of cells A3 and B3. Use structured references in this formula. Once the formula is entered, press the “Enter” key. The outcomes are stated as follows:
- The table (A2:B7) automatically expands to include column C.
- The range C4:C7 is automatically filled with the totals of each row of the table.
The preceding results will be obtained with both structured and regular references. This is because autofilling is a feature of Excel tables. However, with structured references, it is easy to read and understand the formula.
Note that if the user makes a change to the formula of the calculated column, the change is automatically replicated in the entire column.
Example of structured references: In the following image, structured references have been used in the SUMIF formula. The “Calls_Table” is the name of the Excel table. “Day” and “duration” are the names of columns A and E respectively. The cell G2 is a direct 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..
Note: In the following image, the black line pointing to cell G2 has been incorrectly placed. It should have pointed to “day,” which is the name of column A. So, please ignore the misplacement.
#2–Is Dynamic in Nature
Any new insertions to the cells below or to the right of the table are automatically included in the Excel table. This implies that the table expands to include such insertions. Likewise, the table contracts when one or more rows and/or columns are deleted from it. Due to automatic expansion and contraction, an Excel table is often considered a dynamic tableDynamic TableDynamic tables in Excel are ones in which the table automatically adjusts its size when a new value is inserted. It can be done in one of two ways: by using the offset function or by creating a data table from the table section..
Expansion implies that the style, formatting, and formulas of the table are automatically applied to the new entries as well. This eliminates the need to format and edit the formulas of individual cells. By default, the formatting stays uniform throughout an Excel table.
Note that the formulas of the table are adjustable as they take into account structured references. In contrast, the formulas of a normal data range do not adjust with insertions or deletions of entries.
Note: To undo table expansion, press the keys “Ctrl+Z” together.
#3–Makes it Easy to Work With PivotTables
It is advised to create a PivotTablePivot 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. from an Excel table. The major reasons the source data should be an Excel table are listed as follows:
- A PivotTable automatically updates with any changes in the source table. Since Excel tables are dynamic, the data range of the PivotTable also becomes dynamic.
- A single cell of the source table can be selected prior to inserting a PivotTable. In contrast, when the source data is a usual data range, the entire range (or dataset) needs to be selected prior to inserting a PivotTable.
For instance, in the following image, a single cell of the source table has been selected before clicking the “PivotTable” option from the Excel Insert tabExcel Insert TabIn excel “INSERT” tab plays an important role in analyzing the data. Like all the other tabs in the ribbon INSERT tab offers its own features and tools. Under Insert Tab we have several other groups including tables, illustration, add-ins, charts, Power map, sparklines, filters, etc.. Notice that in “table/range,” the name of the source table is reflected.
#4–Freezes Header Row Automatically
When one scrolls downwards in a worksheet, the table headers are visible at all times. This is because these headers are automatically fixed or frozen at their respective places.
Fixed headers prevent the user from going back to the top row (row 1) again and again. Such frozen headers can be seen within a red rectangle in the following image.
Notice that the user has scrolled downwards such that rows 4 to 13 are visible along with the header row.
Note: To see the frozen header row, ensure that a cell of the table is selected before scrolling.
#5–Helps add a Total Row Containing Functions
An Excel table shows various functions in the total row. For displaying this row, perform the following steps:
- Select any cell of the Excel table. The Design tab appears on the Excel ribbon.
- From the Design tab, select the checkbox of “total row” displayed in the “table style options” group.
The total row is added immediately below the Excel table. Select any cell of this row to see a drop-down arrow on the right side. When this arrow is clicked, a list containing functions appears, as shown in the following image.
Once a function is selected from the list, the respective formula is automatically entered and the output is shown in a cell of the total row.
Notice that row 9 of the following image is the total row. Further, if a function is selected in cell D9, the corresponding output will also be displayed in this cell. The formula will be visible in the formula bar of the worksheet.
#6–Eases Restoring to Usual Data Range Without Losing the Table Style
An Excel table can be quickly converted to a usual data range. This serves as a benefit when the user requires only the table style and not the functionality of the table. For such conversion, follow either of the listed steps:
- Select any cell of the table. From the Design tab, click “convert to range” in the tools group.
- Select any cell of the table and right-click it. From the context menu, choose “table” followed by “convert to range.”
The “convert to range” feature of the first pointer is shown in the following image. Once an Excel table is converted to a usual data range, the existing structured references are replaced with the regular cell references. However, the data and formatting of the table are retained in the usual data range.
Note: The “convert to range” feature can be used only when the data to be converted is in the form of an Excel table.
#7–Helps add Slicers for Filtering Data
A slicer helps filter the data of an Excel table. It displays buttons that can be selected or deselected to indicate whether an item is included or excluded from the filter.
Slicers are available in Excel 2013 and the newer Excel versions. The steps to add a slicer to a table are listed as follows:
- Select either a cell within the table or the entire table.
- From the Design tab, select “insert slicer” from the “tools” group.
- The “insert slicer” dialog box opens. Next, select the checkboxes of the columns for which the slicer needs to be created.
- Click “Ok.”
One can have a slicer for each column of the Excel table. Once slicers appear in the worksheet, they can be moved, resized, and formatted according to the requirement.
With a slicer, one can filter and view only particular items (or entries) of the table. To view multiple items of a column, press and hold the “Ctrl” key while selecting the items in the respective slicer.
The following image shows the “insert slicer” option of the Design tab.
Note 1: A slicer can also be added from the “filters” group of the Insert tab of Excel. Click “slicer” in this group and thereafter follow the steps “c” and “d” listed above.
Note 2: A slicer can be used only if the data is in the form of an Excel table.
#8–Assists in Creating Reports in Power BI
Excel tables serve as a source from which data is entered in the Power BI tool. Power BI is a business intelligence tool that helps convert unrelated sources of data into meaningful reports and dashboards. The process of creating reports works as follows:
- Create and upload Excel tables to Power BI.
- Create a report in Power BI by adding visualizations.
- Share the report with other Power BI users or office colleagues.
Excel tables are a preferred data source in Power BI due to the following reasons:
- They allow quick access to the tabular format of the dataset.
- They help ease comparisons within a dataset.
- They can be easily edited and organized prior to being uploaded in Power BI.
How to Turn off Structured References in Excel?
The steps to turn off structured references in Excel are listed as follows:
Step 1: Click the File tab of Excel. It is displayed within a red box in the following image.
Step 2: Select “options” shown in the following image.
Step 3: The “Excel options” dialog box opens. Click “formulas” appearing on the left side of the window. Deselect the checkbox of “use table names in formulas.” This is shown in the following image.
Next, click “Ok.” The structured references will be turned off in Excel.
Note: By changing this setting, the structured references used in the existing formulas of the Excel table are not removed. However, the new formulas applied to the table will contain regular cell references instead of structured references. If regular cell references are required in existing formulas too, they will have to be edited manually.
Further, whether the structured references are turned on or off, the changed setting will be applied to all workbooks that the user is working on.
Frequently Asked Questions
An Excel table displays the data in a tabulated format. Every column should have unique headers (or names) according to the kind of data they contain. Such headers are placed in the top row of the table. The table may or may not be named by the user.
If the columns and table are not named by the user, Excel assigns default names to both of them. These names are used as structured references in all formulas applied within or outside the table (that contain a table reference).
To create a table in Excel, select any cell of the dataset and press the keys “Ctrl+T” or “Ctrl+L.”
Two tables that have a common column can be joined with the help of the VLOOKUP function of Excel. The formula is stated as follows:
For instance, the first table is named “table1” and the second table is named “table2.” To pull the data of a single column of “table2” in “table1,” enter the following formula:
This formula looks up the value of cell A2 in “table2.” It returns an exact match from the third column of “table2.”
One can use either regular or structured references in the given formula. By selecting the cell of the “lookup_value” and the range of the “entire_lookup_table,” Excel will automatically enter structured references in the VLOOKUP formula.
Note 1: Enter the given formula in the first cell to the immediate right of the first table. Once entered, press the “Enter” key. The Excel table expands to include the new column. Consequently, the outputs of the entire column are displayed in the first table.
Note 2: For the given formula to work, the column containing the “lookup_value” should be common to both tables. Moreover, the lookup column (containing the “lookup_value”) should be the leftmost column of the second table. The return column (containing the value to be returned) of the second table should be to the right of the lookup column.
Excel tables should be used in the following situations:
• To arrange data in a tabular format
• To facilitate comparisons of data values
• To improve the readability of the dataset
• To ease data analysis and eventually assist in decision-making
To locate tables in a workbook, click the downward arrow appearing on the right side of the name box. It shows the names of all the tables of the workbook. Clicking on any of these names will select that particular table.
This has been a guide to tables in Excel. Here, we explain how to create/insert/customize Excel tables along with examples and advantages. You may also look at these useful functions of Excel–
- Delete Pivot Table ExcelDelete Pivot Table ExcelTo delete a pivot table in Excel, you must first select it. Then go to the Analyze menu tab under the Design and Analyze menu tabs and select actions. Then, from the Select option's drop-down option, select Entire Pivot Table to delete it.
- Refresh Pivot Table in ExcelRefresh Pivot Table In ExcelTo refresh pivot tables, you may use the following methods - refresh pivot table by changing data source, refresh pivot table using right click option, auto-refresh pivot table using VBA Code, refresh pivot table when you open the workbook.
- Data Table in Excel
- Excel Merge Tables Excel Merge Tables We can use a number of different methods to merge tables in Excel, including the VLOOKUP function, the INDEX function, and the MATCH function.