How to Use Formula in Excel TableĀ (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Excel Table allows you to create formulas that apply to the entire table, copy easily, and are more options than traditional formulas which are called structured references. This article will help you understand all the amazing features of Excel Tables and will convince you to use formula in Excel Table.

What is an Excel Table?

We can say that Excel Tables are containers for our data. Imagine you have a storeroom, so there you will arrange your books to a shelf, electric tools to a different shelf. Excel Tables are like those shelves. It helps to contain and organize data in your worksheets like that. Tables manage excel that all the data is related.

Create a Formula and Apply to an Excel Table

The way we use an Excel formula in a typical Excel sheet and in an Excel Table is a bit different but not so difficult. Iāll show some easy examples which will be well enough to learn that. Letās start with the SUM function. Before that, Iāll introduce you to my dataset first. It represents selling information- Order ID, Date, Unit Sold, and Sell Type of a company.

At first, weāll convert our dataset to a table.

Step 1:

ā© Select any cell from the dataset.

ā© Then click as follows: Insert > Tables > Table

Step 2:

ā© Now just press OK here.

Look that our table is ready. I have changed the name of the table to āSalesā. To do that-

Step 3:

ā© Press any data in the table and click- Table Design > Table Name

Now letās count the total sold unit with the SUM function.

Step 4:

Then you will see that it is showing the header names of our table like the image below.

Itās called the Structured Reference. No need to use the cell references here.

Step 5:

ā© Finally, close the brackets and hit the Enter button.

And now we have got our total sold unit.

Calculate Total Cost:

Next, Iāll show how to use the total column in a formula. For that, I have modified the table. I have added productsā prices and their delivery costs. Then to calculate the total I have added a new column.

Steps:

ā© Type the name of the column adjacent to the last header and hit the Enter button, Excel Table will automatically convert it to a part of the table.

Hereās our new column.

Also, you can click- Home > Cells > Insert > Insert Table Columns to the Right to add a column.

Now weāll find the total cost for each order using Structured Reference.

Steps:

ā© In Cell E5 type the formula given below-

`=SUM(Table1[@[Price]:[Delivery Cost]])`

Now take a look that we have found the total values for all rows. No need to use the Fill Handle tool here. Itās the advantage of Excel Table.

3 More Examples with Formula in an Excel Table

Letās see three more examples of how to use formulas in an Excel Table.

1. SUMIF Formula with Single Criteria

Let, weāll count the sold units which have been sold in online with the SUMIF function. The SUMIF function returns the sum of cells that meet a single condition.

Steps:

ā© Type the given formula in Cell D14ā

`=SUMIF(Sales1[Sell Type],"Online",Sales1[Unit Sold])`

ā© Click the Enter button then.

Hereās our output-

2. SUMIFS Formula with Multiple Criteria

Here, weāll count units with multiple criteria. Weāll count only those units which are Food items and sold in-store by using the SUMIFS function. The SUMIFS function returns the sum of cells that meet multiple conditions.

Steps:

ā© By activating Cell D14 write the given formula-

`=SUMIFS(Sales2[Unit Sold],Sales2[Category],"Food",Sales2[Sell Type],"Store")`

ā© Later, just hit the Enter button.

We are done with the operation now.

3. COUNTIF Formula to Count Based on Criteria

In our very last example, weāll count the online orders with the COUNTIF function. The COUNTIF function counts the number of cells in a range, that meets the given criteria.

Steps:

ā© Write the given formula in Cell D14ā

`=COUNTIF(Sales3[Sell Type],"Online")`

ā© Hit the Enter button to get the result.

Then weāll get the output like the image below.

Conclusion

I hope all of the methods described above will be good enough to use a formula in an Excel table. Feel free to ask any question in the comment section and please give me feedback.

Excel Table Formula: Knowledge Hub

<< Go Back to Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF