 Excel Tips - Tables and Structured References - Excel TV

Excel Tips – Tables and Structured References

Excel Tables arranges data in a structured way in rows and columns. When you have data as output of some analysis then showing it in Excel Table is my suggestion. It makes us easy to work on that data to further process our analysis. In this tutorial Excel expert Oz explains us how to use Table as structured reference to summarize data.

For example, suppose you have a table with columns specifying Product, Price, State and Units Sold. Then, it would be easy to calculate total amount earned by selling specific products that too in specific state. This is just an example as explained by Oz in this tutorial. You can do a lot more using tables. Without any ado, let us look at what Oz has said and how can we make most out of Excel Tables.

Tables and Structured References

I will take you through 3 easy steps to learn how to use structured references and they are as follows,

1 – Creating Excel Table

The foremost step is to know how to create excel table. This is really very simple. There are two ways to create a table. One is directly by inserting the rows and columns and then filling it with the data. This is good when you know how many rows and columns of data you have beforehand. I personally do not suggest this method. I would first enter the data and then make it as a table. Do not get confused! I will explain it.

• First give column names normally and fill the data associated with those columns.
• Then select that entire entered data and click on “Insert” button.
• Now, click on “Table” and check “My table has headers” option. That’s it!

2 – Name the Table

In first step, we have created the table. Now, we need to name it. As we know naming in Excel would reduce our efforts and makes it easy when we need to refer these objects wherever we want. If you want to refer this table in say formula, then we can refer it using the name.

To give name to table, just select the table and click on “Design” tab. Now, on the left most side give the name in the “Table Name” field and hit enter button.

3 – Structured References in Table

Here I would use the table created in step 1 with Product, Price and State as columns and “Transactions” as it’s name. Now, I want to calculate the total sales of products based on State. So, here I would make use of structured references in table as follows,

• Create another table say with columns as “State” and “Total Price”. Fill the “State” column with the states you want to find total price.
• Now, we would write a formula in “Total Price” columns such that, total the price of products from table “Transactions” whose state matches with the second table. Here is the formula,

=SUMIF(Transactions[State],[@State],Transactions[Price])

Here,

‘Transactions [State]’ specifies the ‘State’ column of “Transactions” table.

[@State] specifies the ‘State’ column of the second table which displays the total price.

‘Transactions [Price]’ specifies the ‘Price’ column of “Transactions” table.

As these are used in SUMIF function, it says that if first two parameters are equal, then perform the sum of the third parameter. In our case, if states of both the table matches, then sum the price of products sold in that state.

That’s it! We cracked our requirement using structured references in table and it is easy. Isn’t it?

What’s next?

It is interesting right? We can get most use of tables and this is one of the ways to use excel tables. If you have anything to add, please do share with us through comments.

• Ed Horn says: