New Automatic Duplicate Values Removal in List Data Validation in Excel

Welcome to Excel Jet Consult blog post. In this Excel tutorial, we will see how to use the new automatic duplicate values removal in List Data Validation in Excel. Note, new feature is currently available to Beta Channel users of M365 as at the time of writing this blog post. Let’s get started

Tutorial Video

In the previous blog post on, “Creating Dynamic Cross Tab Report using Single Cell Formula”, we had to use an helper row to return the unique sorted list of order_priority that we used to create the dropdown list. The screenshots below shows that we combined SORT and UNIQUE functions inside the new TOROW function to return the unique sorted list of the order_priority column. Then, we created the dropdown list data validation in cell K1 using the first value in cell L1 and the # spill operator

With the arrival of the automatic duplicate values removal, there is no need for helper column or rows to specifically create a dropdown list from column that contains duplicate records. To use the new features, we are going to clear all the content in cell K1 to P1.

  • By selecting cell K1, in the Data Tools group of the Data tab, click on Data Validation

  • In the Data Validation dialogue box, select List from the Allow dropdown
  • Click in the Source box and select the values in the Order Priority column

  • Click OK
  • Select any of the Order Priority value such as High.

From the screenshot below, the output result is back and we never need to use the helper row again.

See you in the next video

Posted in Blog.

Leave a Reply

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