How to Use Excel Table Reference (10 Examples)

If you are looking for some of the easiest ways to use Excel Table reference, then you are in the right place. Using Table references you can perform any type of calculation faster and you can easily insert or delete any rows without any hassle. So, let’s start with the main article.

Use Excel Table Reference: 10 Ways

Here, we have the following Table containing the records of the marks of some students of a college. To know the way of creating a table you can follow this article “How to Make a Table in Excel”.

We have used Microsoft Excel 365 version here; you can use any other version at your convenience.

Method-1: Using Structured Reference as Excel Table Reference

In this section, we will demonstrate the structured reference system of Table. We have a Table named Marks1 and we will calculate the total marks of each student in the Total column.

Steps:
➤ Select the cell G5 and start typing the formula

Here, C5:C7 is the range of the marks for Daniel and SUM will add up these values.

But, then start to select the ranges C5:C7, Excel will convert them automatically to the structured reference system and modify the formula as follows

Here, Marks1 is the name of the Table, [Physics]:[Biology] is the range of the contiguous 4 columns; Physics, Chemistry, Math, and Biology. @ represents the current row.

After pressing ENTER, you will get the added marks for all of the rows automatically in the Total column.

Method-2: Using Absolute Reference System as Excel Table Reference

You can use an absolute reference system for copying a formula without changing the reference system and performing the desired calculation.

Here, we have a Table whose name is Marks2 and we will sum the marks of Physics, Chemistry, Math, and Biology for the student named Jenny using the SUMIF function.

Steps:
➤ Type the following formula in the cell C14 

Here, Marks2[[Student]:[Student]] is the range in which Marks2 is the Table name and [Student]:[Student] represents the absolute referencing in the Student column, $B$14 is the criteria, and finally Marks2[Physics] is the sum range which represents the Physics column.

➤ Press ENTER and drag the Fill Handle tool to the right.

It will copy the formula to cell D14 and then form the following formula

We can see here that the absolute referenced data range is not changed here, only the Marks2[Chemistry] is changed from Marks2[Physics] and so we finally get the Chemistry marks here.

Similarly, after copying the formula for Math and Biology we are getting the marks for Jenny.

Method-3: Using Relative Reference System as Excel Table Reference

If you want to change the reference based on the relative position of the column where you want to copy the formula, then you can use the relative reference system.

Here, we have a Table whose name is Marks3 and we will sum the marks of Physics and Chemistry, Chemistry and Math, Math and Biology for the students in the Phy+chem, Chem+Math, and Math+Bio columns.

Steps:
➤ Type the following formula in the cell G5 

Marks4 is the Table name, [@Physics] and [@Chemistry] is the corresponding row of the Physics and Chemistry column.

Here, the relative reference system is Marks4[@Physics]:[Marks4[@Chemistry]

➤ After pressing ENTER you will get the sum of marks of the Physics and Chemistry columns and Excel will change the previous formula into the following one

➤ Select the cells of the Phy+chem column and drag the Fill Handle tool to the right.

So, in the Chem+Math column, we have the following formula

It will sum up the marks of Chemistry and Math for each student.

Similarly, we have the summation of the Math and Biology marks in the Math+Bio column.

Method-4: Referencing Multiple Non-Contiguous Columns Using Excel Table Reference

In the previous sections, we have referenced the contiguous columns but here we will work with the reference system for non-contiguous columns.

Here, Marks5 is the Table name and we will add up the marks of Physics and Biology of the students.

Steps:
➤ Type the following formula in the cell C13 

Marks5 is the Table name and [Physics], [Biology] are the Physics and Biology columns.

In this way, after pressing ENTER the sum of the marks of Physics and Biology for all of the students.

Method-5: Referencing Intersection of Columns Using Excel Table Reference

Referencing the intersection of columns indicates the column where two range references overlap. In this section, you will get an idea of this kind of reference.

Here, Marks6 is the Table name and we will add up the marks of Chemistry for all of the students.

Steps:
➤ Type the following formula in the cell C13 

Marks6 is the Table name and [Physics]:[Chemistry] and [Chemistry]:[Math] are the two ranges and among these ranges, the intersected column is [Chemistry] and so, finally we will end up referencing the Chemistry column.

After pressing ENTER, we will get the sum of the numbers of Chemistry for all of the students.

Similar Readings

• Insert or Delete Rows and Columns from Excel Table

Method-6: Using Total Row Option for Filtered Tables

Here, we have a Table whose name is Marks7 and we will use the Total Row option for summing up the marks.

Steps:
➤ Select any cell inside the Table.
➤ Go to Table Design Tab >> Table Style Options Group >> click on Total Row Option.

After that, a new row Total will be added immediately below Table Marks7.

➤ Click cell C12 below the Physics column and you will see a dropdown sign, click on it.

Some function names will appear, select Sum from the options or any other function.

In this way, you will get the sum of the Physics marks, and the following formula will appear here.

SUBTOTAL will add up the visible values of the filtered table, 109 is for SUM, and [Physics] is the column name.

Suppose, you want to hide the marks of the student Jenny by unclicking her name and pressing OK.

After that, we can see the total marks of Physics are changed to 542 as SUBTOTAL only works for the visible cells.

Now, unhide the student Jenny and get the sum of the marks of  Chemistry, Math, and Biology in the same way as Physics.

Here, we entered a new student Leonard’s information and we can see the total values have been changed because of adding up these new marks.

After deleting the student Leonard’s information, we are getting the previous results again.

Method-7: Referencing the Totals for Multiple Contiguous Columns Using Excel Table Reference

Here, we have a Table Marks8 and we will use the reference of the totals and sum up the total value of the marks of Physics and Chemistry.

Steps:
➤ Type the following formula in the cell C14 

Here, Marks8 is the Table name, [#Totals] is the Total row, [Physics]:[Chemistry] is the range of the Physics and Chemistry column.

After pressing ENTER, we will get the sum of marks for Physics and Chemistry.

Method-8: Referencing the Totals for Multiple Non-Contiguous Columns Using Excel Table Reference

Here, we have a Table Marks9 and we will use the reference of the totals and sum up the total value of the marks of Physics and Math.

Steps:
➤ Type the following formula in the cell C14 

Here, Marks9 is the Table name, [#Totals] is the Total row, [Physics], and [Math] is the Physics and Math column.

➤ Press ENTER.

Afterward, you will get the sum of marks for the two non-contiguous columns Physics and Math.

Method-9: Counting Total Rows and Columns Using Excel Table Reference

Here, we will count the total rows and columns of Table Marks10 by using the ROWS and COLUMNS functions.

Steps:
➤ Enter the following formula in cell C13

ROWS will determine the number of rows in the Marks10 table.

Now, use the following formula in the cell C14

COLUMNS will return the total column number of Marks10.

Finally, we have got 7 rows and 5 columns here.

Method-10: Counting Blank and Non-Blank Cells Using Excel Table Reference

By using the COUNTBLANK function and COUNTA function we will get the total blank and non-blank cells of the Physics column of the Marks11 Table.

Steps:
➤ Enter the following formula in cell C13

COUNTBLANK will determine the number of blank cells of the Physics column in the Marks11 table.

➤ Use the following formula in cell C14

COUNTA will determine the number of non-blank cells of the Physics column in the Marks11 table.

As a result, we have got 2 blank cells and 5 non-blank cells here.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to cover some of the ways of using Table references in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Structured Reference Excel: Knowledge Hub

• Create a Structured Reference
• Applications of Absolute Structured References with Table Formulas
• Use HLOOKUP with Structured Reference
• Lock a Structured Reference
• Reference a Dynamic Component of a Structured Reference
• What is an Unqualified Structured Reference
• Use IF Function and Structured Reference

<< Go Back to Table Formula | Excel Table | Learn Excel