Is Excel Remove Duplicates Broken?
September 21, 2021
More Excel resources, problems with Remove Duplicates, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Thank you for reading the news, and you'll get the next email on October 5th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Have you ever been left with duplicate numbers in an Excel list, after you used the Remove Duplicates command? Is that Remove Duplicates feature broken?
That problem happened to me recently, when I tried to clean up a price list worksheet:
There had to be a reason for these duplicates, so I kept digging. Eventually, I found the difference, hidden away in the worksheet's XML file:
On the worksheet, Excel is limited to 15 digits of precision, so that 2 is ignored, and the numbers are treated as equal. You can read more about Excel's floating point precision on the Microsoft site.
I'm not sure why Excel features see that difference though, such as Remove Duplicates, Advanced Filter, and Pivot Tables. Maybe we need a new worksheet function that can see the differences too!
For details on how to check the XML file, and a workaround for fixing the differences, go to the Remove Duplicates Problem page on my Contextures site.
In the last newsletter, I shared a couple of lists for Excel blogs and videos. This time, I've got a longer list of Excel blogs for you to explore. Of course, some of these were in the previous lists, but you'll find new links tool
You can find the list on Martin K's website, Planning Skills, so go and take a look.
And, if you find a blog that you want to visit frequently, drag a link from that site onto your browser's bookmark bar. For example:
TIP: If the bookmark has a long name, right-click it, then click Edit Bookmark, and change the name to something shorter.
Here are some Excel-related links that you might find useful or interesting.
New Features: In a short video, Mynda Treacy show 5 new features in Excel, including the long-requested ability to unhide multiple worksheets at once. (Level - All)
Formula Problems: On the Towards Data Science blog, Joseph Burton looks at three problems you might create in Excel. Two problems are caused by averaging, and there's one VLOOKUP mistake. (Level - Int)
Also see: Previous Newsletter Issues
Long ago, I found the Microsoft Excel newsgroups, where people posted questions, and other people answered them. After a few days of reading questions, I finally got up the nerve to answer one! That was fun, so I kept at it, until Microsoft eventually shut down the newsgroups, and switched to online forums instead.
In 2001, Microsoft gave me an MVP award, for my contributions to the technical community, and every year since then. This year, I was honoured to get a 20-year award, and it's hard to believe it's been so long!
The online forums are a great place to help other people, and an awesome way to learn new things. If you have a few extra minutes during the week, see if there's a forum where you help other people with their Excel questions. It's better than scrolling mindlessly though Facebook or Twitter!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210921ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: September 20, 2021 1:24 PM