How to Add a Total Row to a Microsoft Excel Worksheet

Start making more complex calculations

Whether you’re a seasoned Excel user or just starting to explore the powerful world of spreadsheets, knowing how to calculate totals efficiently can significantlyenhance your data analysis and reporting. In this article, we’ll guide you step-by-step through the process of adding a total row to yourExcel worksheet.

The total row is displayed in bold letters so you can easily differentiate it from the rest of the rows in your Excel worksheet. The data displayed in this row is generated automatically, and it will spare you the tedious work of adding numbers manually. There are several different methods to add the total row, and understanding how to do it will help you become more efficient in Microsoft Excel.

1. Add a Total Row in Excel Worksheet with Table Design Tab

Before using this method to add a total row to your Excel worksheet, you’ll have to convert your Excel data into a table. Follow these steps to do it:

The data in your Excel worksheet is now converted into a table. You’ll notice a new tab appeared on the ribbon namedTable Design. This is the tab you’ll use to add the total row to your table. However, in some cases, it’s possible you won’t be able to see this tab because it’s disabled in Microsoft Excel. Don’t worry, you can easily enable it. Here’s how:

Now you’ll be able to see the Table Design tab on your Microsoft Excel worksheet ribbon.

To add the total row:

Once you check the Total Row in the Table Design tab, your Excel table will automatically add a new row at the bottom where a total number will be displayed in bold.

The new last row on the bottom of your table, the total row contains the wordTotalin the left-most column, and thesubtotal formulais displayed only for the last column. But you can simply click on the empty cell below another column. This will reveal a dropdown menu from which you can choose the type of total you want to create.

2. Add a Total Row in Excel Worksheet with Keyboard Shortcut

Since the total row is very useful, it’s likely you will use it often. To speed up your workflow and skip going to different tabs to enable it, you can use a keyboard shortcut to add totals to your table. Here’s how:

You can use the same keyboard shortcut to toggle off the total row.

Another handy keyboard shortcut will allow you to select different aggregation types in your table. You can switch between Excel formulas easily. PressAlt+Down arrowto activate the cell’s drop-down menu to select an aggregate. You can continue using theUpandDownarrows to navigate the drop-down list. In order to select the wanted aggregation and apply it to the table, pressEnteron your keyboard.

3. Add a Total Row in Excel Worksheet with the Sum Function

You can also add the Total row manually. Here’s how:

4. Add a Total Row in Excel Worksheet from the Right-Click Menu

Another way of adding a Total row is from the right-click menu. Many Excel users will find this to be the simplest method.

This action will add the Total row to your table.

5. Add a Total Row in Excel Worksheet with VBA

If you’re working with tables in multiple worksheets, you’ll be disappointed to hear there’s no way to add a Total row to all the tables within a workbook. Excel also lacks the option to set a Total row by default when you are creating a table. However, there’s a workaround. You can use VBA (Visual Basic for Applications) to toggle total rows on and off in all the tables in your workbook.

In order to use VBA, you’ll need access to the Developer Tab in Excel. If you can’t find it on the ribbon, press Alt + F11 on your keyboard to bring up the VBA window in which you can work.

Once you got the VBA window opened, follow these steps:

Sub AddTotalRow()

Dim ws As Worksheet

Dim tbl As ListObject

For Each ws In ActiveWorkbook.Worksheets

For Each tbl In ws.ListObjects

tbl.ShowTotals = True

Next tbl

Next ws

End Sub

This VBA code will loop through all worksheets in your Excel workbook and all the tables in each sheet. It’ll set the ShowTotals property to True, which in turn adds the Total row for each table.

This code can also turn off the Total row, but you’ll have to change the“tbl.ShowTotals = True” code line into “tbl.ShowTotals = False.”

Now that you know how to add a total row to a Microsoft Excel worksheet, you can start making more complex calculations and organize your data more efficiently. Continue exploring the vast array of functions, formulas, and visualization tools at your disposal, and let Excel do all the work for you when you need tocalculate a loan.

Nicolae is a Jack of all trades technology writer with a focus on hardware, programming languages, and AI image-processing software. Over the last five years, he has ghostwritten numerous tech how-to guides and books on a variety of topics ranging from Linux to C# programming and game development. Nicolae loves everything that has to do with technology and his goal is to share his knowledge and experience with others.Read Nicolae’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved