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 this 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. 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. Likewise, I can add another formula to sum the total column.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
