Data Validation Documenter

If your Excel file has many cells with data validation, it can be difficult to remember what restrictions you’ve put on the worksheet cells.

These tips show you how to find the data validation cells, and create a list of what rules are on the cells.

Select All the Data Validation Cells

If you just want to see where all the data validation cells are, you can use the command on the Excel Ribbon.

1. On the Excel Ribbon, click the Home tab
2. In the Editing Group, click Find & Select
3. Click Data Validation

This command selects all the data validation cells on the active sheet.

Select Specific Data Validation Cells

If you have different data validation rules on the active sheet, you can select only the cells that have the same validation as the active cell.

In the example shown below, cell D6 is active, and it has a data validation drop down list, based on the named range — DaysList.

To select any other cells on the active sheet with the same validation rule:

1. With cell D6 active, click the Excel Ribbon’s Home tab
2. In the Editing Group, click Find & Select
3. Click Go To Special
4. In the Go To Special dialog box, click Data Validation
5. Click Same, then click OK

In the screen shot below, cell D7 was also selected, because it has the same validation rule as cell D6.

However, cell D4 was not selected, because its drop down list is not based on the same named range.

Create a List of Data Validation Cells

The Go To Special commands let you select the data validation cells, but they don’t show you the data validation rules. You would have to check each cell, or group of cells, to see its data validation rules.

If you want the details on each data validation cell for the active sheet, you can use a macro that compiles a list of details.

On the Contextures website, there is a Data Validation Documenter page. On that page, you’ll find sample code that creates a list of data validation details for the active worksheet.

In the sample file, there are two macros –

1. DataValDocumenter and
2. DataValDocumenterSheet

Both macros create a list of the data validation cells on the active worksheet, with the following details:

1. Cell address
2. Data validation type
3. Data validation formula.

The DataValDocument macro creates the list in a text file, named Test.txt, in Excel’s default file location.

To see your default location, click the Excel Ribbon’s File tab, then click Options, and click the Save category. (in Excel 2003, choose Tools | Options, General tab)

In the DataValDocumenterSheet macro, a new worksheet is inserted at the front of the workbook, with a list of the data validation on the active sheet.

Download the Data Validation Documenter

You can download the data validation documenter file from the Contextures website.

To run one of the macros:

1. Click the Ribbon’s View tab, then click Macros (In Excel 2003, choose Tools>Macro>Macros)
2. Select DataValDocumenter or DataValDocumenterSheet
3. Click the Run button

_______________________