If you need to count the number of blank or empty cells in a worksheet in Microsoft Excel, there are a few different ways to do it. One way is to use the VLOOKUP function. This function can look up a value in a column and return the number of cells that contain that value. To use the VLOOKUP function, you first need to specify which column you want the function to look in. Then, you enter the lookup value into the lookup cell (in this example, we’ll use B2), and then press Enter. If the lookup value is found in any cells in the column, VLOOKUP will return a number (in this case, 2). If no cells contain the lookup value, VLOOKUP will return an error message (in this case, “No matches found”). Another way to count blank or empty cells is to use Excel’s SUM function. The SUM function can calculate a total for a set of values and will return an error message if there are any errors while calculating the total. To use SUM(), you first need to specify which column you want the function to look in. Then, you enter your values into cells A1 through A12 (or whatever range you want), and then press Enter. The SUM()function will calculate and return your total values for each cell in that range (in this example, it would return 128). ..
The only thing worse than incorrect data in your spreadsheet is missing data. If you want to count the number of blank or empty cells in Microsoft Excel, we’ll show you two quick and easy methods.
By using a function, you can keep the number of blank cells parked in your sheet. This way, if you change your data, that count will adjust. If you prefer to simply see a fast count of empty cells, you can use Excel’s built-in Find feature. Let’s take a look at both.
RELATED: How to Count Blank or Empty Cells in Google Sheets
Count Blank Cells Using a Function
The COUNT function in Microsoft Excel is handy for many scenarios. So with a variation of that function, you can count empty cells easily. The function is COUNTBLANK and here’s how to use it.
Select the cell where you want to insert the function. This is the same cell that will display the count of blank cells. Type the following formula into the cell replacing the cell range with your own and hit Enter.
You should then see the number of empty cells in the range you selected for the formula.
If you want to adjust the cell range, this is also simple. Click the cell containing the function, go up to the Formula Bar, and place your cursor within the cell range. You can manually change the cell references in the range or drag in or out on the blue box. Then, press Enter.
You can also combine the COUNTBLANK function with itself to count the number of blank cells in different cell ranges of the same workbook. Type the following formula into the cell replacing the cell ranges with your own and press Enter.
Notice the cell ranges for each set are outlined in a different color making them easy to edit if needed.
And you get the total count in one cell for both sets of cell ranges.
If you use colors to differentiate data, you may also find it useful to count colored cells in your spreadsheet.
RELATED: How to Count Colored Cells in Microsoft Excel
Count Blank Cells Using the Find Feature
If you would rather not keep a formula in your sheet, but merely see a quick count of blank cells, use the Find feature.
Select the cells that include the blanks you want to find. Go to the Home tab and click the Find & Select drop-down arrow in the Editing section of the ribbon. Choose “Find.”
When the Find and Replace window opens, leave the Find What box blank. Then, click “Options” to expand the section at the bottom.
Adjust the three drop-down boxes on the left side to use the following:
Within: Sheet Search: By Rows or By Columns (per your preference) Look In: Values
When you’re ready, click “Find All.” You’ll then see the number of cells found on the bottom left of the window.
You’ll also see a list of those empty cells in your sheet. You can click one to go directly to it or click “Find Next” to move to each of the results in the list one at a time.
Click “Close” when you finish.
Counting blank or empty cells in your spreadsheet is easy to do. And if you want to make them stand out so you don’t lose track of them, learn how to highlight blanks in your Excel sheet.
