Replace blanks spaces in seconds

Hi! Have you ever had a sheet in Excel with a bunch of blanks you want to replace with a specific entry? Perhaps 0 (zero)? Let me show you the fastest way to do it.

This week’s video:

Let’s start with the data. A simple grocery list suffices since this is the story of my life right now:

Replace the empty spaces with a specific quantity

What if we want to place a 2 wherever there is an empty space in our grocery list?

First, we select the whole table using Ctrl (or ⌘ for Mac) + A.

While the data is selected, hold Ctrl + G to open the “Go to” menu and click Special in the bottom left corner. This opens the Go To Special menu

Select “Blanks” in the “Go To Special” menu and click OK. This will select all the blank spaces in our selected table

We want to replace the blank spaces with the number 2.

While all the blank spaces are selected, click inside the formula bar and type “=2”, then press Ctrl + Enter (^ + Return for Mac). All the blank spaces are filled with the number 2!

Conclusion

The Paste Special or Go To Special menu in Excel is the Galapagos Island of hidden functionality (NERD ALERT!). Feel free to explore it since getting a social life is expensive these days.

Have a good week!