Editing Existing Drop Down in Excel Template: Data Validation and Filters Not Working

Question

Hello,

I am using Microsoft Office Home and Business 2019, Version 2501.

I am having trouble both editing existing drop-down lists/filters from a Microsoft Excel template and replicating them elsewhere. I am using the "To-do list with progress tracker" template from the Excel template library (pictured + circled below).

The first issue I am having is editing one of the existing drop-down lists. When I click on C2, the Filter tab automatically highlights. All the articles I see on editing or creating Excel drop down lists mention data validation, but when I open data validation (pictured below as "2"), I don't see a source or list with any of the information already loaded in the template's drop down(s). I don't see anywhere to edit the "High, Low, Normal" list (or any of the other drop downs in the template for that matter) in "Filter" nor "Data Validation".

The second thing I am having trouble figuring out is replicating the % COMPLETE column. I have no idea how to do that and getting the progress bar to move with the selection.

Please advise.

Answer 1

Range B2:I7 is a table. In a table, you can only show/hide the filter buttons for the entire table, not for individual columns.

Answer 2

The dropdown arrows in row 2 are not data validation, but filter drop-downs in the header row of the table. You can turn them on and off by clicking Filter.

When you click one of these filter drop-downs, Excel shows a list of the unique values in that column - in the PRIORITY column, you see the values High, Low, Normal and High, so the list is High, Low, Normal. If you change Low in C5 to High, the filter drop-down would list only High and Normal.

The bars in the % COMPLETE column are conditional formatting of type Data Bars.

Answer 3

Thank you HansV!

Follow-up question: the original List Range under Advanced Filter was $B$2:$I$7. I don't want a drop-down on Task or Notes, but I can't toggle just those columns off without turning the drop downs for the entire table off. I tried to edit the list and criteria ranges to just include columns C-G (pictured below), but nothing changed -- there's still a drop down for Task and Notes. "Unique records only" didn't seem to do anything either.

Please advise.