Tame your computer - table top trick


20-05-2019
Roem Tip Time logo

In her regular series for Cambridge Network members, software training expert Karen Roem offers handy tips to help you 'Tame your computer'. This week she explains how to 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 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

Upcoming courses

 

Course

Date

Price

Microsoft PowerPoint Intermediate

Tue 04 Jun

£ 197.00

Microsoft Excel Getting Started

Wed 05 Jun

£ 115.00

Microsoft Word Introduction

Thu 06 Jun

£ 197.00

Microsoft Access Introduction

Mon 10 Jun

£ 197.00

Microsoft Excel Intermediate

Mon 17 Jun

£ 225.00

Microsoft Excel Intermediate/Advanced

Wed 19 Jun

£ 225.00

Microsoft Excel Manage and summarise data

Thu 20 Jun

£ 115.00

Microsoft Word Advanced

Tue 25 Jun

£ 197.00

Microsoft Excel Introduction

Wed 26 Jun

£ 197.00

Microsoft Excel Getting Started

Tue 09 Jul

£ 115.00

Microsoft Excel Further Use

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

 

Independent, innovative software training company based in Cambridge. Registered supplier to local companies such as AstraZeneca, University of Cambridge, South Cambridgeshire District Council and NHS England.

Roem Limited