Quickly Compare 2 Excel Tables
March 5, 2019
Hide duplicates, compare tables trick, 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.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Do you ever need to compare two Excel Tables? This simple formula shows TRUE, if the tables are exactly the same. If there are any differences, the result is FALSE. Thanks to UniMord for this tip!
=SUMPRODUCT((Table1=Table2)-1)=0
SUMPRODUCT multiplies the array of value comparisons, and returns the sum. Can you figure out what the "-1" does in the formula? (see below). See another SUMPRODUCT example here.
This formula compares each cell in the two tables. If you highlight (Table1=Table2), in the Formula Bar, and press F9 to calculate, you'll see all the results as TRUE or FALSE (top image below).
But, if you also include the -1, that operation changes the results to numbers (bottom image below)
When you're working with tables, it's important to have all the key data in each row, so you can sort and filter the data. However, that can make the list hard to read, with lots of repeating text in some columns.
To make the list look cleaner, use conditional formatting to hide duplicate values. In this example, the list is sorted by month, and only the first instance of each month name will appear.
Watch my video to see the steps. The steps haven't changed since I made that video in 2008, but now my mouse doesn't click as loudly!
Here are a couple of Excel articles that you might find useful or interesting.
Charts - With a stacked column chart, you can show the amount for each section, but there's no built-in way to show the column's total. Jon Peltier created a free add-in that quickly puts those totals in stacked chart for you (column, bar, area or line). (Level - All)
Power Query - Ken Puls shows how to pivot stacked data, using Power Query. For example, a mailing list, with names and addresses all in the same column, with blank rows separating them. You don't need complicated formulas now!. (Level - Int/Adv)
Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour
How do you celebrate the end of a long week? We like to have our Saturday suppers by candlelight, on the "good" dishes -- even simple food seems fancy that way! Last week, to give spring a little encouragement, we used our flowery napkins. It probably won't make winter end any sooner, but it can't hurt to try, right?
P.S. Our candle is on the table. We don't try to solve the candle problem, and stick it to the wall.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190305ctx.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
dsdalg@ gmail.com
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.
Last updated: March 3, 2019 3:19 PM