Excel 2013 - Keep Filter Drop Down Lists on the Screen 2

[jmarkus]

I like to use Auto-filter in Excel 2013 to help filter the contents of a column so that I can get a quick look at all the unique entries.

For example, I might have the following colours in my cells below the filter:
red
blue
green
yellow
blue
blue
green
red
orange
yellow

If I click on the drop down for the auto filter I can see that I only have the colours:
blue
green
orange
red
yellow

If I typed in "yellowed" by mistake in one cell, that will jump out as the drop down list from the auto filter will show:
blue
green
orange
red
yellow
yellowed

..and I can easily catch the mistake. However, when I have the same concept applied to 500 cells, where 25 or 50 of them (or more) are supposed to have unique values, it is harder to review the drop down. Sometimes I need to drag the drop down borders wider to be able to review the whole list. As soon as I go to click on another window (e.g. an email pops up and I go to look at it) the expanded drop down goes away and I need to go back and click the drop down and expand the list and figure out where I left off etc, etc.

I was hoping I could just do a screen capture of the scrolling list, but the same problem prevents me from doing that - as soon as I click on my screen capture program the drop down list hides again.

Is there a way I can either
1.keep the drop down list on the screen until I explicitly dismiss it,
OR
2.extract the unique items into another worksheet to review?

Thanks,
Jeff

 

[SkipVought]

Hi,

The short answer:
1) Copy the list to a safe place
2) Data > Data Tools > Remove Duplicates
3) Analyze the list

But to avoid such misspelling of text, use a Data > Validation > List of accepted values in that column. To make that feature seamless as you add/delete rows to your table, consider using the Structured Table feature: Insert > Tables > Table. The Data > Validation for the cell will propagate to the next row automatically.

Alternatively, a unique list can be generated "automatically" via MS Query via Data > Get External Data > From Other Sources > From Microsoft Query > Excel Files* ... and drill down to your workbook. Write a query to: SELECT DISTINCT [YourColumnHeadingName] is the column with color names, which should have a HEADING. Once the QueryTable has been places on a sheet, it only needs to be refreshed. faq68-5829

I might have the following colours in my cells

Really, you do not have "colours". Rather you have various colour TEXT values . In Excel, you can actually filter by colours and you can filter by text.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Since excel 2013 you can use slicers with tables:

https://blogs.office.com/2012/10/24/beyond-column-filters-slicers-on-tables/

combo

 

[jmarkus]

I didn't know about the remove duplicates tool! That's great. I guess jumping from Excel 2003 to 2013, I missed learning about the new functions (or was it there in 2003 and I didn't realize it?).

I'm not using a pivot table, I'm just autofiltering a spreadsheet that was created by someone else in order to review it, so slicers and validation doesn't work for me.

Thanks for all the advice!
Jeff

 

[SkipVought]

So when you say that you want to review the DropDown, do you mean that you need to evaluate the contents of the DropDown list just one time?

Interesting that you have "graduated" from 2003 to 2013. There are so many new features (not functions) that can make your life easier. I remember the trauma I experienced going from 2003 to 2007, where many of these new features appeared. But when I realized the many grand new vista on the Excel horizon, that could enhance my effectiveness in my job, I dug in and endured the discomfort of working through the learning process. I'm still learning, and thankful for combo's contribution above that I've never explored.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Slicers work with tables (i.e. structured tables) too. If you convert table to structured table (selact any cell inside table and Insert>Table), you can remove autofilter and add required slicers instead (Design>Insert slicer).

combo

 

[jmarkus]

Skip,

Yes, I'm just reviewing someone elses work and I use the autofiltering to make it easier. It's essentially a "one time thing" although I need to go back to it multiple times to complete my review (thus the original need).

Combo,

I guess I need to learn about structured tables...I don't quite get it just yet...

Thanks,
Jeff

 

[SkipVought]

Having Excel and not bothering to get acquainted with and use features like Structured Tables is like driving a race car using only 3 gears.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]