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 formulas 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. Using Go to Special Feature to Fill Blank Cells with Formula 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 to use the Go To Special feature to fill blank cells with the formula.

Step 1:

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

Go to Special Feature with Formula

  • Click on “Special” from the dialog box.

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 with N/A in Excel


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

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 the 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 in all the blank cells.
  • Thus we will get all our blank cells filled with data just by some simple click.


Things to Remember

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


Download Practice Workbook

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


Conclusion

In this article, I have shown some easy methods to fill blank cells with formulas in Excel. Take a tour of 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. 


Related Articles


<< Go Back to Fill Blank Cells | Blank Cells in Excel | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo