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:
- 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 )
- Put your cursor in a relevant cell in a column above the table. (If necessary, insert a couple of blank rows.)
- Type = and click on the relevant cell in the total row, which could – for example – be a COUNT, a SUM or an AVERAGE.
- Press ENTER.
See http://www.roem.co.uk/tip572.xlsx for an example. (If you get a warning that the file may contain viruses or otherwise be harmful I can assure you that 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
- Total the data in a table?
- How do I know if a table has been filtered?
- Select rows and columns in a table
- Filter dates by month
- Quickly format and enable filtering of your data
Course | Date | Price |
Tue 04 Jun | £ 197.00 | |
Wed 05 Jun | £ 115.00 | |
Thu 06 Jun | £ 197.00 | |
Mon 10 Jun | £ 197.00 | |
Mon 17 Jun | £ 225.00 | |
Wed 19 Jun | £ 225.00 | |
Thu 20 Jun | £ 115.00 | |
Tue 25 Jun | £ 197.00 | |
Wed 26 Jun | £ 197.00 | |
Tue 09 Jul | £ 115.00 | |
Tue 09 Jul | £ 115.00 |
All prices are per person and exclusive of VAT.
Book more than one person from your organisation on the same course, on the same date, and you get 10% off. Quote TEAM to get your discount.
The course fee includes a handout with exercise files and a month's free support on the topics learned.
20 May 2019
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk