Duplicate data can occur when working on a spreadsheet in Microsoft Excel, and it is easy to run into this problem. It might be anything from a product number to an email address to a phone number to the name of a customer. It is possible to identify duplicates in Microsoft Excel without having to manually scan and search the page using a built-in technique known as conditional formatting. Learn how to use conditional formatting to highlight duplicates in Microsoft Excel by reading the article below!
Read Also: How To Subtract In Excel With Formula
How to Highlight Duplicates with Conditional Formatting in Excel
Conditions formatting in Excel may be a bit overwhelming if you’ve never done it before. Here’s how to get started. You may, however, find yourself using it more and more after a few instances and realizing the versatility and power it possesses.
It takes only a few clicks to identify duplicate data using the conditional formatting tool, which is demonstrated in this lesson. As a result, it is less difficult than you may expect.
Use Microsoft Excel to open your spreadsheet and complete the procedures below.
1. By moving your mouse through the cells, you may select the ones to which you wish to apply the formatting.
2. Alternatively, you may click on the triangle in upper-left corner of any sheet to view it as a whole (where column A and row 1 meet).
3. Select Conditional Formatting from the Styles area of the ribbon by selecting the Home tab and then the Styles section of the ribbon.
4. Select the first option for Highlight Cell Rules from the drop-down box by moving your cursor to the top of the box.
5. Select Duplicate Values from the drop-down menu that appears.
6. When the little pop-up window appears, double-check that the first drop-down menu option is set to Duplicate.
7. Choose the formatting you wish to use for the values from the Values With drop-down box. You may choose from a variety of colours to fill the cells, fonts to use as a border, and even a bespoke format if that is what you like.
8. To proceed, click OK.
After that, you’ll see all of the duplicate data highlighted in the colour you selected before.
If you’re working with Excel on a Mac, the procedure is the same as described above. The pop-up box used to select the formatting, on the other hand, is slightly different.
It is important to leave the Style, Format Only, and Duplicate values boxes in their current positions and in the manner indicated in the picture below. Then, at the bottom of the page, utilize the Format with drop-down box to select the fill, text, and border colours, or create a Custom Format to suit your needs.
Create a Custom Format
You have a choice between six different formatting choices. However, if you don’t like any of these options, you may create a bespoke one of your own. Custom Format can be selected from the Values With (Windows) or Format With (Mac) drop-down menus.
When the Format Cells box appears, click on the Font, Border, and Fill tabs at the top of the window to create a custom format for your cells. As an example, you might use a strikethrough for text, a purple dotted border for cells, or a dark blue fill colour to make a table look more professional.
It is also possible to blend various formats. For example, you might use an italic font in a yellow tone with a blue patterned fill and a bold black border to create a professional-looking design.
You must utilize formatting that is comfortable for you in order to find duplicates quickly and efficiently. When you’re through generating the custom format, click OK, and you’ll notice that the modifications are applied to your sheet almost immediately.
How to Empty Trash on Your Mac
How to Install and Play The 100 Player Mod in Among Us
How to Find Your LinkedIn URL or Change It
The Switch Has Now Outsold The 3DS In Japan
AMD’s RDNA3 7900XT Flagship to Offer 384-bit Memory Bus, 24GB of VRAM
Is Neverwinter Online Crossplay? 2022 All Platforms
Is The Forest Cross Platform?(PS4 & Xbox) Everything You Need to Know
How to Invest in GTE Technology
Learn How to Get Free Robux & Roblox Codes in 2022
Best Skyrim Name Generator – Find All Kinds of Skyrim Names
Hades – Battle out of Hell Early Access Available at Epic Games Store
The Next Final Fantasy XIV: Shadowbringers, Coming Soon in Summer 2019
ELLI is Coming to Nintendo Switch In January, 2019
US Government Sector Employees Banned from using any of Huawei Devices
Red Dead Redemption 2 PS4 Stunning Redefine Support
Technology1 year ago
How We Can Get VPS Hosting & How to Use It
Financial2 years ago
Marvelous Benefits of Hiring Financial Advisors for Your Business
Guide1 month ago
How to Get Free TikTok Followers and Fans in 2022
Gaming3 years ago
GENKI: The Best Bluetooth Audio Solution For The Nintendo Switch