There are various ways to do this:
a) Expand the data validation reference to include the new row
This means that if the original range is MASTER$A$4:$A$6, this should be manually changed to MASTER$A$4:$A$7 to include the newly added row.
b) Insert a row within the existing specified range
Since the range originally specified was MASTER$A$4:$A$6, insert a row between 4 and 6.
c) Drag the new row into the range using the SHIFT key
- Add a new entry at the bottom of the range. If the range is MASTER$A$4:$A$7, add the entry in cell A8.
Click on the new row and hold the SHIFT key as you drag cell A8 into the original range.
d) Using an expandable cell reference
- Convert the range into an official Excel table by clicking anywhere inside the data set and press CTRL + T.
Change formatting based on preference under Design > Table Styles.
Rename the table under Design > Table Name. By default, it is called Table1. In this example, it is renamed to TableDiv.
- To refer to this range, the syntax is =Table_name[header_name]. In this example, is it TableDiv[Division]. This allows the entire range to expand when new rows are added.
- Go back to the cell where the data validation is needed and change the source. Since data validation is unable to process table references, there are two workaround for this:
- Use the INDIRECT() function to convert this text into an address.
This now becomes =INDIRECT(“TableDiv[Division]”
2. Use the Formulas > Name Manager to give the table reference a name to be used as a data validation criteria
This method allows you to add a new entry at the bottom of the range without the need to manually expand the data validation through methods 1 or 3.