How to find blank cells in Excel, color blank cells in Excel

Empty cells in Excel, extra cells in Excel tables will make your table lose its professionalism and aesthetics. These blank cells will affect whether you calculate or apply functions in Excel. To easily find and calculate empty cells in Excel, you can do it in many different ways. We can use the function to find blank cells in Excel, or immediately use the search feature in Excel. The following article will guide you to find blank cells in Excel.

1. Find blank cells in Excel with Find

Step 1:

At the interface in the Excel data table, we zone the data to find the empty cell, then click on Find&Select then choose Find feature.

Step 2:

Display the content search interface, you Click the Options button to expand the search option in the Excel table.

Customize the Find feature in Excel

Step 3:

At this point we will see there are 3 adjustment options including:

  • Within: Search within the sheet.
  • Search: Search by row or by column depending on the table.
  • Look In: ​​Values.

Once set up, you Click Find Next to search data according to the created settings.

Find blank cells with Find feature in Excel

Step 4:

The results of empty cells will be displayed for us to know. When you click Find Next in turn, we will be immediately moved to the empty cells and are highlighted more than the cells with other data in the Excel table.

Find each blank cell in Excel

If click Find All will quickly display the number of empty cells and the position of those empty cells in the Cell column as shown below.

List of empty cells in Excel

Instructions to find blank cells in Excel using the function COUNTBLANK

We have the following statistics table with many random empty cells.

Excel Table

In the input cell the number of empty cells has no value, the user enters the formula =COUNTBLANK(B2:E11) and then press Enter, where B2:E11 is the data area where the user needs to count the number of empty cells.

Formula to count the number of empty cells

As a result, we have got the number of empty cells with no values ​​in the Excel data table.

Number of empty cells

Step 2:

To clearly distinguish blank cells in the Excel table, users can color through the conditional feature in Excel. First of all users black out the entire Excel spreadsheet then click on Conditional Formatting then choose New Rule.

Spreadsheet Condition

Step 3:

Display the New Formatting Rule interface for us to set conditions for the worksheet. We click select Use a formula to determine which Cells to format in the list below.

Use function as condition

Step 4:

Switch to the new interface we enter function =ISBLANK(B2), where B2 is the first cell in the worksheet that needs to use the condition, and then click on Format button.

Coloring conditions

Display the Format Cells interface and then click Fill tab and choose a color to distinguish it from empty cells with no values.

Choose color

Step 5:

Finally, the user clicks OK to confirm the new condition for the data table, and colorizes the cells containing null values ​​in the table.

Determine the condition

The data table results have distinguished between cells with values ​​and empty cells through the color we have selected.

Color the empty box

With just a very simple operation, we have determined the number of empty cells, with no values ​​in the Excel data table. Those empty cells have also been distinguished through color based on the conditional formatting that we set up.

Video tutorial for calculating and coloring Excel blank cells


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *