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.

**Table of Contents**Expand

## 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

`=SUM(C5:C7)`

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

`=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.

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**

`=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.

➤ 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

`=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.

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**

`=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]**

➤ 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])`

➤ 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

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

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.

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

__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**

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

*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**

`=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.

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

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

** 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(109,[Physics])`

**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**

`=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.

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**

`=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.

➤ 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(Marks10)`

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

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.

__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(Marks11[Physics])`

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

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

## 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**