much ado about nothing

I’m no thespian … but even I can’t resist a good Shakespeare pun!

Today’s post is all about how to find empty cells in your spreadsheet.  You might be giving me a blank stare from behind your computer screen, but if I were you, I wouldn’t a-void this post!  #PunsIntended  This post is chock-full of useful tips that you can apply to projects like data cleaning, volunteer on-boarding, and even getting better donation stats.

Fill in the blank

You’re organizing a big action and you have a LOT of roles to fill, so you harness the energy of your most extroverted volunteer.  Which of the two spreadsheets would you rather give her to get her started?

Number 2, huh?  Yeah, I like number 2, too!  And the best part is that I didn’t have to go in and highlight any of those cells.  I used a Conditional Formatting rule to automatically highlight the cell if it was blank.  As soon as someone types in a cell, it turns back to white.  Here’s how to DIY!

1. Select the entire area where the conditional formatting rule will apply
2. Select “Conditional Formatting” on your main toolbar (under Styles)
3. Under the drop down menu where it says “Format Only Cells With:” select “Blanks”
4. Select the Format button to set styles.  Don’t forget to navigate to the tabs in the dialogue box!

I use this technique for data cleaning all the time – especially when I’m the one setting up the data cleaning project and other people are actually doing the cleaning.  You can imagine that it really helps to have the blank cells waaay more visible!

Running on empty

Sometimes you need to know if a cell is empty.  Why?  Because you’re going to give it a snack, of course!  (just kidding).  Usually, it’s to use in a particular formula.  Like, “if this cell is blank, do this other thing!”  Good thing there’s a formula for that!

=ISBLANK(      )

The ISBLANK gives you an answer in the form of “TRUE” or “FALSE.”  So, if the cell HAD STUFF IN IT, the formula would say “FALSE” aka Not Blank.

For advanced Excel users, here’s a warning for ya: ISBLANK will return FALSE if it encounters an “empty string” even though most people would consider empty strings to be blank.  To get around that “gotcha,” you can use the “Length” function, tutorial here.

Once you know whether a cell IS BLANK (or not), then you can plug that answer into another formula, generally an IF formula.  We’re not going to get into that all today, but I did find a very interesting tutorial here that would be an excellent resource for folks on-boarding volunteers in Excel or in a Google Sheet!

A challenge

Excel and Google Sheets automatically treats blank cells as “null,” not as Zeros.  So, if you are taking an average of donations, they simply won’t count people who have an empty cell where their donation amount could be (aka people who haven’t donated).  Usually that is for the best!  However, if you WANT to count those donations as “zero dollars” to get a different perspective on your “average donation rate,” you will need to replace those Blanks with Zeros.  Sure, you could sort your data and then manually type or copy zeros in the empty cells, OR instead, you can use IF( )  and  ISBLANK( ) formulas to do the work for you.  If you figure out how to do it, email me at hello@thedataarealright.blog for a special shoutout!

Well, we didn’t turn water into wine, or tin into gold, but I’ll be damned if we can’t make SOMETHING out of NOTHING.  Have a great holiday weekend, friends!