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

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

__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, 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.

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

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

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

**How to Lookup a Table and Return Values in Excel (3 Simple Ways)****Insert or Delete Rows and Columns from Excel Table****How to Use Calculated Field in Excel Pivot Table (8 Ways)****Use Formula in an Excel Table Effectively (With 4 Examples)**

__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 the 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 *is 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**, **Biology **in the same way of **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.

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

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

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