Categories
Building-Furniture

How to add a total row in excel

One quick and easy way to add values in Excel is to use AutoSum. Just select an empty cell directly below a column of data. Then on the Formula tab, click AutoSum > Sum. Excel will automatically sense the range to be summed. (AutoSum can also work horizontally if you select an empty cell to the right of the cells to be summed.)

How to add a total row in excel

AutoSum creates the formula for you, so that you don’t have to do the typing. However, if you prefer typing the formula yourself, see the SUM function.

Add based on conditions

Use the SUMIF function when you want to sum values with one condition. For example, when you need to add up the total sales of a certain product.

Use the SUMIFS function when you want to sum values with more than one condition. For instance, you might want to add up the total sales of a certain product, within a certain sales region.

Add or subtract dates

For an overview of how to add or subtract dates, see Add or subtract dates. For more complex date calculations, see Date and time functions.

Add or subtract time

For an overview of how to add or subtract time, see Add or subtract time. For other time calculations, see Date and time functions.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

In this video, we’ll look at how to add and configure a Total Row to an Excel Table.

All Excel Tables come with a built-in Total Row feature. The total row allows you to easily show summary calculations below a table.

You can use the total row to calculate counts, sums, min and max, averages, and more.

There are a couple ways you can display a total row for a table. First, with any cell selected in the table, you can use the Design tab of the ribbon, under Table Tools.

There, under Table Style options, you’ll find a checkbox for a Total Row. Check to display, and uncheck to hide.

You can also right-click a cell in a table, and use the Table menu to display and hide a total row.

Once you display a Total row, you can configure which kind of result you want to see. When you click into any cell, you’ll see a drop down menu with available options.

Usually, Excel will have already selected a reasonable option. In this case for example, we see a sum of the Tax column.

However, you can easily switch to average, count, min, max, and so on.

Notice many of these operations use the SUBTOTAL function, as you can see in the formula bar.

The reason SUBTOTAL is used is because it has the ability to ignore hidden rows rows.

This allows the calculated summaries to update properly when the table is filtered.

There is also an option to see a larger list of functions.

If you select a function like SUMIF, you’ll see a familiar configuration dialog.

In this case, for example, I could choose to sum tax for only orders where the color is “red”.

Again, you’ll see the formula in the formula bar, which you can easily edit.

One thing that’s not obvious about the Totals row is that you can add more than one formula.

For example, I could add a count of all orders in another column, like color, and display both a sum and count.

This example teaches you how to create a running total (cumulative sum) in Excel. A running total changes each time new data is added to a list.

1. Select cell B9 and enter a simple SUM function.

How to add a total row in excel

2. Select cell C2 and enter the SUM function shown below.

How to add a total row in excel

Explanation: the first cell (B$2) in the range reference is a mixed reference. We fixed the reference to row 2 by adding a $ symbol in front of the row number. The second cell (B2) in the range reference is a normal relative reference.

3. Select cell C2, click on the lower right corner of cell C2 and drag it down to cell C7.

How to add a total row in excel

Explanation: when we drag the formula down, the mixed reference (B$2) stays the same, while the relative reference (B2) changes to B3, B4, B5, etc.

4. For example, take a look at the formula in cell C3.

How to add a total row in excel

5. For example, take a look at the formula in cell C4.

How to add a total row in excel

6. At step 2, enter the IF function shown below (and drag it down to cell C7) to only display a cumulative sum if data has been entered.

How to add a total row in excel

Explanation: if cell B2 is not empty (<> means not equal to), the IF function in cell C2 displays a cumulative sum, else it displays an empty string.

We can insert a total row in Excel by clicking the Design tab . With the Total row in our table , we can calculate several parameters automatically like average, maximum amount, minimum amount , etc. This tutorial will guide all levels of Excel users on how to use total rows in Excel .

How to add a total row in excelFigure 1: How to Insert Total Row in Excel

Convert Data to Table

To add a total row in excel to a table, we must first convert the data in the data range to a table.

How to add a total row in excelFigure 2: Data table

  • We will click on any part of the data range and click on Insert

How to add a total row in excelFigure 3: Click Insert

  • We will click Table

How to add a total row in excelFigure 4: Create table dialog box

  • We will click OK to create the table

How to add a total row in excelFigure 5: Created table

Add Total Row in Excel

To add the total row to the table, we will click anywhere on the table and click the design tab.

How to add a total row in excelFigure 6: Click on the design tab

  • We will check the Total Row box

How to add a total row in excelFigure 7: Inserted Total Row

To automatically get the total for Column C and D, we will click on Cell E14. We will click on the small box at the bottom-left of the cell, left-click and drag to the left (that is to Cell D14 and Cell C14).

How to add a total row in excelFigure 8: Inserted Total Row for Column C and D

If we click on any of the Cells of the total row, e.g., Cell C14, we will see a drop-down arrow . With this drop-down arrow, we can automatically calculate Sum, Standard deviation, Count , etc.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

This tutorial shows how to sum a column in Excel 2010 – 2016. Try out 5 different ways to total columns: find the sum of the selected cells on the Status bar, use AutoSum in Excel to sum all or only filtered cells, employ the SUM function or convert your range to Table for easy calculations.

If you store such data as price lists or expense sheets in Excel, you may need a quick way to sum up prices or amounts. Today I’ll show you how to easily total columns in Excel. In this article, you’ll find tips that work for summing up the entire column as well as hints allowing to sum only filtered cells in Excel.

Below you can see 5 different suggestions showing how to sum a column in Excel. You can do this with the help of the Excel SUM and AutoSum options, you can use Subtotal or turn your range of cells into Excel Table which will open new ways of processing your data.

How to sum a column in Excel with one click

How to add a total row in excel

There is one really fast option. Just click on the letter of the column with the numbers you want to sum and look at the Excel Status bar to see the total of the selected cells.

Being really quick, this method neither allows copying nor displays numeric digits.

How to total columns in Excel with AutoSum

If you want to sum up a column in Excel and keep the result in your table, you can employ the AutoSum function. It will automatically add up the numbers and will show the total in the cell you select.

    1. To avoid any additional actions like range selection, click on the first empty cell below the column you need to sum.

    How to add a total row in excel

    • Navigate to the Home tab -> Editing group and click on the AutoSum button.
    • You will see Excel automatically add the =SUM function and pick the range with your numbers.
      How to add a total row in excel
    • Just press Enter on your keyboard to see the column totaled in Excel.
      How to add a total row in excel

    This method is fast and lets you automatically get and keep the summing result in your table.

    Enter the SUM function manually to sum a column In Excel

    You can also enter the SUM function manually. Why would you need this? To total only some of the cells in a column or to specify an address for a large range instead of selecting it manually.

    1. Click on the cell in your table where you want to see the total of the selected cells.
      How to add a total row in excel
    2. Enter =sum( to this selected cell.
      How to add a total row in excel
    3. Now select the range with the numbers you want to total and press Enter on your keyboard.

    How to add a total row in excel

    Tip. You can enter the range address manually like =sum(B1:B2000) . It’s helpful if you have large ranges for calculation.

    This option is really handy if you have a large column to sum in Excel and don’t want to highlight the range. However, you still need to enter the function manually. In addition, please be prepared that the SUM function will work even with the values from hidden and filtered rows. If you want to sum visible cells only, read on and learn how.

    Use Subtotal in Excel to sum only filtered cells

    This feature is perfect for totaling only the visible cells. As a rule, these are filtered or hidden cells.

    1. First, filter your table. Click on any cell within your data, go to the Data tab and click on the Filter icon.
    2. You will see arrows appear in the column headers. Click on the arrow next to the correct header to narrow down the data.
      How to add a total row in excel
    3. Uncheck Select All and tick off only the value(s) to filter by. Click OK to see the results.
      How to add a total row in excel
    4. Select the range with the numbers to add up and click AutoSum under the Home tab.
      Voila! Only the filtered cells in the column are summed up.
      How to add a total row in excel

    If you want to sum visible cells but don’t need the total to be pasted to your table, you can select the range and see the sum of the selected cells on the Excel Status bar. Or you can go ahead and see one more option for summing only filtered cells.

    Convert your data into Excel table to get total for your column

    If you often need to sum columns, you can convert your spreadsheet to Excel Table. This will simplify totaling columns and rows as well as performing many other operations with your list.

    1. Press Ctrl + T on yourkeyboardto format the range of cells as Excel Table.
    2. You will see the new Design tab appear. Navigate to this tab and tick the checkbox Total Row.
    3. A new row will be added at the end of your table. To make sure you get the sum, select the number in the new row and click on the small down arrow next to it. Pick the Sum option from the list.
      How to add a total row in excelUsing this option lets you easily display totals for each column. You can see sum as well as many other functions like Average, Min and Max.

    Whether you need to sum the entire column in Excel or total only visible cells, in this article I covered all possible solutions. Choose an option that will work for your table: check the sum on the Excel Status bar, use the SUM or SUBTOTAL function, check out the AutoSum functionality or format your data as Table.

    If you have any questions or difficulties, don’t hesitate to leave comments. Be happy and excel in Excel!

    Sandy Writtenhouse
    How to add a total row in excelSandy Writtenhouse
    Writer

    With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Read more.

    How to add a total row in excel

    Tables in Microsoft Excel provide many benefits for working with your data. And if your table contains figures that you’d like to total at the bottom, this is a simple task. Here are three ways to add a total row to a table in Excel.

    Insert a Total Row Using Table Design

    You can add a total row to the bottom of your table using a simple checkbox.

    Select any cell in your table and go to the Table Design tab that displays. In the Table Style Options section of the ribbon, check the box for Total Row.

    How to add a total row in excel

    You’ll likely see only one total display, which is currently how the Total Row feature works. But in a few clicks, you can fill in the rest.

    How to add a total row in excel

    The total row at the bottom provides you with a drop-down list of options for each column. Click that arrow and choose “SUM” for one of the columns.

    How to add a total row in excel

    Then, select the cell and place your cursor on the bottom-right corner to display the fill handle (plus sign). Drag to the remaining cells in that total row to copy the formula.

    How to add a total row in excel

    If you prefer, you can also click the drop-down arrow in each cell and select “SUM” rather than copying the formula.

    You should then see the totals for all columns in your Excel table.

    How to add a total row in excel

    Insert a Total Row Using a Keyboard Shortcut

    If you’re a fan of using keyboard shortcuts to perform tasks quickly, then this method is for you.

    Select any cell in your table and press Ctrl+Shift+T. And there you go! A total row is added to the bottom of your table.

    How to add a total row in excel

    Luckily, this keyboard shortcut works the same way as the Total Row checkbox on the Table Design tab above, so you can follow the same steps to insert the remaining totals and copy the formula to the other cells in the total row.

    Insert a Total Row by Adding a Row and Formula

    One more way to easily insert a total row in your table is by adding a row, using the SUM formula, and copying it across.

    You can quickly add a row to the bottom of your table by going to the last cell in the first column and typing. For instance, you can type in the word Totals and hit Enter, which automatically creates a new table row.

    How to add a total row in excel

    Next, select the cell to the right to insert your first total. Go to the Home tab and click “Sum” in the Editing section of the ribbon.

    How to add a total row in excel

    Now that you have the total for the first column, you can copy that formula to the other cells.

    Select the cell with the SUM formula, place your cursor on the bottom-right corner to display the fill handle, and then drag to the remaining cells in that total row.

    How to add a total row in excel

    That’s all there is to it! From there, you can format the total row to make it stand out if you like.

    Totaling figures in a spreadsheet is a basic function, so having three simple ways to add a total row lets you use the method that’s most comfortable for you.

    • › How to Check Which Websites Can Access Your Location in Mozilla Firefox
    • › How to Open Control Panel on Windows 11
    • › How to Take Full-Page Screenshots in Chrome on Android
    • › Your Twitch Password Wasn’t Leaked in the Big Hack
    • › What Is Beta Testing?

    How to add a total row in excel Sandy Writtenhouse
    With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
    Read Full Bio »

    How to add a total row in excel

    Excel Tables are very useful for organizing and reporting data in a tabular format. Furthermore, you can also add a dynamic row to your table to display aggregate data while still utilizing the Excel Table features, like using the same styling and automatically updating the results. In this guide, we’re going to show you how to add a total row in Excel tables.

    How to add a Total Row in Excel Tables

    1. Click on a cell inside your table
    2. Open the Design tab
    3. Enable the Total Row checkbox under the Table Style Options section
    4. Click on a cell on a new row, and use the dropdown list to select and add an aggregation function.

    How to add a total row in excel

    Using the Aggregate Row

    Following the steps above and selecting the function is all you need to do for creating a Total Row. The listed functions in this section are essentially variations of the SUBTOTAL function, which allow including or ignoring hidden table rows. The SUBTOTAL function is useful if you use filtering in your table.

    Alternatively, you can use a custom formula in the totals row. For this, all you need to do is click on the cell and type in the formula.

    There are a variety of ways to add up the numbers found in two or more cells in Excel. This lesson shows you several methods including the SUM function and the Autosum button.

    Option One – simple addition

    Let's say you have several cells that contain numbers, and you would like to add those numbers together:

    How to add a total row in excel

    You can add up the values for Monday to Friday (from cell B2 through to B6) by typing the following formula into another cell in your spreadsheet. In this example, I've started typing the formula into cell B6. Notice how the cells in my formula have been highlighted as I typed my formula:

    How to add a total row in excel

    When I press Enter, the formula will be stored in the cell, and the result of the formula will be displayed as shown in the example below:

    How to add a total row in excel

    Note that if one or more of the cells are empty, or contain text rather than a number, Excel will simply ignore those values when calculating the result.

    Also note that Excel doesn't mind if you type the formula in lower case as shown below – Excel will automatically convert each letter to uppercase when you press Enter:

    In other words, typing this:

    is the same as typing this:

    The great thing about adding up cells in this way is that the formula will always show the current value of the sum of these cells. Any time you change one of the values in cells B2, B3, B4, B5 or B6, the value in B8 will change immediately to show the correct sum of these cells.

    A faster way to add up the values in several cell is to follow these steps:

    1. Click on the cell where you want the result of the calculation to appear.
    2. Type = (press the equals key to start writing your formula)
    3. Click on the first cell to be added (B2 in this example)
    4. Type + (that's the plus sign)
    5. Click on the second cell to be added (A3 in this example)
    6. Type + again, and the next cell to be added.]
    7. Repeat until all cells to be added have been clicked.
    8. Press Enter.

    This will create the same formula as shown in the example above without you needing to manually type the whole formula.

    Of course, this approach to adding up cells becomes difficult to manage once you have a large number of cells to be added. For that reason, you may prefer the next method of adding up cells in Excel, which uses a function to do the addition for you.

    Option Two – use the SUM() function to add up a range of cells

    The SUM() function is a much more efficient way to add up cells. It can be used to add up individual cells, as we did in the last example. However, it also allows you to add up a range of cells simply by specifying the first and last cell in a range of cells to be added up. The SUM() function will then add up the values in all the cells from the start to the end of the range. It is particularly powerful because it can be used to write a simple formula add up hundreds or thousands of cells .

    If we look at our earlier example, you could use SUM() as shown in the following formula, which would achieve the same result as our earlier example:

    How to add a total row in excel

    Notice how the cells included in the formula are highlighted – this is a useful way to check whether your formula is using the correct range of cells for its calculations.

    This formula adds up all the cells from B2 to B6 inclusive. This method could be used just as easily to add up several thousand cells, as shown in this example:

    How to add a total row in excel

    You could also use it to add up a row of numbers, such as this example:

    You can also use it to add up cells in more than one row and more than one column at a time, as shown in this example:

    How to add a total row in excel

    Finally, you can add up more than one range of cells at at time. Our earlier SUM() examples have only added up one range at a time. This example adds up several ranges at once. All the cells in all the ranges are added together to give a grand total:

    How to add a total row in excel

    As you can see, this formula specifies three separate ranges, each of which will be included in the same SUM() calculation.

    This brings us to one final example of how to use the SUM() function. Whilst there are no wrong ways to add up cells in Excel, here is an example of a a SUM formula that actually takes longer to create than it should. This is a common mistake made by new Excel users, and it can quickly become a bad habit:

    If you’ve ever had to sum up items across many different sheets, then you know it can be a real pain when there are a lot of sheets. This trick will make it super easy.

    How to add a total row in excel

    In this example, we have a table of sales figures each in a separate tab named Jan through Dec. Each sheet is the same format with the table in the same position within each sheet.

    How to add a total row in excel

    If you wanted to create a Total sheet and have a table in it that sums up each of the tables in the Jan to Dec sheets, then you could use this formula and copy it across the whole table.

    Creating this formula isn’t very efficient though, as it requires selecting the Jan sheet, then selecting the cell C3, then typing a +, then selecting the Feb sheet etc… Going through 12 sheets in all. There is a better way!

    How to add a total row in excel

    Add the sum formula into the total table.

    1. Type out the start of your sum formula =SUM(.
    2. Left click on the Jan sheet with the mouse.
    3. Hold Shift key and left click on the Dec sheet. Now select the cell C3 in the Dec sheet. Add a closing bracket to the formula and press Enter. Your sum formula should now look like this.

    How to add a total row in excel

    The formula will sum up C3 across each of the sheets Jan to Dec. You can also use this technique with other formulas like COUNT or AVERAGE etc… An easier way over cycling through each sheet individually.