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

How does it feel to work?

Looking at the forms all over the screen, I can only look up to the sky and sigh, "watch!" Grid! good! It's hard! Nah! "

The formula keeps reporting errors?

Can't get the right result?

Can't use filtering?

Can't sort all the data?

…… What on earth is making me a mess?! Hey, blame yourself, blame yourself for stepping into these pits in Excel!

Instead of falling into the pit and getting up, avoiding it in the first place is what we should do!

Below, Xiao E will comb through the four most common pits in Excel, the pits that 99.9% of the people have stepped on, and avoid them early!

The curse caused by the abuse of spaces, adjust the column width or change the font size, this unit Grimming in the next line of text how to run up?

Also, I want to calculate the total number of students of each mentor, how to enter the formula and get the result is 0? The formula is obviously not wrong.

Hey, in fact, it's all caused by spaces!

What shall I do? Press [Ctrl+H] to call up the replace command and replace all spaces--

Then look at the calculation results after entering the formula, there is no problem!

If you need to display names that are aligned at both ends, change the alignment to "scatter alignment" in the cell format.

As for cell text wrapping, I've said it more than once-remember to press [Alt] and enter at the same time in the future, and don't abuse spaces!

Blank lines also cause a lot of trouble. Why does it only work on the first few rows of data, no matter in ascending or descending order?

In addition, the "harm" of blank lines is more than that. when using PivotTable, it is clear that there are three departments in the source data--

As a result, there is only "warehouse management" information in the PivotTable--

The data below the space seems to be an abandoned orphan.

Then follow Xiao E to delete the blank line:

❶ first uses the filtering function to filter out all blank lines.

❷ selects the first row again, and press [Ctrl+Shift+ ↓] to select all the filtered blank rows.

❸ right delete, and then unfilter, clear all discontiguous blank lines!

Merging cells is a big taboo. After entering the formula and then filling it down, there is an error! Is there something wrong with my formula?

Select column B and click [Center after merge]-only B2, B7 and B12 have data. No wonder the formula reported an error!

In the final analysis, it is all the harm of merging cells! To make matters worse, merging cells can also lead to the inability to use PivotTable reports and sort them, which is definitely a big hole in Excel!

But have already entered the pit, think of a way to solve these problems first! Look at--

❶ selects column B after the merge is centered, press [Ctrl+G] to locate, and select "null value" for positioning condition.

When all blank cells in ❷ are selected, enter = B2 and press [Ctrl] and enter at the same time.

The error in the ❸ formula will disappear automatically. Finally, select column An and press the [format Brush] button, and then brush it on column B and it will be done!

If you want to rank each department according to the amount of awards per capita, there will be a hint like this if you do it directly.

So, we add a column of auxiliary columns, enter the formula, and then fill it down.

= COUNTA ($A$2:A2) * 10 ^ 5 + E2

Then select column C to column F, click the [sort] button, select "auxiliary column" for the main keywords, select "descending order" in order, and arrange the data in each department according to the per capita bonus from high to low, and finally delete the auxiliary column.

The data are obviously different, why does the conditional format show that they are all duplicated? We want to highlight these duplicate data values--

When using conditional formatting, there is a scene where all the data is considered to be duplicated!

Strictly speaking, this is not caused by non-standard operation, the problem is that the calculation accuracy of numbers in Excel is limited to 15 digits, and more than 15 digits will be automatically treated as 0 by default.

So these first 15 identical figures are judged to be the same!

So we have to add the numbers after 15 digits for statistics in order to correctly detect duplicate values, which requires entering another formula when conditional formatting.

= COUNTIF ($Aq2VRZ / A13 / A2 & "*") > 1

This can be regarded as a duplicate value detected!

These are the most common pits in Excel summarized by Xiao E, and each pit has to fall badly!

So write down Xiao E's sharing today and try to stay away from these pits in the future (P.S. Even if you do meet, you can "save yourself" in time.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Coco, Editor: Zhu Lan

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.