Contextures

Contextures News 2021-09-21

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.

Duplicate Numbers

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:

  • In the formula bar, the numbers in E3 and E4 looked identical
  • Several worksheet formulas compared those cells, and said they were equal

worksheet formulas do not detect difference between amounts

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:

  • In cell A1, the value is 1.68 - a 3-digit number
  • In cell B1, there are 17 digits, with 2 as the final character

different values in cells

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.

Excel Blogs

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:

  • On my Contextures site, point to one of the navigation buttons at the top
  • When the pointer changes to a hand, drag the button up to the bookmark bar
  • To visit a site again later, just click its bookmark!

TIP: If the bookmark has a long name, right-click it, then click Edit Bookmark, and change the name to something shorter.

add site links to bookmark bar

Excel Articles

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

20 Years

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!

weekly photo

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.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Last updated: September 20, 2021 1:24 PM