Shulou(Shulou.com)11/24 Report--

Original title: "good-bye, Vlookup!" These 5 data checking skills are so useful! "

Hello, everyone, Hi~. I am Touyu and I am also learning Tian Tian of Excel.

Many students say that what they fear most at work is to check the data!

If you encounter a table with a lot of data, you will work overtime for 2 hours at least, and stay up all night to finish it (/ / hours / hours /)

Good guy, I only know when I ask, it turns out that the data are checked with the eyes. No wonder there are two big dark circles under the eyes!

In fact, as long as you know how to Excel, it is easy to check the data.

You don't believe me? Let's take a look at these five tricks to check the data and verify it.

1. Check row by row when the order and number of rows of the two columns of data are exactly the same, this method is very suitable if you only want to find out the differences.

❶ press and hold the [Ctrl] key to select two columns of data in batches, and press the key combination of [Ctrl+\] to quickly find the difference data in the same row.

The PS: [\] key is directly above the [Enter] key.

2. Check two tables with the same structure, order and quantity. If you want to find differences from the dense data, you can use this method.

❶ first select the first table, click the "conditional format" function in the "start" tab, select "New Rule", and select "use Formula" for the rule type.

Enter = in the rule edit bar, select the highlighted active cell of the first table with the mouse, and enter, which means that it is not equal to.

❷ then uses the mouse to select the corresponding cell location in the second table. Delete all dollar signs in the formula, click the format button, set the fill color, and click OK.

All the cells with different contents will be colored!

3. When comparing two pure value tables, we can use this method to quickly find the difference between the two tables.

❶ select the table on the left, press [Ctrl+C] to copy, then select the table on the right, click the right mouse button, open the option of "selective paste", select the subtraction operation in the operation, and click "OK". In this way, if the result is not zero, there is a difference in data.

4. Find differences between two tables if there are two columns of data, both in order and quantity, you can use this method to find out the differences between the two columns.

❶ press and hold the [Ctrl] key to select two columns of data in batches, click the "conditional format" button in the "start" tab, select the "highlight repeated values in cell rules" option, change the repetition to unique, and click "OK".

The red mark is the data that is different between the two columns.

5. Find the same between two tables if you have two tables to find rows with exactly the same content, you can use this method.

❶ first select the table on the left, click the "Advanced filter" icon under the "data" tab, set the table on the right as the conditional area, and click "OK".

❷ marks the color of the filtered results.

❸ clicks the clear filter button in the data tab, and the same data is filtered out.

At this point, it is equivalent to filtering the contents of the right table in the left table, and the exact same rows are filtered out in the left table. (have you learned all these five tips for data checking? Is it super simple? let's go and try it.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Tian Tian

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.