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.
Download Practice Book
You can download the free Excel template from here and practice on your own.
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:
ā© Activate Cell D14.
ā© Type =SUM(Sales[
Then you will see that it is showing the header names of our table like the image below.
ā© Select Unit Sold.
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]])
ā© Press the Enter button on your keyboard.
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