Uma has just received a call from her supervisor to quickly bring him a break down of staff names and salaries with subtotals for all departments. She needs a quick and easy solution, What she is after, will look a little bit like this. There is a feature in Excel that summarizes subsections of your data, it is called the subtotal tool. You can find it on the data tab. But when we try and use it with our table, you will notice that it is unavailable. In order to apply this tool, we first need to convert our table back to range. Now, while tables are absolutely brilliant, they're not suited to all tasks. We are really looking to use them whenever we are working with raw data entries. In other words, every row contains an entry to your database. When you are looking to create summaries of sections inside your table, you start running into trouble. The subtotal function works brilliantly in the total row at the bottom of your table. But if you try to use this function in the middle of your table, you would effectively break your table. Now, before we do this, let's make a copy of the database worksheet for this task, so that we don't compromise our original data set. Keep in mind that the report will be just a snapshot of the database as it is now, and will not be updated from here. As a reminder to our future self and others, we will name the worksheet accordingly. Uma's supervisor, wants this break down by department. So let's quickly sort the table before we convert it to a range. You could do this afterwards, but it's just as easy to do it now. Then let's remove the banded rows because they will become messy once they no longer automatically update in the range. And the total row should also be turned off. Then we head over to the table tools, and click on convert to range, confirm the dialog box, and here's our data back as a range. And you also notice that the table ribbon is now gone. You can see that the subtotal feature is available now, and we click on it and there are a few settings we need to check. What we want is a summary for each department, so we need to tell excel that whenever there's a change in value in the column that we select, we want to see a subtotal. So in the first setting, we select department. And in a minute, you will see why we needed to sort our data by department first. Now, we need to choose which function to use. And as we are looking to add up the salaries, we will choose the sum function. We can select multiple columns to summarize In our case, we want the subtotals for annual salary, pension, and package. Generally, the defaults for the next three options will give you what you want. Yes, we want to replace current subtotals. Well, we don't really want to create a page break between the groups. This may be useful to you, but it really depends on your needs. In our case, we want to keep the data together. The third option provides you with a grand total. And I'm going to leave that selected to have the total displayed at the end of our report. So within a few clicks, we were able to create the breakdown by department for Uma's boss. For all departments, you can now see each of the staff, their salaries, pensions, and packages, as well as the total for that department. To quickly improve the layout, let's hide the columns in between the department names and the salaries. One last feature of the subtotal tool that I want to show you is the outline on the left hand side that the subtotal creates automatically. There are three levels, and you can use these little buttons here to navigate between them. We are currently at level three which shows the complete data set. Level two collapses the detail, and shows the subtotals for each department. And finally, level one shows only the grand total. Now you can see why the subtotal feature is not supported by tables, because we now have summary data mixed with our data entries. We made a copy of our range to create this snapshot report. But in case you want to remove the subtotal from your range to keep working in it, it is quite easily done. Go back to the subtotal tool, click on remove all, and here we are back where we started. As we have seen, converting a table back to a range is easy. I recommend that you remove banded rows or columns before you convert the table. The subtotal feature is really handy, and helps to create quick snapshot reports of your data. But remember, you can only use this feature with a range, not a table.