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

There is a useful feature in Excel that can quickly find the person you are looking for in a vast crowd (such as the employee list).

It can also quickly find the information you dream of in millions of data (such as sales details).

It is the filter in Excel. According to the specified criteria, you can quickly filter and present the data you want.

However, filtering can also have some small BUG in practical use, so let's share two common minor problems today.

1. The problem that the filter is empty is shown in the figure below is a salary table.

If we want to filter by department, we usually select row 3, and then click the [data] tab-[filter] to bring up the filter button. As shown below:

Then click the filter button next to the department to screen out the department you want to see.

Sometimes, for the sake of beauty or for the purpose of adding data later, we may insert one or two blank columns in front of the entire data details, as shown below:

Two blank columns are inserted in front of the [C] column, and then we go to the filter button in the department, and the following occurs:

The screening is empty, but the content is missing?

What's going on?

The reason lies in this empty column.

If there is more than one empty column in front of the data list, the filter button for the entire row is selected and the filter button is also displayed in the blank column, BUG will appear.

So you should pay special attention to whether there are extra filter buttons in the blank column.

There are two ways to solve this problem.

The ❶ method is that you can fill in the content on an empty column. For example, enter a space or the contents of other data.

Here we enter a space. As shown below:

After that, go to the department's filter button.

At this point, the content automatically reappears. As shown below:

The ❷ method is to unfilter the button first, then select only the title row [C3:G3], and then click filter to bring up the filter button.

Using the second method, no matter how many empty columns are in front of them, or whether the empty columns are entered or not, the later data will not be affected.

2. The problem of digital screening sometimes, we need to filter out the data in the data table that is greater than, less than or equal to a certain value for later statistical analysis.

For example, in the payroll table below, we want to screen out the list of people whose salary is greater than or equal to 10000:

Click the filter button next to the salary cell-number filter-greater than or equal to, and the Custom automatic filter dialog box appears:

Enter 10000 in it, and click OK.

At this point, data greater than or equal to 10000 can be displayed normally.

If, at this time, we only want to filter the data whose salary is equal to 10000.

You can enter [equal] 10000 in the Custom Auto filter dialog box.

Click [OK], and the result is as follows:

Huh?

At this time, the form is as blank as my heart!

Haha ~

Is Excel kidding us?

There is obviously 10000 of the data in the data, but it can't be found!

What on earth is this, Bug!

The reason is:

Under the equal condition, the input number format is not consistent with the set cell format, which will cause the filtering to be unsuccessful.

There are also two solutions.

The ❶ method is to set the numeric column to a regular format.

Filter again at this time, and the results will be displayed normally.

The ❷ method is to manually enter the exact same format as the cell number format in the Custom Auto filter box. As shown below:

PS: if there is a decimal point and two zeros in the data source, don't forget to enter it. It has to be exactly the same! Just like the following two pictures.

Some friends may ask: why filter in the Custom Auto filter dialog box?

I can filter in the search box below!

Well, that makes a lot of sense!

Friends can enter 10000 in this search box.

See what the result is, you can leave us a message at the bottom of the article!

3. At the end of the day, we shared two small BUG when filtering the data table.

It is hoped that Microsoft can improve the related problems. Also hope that the children in the use of more attention, more learning, more thinking.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

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.