How To Create A Formula With Structured References Excel 2013

Contents

1 What are Excel Tables?
2 How to create a Table?
3 Why Use Tables?
- 3.1 1. Ease of styling and formatting
  - 3.1.1 Banded rows
  - 3.1.2 Header Row
  - 3.1.3 Total Row
  - 3.1.4 Filter Button
- 3.2 2.Table Auto Expands when new Data is entered
- 3.3 3.Tables auto-fill the formulas until the last cell
- 3.4 4.Filter with slicers and timeline
- 3.5 5.Summarize with a PivotTable
- 3.6 6.Table name & named ranges
- 3.7 7.Using structured references
- 3.8 8.Create Connection/relationships between tables
- 3.9 9.Add forms to facilitate entry of new data

What are Excel Tables?

The Excel table is a powerful feature that empowers to manage and analyze a group of related data. It also styles your Data in a presentable format.

How to create a Table?

Locate and click on the Insert tab and then click on Table. The Keyboard shortcut is Alt + N+ T

Alternatively, press "CTRL+T".

After which you get a pop-up box asking for a range of data and an option if the Table has headers or not.

Why Use Tables?

There are multiple advantages of Excel Tables. The following are nine of the main uses of Tables.

1. Ease of styling and formatting

Once you format your data as a table, Excel automatically spruces up the table. In simple terms Excel formats the data into a stylish table. You can change the style as per your choice. There are multiple style options under the Table Design tab. Let's Explore!!

Table Style Options:
Header Row: Lets you keep the header even when you scroll down.
Total Row: Ascertain basic statistic functions like SUM, COUNT, AVERAGE, etc.
Banded rows / Columns: Shading of alternate row/column in a worksheet.
Filter option: Filter the table.

Banded rows

When you convert your data into an excel table, excel by default shades every alternate row so that it's easy to read and distinguish the data. This is known as banded rows. To disable this simply uncheck the banded-row option in the table styles option under the Table Design tab. Similarly, banded column is an option.

Once the data is formatted as a table, the headers are visible even when you scroll down. Freeze Panes? Similar but a table is more efficient as the headers are not frozen on the first-row saving space.

Total Row

Simply check the Total row in Table style options under the Table Design tab. Alternatively, right-click on any cell within the table then select "table" and then "total row". The total of all the rows in a specific column will be ascertained at the end. Additionally, select the cell containing the total and you'll notice it has a filter-like option. When you click on that button you can choose from a variety of functions like MIN, MAX, SUM, etc.

Filter Button

Excel adds filters by default to the table, making it readily available. You can remove the filter by unchecking the Filter Button. You can find all the styling options under the Table Design tab.

2.Table Auto Expands when new Data is entered

Table in Excel expands automatically and encapsulates the new data when entered in adjoining row or column. Pretty cool right? In fact, the new rows or columns take the same format as the rest of the table based on row and column. This is a massive time saver as you do not have to format each new entry.

The best part is that when you're referencing tables in other formulas, it will automatically include the new rows and columns. This enables us to create Dynamic Charts and Pivots. For example, a PivotTable linked to a table will include the new data as and when an addition is made. Additionally, you can find the resize option right below the Table Name to expand your table.

Excel Tables - 9 Reasons Why You Should Use 1

3.Tables auto-fill the formulas until the last cell

Write a formula in the first cell of the column and then press enter. To your surprise, you will notice Excel auto-populates the formula until the end of the column. This acts as a speed booster and saves time.

4.Filter with slicers and timeline

In addition to filter options within the table, you can filter using slicer. Why slicer? This is a very useful tools to filter data in an enticing way. You can find the slicer in the Tools section under the Table Design tab. Alternatively, you can insert a slicer from the Insert tab. In addition, you can also add Timelines.

Note: From Excel 2013 you can use Slicers with Excel tables

5.Summarize with a PivotTable

Once you create a table, you can easily create a pivot table by clicking on "summarize with pivot table" button under the Design tab. Now as we know Tables auto expands on entering data, thus the pivot table will automatically update saving us the manual effort. This happens as the pivot is created based on the tables name and not cell reference.

6.Table name & named ranges

Excel lets you name your table. How is this beneficial? It speeds up the process of referencing. For example, while using VLOOKUP you have to select the range and then apply an absolute reference to freeze the range. Instead, you can use the table name. The table can be named as desired. The option is found to the left extreme when on Table Design tab.
Named ranges are very powerful that make excel user-friendly and dynamic.
On creating a table, by default Excel creates a name for each column based on the header. This when used in formulas or any reference makes it easy to interpret even for a layman.

7.Using structured references

Use the table name and then specify the name of the column in functions. For example, You want to ascertain the total of Fault in the below table. (Look at the image below)
=SUM(Table[Fault]) – Table is the table's name and Fault is the column's name.
When you use Excel tables, you can use table name and names of columns in functions i.e. instead of =SUM(G3:G35) you can use =SUM(Table[Fault]). This is helpful when you are working with a large dataset. Structured references make references easier, more durable and more meaningful.

8.Create Connection/relationships between tables

A relation is extremely helpful when it comes to analyzing data. You can understand the concept behind Connections based on the working of VLOOKUP.

VLOOKUP fetches data from a source based on unique ID and the unique ID creates a link between the two data sources which is similar to Connection. Connections are just one click away, to do so click on the Queries & Connection (relationships) button on the Data Tab.

9.Add forms to facilitate entry of new data

You can build managing spreadsheets where people will be typing into Forms. It will fill up your Excel Table without even knowing. If you want other users to type new entries in your Table, you might definitely want to use Excel Forms. You can make various spreadsheets where people will type into the forms which eventually fills up your excel table.

To get the Form option, follow the below steps:
1. Go to File/Options and then Customize the ribbon OR right click on the Ribbon and Select Customize the ribbon.
2. Select "All Commands" in the top-left drop-down list
3. Select "All tabs" in the top-right drop-down list
4. Under the Tables tab, create a "New group" from the button at the bottom right of the window. This is the location where the Form button will be added
5. In the left list, look for "Form"
Click "Add" and then validate with Ok.

NOTES

(a) Excel tables should not be confused with the data tables that are part of a suite of what-if analysis.
(b) You cannot create or insert tables in a shared workbook.

You can view our collection of videos at our Youtube channel, to refresh and enhance your Excel Knowledge. We keep them updated regularly.