Look Who it Was Random video

Excel - Save Time and Frustration: Adding Subtotals to 168 Columns in Excel Made Easy! - Episode 351

787 views · Published 12 November 2009 · 2:09 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Save Time and Frustration: Adding Subtotals to 168 Columns in Excel Made Easy!

Welcome back to the MrExcel podcast, where we share tips and tricks to help you become an Excel pro. In today's episode, we'll be discussing a common frustration when it comes to adding subtotals in Excel - the limited number of fields that can be displayed in the Data, Subtotals dialog box. This can be especially maddening when dealing with a large data set, as it can take forever to manually add subtotals to each column.

In my book "Learn Excel from MrExcel", I teach you how to use the Data, Subtotal command to quickly add subtotals to an entire data set. However, during a recent power Excel seminar, a participant brought up a challenge they were facing - they needed to add subtotals to 168 columns! This would have been an incredibly time-consuming task, as the Add Subtotal feature only displays three fields at a time.

After some brainstorming, we came up with a solution that will save you a lot of time and frustration. Instead of manually adding subtotals to each column, simply add the subtotal to the very last column and click OK. Then, select all the cells that are missing the subtotal by using the Edit, Go To, Special, Blanks command. This will automatically select all the cells where subtotals need to be added.

Once the cells are selected, hit the AutoSum button and voila! The subtotals will be added in for all the selected cells. This simple trick will save you from having to click on each individual field and manually add subtotals. We hope this tip will make your Excel experience a little less maddening. Thanks for tuning in to another podcast from MrExcel, and we'll see you next time with more helpful Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction to MrExcel podcast
(00:19) Learn how to use Data, Subtotal command in "Learn Excel from MrExcel" book
(00:29) Solution for adding subtotals to 168 columns quickly
(00:43) The trick to adding subtotals to multiple columns at once
(01:15) Selecting cells to add subtotals to
(01:28) Using AutoSum to add subtotals automatically
(01:49) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Adding subtotals in Excel
Adding subtotals to 168 columns
Adding subtotals using AutoSum
Data subtotal command
Data subtotals dialog box
Excel tips and tricks
Power Excel seminar
Quick way to add subtotals in Excel
Selecting cells without subtotals
Subtotal for multiple columns


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152741/

What if you had to total 168 different fields using the Subtotal command? The maddening Subtotal box only lets you choose three fields before scrolling to see three more fields. In Episode 351, learn the cool way to quickly add the subtotals.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel