When I started my career in working with excel dashboards, I always used to face most common challenge in “Data Validation” technique where I want a smart data validation to avoid all blank cells and keep adding and deleting the values from drop down dynamically.

Here we want to talk on an amazing trick to create data validation and avoid blank cells without changing the range. So let’s start with these simple steps:

Step 1: Identify the range which you want to show into data validation list. Here I selected a range from A2:A15. Refer the below screenshot:

Step 2: I will be creating a dynamic drop down list using above Range (A2:15) and applying this validation to Cell “C4” as below:

OFFSET FUNCTION

Step 3: Here we will be using “OFFSET” function to create dynamic drop down list:

“=OFFSET(reference, rows, cols, [height], [width])”

 Note: Click here “OFFSET” to learn this function in detail

Parameters:

Reference: This is a range which we will be using and adjusting. So here it is “$A$2:$A$15”

Rows: As our base row starts from “2” and our data also starts from $A$2. Therefore, we should not be using this parameter

 Cols: As we will be adjusting the same column and no need to shift data anywhere on the sheet Therefore, we should not be using this parameter

 Height: We are going to adjust the height of our data in current example which starts from $A$2 and ends at $A$15. So it depends on the data filled. Currently we have data till 8^th Row. We will calculate the height until filled cell by using COUNTA function

Note: Click here “COUNTA” to learn this function in detail

Width: We do not have width of the data, so can leave this as well

So below is our final formula: “=OFFSET($A$2:$A$15,,,COUNTA($A$2:$A$15),)”

Step 4:  Select “C4” cell and create “Data Validation” list by pasting the formula under “Source” as below:

Click “OK”

Note: You can create a named range by using the same formula and then may use that named range instead of pasting the formula under data validation list

Step 5: Here is your Validation list 😊

Note: Click here “Data Validation” to learn this feature in detail

And once you add a new value in “A9” cell, it will automatically added to drop down list as below:

Hope you enjoyed this article. See you soon again with a new article 😊