Description
In this video I'll show you how to compare two columns for differences in Microsoft Excel. This is a quick way to catch mistakes when you're checking one list against another, like an order against what actually showed up.
Timestamps:
Introduction: 0:00
Compare Columns with a Formula: 0:16
Highlight Differences with Go To Special: 1:17
Make the Comparison Case Sensitive: 2:10
Conclusion: 2:35
Check out my website for other great tutorials about all kinds of technology: https://maxdalton.how/
Support this channel by becoming a member: https://www.youtube.com/channel/UCBbDWMccTJCL0WmbMHNLZIw/join
#excel #microsoftexcel #exceltutorial #techtips #tutorial
Video Transcript:
Step 1. Open your Excel spreadsheet. For this example, I'm using an order check sheet for a comic book shop, with the issues we ordered in column A, the issues we received in column B, and an empty Match column in column C.
Step 2. Click in cell C2, and then type the formula you see on the screen.
=IF(A2=B2,"Match","Different")
This tells Excel to check whether the two cells are the same. If they are, it shows the word Match, and if they're not, it shows the word Different.
Step 3. Press "Enter" on your keyboard. Cell C2 displays Match.
Step 4. Click cell C2, and then double-click the fill handle in the bottom right corner of the cell to copy the formula down the column. Now you can see at a glance that a few rows are marked Different, like Ghostlight Rangers, where we ordered issue 42 but received issue 52.
If you'd rather highlight the differences right in your data, you can have Excel select them for you.
Step 1. Click and drag to select both columns of data, from cell A2 down to cell B21.
Step 2. Click "Find and Select" on the Home tab, in the Editing group on the right side of the ribbon, and then click "Go To Special." The Go To Special window opens.
Step 3. Click "Row differences," and then click "OK." Excel selects every cell in column B that doesn't match the cell next to it in column A.
Step 4. Click the arrow next to the "Fill Color" icon on the Home tab, and then click a color, like yellow. The mismatched cells are now highlighted, so they stand out in your list.
Keep in mind that comparing cells with an equal sign ignores capitalization, so Excel treats Nightglass and NIGHTGLASS as a match. If capitalization matters, use the EXACT function inside your formula instead, like =IF(EXACT(A2,B2),"Match","Different").