Referring to Excel table data in formulas

- Excel tables provide a structured way to store data within your workbooks. One major benefit of using Excel tables is that you can refer to table columns in your formulas using the table and column names which makes your formulas easier for you to interpret. In the columns A through D over on the left side of the worksheet, you can see that I have a table. I have four columns, Year, Quarter, Month, and Sales, and my goal is to create a formula that finds the total of the values in the Sales column.

So I could type a formula such as equal sum left parantheses and then identify the cell range of D2 and type a colon and scroll down and I see that my last row with data is row 25, so I'll scroll back up. So I have D2 to D25, right parentheses and I get 7.5 million. Now this is a perfectly legitimate way of creating a formula. But, if I click cell G2 and take a look at the formula on the formula bar I see that I'm finding the sum of D2 to D25 but it doesn't tell me anything about the data.

Now this is a simple worksheet so I can just look to the left and see, Okay, it's sales data. And it's easy enough to figure out on its own. But if I want to make the formula a little bit more readable, for example, for data that is separated widely from the formula that summarizes it, I can use what's called a table or structured reference. To do that I first need to find out what the name of this Excel table is. So I'll click any cell in the table, I'll just click C4 for convenience, and then I will go up to the ribbon and click the Table contextual tab.

On that tab if I look all the way to the left I can see the Table Name box. And that tells me that the name of this table is Sales Data. So now I have enough information to go forward and create my formula. So once again I'll click cell G2, type an equal sign, and I'll type sum and a left parentheses. But now I can type in the name of the table and that was Sales Data. As soon as I type S-A-L I see Sales Data in the formula auto complete list so I'll press the Tab key.

Now I need to refer to the column, and that columns name is Sales. So I'll type a left square bracket and then S-A-L-E-S. Or I could have used the formula auto complete value. Then I'll type a right square bracket to close out that reference and a right parentheses to close out the formula. Now when I press Enter I get exactly the same result, but when I click cell G2 and look at the formula bar, I see a much more informative formula.

Instead of having to figure out what cells D2 through D25 represent, I know that it's sales data and that it's probably a number.