How to find and remove duplicate values in Excel

Показать описание

Duplicate values are sometimes desirable, but on other occasions, they occur due to data entry errors, imported data, or merged datasets.

Excel has a number of options to find and remove duplicate values. Let's look at three.

𝗛𝗶𝗴𝗵𝗹𝗶𝗴𝗵𝘁 𝗗𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲 𝗩𝗮𝗹𝘂𝗲𝘀

1️⃣ Select your data.
2️⃣ On the Home tab, go to Conditional Formatting ＞ Highlight Cells Rules ＞ Duplicate Values.
3️⃣ Pick from one of the default styles or create a custom one, and choose to highlight duplicate or unique values in each column.
4️⃣ Select OK to confirm the choices.

This method doesn't directly remove anything, but it can serve as a visual aid for finding values you might want to delete manually.

𝘐𝘯 𝘵𝘩𝘦 𝘦𝘹𝘢𝘮𝘱𝘭𝘦, 𝘢𝘭𝘭 𝘣𝘶𝘵 𝘵𝘩𝘳𝘦𝘦 𝘯𝘢𝘮𝘦𝘴 𝘢𝘯𝘥 𝘰𝘯𝘦 𝘥𝘦𝘱𝘢𝘳𝘵𝘮𝘦𝘯𝘵 𝘢𝘳𝘦 𝘩𝘪𝘨𝘩𝘭𝘪𝘨𝘩𝘵𝘦𝘥, 𝘢𝘴 𝘵𝘩𝘦𝘺 𝘢𝘱𝘱𝘦𝘢𝘳 𝘮𝘰𝘳𝘦 𝘵𝘩𝘢𝘯 𝘰𝘯𝘤𝘦 𝘪𝘯 𝘵𝘩𝘦𝘪𝘳 𝘤𝘰𝘭𝘶𝘮𝘯𝘴.

𝗥𝗲𝗺𝗼𝘃𝗲 𝗗𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲𝘀 𝗙𝗲𝗮𝘁𝘂𝗿𝗲

1️⃣ Select your data.
2️⃣ On the Data tab, go to Remove Duplicates.
3️⃣ Select one or more columns that contain duplicates.
4️⃣ Select OK to confirm the columns.

A popup appears, clarifying how many duplicate values have been found and removed, as well as the number that remains. After selecting OK, the changes appear.

𝘐𝘯 𝘵𝘩𝘦 𝘦𝘹𝘢𝘮𝘱𝘭𝘦, 𝘵𝘩𝘦 𝘥𝘶𝘱𝘭𝘪𝘤𝘢𝘵𝘦 𝘯𝘢𝘮𝘦𝘴 𝘢𝘳𝘦 𝘳𝘦𝘮𝘰𝘷𝘦𝘥 𝘴𝘰 𝘰𝘯𝘭𝘺 𝘢 𝘴𝘪𝘯𝘨𝘭𝘦 𝘪𝘯𝘴𝘵𝘢𝘯𝘤𝘦 𝘰𝘧 𝘦𝘢𝘤𝘩 𝘦𝘹𝘪𝘴𝘵𝘴 𝘢𝘭𝘰𝘯𝘨𝘴𝘪𝘥𝘦 𝘵𝘩𝘦𝘪𝘳 𝘳𝘦𝘴𝘱𝘦𝘤𝘵𝘪𝘷𝘦 𝘥𝘦𝘱𝘢𝘳𝘵𝘮𝘦𝘯𝘵𝘴.

𝗨𝗡𝗜𝗤𝗨𝗘 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻

Reference your data range inside the UNIQUE function to return all distinct values.

𝘐𝘯 𝘵𝘩𝘦 𝘦𝘹𝘢𝘮𝘱𝘭𝘦, =𝚄𝙽𝙸𝚀𝚄𝙴(𝙰𝟸:𝙱𝟷𝟾) 𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘰𝘯𝘭𝘺 𝘢 𝘴𝘪𝘯𝘨𝘭𝘦 𝘪𝘯𝘴𝘵𝘢𝘯𝘤𝘦 𝘰𝘧 𝘦𝘢𝘤𝘩 𝘥𝘪𝘴𝘵𝘪𝘯𝘤𝘵 𝘯𝘢𝘮𝘦 𝘢𝘯𝘥 𝘥𝘦𝘱𝘢𝘳𝘵𝘮𝘦𝘯𝘵 𝘤𝘰𝘮𝘣𝘪𝘯𝘢𝘵𝘪𝘰𝘯.

#exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit

---

The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.

Find us on: