Excel provides numerous methods to count blank or empty cells in a selected range of cells. In this article, you need to learn how to apply these methods to count blank or empty cells in Excel. First, you need to understand the difference between Blank and empty cells.
How to count blank or empty cells in Excel
Welcome to my channel! Here you'll find mediocre comedy, extreme amounts of sarcasm, terrible acting, and a dude with thousands upon thousands of hours playi. Learn to use String.isBlank method to determine is a given string is blank or empty or contains only white spaces. IsBlank method has been added in Java 11. To check is given string does not have even blank spaces, use String.isEmpty method.
Help continue to make SPACES one of the best places by telling us what you think. Take the survey. 2900 Detroit Avenue Cleveland, OH 44113 (216) 621-2314.
Blank cells are those cells which do not contain any number or text values, space(s), logical value or formula errors. If a cell contains a formula that returns empty text (“”) is also considered a Blank cell. Empty cells are those that are empty by all means, without having a value, space(s), error(s) and formula that returns empty text (“”).
There are multiple features, functions, and formulas to count Blank or empty cells in Excel that will be discussed in this article.
Cell Empty Spaces The Blog -
Using Go To Special functionality
It’s an Excel built-in functionality. Using this functionality, you can highlight and manually count blank and empty cells as it only highlights blank or empty cells in a selected range of data. To do this, you need to select the range of cells where you want to count blank cells. On Home tab, go to Editing section and click on Find & Select drop-down list and click on Go To Special…
Go To Special dialog box opens, containing multiple options and feature. You need to select Blanks radio button and press OK.
When you select OK, this functionality highlights all blank and empty cells in your selected range, as shown below;
Using Find and Replace functionality
This is also Excel’s built-in functionality to count blank or empty cells. The Find and Replace functionality can count the total number of blank or empty cells in your selected range of cells. To do this, you need to select cells range where you need to count blank or empty cells and press Ctrl+F short keys on your keyboard. A Find and Replace dialog populates.
Keep the Find what field empty, press Options >> button, select Match entire cell contents checkbox and select one of options Formulas or Values from Look in: drop-down list as per requirement as described below;
- If you select Values option, then it will count both Blank and Empty cells in the selected range.
- If you select Formulas option, then it will count only Empty cells.
When you press Find All button, the dialog gives you count of all Blank cells along with the details of cell references of blank cells in a range. It’s a very handy and user-friendly method to count these using this method.
Using Formulas to count Blank or Empty cells
There are various Excel formulas or functions to count blank or empty cells in selected cells’ range, such as COUNTBLANK, COUNTIF, and SUMPRODUCT. Here we will discuss these formulas one by one.
The COUNTBLANK function is designed in Excel to count blank cells. You can easily count blank cells by entering reference of cells range in this function, such as;
Empty Spaces Song
Excel’s COUNTIF function is designed to count cells that meet supplied criteria or condition. If you need to count blank cells in given range, then you need to supply empty text (“”) in criteria argument of COUNTIF function, such as;
Excel’s SUMPRODUCT function can also be used to sum the count of cells that meet the criteria of being blank or empty, such as;
When you apply this formula in this manner, it gives you the total count of blank cells. Double Hyphen symbol (–) converts the cells as 1s and 0s where the applied condition is TRUE or FALSE, and SUMPRODUCT sums these digits to give you the total count of Blank or Empty cells.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
You have a spreadsheet with information missing because it was summarised for viewing as a report, but this format is no good for data analysis. You know you want to fill the empty cells with values, but how do you do it quickly?
Perhaps you copied data from a report somewhere, and some columns were summarised with blank spaces sorta like the spreadsheet below.
As you probably know already, the above layout will not work for any kind of data mining, because columns A and B ('Region' and 'Sales Channel') are missing values.
Empty Spaces Linkin Park
You may know that rows two through five refer to the 'South East' region, however from a data (and database perspective) they are just empty, devoid of region information.
So given that this data layout does not work if you wish to use it as part of a vlookup, or if you want to use it within an 'Excel Autofilter' or 'Excel Advanced Filter'.
You will probably set about copying or dragging each of the ranges down to fill all empty cells on each of the columns.
There is however a much cooler way to do this, one that will also give you massive time savings on very large data sets.
Highlight The Range To Adjust
First select any part of the range, in this example you could click cell C5 then hold down the 'Ctrl' and '*' (asterisk) key. This will select the 'current region' around the cursor and the selection should now look like this.
Now press your 'F5' key this will result in the 'Go To' dialog box appearing.
Then click the 'Special…' button on the bottom of the 'Go To' dialog box.
This will call up the 'Go To Special' dialog box pictured to the right.
On the first column of the 'Go To Special' dialog box click the 'Blanks' Radio Button.
Then click “OK”.
This will result in only the blank excel cells being highlighted, see below.
Now Fill Empty Cells In Excel Using Equals Up Arrow
Without moving your cursor (you don’t want to lose your selection), type “=” then move the formula cursor up one cell, and hold down the 'ctrl' key, and press return at the same time.
So for example if the current cell is 'B3', as in the image above, then 'B3' will contain the formula '=B2'.
In fact as a result of the previous action, every blank highlighted cell will contain a formula referencing the cell above it.
Notice below that every highlighted cell which was previously empty, now contains the value of the cell above it, effectively filling out the blanks.
Convert The Formuli To Values
All that now remains is to convert the formulae that are in the highlighted cells to values.
Simply select column A and column B
Cell Empty Spaces The Blog Template
Then copy (ctrl+c on the pc).
Then choose Paste Values as in the image below.
Your data range will now be completely filled out with the formulae converted to values.
Clelmzempty Spaces The Blog Example
This protects against your cell values accidently being changed, resulting in incorrect information.
Empty Spaces Lyrics
You now have a data range that is more professional and can form the input to other sheets in your spreadsheet, a data range that works perfectly with autofilter, advanced filter and pivot tables.
Clelmzempty Spaces The Blog Template
This methodology can also be implemented elegantly in visual basic for applications (vba).