Tip of the week # 572
17 May 2019


Show totals above a table column that match specific criteria (Microsoft Excel) *

The other day one of my delegates (thanks, Kate) asked whether it was possible to get the totals of a list of numbers in a column in a table at the top, rather than at the bottom. This is something you can easily do in a PivotTable (I'll save that for when I write a tip about my favourite PivotTable design settings), but there isn't a setting in a table. OK, you could use the Subtotal functionality, available from the Outline group on the Data tab. Or use the DSUM function. But as the total row functionality in a table reflects the filter - if any - I thought you might like a simple workaround.

Here's how:

  1. If necessary, format your data as a table and add a total row. (CTRL + T and CTRL + SHIFT + T spring to mind; see tip_561.php)
  2. Put your cursor in a relevant cell in a column above the table. (If necessary, insert a couple of blank rows.)
  3. Type = and click on the relevant cell in the total row, which could - for example - be a COUNT, a SUM or an AVERAGE.
  4. Press ENTER.

See tip572.xlsx for an example. (If you get a warning that the file may contain viruses or otherwise be harmful I can assure you can click on OK to open it.) The file shows a filtered table with a row at the top, counting the number of females with the total spend on their salaries as well as an average of their medical insurance. Use the filters as normal to automatically change the figures to match specific criteria.

Related tips

  1. Tip # 561: Total the data in a table? - tip_561.php
  2. Tip # 537: How do I know if a table has been filtered? - tip_537.php
  3. Tip # 525: Select rows and columns in a table - tip_525.php
  4. Tip # 505: Filter dates by month - tip_505.php
  5. Tip # 429: Quickly format and enable filtering of your data - tip_429.php

.


* Unless stated otherwise, these tips were written for Microsoft Office 2010.