How to Fill Blank Cells with Formula in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with Excel worksheets sometimes you will find blank cells between the dataset. For working advantages or while printing the dataset you might need to fill those blank cells. In this article, I am going to share with you some quick methods to fill blank cells with formula in excel. 


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Simple Methods to Fill Blank Cells with Formula in Excel

In the following dataset, I am describing 2 simple methods to fill blank cells with formulas in excel. Suppose we have a dataset of a grocery shop containing the Date of sales, Product Code, Product Name, and Sales Amount of products. Now we are going to fill the blank cells with formulas in the date column. 

Fill Blank cells with Formula in Excel


1. Use Go to Special Feature with Formula to Fill Blank Cells in Excel

With the “Go to Special” feature we will select blank cells from the dataset and using the formula the blank cells will be filled. Follow the steps below-

Step 1:

  • Select the whole dataset.
  • Press F5 to open the “Go to” dialog box.

Go to Special Feature with Formula

Go to Special Feature with Formula

  • A new dialog box will appear named “Go to Special”.
  • Select “Blanks” from the options and press OK to continue.

Go to Special Feature with Formula

  • You will find all the blank cells will be selected.

Step 2:

  • While the blank cells are selected press equal sign(=) from the keyboard.
  • Then press the arrow key upwards from the keyboard to select the cells filled.
  • Now we can see that cell reference is mentioned in the active cell.
=B5

  • Press Ctrl+Enter to get the result.
  • Thus you will see all the blank cells are filled by getting data from the reference cell.

Go to Special Feature with Formula

Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)


Similar Readings


2. Apply Find and Replace Command with Formula to Fill Blank Cells in Excel

This method is quite similar to the previous one. In this method, we will use the “Find and Replace” command to select blank cells and then use formulas to fill those blank cells in the dataset.

Step 1:

  • Choose the whole dataset from the workbook.
  • Press Ctrl+F to open the “Find and Replace” dialog box.

Find and Replace Command with Formula to Fill Blank Cells in Excel

  • From the “Find and Replace” dialog box click on “Find All”.

Find and Replace Command with Formula to Fill Blank Cells in Excel

  • Then press Ctrl+A to select all the blank cells.
  • Press Close.

Find and Replace Command with Formula to Fill Blank Cells in Excel

  • As you can see, we have successfully selected all the blank cells from the dataset.

Step 2:

  • Now press equal sign(=) from the keyboard while the blank cells are selected.
  • Click the up arrow key(↑) from the keyboard to get the cell reference.
  • The formula in the associated cell is,
=B10

Find and Replace Command with Formula to Fill Blank Cells in Excel

  • Press Ctrl+Enter to fill all the blank cells.
  • Thus we will get all our blank cells filled with data just by some simple click.

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)


Things to Remember

  • If you are using “Mac” then press “⌃ + Gto open the “Go to” dialog box. 

Conclusion

In this article, I have shown some easy methods to fill blank cells with formulas in excel. Take a tour at the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo