FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
February 18, 2016
 

Greetings from The Excel Addict
Hi fellow Excel Addict,

T
hanks for joining me today for another 'Excel in Minutes' tip that I hope you will be able to use. If you missed Tuseday's newsletter that showed how to 'Change the Starting Page Number for Your Footer', you can read it here.

Learn how to create highly professional, interactive dashboard reports in Microsoft Excel

I am often asked about how to remove duplicates from a list but maybe you might want to consider preventing duplicates from being entered in the first place. In today's tip, I will show you how you can set up a range of cells to prevent duplicates from being entered. I hope you'll find this tip helpful.

Please feel free to share it with anyone you think could use a little Excel help.


Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)


Free Excel Dashboard Webinar


If you missed my last newsletter, you can click here to view it online.

 

 
TheExcelAddict.com Quote of the Day

"If you have a dream, don’t just sit there.
Gather the courage to believe that you can succeed
and leave no stone unturned to make it a reality."

-- Dr. Roopleen
--
 
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Prevent Duplicate Values in a Column

If you've ever needed a way to prevent users from entering duplicate values in a specific range of cells, you can do this using Excel's Data Validation feature.

DOWNLOAD PRACTICE FILE HERE

Let's use an example where we want to prevent users from entering duplicate values in cells B5:B50.

1) Select cells B5:B50. In most cases, with duplicate entries, you will be dealing with a single column, however, this can be used on any range of cells;

2) From the Data tab click Data Validation;

3) Click the Settings tab;

4) Click the drop down arrow under Allow and select Custom. A new Formula box appears;

5) In the Formula box, enter this formula...

=COUNTIF($B$5:$B$50,B5)=1

Custom_Data_Validation_Formula_To_Prevent_Duplicates_On_Entry_in_Microsoft_Excel_2007_2010_2013_2016_365
Explanation of the formula: The COUNTIF part of this formula looks in the range B5 to B50 and counts the number of cells that are equal to the value in cell B5. The =1 part of the formula says that any value in this range may occur only once. Therefore 1=1 equals TRUE and the entry is allowed. When you try to enter a duplicate value, the result of the COUNTIF part of the formula is 2. The 2=1 equals FALSE and the error is detected.

Note that the formula uses absolute references for the range you want to apply the Data Validation criteria to
($B$5:$B$50) and relative referencing for the input cell you want to evaluate (B5). This is critical for this to work.

Absolute Referencing uses $ symbols to lock the column/row references so they do not adjust when a formula is moved or copied. Relative Referencing doesn't use $ symbols, therefore when a formula is move or copied, the references adjust based on the relative position of the rows and columns. So, in this example, when this data validation rule is applied, the data validation formula for cell B6 will be
=COUNTIF($B$5:$B$50,B6)=1 . Notice that only the 'Relative' reference to cell B5 adjusted to B6 because it is one row down, however the range B5:B50 did not adjust in the formula because it used Absolute Referencing.

6) On the Input Message tab you can create a message here that will pop up whenever the user selects any cell within the validation range (i.e. B5:B50). An input message is great for situations where you have a data validation rule applied to an single cell but, for a range of cells, that message would be popping up every time you selected a cell. Annoying! So, in this case, you can leave the input message blank;

Data Validation Input Message_in_Microsoft_Excel_2007_2010_2013_2016_365
7) On the Error Alert tab you can create a message that will appear if an entry typed into a cell breaks the data validation rule. You can enter a Title if you want but the Title box is limited to only 32 characters. So, if you want to use a longer message, you can leave the Title blank and enter your message in the 'Error message' box (i.e. 'Duplicate values are not permitted in this column !!');

Data Validation Error Alert Message_in_Microsoft_Excel_2007_2010_2013_2016_365

8) Click OK and try entering a duplicate value to test it.

Data Validation Error Message_in_Microsoft_Excel_2007_2010_2013_2016_365
Caution: Excel's Data Validation feature is not foolproof. This feature works only on direct user input. Duplicate values resulting from (a) data being pasted in to the range, (b) Find & Replace operations, (c) the result of calculations or (d) changes made by macros (VBA) code will override this feature.
 

Free Excel Dashboard Webinar
Thanks for supporting this newsletter and website


FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630