How To Use The Subtotal Feature And Subtotal Function In Excel

[@barbvolpe4607]

Jon - you are an excellent educator!!! You break down topics and explain them very clearly. Thank you!

[@wayneedmondson1065]

Hi Jon.. thanks for this second half of the SUBTOTAL lesson. It is a strange inconsistency that the Subtotal feature (from the ribbon) excludes the last Subtotal for the range defined in the Grand Total. Worth mentioning that the Subtotal feature (from the ribbon) can also remove the subtotals and row groupings for you via the Remove All button on the Subtotal dialog box. Another good trick.. if you want function_num 109 instead of 9, you can just do a Find and Replace.. i.e. Find "SUBTOTAL(9" and Replace with "SUBTOTAL(109" against the desired range.. after you have set up the data using the Subtotal feature (from the ribbon). This saves the time of manually modifying each formula.. handy if you have many. Thanks for the lesson.. always learn something new and interesting at your channel. Thumbs up!! PS - Good tip below by Eliot that the data must be sorted by primary grouping before using Subtotal.. too bad Excel will not automatically recognize like items in the primary group and do that automagically for the user :))

[@wramos3]

I love this and your others videos. I really appreciate what you doing, the way your explain and the pertinent of your topics. I'm improving my knowledge in Excel thanks to you, nobody show the level of knowledge that you have and the way you teach. Sometimes I'm lost, but it's for my level of English. Thank you very much.

[@ExcelCampus]

Thank you for the nice feedback, wramos! I really appreciate and I'm happy to hear you are learning so much about Excel. Awesome! 🙌

[@SurjeetSingh-sn8mg]

Thank you for your comment. We also love his channel along with anothher simple KZbin channel: kzbin.info for excel simple tricks!!

[@ARS1508]

Jon your videos are proving to be immensely helpful in office. Thank you

[@robinq5511]

This was very helpful for someone who is not too savvy with Excel functions- Thanks!

[@scheinweltco]

I just realized how much I had taken for granted the subtotal function haha. Such a valuable knowledge in just 8 minutes! 😊

[@emmadkareem5247]

Very good. Not sure why many videos on Subtotal overlook mentioning that the data must be sorted before applying Subtotal.

[@ExcelCampus]

Thank you! 😀

[@abilyn-si3yo]

You save me with this video, I need to tell you this, I was trying to obtain subtotal; my first language is Spanish so I sought for instructions in Spanish, but I'm using an excel that's all in English; this laptop was given to me by an American company well, anyway, In Spanish instead of a coma* is this sign ; so I was getting frustrated 'cause I was not getting the formula, if it wasn't for you I wouldn't , thanks a lot!

[@fozzyunlv]

Thank you for providing explanations for both Subtotal systems. The feature is so much more fun to play with. I can't wait to tell my nerd club!

[@briannyamuzinga6694]

Thank you Jon, I appreciate your comprehensive explanations. Keep it up!!

[@Zary2016]

Uhm BLESS YOU, I literally have been panicking because I need to know this for a new job.

[@anarosacastaneda1051]

Thank you very much. Very well explained and comprehensible.

[@stevejez]

Hi Jon, I use Subtotal in cost reports (excel structured table) with slicers, pivot tables are way beyond most the people I work with. However, I include the header row in the sum range to capture any inserted rows at the top, I also include a color filled thin row above the subtotal - included in the sum range - to try & capture any inserted rows at the bottom, although this isn't foolproof if the user inserts a row immediately above the total row, this isn't included in the sum range. Summing the whole range with subtotal is safer than selecting individual cells in a sum formula. Another safe guard is to use a subtotal formula outside of the table - =SUBTOTAL(109,CostSummary[Cost]) - using the structured table reference, you can then be confident that everything within the table is included in the sum range.

[@Sialexii]

OH MY GOSH! Thank you so much!! I've been having a problem with the hidden rows being part of the sum.

[@lilskies7806]

This is very detailed and helpful. I can totally shorten my time doing my work. More power!

[@ExcelCampus]

Thanks for your feedback, Lil! :)

[@EliotPowell]

You didn't mention, but I think important for the Subtotal Feature ... the item for subtotaling (in your example "Product") must be sorted and grouped before subtotaling. If an item is out of order (e.g., a single Product B is below Product C), then the Subtotal feature will add a second Subtotal for the misplaced item.

[@ExcelCampus]

Great point Eliot! Thank you for mentioning that! 👍 I added your comment and a note on this to the blog post as well.

[@ExcelCampus]

And also another reason to use pivot tables, as the source data for a pivot table does not need to be sorted.

[@kathysmith3035]

The best explanation by far!! Thank you! I look like a genius now!

[@charmssunny8120]

I really admire your clear explanation in each & every video you upload! Thank you for spreading your knowledge across, you are doing a kind job😊

[@SarahPhillips-k1y]

Thank you!!! You are so good at explaining this in a way even I can easily follow.

[@ExcelCampus]

Thanks for your feedback, @user-xo7zi5li1r 😀

[@terriallen5405]

This video helped me learn more about excel, for own knowledge and for my class. You have nice teeth.

[@mireillecantrell4638]

I have not used Excel in several years(10) and it was driving me crazy as to where the Subtotal tool resided. Thanks so much for your video! #Rusty

[@antman7027]

Jon, your videos are awesome. Thank you so much!

[@rondenning]

Hi Jon. Excellent tutorial! I am curious where you explain how to get the number columns on the left of your table. (1,2,3) in your video. If there is another video that includes this, I haven't located it yet. Thanks.

[@rondenning]

Disregard, I re-watched and saw it adds it when do the outline.

[@sophiah.4227]

this was super helpful, you saved my life. thanks so much!!

[@rathnakumarrjd6060]

Thank you for your clear explanation 👍🤝

[@fbergmansr]

Really Great!

[@ExcelCampus]

Thanks, @fbergmansr! 😀

[@SamerDebs]

Great video. Who the hell down votes a good video?

[@charmiejanemagolhado1419]

thank you so much for this very helpful explanations......easy to understand :)

[@17aig]

Great video thanks for the all great info you are providing very helpful

[@shivendrasingh5916]

Thanks Jon, this is really helpful - thanks for taking the time to share with us.

[@ExcelCampus]

Glad it was helpful! 😊

[@darrellmartinquinn6605]

It is absolute useful. I can't do my work without this video and (kzbin.info/www/bejne/Y4W1nIiQjtWmnbc)

[@darrylmorgan]

Great Explanation...Thank You Jon :)

[@abubakarrkamara7630]

Fantastic lecture on subtotal. Thanks

[@ExcelCampus]

You're welcome, Abu! 😀

[@michalroesler]

Excellent video.

[@johng5295]

Awesome. Great content.

[@ExcelCampus]

Much appreciated! 😀

[@scottjackson1420]

This is terrific stuff. Thanks!

[@kirstenholland5316]

brilliant tip. Thank you

[@binhammady]

Very nice 👌 thank you brother

[@ShakrinJahanMozumder]

Thanks!

[@sudeshnadas248]

Thank You so much.

[@md.nazrulislam8997]

pls show subtotal from negative positive numbers at a time. I badly need it.

[@gaetanomaximus8650]

Awesome, thanks!

[@businessblueprintA]

Fantastic Thank you so much!!!

[@robertramirez7508]

I’m trying to have excel see a list of numbers and number them in order 1-27 which function would be best?

[@ShubhamYadav-uc2mp]

Thanks :)

[@adrianadavila368]

Can I use the subtotal feature inside a Table?

[@lukelawrence9331]

Thank you

[@vennesavanwyk1778]

thank you!!

[@alinecarvalho6584]

I loved it!! great tip and great explanation as usual! Thank you Jon

[@ExcelCampus]

Thanks Aline! I appreciate your support! 😊

[@Arvindp22]

Hi Jon, Brilliant video, I have two questions and if you can help me will be appreciated. 1. How did you create the drop downs on the left of your screen. 2. If there is a number that is not part of the subtotal then how do we stop it from being added into the subtotal range at the end. In between my subtotals i have number and it adds that number to the main subtotal at the end. Regards,

[@arletterosario9636]

thanks for the video, can you use the subtotal with a condition for exambple sumif?

[@charlottepilard165]

Hello, thanks for this feature Do you know how to do the same in Sheet please ? which add new rows like in Excel according to column Thanks by advance

[@aileineras.28]

Thank youuu!!

[@kalapalanivelu4139]

Hi Jon, does the subtotal function work if you have a row separating eg each product type .

[@barrylibrando9658]

sir why is it that the sub total product C total is not included in the grand total calculation , just want to know the reason.Thanks

[@maryheller8806]

I have been using the subtotal feature for a while but I have a question. Is there a way to show the information from the Price row in the subtotal row as it is currently blank. I have been dragging the cell information but I wonder if there is an easier way. Thanks for your videos!

[@ExcelCampus]

Thank you, Mary! Dragging is a good option. Another option is to copy either one of the $ formulas (Revenue or Shipping) from the same row and paste it into each Price spot. A third option is to check the box next to Price in the Subtotal pop-up window. The Price was left blank on purpose since a sum of unit prices may not make sense (although an average of unit prices might). Hope that helps!

[@DhirajKumar-rx8hi]

If someone wants to apply subtotal on two columns one after another then how to do that

[@0305SEA]

is there a Subtotal button or create one?

[@LoveNLIL]

I tried subtotal in a dataset with country and total income but it gave the same country where then once. Probably because there are spaces and therefore sees same countries as diff categories?

[@XMegaJuni]

pretty cool video, i like that its current. Thank you for this. you mention using pivot tables over this, does it mean that subfunction total is obsolete?

[@jaewonchoi5205]

thank you.

[@xujueyao]

What if I want to keep the price in the subtotal row? How to do that?

[@johnmcconalogue6498]

excellent

[@vijayr9262]

Hi Jon, Thanks for your videos, all superb and useful in day to day works! I have one query, I have received the Cheque from one of my Customer but do not know from whom. How to validate with my available data. Maybe the Chq. amount for multiple invoices per customer record. Do we have any functions or VBA code. It’s like using sumifs and goalseek function. Pls. do the needful. Thanks in advance!

[@msjaejontelle]

Is there a way to chart multiple subtotal amounts in a pie chart showing as a percentage of the grand total, ie Product A is 30 % of the grand total, Product B is 15% of the grand total, etc but showing this in a pie chart??

[@siamaksaadatpur]

hi Thank's

[@Gropmag215]

Great vid! If you have a table of data and one of the columns has times in it. Is there a way to create a Subtotal for the column of time (ex. idle time) and have it sum that data?

[@markblewitt7650]

Thank you, you have saved me an afternoon.🙂

[@ExcelCampus]

You're welcome! 😀

[@pwintphyuaung2784]

thanks jon !

[@ExcelCampus]

Thanks Pwint! 🙂

[@onlineronliner1358]

Hi Jon, thanks for your videos. I have a simple question, not at all related to this video. I have a column of IDs (146 rows) and I want to randomly assign these to another column (about 50,000 rows). Please how do I go about it. Thanks in advance.

[@JamesBond-qg5gx]

Hi Could you please help me to resolve the following: Columns : Products, Price, Qty sold I want to sum(price *qty sold) as well as subtotal in case the filter is applied Eg. =Subtotal(9,Sum(B1:B10*C1:C10)) Thanks

[@ericgilson9060]

Looking to use TRIMMEAN calculation in SUBTOTAL function. Did I just discover the first thing that excel cannot do?

[@blackorangey]

Hi Jon, what if the Products were not sorted i.e. all Product A listed together, would the subtotal function still work?

[@VicsChannel]

At 5:39 you discuss the subtotal feature in the Outline tab. I see Subtotal in the ribbon but it's greyed out. Why is that? What can I do to "fix" it so I can use "Subtotal". Group and Ungroup are both available.

[@muhammediqbaltp2024]

Thanks , how to apply validation for subtotal, drop down list ?

[@rudrudolph7376]

Hi...please filtering on a duplicated row takes much time..can u please help out🙏🙏🙏

[@manjulachourasiya8848]

Sir, please can you help me? I am beginner. Can you tell me that is it necessary to sign in to use excel, PowerPoint, word etc.? And also is ms office free to use or we have to pay for it?

[@dnowinsky]

What if I want to add more data to my subtotal group. How do I refresh the subtotal groups to include the data that I just added with my currently formatted subtotal group?

[@Bigskydog]

is there anyway to subtotal rounded numbers? You can sum(round...) but I can't get it for subtotal

[@MohannadAlhariri]

Hello Jon , how can I exclude the outliers in the data set ??? Thank you :)

[@NewYears1978]

Is there any way to subtotal and get a sales total but then manipulate other columns data that are strings? So it's a list of a customers orders, and I get the subtotal that's fine, but say another column has strings that could match or be diff..is there a way to make the subtotals show like specific data? (Like if the rows subtotaling included the words Personal it could say "Personal" in the subtotal?

[@richardhay645]

The biggest problem with th Subtotal feature it does not work with Excel Tables and the SUBTOTAL function only works as a a Total Row. That fact needs to be explicitly in any video on the Suntotal.

[@wongpeggy4363]

Hi but I don't understand why use 9?

[@MarksmanSpecialist]

woooow what the heck? i been using excel for years and i just noticed the subtotal feature....

[@MyChannel706]

Excellent explanation! Thank you!

[@SkyTravelBlog]

Thank you , helped alot.

[@kirk8985]

Thank you!

[@roberthartmann8723]

Thank you

[@07XTX]

Thanks Jon, this is really helpful - thanks for taking the time to share with us.