These tutorials based on readers' questions detail how to combine formulas with Excel data validation and present a Word replacement trick.

I recently answered questions from readers Venkat and Vekaria, who couldn't get the solutions in two of my articles to work for them. In the case of Venkat, I offered a quick solution from the top of my head that didn't work, but from there, the solution easily reached Venkat. Vekaria's solution was in the article, but since he was applying it differently, it was not obvious. Fortunately, both solutions are easy to implement.

I am using Office 365 on a Windows 10 64-bit system, but can use earlier versions of Excel and Word. The Excel data validation formula works in the browser, but the Word replacement code does not. You can work with your own data or download the demo files .xlsx and .docx.

** MORE INFORMATION: Features and prices of the Office 365 consumer **

## More limits for the data validation formula

Venkat read Use Excel data validation to avoid duplicate values in a column and He approached the necessary solution, but it was not a complete success. This article combines a formula and data validation to omit duplicates within a column. It is an all or nothing solution, and Venkat wants to allow duplicate zeros. Fortunately, the formula is easily modified to allow this situation. Before we get to the modified Venkat formula, let's review the basic formula.

** Figure A ** shows a simple data set in a Table object, and we want to make sure that all values in the Membership Number field are unique. Add the data validation control as follows:

- Select all existing data cells in the column in question, which in this case is B3: B6.
- Click the Data tab and choose Data Validation from the Data Validation drop-down menu in the Data Tool Group.
- In the resulting dialog box, choose Customize from the Allow drop-down menu.
- In the Formula control, enter the formula (
**Figure B**)

= COUNTIF (INDIRECT ("Table1 [Membership Number]"), B3) <= 1

making sure to use straight quotes (not curly). If you are working with your own data, be sure to update the name of the Table and the Column. - Click OK.

** Figure A **

** Figure B **

Test the control when trying to enter a duplicate value (** Figure C **). As you can see, Excel rejects the value. At this point, Excel will allow you to enter 0 once, but not twice. The INDIRECT () function accommodates the structured reference for the Table object. (Instead, you could use a named range, but a Table object is dynamic.)

** Figure C **