Tanjim Reza
Sometimes in our Excel dataset, we find so many blank cells which 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.
Practice Workbook
You can practice by downloading our workbook here.
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 too. 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.
3 Examples: How to Fill Blank Cells in Excel with Go To Special
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. Fill Blank Cells 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 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 to 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 is referring to the D5 cell, 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 C6 cell. So, you can see that, it has put the formula using this command and when you click on the C6 cell, you will see it refers to the C5 cell at the formula bar. If you click on any blank cell now, you will see that it has consumed its upper cell’s value.
Read More: Fill Blank Cells with Value Above in Excel (4 Methods)
2. Fill Blank Cells with One Unique Value
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 Blank Cells with Value Above in Excel VBA (3 Easy Methods)
Similar Readings
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
- Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
- How to Ignore Blank Cells in Range in Excel (8 Ways)
- If Cell is Blank Then Show 0 in Excel (4 Ways)
- How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
3. Fill 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. 👇
Read More: How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)
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. If you have any further queries or recommendations regarding this article, please feel free to contact me. And if you are looking for many more articles like this, you can visit exceldemy.com.
Related Articles
- How to Fill Blank Cells with 0 in Excel (3 Methods)
- How to Remove Blank Cells Using Formula in Excel (7 Methods)
- Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
- How to Fill Blank Cells with N/A in Excel (3 Easy Methods)
- How to Fill Blank Cells with Color in Excel (5 Methods)
- Fill Blank Cells with Dash in Excel (3 Easy Ways)
