Excel - Adding Subtotals to Specific Categories in Excel - Episode 2432

Microsoft Excel Tutorial - Adding Subtotals to Specific Categories in Excel.

Welcome to another episode of the MrExcel Podcast, where we share valuable Excel tips and tricks to help you become an Excel pro. In today's episode, we will be discussing a question that was raised after our previous episode on Subtotals. The question was, "Is it possible to add subtotals to only one category of your data?" Well, the answer is yes, and I am here to show you how.

First, let's take a look at our data set. We have several categories, but only one of them needs subtotals. In the past, we have always used headings above our data to add subtotals. However, in this case, we will use the last data row in healthcare as our headings for the manufacturing category. Then, we will go to the Data tab, click on Subtotal, and choose the "At each change in" option for the customer Merck. We will then use the sum function for the four columns we want to subtotal and click OK.

Now, you may notice that we also get a grand total, which we don't need. But don't worry, I have a solution for that. In a previous video, #849, I showed how to remove the grand total from subtotals. Simply start at the grand total row and go up to the first data row, click on "ungroup," and then delete the grand total row. And just like that, we have our subtotals for the manufacturing category.

But wait, there's more! I started thinking, what if we also want subtotals for the categories that only have one customer, like retail and transportation? Well, I have a bonus for you. We can use the same method as before, but this time, we will use the "At each change in" option for the sector and choose the sum function for the columns we want to subtotal. And just like that, we have subtotals for all the categories in our data set.

I hope you found this episode helpful and learned something new about adding subtotals to specific categories in Excel. If you want to learn more Excel tips and tricks, be sure to check out my book, MrExcel 2021 Unmasking Excel. And if you enjoyed this video, don't forget to like, subscribe, and ring the bell to be notified of our future episodes. As always, feel free to leave any questions or comments down below. Thank you for watching, and I'll see you in the next episode of the MrExcel Podcast.

Table of Contents:
(00:00) Problem Statement: Subtotals for only one category in Excel
(00:26) Using headings for subtotals in VBA
(00:50) Example data set
(01:01) Adding subtotals for specific category
(01:25) Removing grand total from subtotals
(01:57) Bonus: Adding subtotals for other categories
(02:44) Final result
(02:59) 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 by category in Excel
Adding subtotals by sector in Excel
Adding subtotals to only one category of data
Excel subtotals tutorial
Learn Excel from MrExcel Podcast episode 2432
Liking, subscribing, and commenting on YouTube videos
MrExcel 2021 Unmasking Excel book
Removing grand total from subtotals in Excel
Saving subtotals in Excel using column numbers
Unchecking Replace Current Subtotals in Excel
Using buttons for subtotals in Excel
Using VBA for subtotals in Excel

Adding Subtotals to Part of a Data Set.

Geert asks: "in my experience the datasets are non-uniform in the sense that only a subpart of the data needs subtotals. (categories of data are sequentially listed and only 1 category has a further breakdown that needs subtotals – the other categories do/must not) Is it possible to do that using the buttons on the ribbon?
This episode shows how to add subtotals to each customer in the manufacturing region.

At the two minute mark, you will also see:
how to add a second level subtotal feature excel
how to subtotal by two levels in excel

#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks

[GeertDelmulle]

Mr.Excel, that is exactly what I asked for! Thank you!
In summary: sub-select and press that one button. Can be repeated (nested).
We’ll try to remember that. :-)

[wayneedmondson]

Nice one Mr. Excel!! Thanks and thumbs up!!

[RambozoClown]

Very cool trick. Have to put this one in the bag.

[simfinso]

This video means " selection subtotal " is it??