When you work with data, you often want to select certain cells, for example all blank cells. You might want to fill them or just mark them. In this article we’ll learn how to select all empty cells.
How to select all blank cells
Using “Go To Special” could be helpful:
- Select the range of cells you want to work with, also cells with content.
- Go to “Find & Select” in the Home ribbon.
- Click on “Go To Special”.
- Select “Blanks”.
- Click “OK”.
Now, you can work with all the empty cells at the same time as they are marked. If you want to fill them with text – let’s say “Empty Cell”, type “Empty Cell”. Instead of just pressing Enter, press “Ctrl + Enter”. Now all previously empty cells show the text “Empty Cell”.
Example: How to fill each empty cell with the content from the next not blank cell above
Let’s have a look at an example: Oftentimes, you got a column of data as shown in the picture above. In column C you can see the color of cars. Now we want to copy the color from the next filled cell above to each blank cell below so that it says ‘Red’ in cell C4; C5 should stay with blue whereas C6 and C7 should say Blue as well.
- First, we need some preparation: Write this formula in cell E2: ‘=E1’ and copy it. You can do this with any cell but it should link to the cell above.
- Follow the steps 1-5 as described. Make sure that cell E2 is still copied.
- After you are done with steps 1-5, cells C4 and C6:C7 should be selected.
- Press paste (Ctrl + v).
