How to Use an Excel Table Reference -10 Methods

The following sample Table contains students’s marks.

Method-1 –Using a Structured Reference as an Excel Table Reference

Marks1 is the sample table.

Steps:
➤ Select G5 and enter the formula.

C5:C7 is the range of the marks for Daniel and SUM  adds these values.

By selecting the range C5:C7, Excel will convert them automatically to the structured reference system and modify the formula:

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

Press ENTER, and the added marks for all rows will automatically be displayed in Total.

Method 2 – Using the Absolute Reference System as an Excel Table Reference

In Marks2  the SUMIF function will sum the marks of Physics, Chemistry, Math, and Biology for Jenny.

Steps:
Enter the following formula in the C14.

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

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

It will copy the formula to cell D14 and showcase the following formula

The absolute referenced data range is not changed, only Marks2[Physics] changed to Marks2[Chemistry] to return the Chemistry.

Copy the formula for Math and Biology to see the marks for Jenny.

Method 3- Using the Relative Reference System as an Excel Table Reference

In the table Marks3, marks in Physics and Chemistry, Chemistry and Math, Math and Biology will be summed for the students in the Phy+chem, Chem+Math, and Math+Bio columns.

Steps:
Enter the following formula in G5 

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

The relative reference system is Marks4[@Physics]:[Marks4[@Chemistry]

Press ENTER to see the sum of marks in the Physics and Chemistry columns. Excel will change the formula into:

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

In the Chem+Math column, the formula is the following:

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

The sum of Math and Biology marks is displayed in the Math+Bio column.

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

Here, Marks5 is the Table name. Marks in Physics and Biology will be added.

Steps:
Enter the following formula in the C13 

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

Press ENTER  to see the sum of the marks in Physics and Biology for all students.

Method 5 – Referencing the Intersection of Columns Using an Excel Table Reference

Here, Marks6 is the Table name. Marks in Chemistry will be added for all students.

Steps:
Enter the following formula in the C13. 

Marks6 is the Table name and [Physics]:[Chemistry] and [Chemistry]:[Math] are the two ranges. The intersected column is [Chemistry] which will be  referenced.

Press ENTER to see the sum of marks in Chemistry for all students.

Similar Readings

• Insert or Delete Rows and Columns from Excel Table

Method 6 – Using the Total Row Option for Filtered Tables

In Marks7  the Total Row option will be used to sum the marks.

Steps:
Select any cell in the Table.
Go to the Table Design Tab >> Table Style Options >> click Total Row.

A new row (Total) will be added.

➤ Click C12 and click the dropdown sign.

Select Sum.

You will see the sum of the marks in Physics and the following formula will be displayed.

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

Hide the marks of a student (Jenny, here) by unclicking her name and pressing OK.

The total marks in Physics are changed to 542 as SUBTOTAL only works for visible cells.

Unhide Jenny and see the sum of the marks in Chemistry, Math, and Biology.

Adding new data (Leonard’s marks) will update Total values.

Deleting Leonard’s data, returns the previous results.

Method 7 – Referencing the Totals for Multiple Contiguous Columns Using an Excel Table Reference

In Marks8  the reference of the totals will be used and the total value of the marks in Physics and Chemistry will be summed.

Steps:
Enterthe following formula in the C14 

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

Press ENTER to see the sum of marks in Physics and Chemistry.

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

In Marks9  the reference of the totals will be used and the total value of the marks in Physics and Math will be summed.

Steps:
Enter the following formula in C14.

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

Press ENTER.

You will see the sum of the marks for the two non-contiguous columns Physics and Math.

Method 9 – Counting Total Rows and Columns Using an Excel Table Reference

Total rows and columns in Marks10 will be counted by using the ROWS and COLUMNS functions.

Steps:
Enter the following formula in C13

ROWS will determine the number of rows in Marks10.

Use the following formula in C14

COLUMNS will return the total number of columns in  Marks10.

This is the output.

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

The COUNTBLANK function and the COUNTA function will return the total blank and non-blank cells in the Physics column, in Marks11.

Steps:
Enter the following formula in C13.

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

Use the following formula in C14

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

This is the output.

Practice Section

Practice here.

Download Workbook

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