How to Add a Total Row in Excel Table

65

This tip is about the how to Add a Total Row in Excel Table. So read this free guide, How to Add a Total Row in Excel Table step by step. If you have query related to same article you may contact us.

How to Add a Total Row in Excel Table – Guide

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It has calculation or calculation capabilities, graphical tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA). Excel is part of the Microsoft Office software suite. Microsoft Excel has the basics features of all worksheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations such as arithmetic operations. It has a battery of functions provided to respond to statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms, and graphs, and with a very limited three-dimensional graphical display.

This allows the data to be sliced ​​to see the dependencies of different factors for different perspectives (using pivot tables and scenario managers). A PivotTable is a tool for data analysis. It does this by simplifying large datasets through PivotTable fields. It has a programming aspect, Visual Basic for Applications, allowing the user to use a wide variety of numerical methods, for example to solve mathematical physics differential equations and then report the results back to the spreadsheet. It also has several interactive features features enabling a user interface that can completely hide the spreadsheet from the user, so the spreadsheet presents itself as a so-called application, or decision support system (DSS), a custom design through the user interface provided by, for example, a stock analyst, or in general, as a design tool that asks the user questions and provides answers and reports.

What is an Excel table?

Many people think that the data in an Excel worksheet itself is in a table as it is arranged in rows and columns. However, your data cannot really be a ‘Table’ until you specify it as one. An Excel table is a dynamic set of rows and columns that are pre-formatted and arranged along with various special table features like data aggregation, data styling, automatic updates etc. Data aggregation comes in the form of a total row, which provides summary calculations for each column, such as sum, average, count, etc. overview of your data with minimal effort.

Converting your dataset into an Excel table

To view the Total Row, your data must first be converted into an Excel Table. Let’s say you start with a raw dataset as shown below: Here are the steps to convert a dataset into an Excel data table:

Click on any cell within your dataset.

In Home tab, select the ‘Format as Table’ option button (found within the Styles group).

Select your preferred table style from the drop-down list that appears.

This will bring up the ‘Format as Table’ dialog.

Make sure your dataset range displayed under “where is your table data” is correct.

You should also see the green box of ants marching around the cells that will be included in your table. Correct the range if it doesn’t cover all your data.

If your data has headers, make sure the “My table has headers” box is checked.

Click OK.

Your dataset should now be converted to an Excel table. You can tell by the change in the style of the dataset and the appearance of small arrows in every cell in the top row or header row:

Adding a Total Row to Your Excel Table

Once you’ve converted your dataset into an Excel data table, adding and setting up a total row is very easy. There are two ways to do this.

method 1

Select any cell within your Excel table.

Select the Ribbon Design tab (under Table Tools).

In the Table Style Options group, you should see a check box next to Total Row.

Check the box to ensure that it displays the Total Row at the bottom of the table.

method 2

Right-click on any cell within your Excel table.

Select the Table option from the context menu that appears.

Select Totals Row from the submenu that appears.

Regardless of the method you choose, you will now be able to see a Row Total added to the bottom of the table, with the total for the last column displayed. Once the Total Line is displayed, you can configure it to display the type of result you want to see. If you want to see results in addition to the total, click on any cell in the row and you will see a drop-down menu with available results options.

Using other aggregation functions on the total row

The ‘Total Row’ gives you the option to display various aggregated results such as mean, min, max, standard deviation and even the result of a custom function. For example, let’s say you want to display the Average Sales Price. In that case, here are the steps you need to follow:

Select the cell in the Total Row corresponding to the Sales Price column (cell E7).

You should see a small arrow next to the cell. Click on this arrow.

In the drop-down menu that appears, select the ‘Average’ option.

You should now see the average sales price displayed in cell E7.

If you click on one of the displayed results, you will notice in the formula bar that most aggregation operations on the Total Row use the SUBTOTAL function:

This is because the SUBTOTAL function has the ability to ignore hidden rows. Therefore, the calculated aggregates update correctly even when the table is filtered.

You can also use functions other than those shown in the drop-down list. For example, if you want to find a conditional sum of Qty, you can use the SUMIF function as follows:

Select the cell in the Total Row corresponding to the Qty column (cell C7).

You should see a small arrow next to the cell. Click on this arrow.

In the drop-down menu that appears, select the last option, ‘More functions…’

This will bring up the ‘Insert Role’ dialog from where you can select the role you want to use.

Final note

I hope you like the guide How to Add a Total Row in Excel Table. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.