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:
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 8th 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
Video: How to Hide Worksheet in Excel? Hide Sheet in Excel When I was creating an excel dashboard, there were multiple sheets which I used for calculation purpose and never wanted anybody to make any…
Lock Cells to avoid editing, Hide Formulas Sometimes you create amazing projects, dashboards however people can make mistakes and edit the calculations, formats what you have made. Still you can protect the sheets, workbooks and…
How to Get RGB Codes of a Color? Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this through…
How to Insert Symbol in Excel? I came across many queries regarding inserting special symbols in Excel. Here we are guiding how you may do this quickly in excel. Follow these steps and you may…
How to Compare TWO Columns in Excel? Comparing Columns in Microsoft excel is much often feature that is used while managing database. We have many ways to compare the columns in excel and can be…
This video will help you to understand how you may use conditional formatting to highlight row based on conditions. Subscribe us for more updates