4 Easy Ways to Fill Blank Cells in Excel with Formula

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.

Selecting the dataset

  • Now you will get a dialogue box name Go To.
  • Thirdly, click on the Special.

Using Special button to Go To window

  • Next, you will get another dialogue box name Go To Special.
  • Fourthly, check on the Blanks.
  • Then tap OK.

Selecting Blanks and then clicking Ok to Go To Special window

  • Finally, you will get your empty cells selected.

Selecting Empty cells in Excel

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.

Sample dataset containing price list of sold products

APPROACH

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.

selecting the entire cells

  • Now you will find a dialogue box name Go To.
  • Thirdly, you need to press on Special button

Using Special button to Go To window

  • After that dialogue box name Go To Special will appear to you.
  • Fourthly, you have to check on the Blanks.
  • Now tap OK.

Selecting Blanks and then clicking Ok to Go To Special window

  • Afterward, you will get your blank cells selected.

Selecting Empty cells

  • Fifthly, write the following formula on the cell of B7.

=B6

  • Sixthly, click Ctrl+Enter.

Using Excel formula on the empty cells

  • Finally, this will make you fill in empty cells of Excel.

Filing the empty cells with names

Undoubtedly, this is a very handy way to work on.

APPROACH

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.

Using Excel formula on the empty cells

  • Consequently, you will see the desired result you wanted to see.

Filing the empty cells with names

Truly, this approach is straightforward to achieve.

APPROACH

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.

Initially selecting the dataset and then clicking on Find All from Find and Replace dialogue box

  • Afterward, press Ctrl+A.
  • Then tap on Close.

Tapping Close option from Find and Replace dialogue box after selecting the books

  • Fifthly, write the following formula on cell B12.

=B11

  • Then press Ctrl+Enter.

Using Excel formula on the empty cells

  • Eventually, you will get the result as well.

Filing the empty cells with names

Undoubtedly, you can do this very easily.

APPROACH

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.

Selecting Empty cells

  • 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.

Pressing Replace All from Find and Replace window

  • Eventually, you will see all of the blank cells are filled with 0.

Filling the empty cells 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.

Pressing Replace All from Find and Replace window

  • Finally, you will find the required result.

Filling the empty cells with texts

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.

Using the IF function to set cell blank in Excel

  • Fourthly, use the fill handle icon to drag the formula to the entire column.
  • Eventually, you will see the required values as well.

Leaving those cells blank whose corresponding quantity is nil

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

(Visited 25 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo