MS Excel - Subtotal

hi in this video we'll be looking for subtotals how we get subtotals for different areas let's see those one by one in this data if you see on the screen I have date region I have sales rep and sales now I can get a subtotal based on region a subtotal based on sales rep let's go with the first one that is region if you want a subtotal based on the region the very first thing criteria is your sales that is your region should be sorted maybe in ascending or descending order then we can go for this subtotal so I'll just keep my cursor and I'll sort it so I'll go in the Home tab sort and filter and I can select this sort Z to a so if you see this column is now sorted that is a region column now the second step is in the data tab I have to use subtotal I'll go to data tab and you can see here subtotal so I will get this dialog box whichever column you have sorted I have sorted this region column so that column needs to be selected from this place so that is at each change in that option that is the region what is each change in that means these are the data which is changing so after that change I want a subtotal after Southeast I want a subtotal after noticed I want a subtotal next is which function do you want we want some count average maximum what do you want so from here by default some is selected so I will keep it as it is next is I can only take a subtotal of this column because these are numbers if I want count I can take for others but only some I can take of this region that is sales so I'll say yes add subtotal to sales and I'll click on ok so once I click on ok you see I got a subtotal that is Wester total I have Psaltis total northeast and not so this way I can get a subtotal of all the options but now if you are not interested if you are managing the company you don't want to go in the details what you can do is you can hide the details and only get a subtotal how do you do that that's the easy trick method on the left hand side if you see if there's numbers as 1 2 3 these are nothing but grouping done based on the subtotals so if I click on 3 nothing happens if I click on 2 you can see there are some changes done so I'll click on this two button I'll click on this too yeah so you see all the details are hidden and I'm getting subtotal based on the region again if I click on this number one what will happen is I will not get the subtotal I will get the grand total again if you want you can just click on this two button and you see you'll get subtotal according to region next this again if you click on this 3 you will get based on all the detail itself so based on the numbers and the region you will get a subtotal next is if you want a remove a subtotal how do you do that again I'll go back to the data tab and I'll click on this subtotal in this subtotal you have a button at the bottom known as remove all so once you click on remove all the subtotal goes away your data comes back to the place right let's see one more example if if I if I want a sales rep subtotal how do I do that same process keep your cursor in that column and sort that how do you sort it in the data tab you can see us ascending to descending or descending to ascending so now at each change of the data let's say John John and I have Mary I have Mike so based on each total each change I'll get a subtotal same way I'll go to data tab and click on subtotal in this subtotal I'll select this at each change in sales rep at each change in cells sales rep and I'll select this sum I want for sales that's it so once I click on OK based on the sales rep I am getting a subtotal based on sales if again if you click on this number two you see all the details will be hidden and you'll get only subtotal of all the sales step similarly if I click on this one I'll get a grand total and now the last one if I want to remove the subtotal I'll go to the data tab click on subtotal and click on remove all so I'll go to get back it so in this video we have seen how to get a subtotal for the region column and for the sales live column that's all for this video

Channel: Tutorialspoint

Views: 590,863

Keywords: MS Excel, Excel, Microsoft, MS, MS Excel - Subtotal, Subtotal

Id: -F21Z_aBFTs

Length: 4min 9sec (249 seconds)

Published: Mon Jan 15 2018