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
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.
Display the content search interface, you Click the Options button to expand the search option in the Excel table.
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.
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.
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.
Instructions to find blank cells in Excel using the function COUNTBLANK
We have the following statistics table with many random empty cells.
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.
As a result, we have got the number of empty cells with no values in the Excel data table.
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.
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.
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.
Display the Format Cells interface and then click Fill tab and choose a color to distinguish it from empty cells with no values.
Finally, the user clicks OK to confirm the new condition for the data table, and colorizes the cells containing null values in the table.
The data table results have distinguished between cells with values and empty cells through the color we have selected.
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