Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Filter not capturing all content

Copper Contributor

What would keep data from automatically being added to the filter? 

 

Example:  A:1 "State" 

                A:2    NY

                A:3    CA

                A:4    TX

                A:5  

                A:6    NY

If I have the filter on row 1, I should get a drop down that shows me NY, CA, TX and (Blank) as options to select a checkbox for, so far, so good. 

Now if I type into A:5 "IL", I should now see IL added to my list (and no longer see blank). 

 

But what if I type in IL and it doesn't show up in the list within the filter? 

 

Do I have some kind of global setting on the sheet or on filters that like 'locked' them in at a set point?

Why wouldn't new content in a cell get automatically added to filter? 

 

3 Replies

@JoeLongMusic 

 

It's a bit difficult to diagnose with any certainty with your brief description. For example, we don't know the layout of the screen, only that when you click on the little filter carat (an assumption) you see the choices of NY, CA, TX, but those could come from the top three rows alone. You're assuming A6 has been included, but it needn't be. Are the top three rows designated as a table, with the rows beneath not included (all this perhaps inadvertent). If you highlight all the rows, including A6 and beyond, what happens?

 

Perhaps you could fill in some of the gaps in your description.

best response confirmed by Hans Vogelaar (MVP)
Solution

It could be a blank row within the data set. If you don't specify the range when applying the filter Excel will presume the 'current region' (If you're active within the data and press ctrl+a you can see what's being considered part of the current region. These rows will be considered by the filter).

If there are blanks in the data set, you can remove the filter, make a point to select all your data (header included) and then apply the filter.  Sorting works on the same principle.

Thank you

 

This is in fact what had happened there was a blank row that had somehow snuck in (the joys of shared data) without me seeing it and it was causing the issues with my sort. 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

It could be a blank row within the data set. If you don't specify the range when applying the filter Excel will presume the 'current region' (If you're active within the data and press ctrl+a you can see what's being considered part of the current region. These rows will be considered by the filter).

If there are blanks in the data set, you can remove the filter, make a point to select all your data (header included) and then apply the filter.  Sorting works on the same principle.

View solution in original post