Qling | Resources | Excel - Format as Table

Format as Table or Structured References

Format as Table or Structured References tells Excel to treat a set of data as a table. When you use this formatting option, you can assign the top row as the header row, which gives a name of your choice to each column and treats the data that follows as different rows within the table.

Format as Table or Structured References

Format as Table or Structured References tells Excel to treat a set of data as a table. When you use this formatting option, you can assign the top row as the header row, which gives a name of your choice to each column and treats the data that follows as different rows within the table.

A desk with a keyboard, phone, and a chart

Why should you use Format as Table or Structured References?

One of the most popular uses of Excel is the use of tables. The spreadsheet format of Excel makes it easy to create tables and use them in formulas such as XLOOKUP, VLOOKUP, INDEX, MATCH, COUNTIF, etc.

The formulas usually refer to particular columns or rows and find a relevant data point, aggregation, or some other insight about the data. However, in case you are referring to a sheet that you had created some time ago or if you are referring to a sheet created by someone else, then it becomes slightly challenging to understand the formulas by just reading them. Formulas usually refer to ranges such as A4:A10, Q46:Y754; and you need to go to those cells to understand what data they are referring to. If the data is in some other worksheet then understanding the formula can be that more challenging.

Let us look at a simple example here. XLOOKUP (A12, A2:A8, B2:B8) is a formula that could exist in a worksheet. By reading the formula we are not able to derive much about what will the formula achieve for us. It does point to the ranges where it is looking for data, but by simply reading the formula it is difficult to interpret the type of data that the formula is looking for.

Thankfully, Excel has a smart solution for this. The solution is Format as Table option. Let us first understand how to use the Format as Table option and then look at how the formula would change and finally what are some of the other benefits that you will get by using this type of table formatting.

How to use the Format as Table option?

You can do this by clicking on the Format as Table option in the Styles group (under the Home ribbon). If you click on the drop-down you can select the table formatting that you prefer or is closer to what you want. You can always make modifications to the format as per your preference after selecting a particular format.

Click on Format as Table option in the Styles Group in Home ribbon
Click on Format as Table option in the Styles Group in Home ribbon

Let us see the difference. The data on the right is a Table (using the Format as Table option), while the data on left is formatted manually to make it look like a table; whereas in reality, it is only a data range. Once you format the data as a Table then another tab – ‘Table Design’ appears. You can provide a name to the Table which is easy to understand and remember. In this example, the table has been named EmpDB - short for Employee Database.

Difference between a range formatted manually as table and a Table created using the Format as Table option

Format as Table option makes it easy to read and write formulas

Returning to the readability of the formula; the formatted Table provides a clear improvement. With the formatted Table the formula will look as = XLOOKUP (F12, EmpDB[Employee No.], EmpDB[Employee Name]).

By reading the formula it is easy to understand that we know the Employee No. (cell A12) and are finding the corresponding Employee Name. Getting this kind of clarity by just reading the formula was not possible in the earlier option.

This also makes it very easy for us to write formulas as Excel lists out the column names as soon as you type the table name while writing formula. Alternatively, you could continue to select the column like you would select a range while writing any formula.

Other advantages of using Format as Table option

 If you add more data in the rows below the table, then Excel automatically recognizes them as part of the table. This has multiple benefits:

  1. You need not modify your formulas that are referring to the table as Excel automatically includes the newly added rows in its calculations as it uses column names instead of fixed ranges such as A2:A8 or B2: B8
  2. If any column in your table is a calculated field, then the formula is automatically copied to the new row, thus saving you the trouble to manually copy the formula in each row that you add
  3. Formatting is automatically copied and you do not have to worry about formatting the rows(s) added later
Qling Logo
CONTENT
COMPANY
LEGAL
LinkedIn Logo
Facebook Logo
Instagram Logo
Pinterest Logo
Qling Logo
CONTENT
COMPANY
LEGAL
LinkedIn Logo
Facebook Logo
Instagram Logo
Pinterest Logo