Contextures

Contextures News 20190115

Excel Quick Subtotals Tip

January 15, 2019

Dynamic chart title, automatic subtotals, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Dynamic Title

When you add a title to a chart, it might show generic text, such as "Chart Title". You could type new text in the title, but I like to use this trick to create a dynamic chart title.

For example, here's how to make a chart title change, based on what's selected in a pivot table report filter:

  • In cell G1, this formula checks the report filter selection (C2):
    ="Annual Sales -- " & IF(C2="(All)", "All Regions",
         IF(C2="(Multiple Items)", "Multiple Regions", C2))
  • Click on the border of the chart title, to select it
  • Click in the Formula bar, and type an equal sign
  • Click on the formula cell (G1), to select it
  • Press Enter, to complete the formula

chart title linked to formula cell

See the step-by-step video, and written instructions, on my Contextures blog.

And if you need to make complex charts, take a look at Jon Peltier's Excel Charting add-in.

Quick Subtotal

After you create a named Excel table, you can click the Total Row check box on the Design tab. Excel automatically puts a Subtotal formula into the last column of the total row, to show a sum or count of the items in that column.

total row subtotal formula

Excel uses the SUBTOTAL function in the total row, because it only shows the total for items that are visible after applying a filter.

Instead of using the Total Row check box, you can type a formula in the row directly beneath the table. In the next screen shot, I typed an AGGREGATE formula in cell C9 -- it has more functions and options than SUBTOTAL.

When you press Enter, an option button appears, and you can put the formula in the Total Row, or choose one of the other locations.

choose location for formula

Excel Articles

Here are a few Excel articles that you might find useful or interesting.

Excel Tips - There's a lively discussion on Reddit - What Excel formula or tool has had the greatest impact on your Excel use? I didn't see anything offensive, but as always with Reddit, read at your own discretion. (Level - All)

Data Viz - Ann K Emery looks back at Data Viz in 2018, and makes predictions for 2019. I agree with her wish to use larger fonts! (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Old Database

Our basement is like a dusty technology museum, with boxes of mysterious cables, chargers for ancient devices, outdated hardware and decades-old software. Last week, I opened this box, and found the disks and manuals for Symantec's Q & A database software. It's version 4 for DOS, dated 1991.

Sales and support for Q & A ended in 1998, but some of my clients kept their databases going for years after that. Eventually (and reluctantly), most of them were converted to Microsoft Access databases. Did you ever use Q & A?

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190115ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsdalg@ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: January 11, 2019 4:21 PM