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.

Download Workbook


10 Ways to Use Excel Table Reference

Here, we have the following Table containing the records of the marks of some students of a college. To know the way of creating 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 according to your convenience.

Excel Table Reference


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.

Excel Table Reference

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

=SUM(C5:C7)

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

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

=SUM(Marks1[@[Physics]:[Biology]])

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.

structured reference

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

structured reference


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.

Excel Table Reference

Steps:
➤ Type the following formula in the cell C14 

=SUMIF(Marks2[[Student]:[Student]],$B$14,Marks2[Physics])

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.

absolute reference

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

absolute reference

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

=SUMIF(Marks2[[Student]:[Student]],$B$14,Marks2[Chemistry])

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.

absolute reference

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

Excel Table Reference


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.

Excel Table Reference

Steps:
➤ Type the following formula in the cell G5 

=SUM([Marks4[@Physics]:[Marks4[@Chemistry])

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]

relative reference

➤ 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

=SUM([@Physics]:[@Chemistry])

relative reference

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

relative reference

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

=SUM([@Chemistry]:[@Math])

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

relative reference

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

=SUM([@Math]:[@Biology])

Excel Table Reference


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.

Excel Table Reference

Steps:
➤ Type the following formula in the cell C13 

=SUM(Marks5[Physics],Marks5[Biology])

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

non-contiguous columns

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

non-contiguous columns


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 the 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.

Excel Table Reference

Steps:
➤ Type the following formula in the cell C13 

=SUM(Marks6[[Physics]:[Chemistry]] Marks6[[Chemistry]:[Math]])

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.

intersection of columns

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

Excel Table Reference


Similar Readings


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.

Excel Table Reference

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

Excel Table Reference

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

total row

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

total row

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

total row

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

=SUBTOTAL(109,[Physics])

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

Excel Table Reference

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

total row

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

total row

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

total row

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.

total row

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

Excel Table Reference


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.

Excel Table Reference

Steps:
➤ Type the following formula in the cell C14 

=SUM(Marks8[[#Totals],[Physics]:[Chemistry]])

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

totals for contiguous columns

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

totals for contiguous columns


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.

Excel Table Reference

Steps:
➤ Type the following formula in the cell C14 

=SUM(Marks9[[#Totals],[Physics]],Marks9[[#Totals],[Math]])

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

totals for non-contiguous columns

➤ Press ENTER.

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

totals for non-contiguous columns


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.

Excel Table Reference

Steps:
➤ Enter the following formula in cell C13

=ROWS(Marks10)

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

total rows and columns

Now, use the following formula in the cell C14

=COLUMNS(Marks10)

COLUMNS will return the total column number of Marks10.

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

total rows and columns

Read more: How to Group Columns in Excel Pivot Table


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.

Excel Table Reference

Steps:
➤ Enter the following formula in cell C13

=COUNTBLANK(Marks11[Physics])

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

blank and non-blank cells

➤ Use the following formula in cell C14

=COUNTA(Marks11[Physics])

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.

blank and non-blank cells


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.

practice


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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo