Locating a Data Validation table

New Contributor

How can I locate a drop-down list in an excel document that someone else created?  I've been given a document that was already set up and I need to make changes.

8 Replies
Under the home tab > editing section > magnifying glass > "Go To Special" > Data validation.

This should highlight the cell that contains data validation for you.

@PReagan   Correct, but I am looking for the source of the list?  It is somewhere within the workbook, but I cannot locate it.

Click on the drop-down list > under Data Tab click Data Validation > Under Settings you should see the Source of the list.

=Table!$A$2:$A$7  This is the area in which the drop-down list is used, not the source of the list.   It is hidden somewhere in the workbook.


I apologize but I must be misunderstanding your question. Would you mind sharing your workbook so that I may be able to understand your question a little better?

**Please omit any sensitive/confidential information

@Ben Schorr   Thank you.  The mystery has been solved.  The author of the document had hidden a tab where the drop-down list was located.  When opening the Data Validation dialog box it referenced "=Table!$A$2:$A$7, but I wasn't able to locate the word "table" in searching each tab.  A co-worker showed me how to unhide the tabs.   




For situations like this (and MUCH more complicated) there is a Tool that comes for Rescue: The INQUIRE Tab of the Ribbon:

This tool will enable you to find a Table, data Validation, a Formula, Errors, not only if the Sheet is Hidden but even if the Sheet is VERY Hidden (VBA Term)

To add the INQUIRE TAB:

  • File >> Options >> ADD Ins
  • Manage (lower left corner) >> COM Add-ins >> Go
  • Check INQUIRE >> Save the File >> Click on WORKBOOK analysis report...


Here is a short tutorial about the Magic of the Inquire Tab


Hope that Helps

Nabil Mourad


Starting from Excel 2013 we have a new feature known as Inquire. With Inquire you can analyze, audit and review your workbooks along with data dependencies a...