Sometimes we need to fill the blank cells in Excel with formula. In this article, we will show you how to fill the blank cells in Excel with formula with 4 easy approaches. We have also included some relevant concerns at the end of this article. So, letโs get started.
๐ย Download Excel File
Download free workbook from here:
How to Select Empty Cells in Excel
If you want to fill the blank cells in Excel with a formula, you need to select the empty cells first. To do this you need to maintain the following steps.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- Initially, you have to select the entire cells.
- Secondly, press F5.
- Now you will get a dialogue box name Go To.
- Thirdly, click on the Special.
- Next, you will get another dialogue box name Go To Special.
- Fourthly, check on the Blanks.
- Then tap OK.
- Finally, you will get your empty cells selected.
Thatโs how you can select the empty cells in Excel.
Read More: 3 Approaches to Make Empty Cells Blank in Excel
Learn to Fill Blank Cells with Formula in Excel with These 4 Methods
While writing this article, โHow to Fill the Blank Cells in Excel with Formulaโ, weโve considered a dataset that contains approximately 5 columns and 15 rows. In this dataset, we added Salesman Name, Sold Products, Date, and Price to make you better understand. But if you want, you can change the entities and put your own values.
1. Apply Go To Special Feature
You can apply Go To Special option to fill the blank cells in Excel with a formula. It is a very easy task. You can apply this with ease. You just need to follow the below steps to achieve this.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- Primarily, you need to select the entire cells.
- Secondly, just press F5.
- Now you will find a dialogue box name Go To.
- Thirdly, you need to press on Specialย button
- After that dialogue box name Go To Special will appear to you.
- Fourthly, you have to check on the Blanks.
- Now tap OK.
- Afterward, you will get your blank cells selected.
- Fifthly, write the following formula on the cell of B7.
=B6
- Sixthly, click Ctrl+Enter.
- Finally, this will make you fill in empty cells of Excel.
Undoubtedly, this is a very handy way to work on.
2. Fill Blank Cells with Value Below or Above Cell
Filling blank cells with values below or above cells is another technique to fill the blank cells in Excel with formula. You can achieve this by following the steps given below.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- Firstly, select the blank cells consecutively.
- Secondly, write the following formula on the cell of B7.
=B6
- Tap Ctrl+Enter.
- Consequently, you will see the desired result you wanted to see.
Truly, this approach is straightforward to achieve.
3. Use Find and Replace Command
You can also use Find and Replace command to fill the blank cells in Excel with formula. It is one of the easiest ways. Just follow the below-given steps.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- Initially, Primarily, you need to select the entire cells.
- Secondly, just press Ctrl+F.
- Now you will find a dialogue box name Find and Replace.
- Thirdly, you need to click on Find All.
- Afterward, press Ctrl+A.
- Then tap on Close.
- Fifthly, write the following formula on cell B12.
=B11
- Then press Ctrl+Enter.
- Eventually, you will get the result as well.
Undoubtedly, you can do this very easily.
4. Fill Empty Cells with 0 or Text or Dash or Other Specific Values in Excel
You can fill empty cells with 0 or text or dash or other specific values in Excel. It is one of the methods to fill the blank cells in Excel with formula. You can do this easily if you maintain the following steps.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- Initially, select the blank cells.
- Secondly, tap F2.
- Now you will get a dialogue box name Find and Replace.
- Thirdly, write 0 on Replace with and leave Find whatย empty.
- Fourthly, click on Replace All.
- Eventually, you will see all of the blank cells are filled with 0.
You can also write text on the blank cells.
- Follow the above-mentioned procedures.
- Then you will get a dialogue box name Find and Replace.
- Thirdly, write the desired text you want to write on Replace with and leave Find whatย empty.
- Fourthly, click on Replace All.
- Finally, you will find the required result.
Similarly, you can fill empty cells with a dash or other specific value in Excel. Truly, this approach is very handy to work on.
๐ Read More: 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula
How to Set Cell to Blank in Formula in Excel
If an analysis in Excel does not satisfy our requirements, we may occasionally wish the results to be shown as a blank cell. If there are blank cells or if the computation results in a blank cell, Excel formulas typically produce a value of zero. Weโll examine a special method for setting a cell to blank in an Excel formula using the IF function.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- Primarily, select cell F6.
- Secondly, write the following formula on that cell.
=IF((D6*E6)>200,D6*E6,โโ)
- Thirdly, press Enter.
- Fourthly, use the fill handle icon to drag the formula to the entire column.
- Eventually, you will see the required values as well.
Undoubtedly, you must say that this approach is very easy to set a cell to blank in an Excel formula.
๐ Important Notes
You should be aware of the following things while performing the processes mentioned above:
๐๏ธย You need to be careful while selecting the empty cells in Excel.
๐๏ธย You have to be conscious when you intend to use the Excel functions.
๐ Takeaways from This Article
If we summarize the whole article, we have got some points.
๐ย Initially, we selected empty cells in Excel.
๐ย Secondly, we applied to Go To Special feature to fill the blank cells in Excel with formula.
๐ย Thirdly, we filled blank cells with values below or above cells in Excel.
๐ย Fourthly, we used the Find and Replace command.
๐ย Fifthly, we tried to fill empty cells with 0 or text or dash or other specific values in Excel.
๐ย Finally, we tried to show you a method for setting a cell to blank in an Excel formula.
Conclusion
We wish that you would be able to fill the blank cells in Excel with formula using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
Related Articles
- 10 Quick Tricks to Remove Blank Lines in Excel
- How to Determine and Count If Cell Is Not Blank in Excel
- 9 Quick Tricks to Delete Row If Cell is Blank in Excel