Filter Subtotaled Data

Learn Excel from MrExcel Podcast, Episode 2174: Filter Subtotal Data.

Alright, today's question from RA. He says, "Someone sent me a data set and it has subtotals at the top of each group. How do they do that? And then they created subtitles that you can filter. How can you do that? And then, later on, how can we get the percentage calculation on the subtotal row to work well?"

The beautiful thing about the Subtotal function-- =SUBTOTAL 9-- is that it ignores other subtotals in the set. But it also always excludes filtered out cells if you're using the filter. Now, if someone manually hid the rows, you'd have to use 109 instead of 9. But in this case, we're just talking about filters so it should be pretty straightforward.

So let's start. We really have three things to do today. We have to add subtotals at the top of the group. So, we'll go to the Data tab, here I'm going to add subtitles by Customers-- so, the first thing I do is Sort by Customer, which this data is already sorted and then click the Subtotal command at each change in Customer. We're going to use the SUM function-- I'm going to add it to all five of these, even though this Gross Profit Percent is not going to work-- and then if I want the totals to appear at the top of each data set, I uncheck this "Summary below data, click OK. And what I'll get is-- here's ABC Stores and the total for the ABC Stores appearing on top of that, alright? So, that's how to get the subtotals on top.

Next up, we want to be able to filter this data. And, so, I'm going to filter based on Product, so I choose one cell, click the Filter icon, and I want to just see the Totals for ABC. Alright. So, initially, I come in here and I choose only ABC, like that, click OK. And I realized that I'm seeing the ABC records, but now I'm not seeing the Totals. So, the trick here is going to be, if I want to see all the ABC records and the Totals for Product ABC, I'm going to choose both ABC and (Blanks), because the Totals are going to appear where the Product is blank, alright?

So, now I'm seeing AT&T had two different records for ABC, and let's just do a little test here. Choose those two cells, total's 23,978, and, sure enough, it's working-- 23,978. If I would clear this filter, the AT&T total is $498,000. But the 23,000 of Product ABC. Alright so let's try this again for a different record. Choose DEF, choose (Blanks), click OK. And we're seeing that AT&T--all of the DEF records, which they buy a lot of-- is 237,000. So, sure enough, the Filter command is working with the Subtotal. Alright, which is cool that that works.

But whats not working here, is the Gross Profit Percent. Alright, so we have these four records for ABC Stores, and it's summing them-- 225%... And it wouldn't even be correct to change this to an average. It would average these 4, but if you had, you know, larger orders and smaller orders, that's not the correct answer. And just to prove that-- so right here the average of these is 56.3. Now, what we're going to do is, we're going to take one of the formulas that's working-- so G4 divided by E4-- we're going to copy that formula, and we're going to paste it throughout the whole column, alright? Including the Totals-- so I'll paste. And we see that the average Gross Profit percent, using these numbers here-- Profit and Revenue, it's not 56.3 but it's actually 56%. This is one of those rules where you can't just take the average on the Total row, definitely can't take the Sum. But by copying the same percentage formula you're using for the Detail rows to the Subtotal rows, it will work.

Alright. My book, Power Excels with MrExcel, 2017 Edition, has a lot of Subtotal topics. If you're a Subtotal fan, you're going to love this book. Click that "I" on the top right-hand corner.

Alright. Today, RA: Can you filter data that's been subtotaled? Yeah, actually you can. It's just going to work, provided you include the blank cell as well. How do you get the Subtotals to appear at the top of each group? When you're creating the Subtotals, uncheck the box for a Summary Below Data. And then, "Hey, why aren't the percentage calculations working in the Subtotal rows?" Well, because it's one of those calculations that you have to do again on the Subtotal row. So just choose one of the percentage calculations from a detailed row, and copy it to the other rows.

Well, I want to thank RA for sending that question in, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.