Dynamic Data Validation
The data validation tool in Excel is a wonderful feature that can help you quickly select an item from a list. However, if the list is growing over time, then you will want the data validation to be dynamic and expand to accommodate the growing list. One way to do this very efficiently in Excel is to use the Table Tool.
The Situation
In the table below on the left, we have a data table with invoice numbers for sales into various regions (download the file here). Information about each region can be found in the lookup table on the right which shows the city and country. As the business grows, more invoices and more regions will be added.
Creating the Tables
One of the simplest and most powerful automation tools in Excel is the Table feature. We will use it here in order to automate the contents of our dropdown list. First, we need to create Tables using Excel’s Table feature. To start, select the data table on the left including the headers and click Insert – Table (CTRL T). The dialogue box below should appear indicating the Table has headers. Click OK.
Your table should now look like the one below.
You could now go into the Formulas – Name Manager (ALT MN or CTRL F3) to see the name of the Table shown below as Table1. By clicking Edit, you could rename this Table as InvoiceData.
A similar procedure could be followed for the lookup table on the right which could be named RegionLookup. This will result in the two tables shown below.
Adding Dynamic Data Validation
Next, we would like to add data validation to the region field in the InvoiceData table. The trick here is to make sure we add data validation to every region listed in the InvoiceData table. In the InvoiceData table, select from the first region (cell C3) down to the last one (cell C10). Now click Data – Data Validation (ALT A V V) which will generate the dialogue box below.
In the Settings tab, select List and then select the Source. For the Source, select all of the regions from the RegionLookup table (cells F3 – F8). As shown below, the Source looks static since it appears as though it will only include the items from cells F3 to F8. Click OK.
Note: the data validation could also be set up by using structured reference function format as =RegionLookup[Region]. However, this reference would need to be pasted into a defined name which the data validation could refer to. This technique is more complicated than the approach we are suggesting here.
Testing the Solution
Now it is time to test your data validation. Enter a new record in the RegionLookup table. First, add a new region (106) to cell F9 in the RegionLookup table. Once you hit ENTER, you will see the RegionLookup Table expand to include this region. Now add Dublin in cell G9 and Ireland in cell H9.
Next, we will enter new records to the InvoiceData table. Add the next invoice number of 11008 into cell B11. The table will expand downwards. Now, when you move into the region (cell C11) for this invoice, the cell will already have data validation. Opening the data validation (ALT DOWN ARROW) will show that the selection has expanded to include region 106 as shown below. The invoice amount of $31,058 can now be entered into cell D11. The tables shown now look like the ones shown below.
Checking the data validation settings in cell C11 should show the dialog box below. Even though the Source looks static, it is behaving in a dynamic way since it is referencing a Table. As shown below, the Source has expanded down to include cell F9.
Summary
This is a great way to use Tables to ensure that data validated cells will dynamically pick up new items when they are added to a list. Even though the source for data validation may look static, it will behave in a dynamic way since it is referencing a Table. As we add new regions to the RegionLookup table, they will automatically appear in our data validation in the Region column of the InvoiceData table.
As a next step, you may be inclined to use a lookup function in the InvoiceData table to add a column for City and Country. This would allow you to have one table with all the information together. However, one of the things we discuss in our Power BI course is that this is not efficient since it leads to repetition of the City and Country information every time an invoice is loaded in for a certain region. Instead, we keep these two tables separate and form a data model by connecting them using the relationship between their common Region keys. Avoiding repetition by creating a data model is one of the principal reasons why Power BI can analyze large data sets very quickly.
FAQs
How do I Data Validation a list from a dynamic table in Excel? ›
Select the column in your data entry table that you wish to add data validation to. Go to Data/Data Validation or Alt + D + L to open the Data Validation window. Select “List” from the “Allow” dropdown menu. In the “Source” box, hit the F3 key and select your defined name from the “Paste Name” box.
How do I create an interactive drop-down list in Excel? ›- Select the cells that you want to contain the lists.
- On the ribbon, click DATA > Data Validation.
- In the dialog, set Allow to List.
- Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.
- Select one or more cells to validate.
- On the Data tab, in the Data Tools group, click Data Validation .
- On the Settings tab, in the Allow box, select List .
- In the Source box, type your list values, separated by commas. ...
- Make sure that the In-cell dropdown check box is selected.
In Excel, you can validate dynamic data using a dynamic drop down list. This is done by selecting the source for the drop down list as the range where new dynamic data will be added later.
How to multiselect a dynamically populated dropdown based on another dropdown value selection? ›It's simple: just add an onClick to call a function every time the options are checked (or unchecked), and then, loop the checkboxes looking for the cheked options, concatenating their texts and showing it at the main label of the select. Same thing is possible if you want to show the number of items selected.
Can you have a drop down list in Excel with multiple selections? ›Select Multiple Items From Drop Down List. Instead of limiting the drop down list to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
What is a dynamic dependent drop-down list? ›Dependent Drop Down - Dynamic Arrays. Limit choices in one Excel drop down list, based on the value in another cell. This example uses dynamic arrays and spilling to create the lists used in the drop downs. Select a region name, and then select an employee from that region.
How do I create a dynamic array in Excel? ›A dynamic array formula is entered in one cell and completed with a regular Enter keystroke. To complete an old-fashioned array formula, you need to press Ctrl + Shift + Enter. New array formulas spill to many cells automatically. CSE formulas must be copied to a range of cells to return multiple results.
How do I set up Data Validation multiple selections? ›Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the settings tab, select 'List' as Validation Criteria. In Source field, select the cells which have the items that you want in the drop down. Click OK.
How to do conditional grouping in Excel? ›To group data by conditional formatting in Excel, select the data you want to group and click the "Group" button on the "Data" tab. In the "Group" dialog box, select "Conditional Formatting" and click "OK". You can also group data by conditional formatting using the keyboard shortcut Alt + Shift + C.
How do you create a dynamic validation? ›
- We can use OFFSET function to make dynamic data validation list.
- Press ALT + D + L.
- From Settings tab; click on Allow.
- In Source box, enter the following formula.
- =OFFSET($A$2,,,COUNTA($A:$A)-1)
Automated data validation is faster and more accurate than manual methods. Automated data validation tools catch more existing errors and eliminate human error. Automating data validation standardizes and streamlines the data pipeline, ensures high-quality data while reducing costs.
How do I create a dynamic drop down list in Excel without blanks? ›For all your empty cells, enter just a single empty character. Select a cell, hit the space bar, get out of the cell, and copy that value down. Now, your empty cells technically aren't empty because they contain a space. And by doing so, the drop down list now starts from the top again.
How do I set the selected value of a dropdown dynamically? ›- Declare HTML Select Wrapper Element.
- Create JavaScript Object Model.
- Add Options Dynamically To Select Element.
- Attach Change Event To Select Drop Down List.
- var subjects = document. getElementsByTagName("select"). getElementById('selectSubject'). ...
- var e = document. getElementById("selectSubject"); var selected_value = e. ...
- var e = document. getElementById("selectSubject"); var selected_value = e.
- // Instance created on multiselect component.
- var multiselectObj = document.getElementById('ddlFilterEmployee').ej2_instances[0];
- // Get the selected value through Value property.
With the multiple attribute, you can allow the user to select multiple items from the dropdown. This makes 4 items visible by default.
How do I create multiple selections in a drop down list in Word? ›Under Insert controls, click Multiple-Selection List Box. If you cleared the Automatically create data source check box in step 3, select a repeating field in the Multiple-Selection List Box Binding dialog box to which you want to bind the multiple-selection list box.
Is there a limit to Data Validation list in Excel? ›There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.
How to use Data Validation with if function? ›Using the IF function in the data validation formula we will make the conditional list in the right-side table. Steps: ➤ Select the range E3:E12 and then go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option. Then, the Data Validation dialog box will appear.
How do I create a custom validation rule in Excel? ›
- Select one or more cells to validate.
- Open the Data Validation dialog box. ...
- On the Settings tab of the Data Validation dialog window, select Custom in the Allow box, and enter your data validation formula in the Formula box.
- Click OK.
The best method to create a dynamic drop-down list is using an Excel table for source data. Here are the simple steps to create a dynamic drop-down list in Excel. First of all, change your normal range into a table. Select your list.
What is dynamic range in Excel? ›Dynamic named range in Excel is the ranges that change as the data in the range changes, and the dashboard or charts or reports associated with them. So that is why it is called dynamic. So we can name the range from the name box, so the name is a dynamic name range.
What does dynamic () do in Excel? ›Dynamic Formulas allow you to insert Excel's formulas into cells even when the formula must reference rows that will be inserted later during the merge process. They can repeat for each inserted row or use only the cell where the field is placed.
What is the difference between dropdown and dynamic dropdown? ›The Static Dropdown is for cases when you, as a form author, want to provide, ahead of time, in Form Builder, the list of all the different choices users will be able to choose from. This is in contrast with the Dynamic Dropdown where the list of choices will be loaded at runtime from a service you provide.
How do I create a dynamic list in Excel based on criteria? ›- Select a cell where you want to create the drop down list (cell C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
A conditional dropdown is a list whose items depend on a selection in another field. These items change depending on what a user selects in another list. For example, a conditional dropdown for employees could list only certain cities because in another field, a particular country was selected.
Can we make array dynamically? ›A dynamic array is an array with a big improvement: automatic resizing. One limitation of arrays is that they're fixed size, meaning you need to specify the number of elements your array will hold ahead of time. A dynamic array expands as you add more elements. So you don't need to determine the size ahead of time.
Which formula would provide a dynamic array in Excel? ›For example, =SORT(D2:D11,1,-1), which sorts an array in descending order, would return a corresponding array that's 10 rows tall. But you only need to enter the formula in the top left cell, or F2 in this case, and it will automatically spill down to cell F11.
What are the four known data validation strategies? ›The following are the common Data Validation Types:
Range Check. Format Check. Consistency Check. Uniqueness Check.
What are the 4 ways to validate a data from database? ›
- Data Type Check. A data type check confirms that the data entered has the correct data type. ...
- Code Check. A code check ensures that a field is selected from a valid list of values or follows certain formatting rules. ...
- Range Check. ...
- Format Check. ...
- Consistency Check. ...
- Uniqueness Check.
- A) Prospective validation (or premarket validation)
- B) Retrospective validation.
- C) Concurrent validation.
- D) Revalidation.
- Name : ProductList.
- BorderColor : Menu bar.
- ColumnCount : 4.
- ColumnWidths : 100 pt;80 pt;80 pt;100 pt.
- DropButtonStyle : 1 or 2.
- LinkedCell : ProductName.
- ListFillRange : ProductTable.
- ListWidth : 390 pt.
- Select the cell to which the validation rule applies and press Ctrl + C to copy it.
- Select other cells you want to validate. ...
- Right-click the selection, click Paste Special, and then select the Validation option. ...
- Click OK.
Re: Excel multiple data validation
Here's how you can do it: Select the cells you want to apply data validation to. Go to the Data tab on the ribbon and click on Data Validation. In the Data Validation dialog box, select Custom from the Allow drop-down list.
Select Home > Group by. In the Group by dialog box, select Advanced to select more than one column to group by. To add another column, select Add Grouping.
How do you do conditional formatting with 3 conditions? ›Conditional Formatting With Multiple Conditions
To highlight cells according to multiple conditions being met, you can use the IF and AND Functions within a conditional formatting rule. Select the range you want to apply formatting to. In the Ribbon, select Home > Conditional Formatting > New Rule.
The Group is an Excel tool that groups two or more rows or columns. With this function, the user has the option to minimize and maximize the grouped data. The rows or columns of the group collapse on minimizing and expand on maximizing. The group option is available under the Outline section of the Data tab.
How do I create a Data Validation list from a table? ›Select the cell in the worksheet where you want the drop-down list. Go to the Data tab on the Ribbon, then click Data Validation . On the Settings tab, in the Allow box, click List . If it's OK for people to leave the cell empty, check the Ignore blank box.
What is dynamic Data Validation in Excel? ›Dynamic Data Validation. The data validation tool in Excel is a wonderful feature that can help you quickly select an item from a list. However, if the list is growing over time, then you will want the data validation to be dynamic and expand to accommodate the growing list.
How do you dynamically extract a list of unique values from a column range in Excel? ›
- Go to Data tab in the menu.
- In Sort and Filter box, Click Advanced button.
- Choose "Copy to another location"
- In "List range :" box, select a range from which unique values need to be extracted (including header)
- In "Copy to :" box, select a range in which final output to be put.
Time - The user should enter a time. Text Length - It validates input based on the length of the data. Custom - It validates the user input using a custom formula.
How do you set a validation rule for an entire table? ›Open the table for which you want to validate records. On the Fields tab, in the Field Validation group, click Validation, and then click Record Validation Rule. Use the Expression Builder to create the rule.
Which Data Validation type is used to create a list? ›Use a Delimited List. Notes: This method of Data Validation is case sensitive -- if a user types YES, an error alert will be displayed. Space characters can be typed before or after the valid items, and no error message is displayed, e.g. " Yes " would be allowed.
How do I automate Data Validation in Excel? ›- The Data Validation dialog box will pop up.
- From the dialog box, open the Settings menu. Click on the List option given in the Allow drop-down menu.
- Enter the name range value '=Fruits'.
- Press OK.
Dynamically Add Validators
We need to listen to optionB value changes and based on that we add or remove the validators we require. We also call the control's updateValueAndValidity() method, as we need to recalculate the value and validation status of the control.
- Select the cell where you want the first (main) drop down list.
- Go to Data –> Data Validation. ...
- In the data validation dialog box, within the settings tab, select List.
- In Source field, specify the range that contains the items that are to be shown in the first drop down list.
- Go to site > Merchant Tools > Search > Sorting Rules.
- On the Sorting Rules page, click the Dynamic Attributes icon in the section header. ...
- On the Dynamic Attributes window, click New to add a new dynamic attribute or select an attribute from the top section to edit it.
Here is a way to make it dynamic (and it's nothing but a minor tweak in the formula): Select a cell where you want to create the drop down list (cell C2 in this example). Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
What is the unique dynamic range in Excel? ›The UNIQUE function in Excel returns a list of unique values from a range or array. It works with any data type: text, numbers, dates, times, etc. The function is categorized under Dynamic Arrays functions. The result is a dynamic array that automatically spills into the neighboring cells vertically or horizontally.