One of the more common uses for Excel is financial reporting based on templates. The one problem is that if you use a template you have to pre populate it with all the options and when you use it, perhaps only a few are used. If you want a better presentation you may want to hide these blank rows. As a result you may need to check if the row is blank in Excel.
There are a number of ways to check if a cell is blank. It is a bit more difficult when you want to check a row, column or cells (but only a little bit). Below how to identify blank cells and rows, and lower down how to use this to Hide/ unhide rows.
Table of contents
YouTube Check for blank rows in Excel
Check if a cell is blank
The easiest is probably an IF function.
As shown below the IF function would say
=IF(K45=””,1,0)
The important thing to note is that to tell Excel to search for a blank, you need to have the 2 inverted commas directly next to each other. If you say =” ” (notice the space) then you are asking Excel to look for a space.
Another way is to use the ISBLANK function i.e. =ISBLANK(K45). This will return a TRUE or FALSE and you can then work from there (quick tip, putting 2 negative signs in front i.e. =–ISBLANK(K45) will change the TRUE/ FALSE to 1/0).
That’s fine if you consider the row blank if that one cell is blank but what do you do if a blank row is only one where multiple cells are blank?.
Check if multiple cells in a row are blank
In this case you can use an IF statement as above but then it would need to be multi nested IF which is not ideal, so a few other options are:
AND function
The AND function allows you to check multiple cells and only returns a TRUE or FALSE (great with an IF function). So in the example below we can check 3 cells by using
=AND(G49=””,K49=””,M49=””) (again note that there is no space between the inverted commas and if you want a 1 or 0 add a — in front).
This is useful if there are a few cells that you want to check.
What happens if there are lots of cells to look at? You effectively want to ISBLANK multiple cell or check if a range of cells is blank.
COUNTIF and COUNTBLANK
The COUNTBLANK function does exactly what it says. You can point at a range of cells and count how many are blank. Once you know this answer you can decide what it means to Excel with an IF function.
So the example below [=COUNTBLANK(G49:M49)] would result in the number of 5 when we have highlighted 7 cells. This must mean it is NOT blank. If the answer is 7 then it must be a blank row.
Similarly, you could use the COUNTIF function (and the COUNTIF could be used if there are other reasons that you consider a row blank e.g. all zeros).
By typing in =COUNTIF(G50:M50,””) you are doing the same thing as COUNTBLANK. Remember that the inverted commas are right next to each other with no space inbetween.
Using your knowledge of the blank rows to create a Hide/ Unhide button
Now we understand the ways to check if the row is blank, the video clip below shows how you can create a simple macro that can hide and unhide rows depending on their status. By combining these 2 tricks you can easily create a spreadsheet that hides the unnecessary rows.
Other uses once you know where the blank rows are in Excel
Once you can identify which rows are blank, either based on a single or multiple cells you can
- delete the blank rows by using the Data Filter to view the blank rows and delete these rows
- Highlight the blank rows using conditional formatting with a formula that references to your cell that tells you if the row is blank or not.
Related
Auto hide/ unhide columns in Excel (without VBA)
IF NOT blank then calculate in Excel