Md. Ahaduzzaman
We often need to know the total value from a certain dataset in Excel. We can use it to determine the cost or revenue of a company. It can be the aggregate of the number of a student for a certain term. So it is quite a common task for a dataset. Here we are going to learn 4 easy and user-friendly ways to quickly insert a total row in an Excel datasheet.
📁 Download Excel File
Download the practice datasheet from here.
Learn to Insert a Total Row in Excel Using These 4 Methods
Here we are going to learn 4 ways to insert a total row in excel. We are using a product order sheet of a company. We want to know the total cost of all the products in a single row. We can see the datasheet for our demonstration purpose below.
1. Enabling Total Row Option in Table from Design Tab
Here we are going to learn how to insert a total row option in a table using the table design tab. So the step-by-step procedure for this method is as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select our data to range from the data set.
- Then we go to the Insert option and select the Table option.
- In this step, we get the Create table dialogue box.
- We select My table has headers and press OK.
- After doing this our data range is changed into a table.
- Now we go to the Table Design Option and select Total Row.
- We will immediately see a new row at the bottom with the Total cost of our product.
Using Other Aggregating Functions in the Total Row
Here in our demonstration, we have used the SUM function by default for the total row in our Excel datasheet. But excel gives us the choice to use other functions as well. Therefore we can select AVERAGE, MAX, MIN, COUNT, or any other function we like. A snapshot of some other choices is attached below.
Read More: 3 Ways to Insert Rows Between Data Using Formula in Excel
2. Applying Keyboard Shortcut
Here we are going to learn how to apply a keyboard shortcut to effectively insert a total row in Excel. So the step-by-step procedures are shown below for better understanding.
⬇️⬇️ STEPS ⬇️⬇️
- Just like the previous method we again select our data range and turn it into a table.
- Then we press the buttons given below.
Ctrl+Shift+T
- As a result, we will get a row with the total cost at the bottom of the table.
Read More: 7 Quick Ways to Insert a Row Within a Cell in Excel
3. Using Context Menu
Here we are going to learn how to use the context menu efficiently to insert a total row in Excel for a dataset. So the step-by-step procedure for this method is as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Like Method 1 at first, we select our data range and insert our data range into a table first.
- Then we select any cell from our data range and Right Click with our mouse. We will get some options.
- At first, we select the Table option. Then we select the Totals Row.
- After selecting the Totals Row we will immediately see a new row at the bottommost row of our data table.
- Thus we will get the total cost for our products in cell E12.
4. Applying Excel Functions
Here we will see how to apply different Excel functions to insert a total row in Excel. Three different functions with step-by-step procedures are demonstrated in the following sections.
4.1 Using SUM Function
Here we are going to learn how to use the SUM function to get the total row in excel. So the step-by-step for this procedure is as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select our data range and insert it into a table. Now our data range is table-type data.
- Then we write Total in cell B12 and select Enter.
- Just after this step, we will see that an entire row is added to our table.
- Since there is no formula added in the formula box the Total cost at the moment is shown to be zero.
- Finally, in the formula box of cell E12, we write the formula below and get the result of the Total cost.
=SUM(E6:E11)
4.2 Using SUBTOTAL Function
Here we are going to see how to use the SUBTOTAL function properly to insert a total row in excel. So the step-by-step procedure for doing this is as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Just like the step shown in section 4.1, at first, we select our data range and insert it into a table format.
- After doing that we add another row at the bottom by typing “Total” at cell B12.
- Finally, we write this formula in cell E12.
=SUBTOTAL(9, E6:E11)
🔨 Formula Breakdown
👉 Here 9 is the SUM function which sums the value from E5 to E11.
👉 Here SUBTOTAL function gives us the sum of the selected data cells.
4.3 Using BYCOL Function
Here we are going to learn how to efficiently use a function called BYCOL to add a total row to a table in Excel. So the step-by-step procedures for using this function properly are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Again like the steps shown in sections 4.1 and 4.2 we select our data range and insert it in a table format.
- Then we insert a total row section by typing “Total” in cell B12.
- After that, we type this function in the function box of cell E12.
=BYCOL(E5:E11, LAMBDA(col, SUBTOTAL(9, col)))
🔨 Formula Breakdown
👉 Here 9 is the SUM function which sums the value from E5 to E11.
👉 Here SUBTOTAL function gives us the sum of the selected data cells of column E.
👉 LAMBDA function defines that SUBTOTAL calculation will be performed in column E.
👉 Here BYCOL function allows referencing the data range to create a calculation for each column.
📄 Important Notes
🖊️ While inserting data into a table we have to carefully select our data range. Otherwise, the result may be faulty.
🖊️ We have to write the arguments of a function properly to get the desired results.
📝 Takeaways From This Article
📌 We can use 4 different methods to insert a total row in excel.
📌 Apart from the keyboard shortcuts we can also use a different function to do the same task.
Conclusion
Here we have learned 4 easy and quick methods to insert a total row in Excel. We can use the Table Design Tab, Keyboard Shortcut, Context Menu, or Excel function to insert a total row in Excel. Users can use any of the methods they like or feel comfortable with. Also, readers can comment in the comment section if they have any queries. The writer will try his best to come up with a solution. Also, follow our site Excelden for more Excel-related problems and queries.
