How to Calculate Total Row and Column in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with a large worksheet in Microsoft Excel, we need to sum up cells in Excel. To sum up, cell values mean total columns and rows values, and MS Excel provides the most useful function named SUM. The SUM function is a built-in function in Excel. We can also use the AutoSum formula, the ROWS and COLUMNS functions, and the Table Design Option. Today, In this article, Excel total row and column, how to calculate, we will learn, by using SUM, and AutoSum functions and also creating a Table Design.


Calculate Total Row and Column in Excel: 4 Suitable Ways

Let’s say, we have a dataset that contains information about 9 different persons. The names of the sales representatives and their sales in different quarters are given in columns B, C, D, and E respectively. We will calculate total rows and columns by using the SUM function, AutoSum formulas, and so on. Here’s an overview of the dataset for today’s task.

Excel Total Row and Column


1. Use the SUM Function to Calculate Total Row and Column in Excel

We can easily apply the SUM function in our quotidian work. This function has a vast application in our day-to-day life. In Excel, we can use the SUM function both in rows and columns. In this method, we’ll use the SUM function to calculate the total rows and columns in the 1st, 2nd, and 3rd quarters from our dataset.


1.1 Calculate Total Row

In this sub-method, we will learn how to apply the SUM function along with rows. Let’s follow the instructions below to learn!

Steps:

  • We will learn how to calculate row values from our dataset. For that, again select a new cell. From our dataset, we select cell F5 and write down the SUM function in that cell. The SUM function is,
=SUM(C5:E5)

Calculate Total Row

  • Hence, press Enter on your keyboard and you will be able to get $163,347.00 as the output of the SUM function.

Calculate Total Row

  • Further, place your cursor on the bottom right side of cell F5 and an AutoFill sign pops up. Now, drag the AutoFill sign downward.

  • After completing the above process, you will be able to get the output of the SUM function along with the rows that have been given below screenshot.

Calculate Total Row


1.2 Calculate Total Column

After learning the SUM function along with rows. Here, we will learn how to calculate value along with columns. Let’s follow the steps below.

Steps:

  • First of all, select cell C14, and type the SUM function to calculate the total sales in the first quarter in column C. The SUM function is,
=SUM(C5:C13)

Use the SUM Function to Calculate Total Row and Column in Excel

  • After typing the SUM function in the Formula Bar, now, simply press Enter on your keyboard and you will be able to get $492,162.00 as the return of the SUM function

Use the SUM Function to Calculate Total Row and Column in Excel

  • Hence, autoFill the SUM function in columns D, and E and you will get your desired output which has been given in the below screenshot.


1.3  Non-contiguous Row and Column

Now, let’s say, we want to calculate some selected quarter’s sales from our dataset. From our dataset, we will calculate the sales of Tom, Ema, and John in the 3rd quarter. Let’s follow the steps below.

Steps:

  • First, select cell E15.

Use the SUM Function to Calculate Total Row and Column in Excel

  • After that, write down the SUM function in that cell.
=SUM(E5,E8,E13)
  • Where E5 is the sales of Tom, E8 is the sales of Ema, and E13 is the sales of John in the 3rd quarter.

Use the SUM Function to Calculate Total Row and Column in Excel

  • Hence, press Enter, and you will get your desired output that has been given below screenshot.

Read More: How to Total a Column in Excel 


2. Insert the AutoSum Formula to Calculate Total Row and Column in Excel

To calculate the total rows and columns, we can use the AutoSum formula. This is the easiest way to calculate the total rows and columns. You can automatically add up the columns and rows values, and the total will appear after the selected cells. Please, follow the steps below.

Step 1:

  • To sum up cell values automatically by applying the AutoSum formula, first, select cells C5 to E13.

Insert the AutoSum Formula to Calculate Total Row and Column in Excel

  • Hence, from your Home Ribbon, go to,

Home → Editing → AutoSum

Insert the AutoSum Formula to Calculate Total Row and Column in Excel

  • Now, simply press on the AutoSum menu, and you will get the return of the AutoSum formula that has been given in the below screenshot.

Insert the AutoSum Formula to Calculate Total Row and Column in Excel

Step 2:

  • Further, select rows 5 to 14 from our dataset.

  • After that, from your Home Ribbon, go to,

Home → Editing → AutoSum

Insert the AutoSum Formula to Calculate Total Row and Column in Excel

  • Finally, you will be able, to sum up, the total rows by using the AutoSum option.


3. Apply the ROWS and COLUMNS Formula to Calculate Total Row and Column in Excel

Another easy way to calculate total rows and columns is by using the ROWS and COLUMNS functions. Let’s follow the instructions below.

Steps:

  • First, select cell H6, and type the COLUMNS function. Then type in the Formula Bar is,
=COLUMNS(B4:E13)

Apply the ROWS and COLUMNS Formula to Calculate Total Row and Column in Excel

  • After selecting cell H6, press Enter on your keyboard and you will get 4 as the return of the COLUMNS function

Apply the ROWS and COLUMNS Formula to Calculate Total Row and Column in Excel

  • Again, select cell H7 and write down the ROWS function. Then type in the Formula Bar is,
=ROWS(B4:E13)

  • Hence, press Enter on your keyboard and you will get 10 as the output of the ROWS function.

Read More: How to Sum Every nth Column in Excel


4. Perform Table Design Option to Calculate Total Row and Column in Excel

We can calculate total rows and columns by using the Table Design option. Let’s follow the steps below to learn!

Steps:

  • We select any convenient cell for our work. Let’s say, we select cell C5 Then, go to,

Insert → Tables → Table

Perform Table Design Option to Calculate Total Row and Column in Excel

  • While clicking on the Table option, a Creating Table window instantly will appear in front of you. From that window, press OK.

Perform Table Design Option to Calculate Total Row and Column in Excel

  • Hence, from the Table Design ribbon, go to,

Table Design → Table Style Options → Total Row

  • Clicking on the Total Row, you will get the sum of column E in cell E14.

Perform Table Design Option to Calculate Total Row and Column in Excel

  • Further, select cell C14, and a Filter Button will appear in the bottom right corner of that cell. Now, click on the Filter Button, and a window will appear from that window select the Sum option.

  • After that, you will get $492,162.00 as the sum of column C in cell C14. Similarly, you will be able to calculate the total sum of column D in cell D14 by performing the Table Design Option that has been given in the below screenshot.

Perform Table Design Option to Calculate Total Row and Column in Excel

Read More: How to Sum Columns in Excel Table 


Things to Remember

👉 You can use Ctrl + Shift + T as keyboard shortcuts instead of the Table Design option. Another way to apply the AutoSum formula is Alt+=.

👉 #NAME error will happen when the argument is not properly passed.

👉 The error named #VALUE! happens when the value is not expected by the SUM function.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to calculate total rows and columns will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles


<< Go Back to Sum Columns | Sum in Excel | Calculate in Excel | 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.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo