Excel Quick Subtotals Tip
January 15, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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:
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.
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.
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.
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)
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?
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.
Last updated: January 11, 2019 4:21 PM