True story:
On Friday (17th April – 2015), I flew from Vizag (my town) to Hyderabad so that I can catch a flight to San Francisco to attend a conference. As I had 10 hours of overlay between the flights in Hyderabad, I checked in to a lounge area so that I can watch some sports, eat food while pretending to do work on my laptop. There was a gentleman sitting in adjacent space doing some work in Excel. As I began to compose few emails, the gentleman in next sitting space asked me what I do for living. Our conversation went like this.
Me: I run a software company
He: Oh, so you must be good with computers
Me: smiles and cringes at the stereotyping
He: What is the formula to select all the blank cells in my Excel data and highlight them in Yellow color
Mind you, he had no idea that I work in Excel. We were 2 random guys in airport lounge watching sports and eating miserable food.
Me: Well, what are you trying to do?
He: You see, I am auditing this data. I need to locate all the blank rows and set them in different color so that my staff can fill up missing information. Right now, I am selecting one row at a time and filling the colors. Is there a one step solution to this problem?
Needless to say, I showed him how to do it faster, which led to an interesting 3 hours at the lounge.
End of true story.
So today, let’s understand how to find & highlight all the blank cells in the data.
Let’s take a look at the data:
Here is a sample of data.
One important thing to keep in mind:
- This data is not structured as table.
There are 3 powerful & simple methods to find & highlight blank cells.
Method 1: Selection & Highlight approach
In this method, we just select all the blank cells in one go and fill them with yellow color.
First select the entire range of cells where you data is located. Using CTRL+Arrow keys is not going to work because of the blank cells in-between. Instead, follow this:
- Select the top-left cell of your data (say B2)
- Click and drag the little rectangular box in vertical-scrollbar all the way down.
- Hold Shift and click on the very last cell (bottom-right)
Now that all the data is selected,
- Press F5 and click on Special
- Choose blanks. Click ok.
- This will select only the blank cells.
- Fill yellow (or other) color by clicking the fill icon and selecting the color
- Done!
Here is a quick demo of this:
Method 2: Filter approach
The above approach (selection & highlight) works fine if you care about blank cells anywhere. What if you just want to find & highlight only rows have blanks in a certain column. Say, you want to highlight all rows where comments are empty.
In this case,
- Select all data using the steps in method 1.
- Press CTRL+Shift+L to activate filters
- Keep the selection on & Filter the column you want to show only blank values
- Now fill yellow color
- Done!
Method 3: Conditional formatting approach
Both method 1 & method 2 have a draw back. If your data changes, you must clean up & highlight again.
This is where conditional formatting shines. You can tell Excel to highlight cells only if they are blank. Once some data is typed in (or copy pasted or connection refreshed), the color will go away automatically.
To set up conditional formatting,
- Select all the data
- Go to Home > Conditional Formatting > New rule
- Click on “Format only cells that contain”
- Change “Cell Value” option to “Blanks”
- Set up formatting you want by clicking on Formatting button
- Click ok and you are done!
This will automatically highlight all blank cells in your favorite color.
Oh wait, what if I want to highlight entire row if a certain column is blank?
You can use conditional formatting in such cases too. Follow these steps.
Assuming you want to check for blanks in Column G and your first data point is in G4.
- Select all the data (just data, no headers)
- Go to Home > Conditional Formatting > New rule
- Select rule type as “Use a formula…”
- Type the formula as
=LEN($G4)=0 - Set up formatting you want
- Click ok and you are done.
Wait a sec, What is the LEN($G4)=0 thing?
LEN() formula tells us what is the length of a cell’s content. So if a cell is blank, LEN(cell) would be 0.
$G4 is a mixed reference style. This way, even when conditional formatting is checking other columns, it still looks in column G to see if that is really empty.
Related: An introduction to Excel cell references.
Bonus tips:
Q) How to highlight if either of column G or H are blank?
A) =OR(LEN($G4)=0, LEN($H4)=0)
Q) How to highlight if both column G & H are blank?
A) =AND(LEN($G4)=0, LEN($H4)=0)
Go ahead and whack them blank cells.
How do you deal with blank cells?
Do you sneak up on an unsuspecting fellow passenger in an airport lounge and ask them how to deal with the blank problem? Do you manually select the blank cells and deal with them one at a time? Or do you use some ninja level trickery to fix the blanks?
Go ahead and tell me your blank story in the comments.
Fill the blanks in your Excel knowledge
If you have gaps in your Excel know-how, then you have come to right place. Use below links and fill those blanks.
27 Responses to “Find and Highlight all blank cells in your data [Excel tips]”
I use conditional formatting - generally when I'm sending messages out to the sales team to fill in the blanks on their forms.
Often I use a double-highlight option.
Highlighting the entire row where a blank appears in a light color - but using a bolder color to also highlight the specific cell.
On a few forms where I have to be very specific about changes made, I also do a reverse of this on my copy of the form - create a duplicate copy of the worksheet, and format my original worksheet to highlight when the data does not match the copy.
But, I'm ashamed to say - I still use my old version of looking for blanks in the conditional formatting =A5=""
I need to remember that 'blank' is part of the Contains rule types.
Good tip about double-highlighting. Thanks.
Since a lot of my data comes from sources that may include spaces instead of blanks as well as unprintable characters, I use Trim(Clean()) to include these. So, instead of selecting for Blanks in the Conditional Formatting example my formula looks like "=Len(Trim(Clean(A1)))" after select the whole area with data.
When the users type/paste in the real values that replaces what is there.
So I wanted to try the trick to highlight an entire row if a certain column is blank, but it didn't quite work as expected. My data was in A2:I6, as I inadvertently overlooked the fact that for the example to work my data needed to begin in G4. The result I got didn't make any sense at all. It highlighted some rows that had blanks in various cells, and one row that didn't have any blanks at all. Can someone explain what happened? Why does the cell reference in the formula need to be the first data point?
Thank you!
Gary
From what I see you've tried to start in the middle of the range, which makes to sense to me. Let me see if I can interpret what I see to what you may want.
The range you are trying to Conditionally Format is $A$2:$I$6. The formula to be tested is =Len($G2)=0.
Once applied, Excel uses the Absolute and Relative Addresses to establish the rule for each cell relative to the upper left "anchoring" cell. So if I "slide across" row 2, the formula continues to look at G2, but when we slide down to row 3, it now looks that G3.
Likewise, you could write "=$G2=$G1", to look for consecutive duplicate values.
It being baseball season, if I wanted to look across all of the innings played by a team, side by side, for any where at least one team scored and both teams scored the same number of runs, by highlighting the two very long rows for a (about 1500 columns), the formula would be "=AND(A$2+A$3 > 0,A$2=A$3)": The columns "slide" while the rows stay steady.
Gary,
If a formula contains a relative reference it will be interpreted with respect to the active cell. Usually you will want to use the active cell as the reference in your formula.
I too use Heather's method, so thanks for the tips. It's nice to find easier ways to accomplish this. Too often we do what we know, even if it's not the best method. Years ago I used to tell my students there are always 1+ ways to do something in a MS product -- but you have to remember 1 or who cares how many there are.
PS -- Fire your travel agent! Ten hour layover?! Yuck! 🙂
hi chandoo,
asume that you have a data table inc. numbers only. every month I add new rows to this table and sent to my collugue. and sometimes I need to change some figures that are already entered previous weeks. to take his notice, I change the fill color of a cell that I revise.
I wonder if it is possible make this with conditional formatting. sayin Excel that : Hey, if any cell (whose cell lengths is bigger than 0) are revised change the fill color !
is it possibe 🙂
Orkun...You can do this using Conditional Formatting if you make a copy of the worksheet before you start making changes. Then, assuming your data is in A2:M100 of worksheet "Report" and the second worksheet is called "Copy", you can try this.
* Select the range for the conditional formatting, i.e. A2:M100
* Enter "=AND(LEN(A2)>0,A2Copy!A2)" (no quote marks) as the Conditional Formatting formula.
* Set the formatting for the changed cells.
To reset the colors to No Fill, copy from "Report" and paste the values into "Copy".
Alternatively, there is a VBA option. Add something this to the worksheet module in the Visual basic Environment (VBE).
Private Sub Worksheet_Change(ByVal Target As Range)
If Len(Target.Value) > 0 Then
Target.Interior.Color = rgbYellow
End If
End Sub
To reset these colors to No Fill, highlight the area and just clear the color fill formatting.
This CF formula highlights empty cells but not if the entire row is empty:
=(B3="")*NOT(AND(($B3:$J3="")))
Amazing story, Chandoo. 🙂
You could also highlight the whole row when any value is blank:
=OR(ISBLANK($B3:$J3))
[…] he is sitting in airport lounges, Chandoo is still helping people with Excel problems. Read his tips for highlighting blank cells – he shares 3 ways to do […]
I just use the Replace function. Highlight the region that contains cells that DO HAVE and DO NOT HAVE values or are blank. Type CNTRL+H and do a format fill on the "replace with:" cells with the color of your choice.
What if I want to highlight a row, but only if a condition exists. For example, my spreadsheet has 10 columns and I want to highlight blanks within the row but only if $A1 is not equal to blank.
Damaris624,
CF Formula:
=($A1"")*(A1="")
WordPress rmoved my less than and greater than signs.
CF Formula:
=($A1less than,greater than"")*(A1="")
Hi,
Thanks for the example. Learned something new today.
I tried to use the formula below in my table.
=OR(LEN($B4)=0,LEN($C4)=0)
Instead of highlighting entire row, it is highlighting the row which is just above the row in which the cell value length=0.
Sorry i am a beginner.
@Manoj
Lets assume that your data range is A10:D15
If you had chosen A9:D15 instead of the original range that would explain that
Select the range and remove the CF
Try again
If this doesn't help can you ask the question at the Forums and attach a sample file
http://chandoo.org/forum/
I am trying to use the "Go To Special-Blanks" feature to fill in blanks from a pivot. I use this all the time, but am trying to do this from my laptop (and not on my docking station as it usually is) and it's not working. Is this not possible from a laptop??
Thank you for these clear instructions; you just made my life (at work) much easier!
Hi Chandoo,
I need your help regarding conditional formatting for blanks.
I selected the range for my worksheet used the conditional formatting for displaying blanks and used the filters to narrow downto individual persons so that they would fill in the blanks. So I created macros for each person who needs to fill up the blanks.(to save time)
However after recording a macro instead of displaying/highlighting only empty cells it is highlighting the whole row with blanks. How do I correct this.
Below is the code
Range("A3:L27").Select
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Range("A3:L27,N3:N27").Select
Range("N3").Activate
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Range("A3:L27,N3:N27,R3:R27,W3:W27").Select
Range("W3").Activate
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(W3))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveSheet.Range("$A$3:$W$27").AutoFilter Field:=7, Criteria1:= _
"Sharon Dev"
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 4
End Sub
why not vba macro to avoid having repeat all this? Something that hides rows with no blank cells and highlights blank cells?
Hi Chandoo,
I used conditional formatting for finding and sisplaying all blank cells in an exel worksheet. I used filters on a column as I need to find out number of blank cells each employee has. I recorded macros against each employee and assigned some shortcuts in the macro.
For eg; If say a macro is applied ctrl+Shift+j for John Doe it would display the blanks so he has to enter them.
Now I want to know the count of blank cells for each employee and having difficultis in it.
How do I go about it . Here is some code
Sub Displayblanks_John_Doe()
'
' Displayblanks_John_Doe Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Range("A3:L26").Select
Range("A3:L26,N3:N26").Select
Range("N3").Activate
Range("A3:L26,N3:N26,R3:R26").Select
Range("R3").Activate
Range("A3:L26,N3:N26,R3:R26,W3:W26").Select
Range("W3").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A3))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveSheet.Range("$A$3:$W$26").AutoFilter Field:=7, Criteria1:= _
"John Doe"
Dim mycount As Long
mycount = Application.WorksheetFunction.CountBlank(ActiveSheet.Range("$A$3:$W$26"))
MsgBox ("Number of empty cells = " & mycount)
End Sub
However it doesnt display the blank cells correctly . Also when I close the mssgbox it goes back to the original sheet. I need it toi stay on the sheet so as to allow employees to fill in the blanks.
I have a table of data. Each row is a logged message that I am tracking. Completed messages in a column are white, pending messages in a column are red. Each message is routed to different staff members, which shows where the last stop is with a specific staff member. With the pending messages, I want to find the last stop of the message was with and I want to count how many pending messages does a particular staff member have. I have 5 staff members to tally pending messages for. Please e-mail with the formula. My e-mail is pierrevu1013@gmail.com.
Thank you
Hi
I stumbled on this page while looking for tips on excel. I am creating an Excel template for my application users to upload it with data. To keep the data clean, I am creating validations. One place I need help is I have two data fields V2 and W2, either one of them is a required field. If the user didn't enter any data in V2 then I need to highlight W2 field. Does anyone how I can achieve this. Thank you in advance.
I cringed at this storyline, but thanks nonetheless