What should I do if there is data in the Excel table but the blanks are filtered?

In Excel tables, sometimes there is data but the blanks cannot be filtered out, which is confusing. Don’t worry, though, because there are some simple ways we can fix this problem. Below, PHP editor Shinichi will introduce to you how to filter out blank data in Excel tables, allowing you to easily handle this situation.

　What should I do if there is data in the Excel table but the blanks are filtered?

The first reason is that the table contains blank rows

We want to filter all people with the surname "Li", but we can see that the correct results are not filtered out because the table contains blank rows. How to deal with this situation?

Solution:

Step 1: Select all content and then filter

Press ctrl shift l to cancel filtering, then select all the contents of the table, and then press ctrl shift l to filter. You can see that tables containing blank rows can be filtered.

Step 2: Delete empty rows in the table in batches, don’t delete them one by one

Select a column, then ctrl g, positioning conditions - null value, delete, select the positive row, so that you can filter correctly.

The second reason is that there are merged cells that cannot be filtered

Use ctrl shift l to filter the table. You can see that there are clearly three people in the sales department, but only one person is filtered out. There are two processing methods.

Solution:

​Step 1: Cancel merged cell filtering

① Select the column of merged cells and then cancel the merged cells.

​​②Ctrl g, select a null value for the positioning condition, enter =C2, which is the previous cell, and then ctrl enter to fill in the content, and you can accurately filter.

Step 2: Do not merge cells manually

①Select the table, then data-subclassification-selected summary items-select department.

② Select the summary column, ctrl g, positioning conditions, select null values, and center the merged cells.

③Click any area of ​​the table, data-category summary-delete all.

④ Then copy the blank merged cell column, then format the brush, refresh the data, and merge the cells in batches.

By merging cells in this way, you can filter.

The above are the two situations where there are values ​​in the table but cannot be filtered, and the two processing methods respectively.

The above is the detailed content of What should I do if there is data in the Excel table but the blanks are filtered?. For more information, please follow other related articles on the PHP Chinese website!