Sometimes in our Excel dataset, we find many blank cells that will have the same values or same formula. But, it is tiresome and boring to fill every blank cell manually. It would be a lot easier if we could choose all the blank cells and fill them up with their values or formulas without putting individual values in every cell manually. In this article, I will show you how to fill blank cells in Excel with Go To Special.
What Is the Go To Special Command in Excel?
Go To Special command is a special feature to find and select cells from a big range of data according to their criterion. For example, you can find and select all the blank cells from millions of data using this command. Moreover, you can also find and select cells containing the same formula through this command. This is really very handy to manage big-range data.
You can call on this command in three ways. Such as:
- Go to the Home ribbon >> Click on Find & Select under Editing group >> Choose Go To…>> Click on Special… from the Go To window.
- Press the F5 key >> Click on Special… from the Go To window.
- Press Ctrl + G >> Click on Special… from the Go To window.
Here, in our dataset, we have three columns. The first one represents the order ID of a store. The second one represents the customer name and the third one represents the ordered product name. Here are a few blank cells in our dataset. Here, I will show you how to fill these blank cells in Excel with Go To Special. I will show you 3 examples to explain the command better.
1. Using Go to Special Tool to Fill Blank Cells in Excel with Previous Cell Values
Suppose, you have to fill the blank cells with the previous value of your column. You can use Go To Special to do this. Follow the steps below to do this. 👇
Steps:
- Firstly, select the entire range where you have blank cells.
- Go to the Home ribbon >> click on Find & Select under the Editing group.
- Click on Go To… from the Find & Select context menu.
- You can also press Ctrl+G or F5 key to bring in the Go To window.
- Click on Special… from the Go To window. It will pop up in the Go To Special window.
- You can also skip the 3rd,4th, and 5th steps and click on Go to Special… from the Find & Select context menu. It will also pop up the Go To Special window directly.
- Put the radio button on Blanks under the Select options. Click on the OK button.
- Now, you will see all the blank cells are selected from the selected range.
- One of the cells is activated automatically. And the other cells are inactive as they are greyed out.
- Now, put an equal (=) sign. And put the upper arrow(^) which will reference the upper cell as a formula.
- Press Ctrl+Enter. Thus, the formula will be duplicated for every blank cell. And every blank cell will consume the upper cell’s value.
Note:
Here, you can see a special thing. We have put the formula only on the D6 cell which refers to the cell D5, which is “Printer”. But, as we have used cell reference, it didn’t read the value only, but the formula. As a result, in the “Customer Name” column, under the value “Henry”, Excel has put the value “Henry” in the blank cell C6. So, you can see that, it has put the formula using this command and when you click on the cell C6, you will see it refers to the cell C5 at the formula bar. If you click on any blank cell now, you will see that it has consumed its upper cell value.
2. Filling Blank Excel Cells with One Unique Value Using Go to Special
Now, suppose you want to fill blank cells of an entire range with one unique value. Say, you want to fill the blank cells of the ‘Customer Name’ column with ‘David”. Go through the following steps to do so:
Steps:
- Firstly, select the entire range of the ‘Customer Name’ column where you have blank cells.
- Now, go to the Home ribbon >> click on Find & Select under the Editing group.
- Subsequently, Click on Go To… from the Find & Select context menu.
- You can also press Ctrl+G or F5 key to bring in the Go To window.
- Now, click on Special… from the Go To window. It will pop up in the Go To Special window.
- You can also skip the 3rd,4th, and 5th steps and click on Go to Special… from the Find & Select context menu. It will also pop up the Go To Special window directly.
- At this time, put the radio button on Blanks under the Select options. Click on the OK button.
- Finally, you will see all the blank cells are selected from the selected range.
- Now, one of the cells is activated automatically. And the other cells are inactive as they are greyed out.
- At this time, put the value that you want in the blank cells.
- Now, press Ctrl+Enter. Thus, the value will be duplicated to every blank cell. Finally, the result will look like this. 👇
Read More: How to Fill Empty Cells with Default Value in Excel
3. Applying Excel Go to Special for Filling Blank Cells with Different Individual Values
Now, suppose you want to fill blank cells of an entire range with different individual values. Say, you want to fill the blank cells of the ‘Customer Name’ column with ‘David’, ’Mike’, ‘Andrew’, etc. different individual names. Go through the following steps to do so:
Steps:
- First, follow the first 10 steps from the 2nd example. You will get the following result by then.
- Now, apart from the 2nd example, Instead of Ctrl+Enter, press only Enter. It will result in filling only the active cell with the input value. And the active cell will shift to the next blank cell.
- At this time, you can put another individual value in this cell, and afterward, press Enter.
This will result in activating the next blank cell and just like this, you can fill up every blank cell individually with different inputs. Finally, the result will look like this. 👇
Practice Workbook
You can practice by downloading our workbook here.
Conclusion
So, I have shown you how to fill blank cells in Excel with Go To Special. Though, there are several ways to fill blank cells in Excel, using the Go To Special option is one of the easiest and best choices. Please, drop comments, suggestions, or queries if you have any in the comment section below.
