MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Filter Subtotaled Data


November 14, 2017 - by Bill Jelen

Filter Subtotaled Data

Can Excel apply a filter to data that has been subtotaled? This article will show you how.

Watch Video

  • R.A. asks? Can you filter data that has been subtotaled?
  • How can you get the subtotals to appear at the top of each group?
  • When you create the subtotals, uncheck Summary Below Data
  • Can you use a Filter in a data set that has been subtotaled?
  • Yes - but you should include blank cells as well
  • How can you get the percentage calculation to work in the subtotal rows?
  • Right now, the percentage is totaling the other rows instead of doing the calculation
  • After adding subtotals, copy the percentage calculation from a non-subtotaled row to all other rows.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2174 filter subtotal data all
  • right today's question from RA he says
  • someone sent me his 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 equal
  • subtotal nine 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 hit the rows you'd have to use 109
  • instead of nine 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 gonna 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 gonna use the sum
  • function I'm gonna 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 up here 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 do we get the
  • subtotals on top next up we want to be
  • able to filter this data and so I'm
  • gonna filter based on product so I
  • choose one sell 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 gonna 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 gonna appear where the
  • product is blank or in so now I'm seeing
  • AT&T had two different records for ABC
  • and let's just do
  • test here choose those two cells totals
  • 23 9 7 8 and sure enough it's working 23
  • 9 7 8 if I would clear this filter the
  • AT&T total is $488,680.50 it to an
  • average it would average these 4 but if
  • you had you know larger orders and
  • smaller orders that 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
  • gonna do is we're gonna take one of the
  • formulas that's working so G 4 divided
  • by e forward copy that formula and we're
  • gonna paste it throughout the whole
  • column right including the totals it's
  • all pasted and we see that the average
  • gross profit per set using these numbers
  • here profit and revenue is 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 using for the
  • detail rows to the sub total rows it
  • will work all right
  • I'm book power excels MrExcel 2017
  • edition has a lot of subtotal topics if
  • you're a subtotal fan you're gonna love
  • this book click that I on the top right
  • hand corner alright today are a can you
  • filter data that's been subtly yeah I
  • actually you can it's just gonna work
  • provided you include the blank cell as
  • well how do you get the subtotals appear
  • at the top of each group when you're
  • creating the subtotals uncheck the box
  • for a summer and 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 will
  • thank RA for saying that question it I
  • want to thank you for stopping by we'll
  • see you next time for another net cast
  • from MrExcel

Download File

Download the sample file here: Podcast2174.xlsm

Title Photo: pixel2013 / Pixabay


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.