Data Validation drop-down boxes are missing in a different version on Excel

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

MS Excel has some known inconsistency or incompatibility when it comes to its data validation list feature. The exact problem is that, if your file contains data validation lists created in Excel 2007, these data validation lists are gone when you open it in Excel 2010.

Data validation are missing when the file is opened in a different version of Excel

If this "data validation" thing in Excel is new to you or something that you have not tried so far, please read this article for the complete and detailed procedures in creating and using data validation lists:

Let us take as an example the sample file I shared in my previous article. It can be downloaded from the link below:

Attachment Size
Sample-FeedbackForm-with-Data-Validation.xlsx 11.88 KB

The file is created in MS Excel 2010 and so when I open the file, the combo boxes for the data validation list are shown like this screen shot below:

Combo box generated by the data validation list shown

Now, the data validation list settings that produce the combo box in the screen shot above is this:

Sample data validation settings showing the source cell range

Unfortunately, this file when opened in other versions of Excel will lose all the data validation lists settings including the combo boxes when you select the cells with data validation like cell B7.


How to Make Data Validation List That is Compatible to All Excel Versions

In order to make your data validation lists compatible across all versions of MS Excel, there is only one thing you need to do in creating the valication list. That is to "name" the cell ranges that you will be using as the "source" cell range like the one shown in the screen shot. That means that instead of using =DATA!$D$2:$D$6, use "named cell ranges" instead like =education.

How to Name Cell Ranges

To name cell ranges, here are the steps:

  1. Simply highlight the entire range, excluding the header or the title of the column.
  2. Then in the name box that is located right above column A, click on it then enter the name of the selected cell range.
  3. Then press Enter. The selected range shall be named this time.
    Sample cell range being named
  4. To test if you named your cell ranges correctly, simply select the range and the name of the range shall be displayed in the Name Box.

Now, in order to create a cross-version data validation list, you need to enter the "name" of the cell range for the SOURCE instead of the cell range notation itself. This simply means that you need to redo the setting for your data validation list if you are working on a file with an incompatible data validation lists. Do not worry, when you select the range for the SOURCE, the assigned names will be provided automatically.

Sample source data showing the range name automatically

After completing the entire process for all your data validation lists, the resulting Excel file will be compatible with other versions of Excel.

You can download the sample file in the link below.


MS Office Software: