Excel 2010 in Windows 7 is a boon for people who look to filter data from huge data files easily and efficiently.
Hi, my name is Ghamza Jacobs. I am an IT trainer with New Horizons in London. I am here today just to show a couple of cool little tips and tricks on Windows 7 and Office 2010.
How to create subtotals in Microsoft Excel? Subtotals are extremely useful ways to look at totals per regions, per divisions, per departments, per areas and an easy way to work with this is if you look at the file that I have got at the moment, I have got a quite a bit of data in there, not too much just a little bit but also, I have the divisions so someone comes to you, your boss comes to you and asks you, "What was the total gross pay per division?" Then go in, "Well, I need to filter and then do this and that," you don't need to. First thing, you want to do that, anyone asks you any type of question like that, what is the total per country, total per city anything like that, all you need to do is click into the division column or the column you would like to get the total by. I am going to use a very quick ascending sort and in 2007 and 2003, it is under the home tab on the far left, brought the arrow down, I am going to sort a to z.
Notice that sorts the division together so I have got Australia, then Canada, then Germany, then Great Britain. Now, to create the total per division, remember I have sorted and that is important, sort by the column, so sort by the name, sort by the client, sort by the area whatever you need to sort by, sort by that first. Then we will go to data, subtotal that was data, subtotal.
At "each change in" here, what you are doing is you are saying, when my division changes, use the sum, notice you can change this to any other function, use the sum under gross pay, click OK. Here we go, quick easy totals total for Australia, total for Canada, total for Germany, Great Britain, with the grand total at the bottom. Another nice thing about your subtotals is if you look on the far left, I have got numbers over here, this creates groups, so now what you can do is click on the number two, you have got your totals.
I can now easily leave this in this view and click on the plus sign or minus to expand or collapse it. To remove the totals, very easy, you go back to data, subtotals, remove all, like you have never touched the data. That was how to create subtotals in Microsoft Excel. .
