Filtering Data Table showing rows of data which should not be included

Question

Hi all,

I have created a report in which I try to show what kind of customer we have (ABC-D).

In order to do this I have written a formula which creates a running total (=SUM($"invoice-column"$12:"Invoice-column) which works perfectly. I then created a share% based off of what dealer invoiced divided by total invoiced, this also works perfectly, and finally I created a column which will label a dealer ABC-D based off of their inclusion in the 0-50%, 51-80%,81-99% or 100% tier.

The issue which occurs is that when I apply a filter on a column to get a better idea how a region is doing the filter isn't only showing  the region, it somehow is including additional row(s)--the latter depending on how many times I've tested out the filter...

As you can see in the screen show, I've filtered the column Geo Region to show only CHN, however, if I select the drop-down on the column Type I have the option to keep on selecting value B or C even though I see an additional line (row 326) showing me a D type for region NAM... I've highlighted in Blue the row numbers 43 and 56 which are also in blue (as is the norm in excel on filtered rows), yet, why am I also seeing row 326?

There isn't any blank data in the entire set, the only thing I noticed is that if I select the entire data set, copy paste special value (thus removing the formulas) the filter starts working correctly...problem is, I need those formulas in the data set.

Does anyone know a fix, or am I the only person who's ever had such an issue show up?

Thanks in advance for any help,

Willem

Answer 1

Your cell may not be included in the filtered range. Try removing the filters, then selecting the entire range again - including cells past row  326 - and re-applying the filters.

OR

Convert your range to an Excel Table - that should make the filter range autoresize if you add data at the bottom of the table.

Answer 2

Hi Bernie,

Thanks for the response... and it worked, however, I can't understand the logic behind it.

I added blank lines to the filter-range (data is already converted to a Table) and it now is capturing all the data which I need... however, as I am using a Slicer, I know have the joy of watching the (blank) option present itself.

Minor cosmetic issue figuring that the main issue has been resolved, however, odd that Microsoft hasn't done something about this...

Thanks for the help!

Willem