Excel difference ranges vs tables

Data validation in EXCEL by referencing tables or ranges.


How do we generate those fancy validation lists in excel you ask?

I am happy to announce that I am again actively writing on my page! This time the cause is that I am not happy that I spent a lot of time finding out why validation lists are so cruelsome to some. Ok let us get to the point, shall we?

You want to validate inputs in a column based on a list you have defined. You should use the following approach:

First create the following structure in EXCEL and press “CTRL+F3”:

Name manager in excel to generate a named range

Name manager in excel to generate a named range

Next Name your range:

Select a range for the validation range

Select a range for the validation range

Next close the dialogue:

Generate a growing range in excel

Generate a growing range in excel

Choose the Cells you want to keep validated:

Choose the validation option in excel on a cell range

Choose the validation option in excel on a cell range

Select the following parameters and input your range-name. In this stage, if you created your validation range by using “Format as Table” there is a possibility that it would not work and fail with a message that your formula is wrong!:

Generate validation in excel dialogue

Generate validation in excel dialogue

Enjoy!

excel validation list

Excel validation list

Important! In order to link tables to ranges just create the table first with “Format as Table” and link the specific range inside the table. It should be referenced now with a structural reference e. g. “Table1[mycolumnname]”. You can distinguish them also in the Name:

Excel difference ranges vs tables

Excel difference ranges vs tables

Leave a comment

Your email address will not be published. Required fields are marked *