Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Dealing with blank cells in Excel sometimes causes problems. Sometimes it presents challenges as well. In this particular worksheet, Column I has some blank cells and with good reason. It indicates the benefits package that some of the employees have here and some don't. M presuming meaning Medical, DM Dental or Medical, and the cells that are blank indicate there are no benefits at all. Nothing wrong with leaving them blank maybe, but on the other hand, you might want to put in a different word. Now possibly we could sort these to pull them altogether, but another technique that's available and pretty easy to use too is let's just click the column here, column letter I, and on the Home tab in the Editing group under Find & Select, you will see a choice called Go to Special.
There's strange set of choices here, but there is one called Blanks. And when we click OK, we do see that the blank cells have been highlighted. Your first thought might be well, how about Column I after the data, after row 750, whatever it is we've got here? It doesn't highlight the data in the remaining part of the worksheet fortunately, because that's many, many rows down there. So for the moment, these are all selected and we might want the word None to go in here or NA, something like that, either way. We will put in NA.
And you may well know one of our top ten tricks. If we got cells highlighted in different locations, if we want the same information to go into all of them, we need only after selecting them, type an entry and then press Ctrl+Enter, so we filled in all these blank cells with NA, which for our purposes here perhaps is more appropriate. There are some other issues associated with blank cells as well. Here is one. Here is a formula right here that's averaging the sales. Let's say that's for a five-day period for a given salesperson, and there is the identical information here.
Now, one scenario might say, well, this person was very unproductive and maybe these are Monday through Friday. Unproductive on Tuesday and Thursday. So maybe we should have put in 0 there. How does the Average function work? It tabulates the total and divides by the number of cells that have entries, those are blank cells. So depending upon situations and circumstances, we might here too want to consider what goes into the blank cells. In the two examples here, I am just going to put in 0 manually, highlight them both, type 0 and press Ctrl+Enter, and that's quite a bit different.
And it's just a reminder and awareness, if we are adding data, it doesn't make any difference whatsoever, but when averaging data, you do have to think out the implications of blank versus 0 and sometimes you want to put in 0 and sometimes you don't. If this person just wasn't very productive on the two days, then rightly we should put in 0s there. Whereas if the person wasn't available, was on sick leave or vacation or something, it's more appropriate to leave them blank. But at least think out the differences there. Another way to use blanks, and it's a little bit unusual, but you might have seen the option under Paste Special.
Maybe what we would like to do is to show in this column the latest sales or contact totals here, and we would like to copy this information into Column C, and we could sometimes go either direction. But if we copy this information, do we want the spaces to wipe out the data over here? Well, if we do a regular copy, how about just a copy and then right-click here and paste? Keep your eye on a couple of options there. The space has essentially wiped out the data that had been there.
I just did an Undo so we can see how they had been. You do have the option when you are copying data. Under Past Special, you can skip blanks. Now, before I click OK here, those cells in Column C that have blanks next to them are the 77, the 66, and the 55, so easy to remember. So they will not change. In other words, we are skipping, copying data from the blanks on top of the other data to right.
I seem to need this feature about once every two years. I still think somewhere out there is a stronger example, but be aware that there is that capability. So when you are copying data from one location to another and it does include blank cells, you have the option of not having the blanks overwrite existing data.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.