Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to highlight/conditional formatting unique/duplicate values in Excel?
In Excel, conditional formatting is a feature that allows users to apply formatting to cells based on specific conditions or criteria. By using conditional formatting, the user can color, highlight, or format cells dynamically, that depends upon the values, formulas, or rules the user defines.
Unique values are the ones that do not have any exact matching value in the Excel sheet. While the duplicate value is the one that has the same values available in the list. This article illustrates two common examples of the same. The first example allows the user to determine the duplicate value. In this example, all the duplicate values will be highlighted properly. While the second example allows the user to determine the unique values available in the specified data range.
Benefits of formatting data include
Validation of data to make sure that the formatting data includes the integrity and validated data only. It ensures that the data should be free from all inconsistency.
Data analysis includes generating useful insights from the available data.
It also helps in cleaning the data, by highlighting all the available redundant data values. Using conditional formatting allows users to set values properly according to their requirements.
Consolidating data by merging it with different sources, identifying the overlapping records, and making an informed decision about data.
Example 1: To highlight the duplicate values by using the conditional formatting options from the provided data range.
Step 1
In this article, users will understand the process of evaluating duplicate values. This excels sheet stores the name of a few products along with the quantities. A snapshot of the same is provided below:
Step 2
Before, moving further select the data where the user wants to find the duplicate values. Go to the “Home” tab, click on the “Conditional Formatting” option, and then select the “Highlight Cells Rules” option. Further, select the option “Duplicate Values”. This step allows the user to determine the duplicate values. This simply means that the duplicate quantity values will be displayed as a result value in the final step. A snapshot for reference is provided below:
Step 2
The other dialog box “Duplicate Values” will open. This dialog box allows the user to select the data as “Duplicate”, and in the “values with” label, select any color user wants to apply. But, here, will be using “Light Red Fill with Dark Red Text” values, and finally click on the “OK” button. snapshot for the same is provided below:
Step 3
The final obtained duplicate values are displayed below:
Example 2: To highlight the unique values by using the conditional formatting options from the provided data range.
Step 1
In this example, users will understand the process of evaluating unique values. This excels sheet stores the name of a few products along with the quantities. A snapshot of the same is provided below:
Step 2
Before moving further select the data where the user wants to find the unique values. In this example, will learn to use conditional formatting. Go to the “Home” tab, click on the “Conditional Formatting” option, and then select the “Highlight Cells Rules” option. Further, select the option “Duplicate Values”. A snapshot for reference is depicted below:
Step 3
The above step will open a “Duplicate Values” dialog box. Click on the first drop−down arrow, and then choose the option “Unique”, and in the second drop−down menu, choose any available data row. In this example, will be choosing the option “Green Fill with Dark green Text”. This option basically highlights the unique values text in the green color. A snapshot for the same is provided below:
Step 4
The final highlighted unique values are provided below:
Conclusion
This article briefs two examples, the first example allows the user to identify duplicate values from the available list of data, and the second example allows the user to identify the unique values from the available list of passed data values.
164 Views
