How to set excel to prohibit entering duplicate values? How to set excel to prohibit entering duplicate values

Excel prohibits entering duplicate values. How to avoid entering duplicate values ​​in Excel tables? This is always a headache for many users. To solve this problem, this article will provide a step-by-step guide detailing how to set up Excel to prevent duplicate values ​​from being entered. Workaround overview: To learn how to easily resolve this issue, continue reading below. This article will introduce each step step by step to help you effectively prevent duplicate data from appearing in Excel tables.

　How to set excel to prohibit the entry of duplicate values

　Method 1: Use conditional formatting

　1. We can select the data in column A, and then set it in the conditional format to highlight the duplicate values.

<code><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/172369303885830.jpg?x-oss-process=image/resize,p_40" class="lazy" alt="How to set excel to prohibit entering duplicate values? How to set excel to prohibit entering duplicate values"></p>
</code>

　 2. When it is a repeated value, we can set its color to light red fill color, as shown below.

<code><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/172369303928709.jpg?x-oss-process=image/resize,p_40" class="lazy" alt="How to set excel to prohibit entering duplicate values? How to set excel to prohibit entering duplicate values"></p>
</code>

　 3. At this time, when the data we entered on the left is repeated, it will automatically mark the color.

<code><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/172369304035003.jpg?x-oss-process=image/resize,p_40" class="lazy" alt="How to set excel to prohibit entering duplicate values? How to set excel to prohibit entering duplicate values"></p>**方法1：使用条件格式**
</code>

This method can be used to remind you of the occurrence of repeated values. If we want to enter duplicate values, a pop-up window will prompt an error and cannot be entered

Method 2: Use data validity

1. We select the data in column A, and then under the data tab, click Data Validation
2 , then in the pop-up window, select Custom and enter the restriction formula: =COUNTIFS (A:A,A1)《=1
That is, the data in column A is counted, allowing》=1 occurrence

　3. After setting it up, if we enter a repeated value, it will pop up an error window and ask us to enter it again, as shown below:

<code><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/172369304327533.jpg?x-oss-process=image/resize,p_40" class="lazy" alt="How to set excel to prohibit entering duplicate values? How to set excel to prohibit entering duplicate values"></p>
</code>

　　4. If the entered data is not repeated, it will allow us to enter it.

<code><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/172369304459293.jpg?x-oss-process=image/resize,p_40" class="lazy" alt="How to set excel to prohibit entering duplicate values? How to set excel to prohibit entering duplicate values"></p>
                        </code>

The above is the detailed content of How to set excel to prohibit entering duplicate values? How to set excel to prohibit entering duplicate values. For more information, please follow other related articles on the PHP Chinese website!