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

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. 

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.

• Click on “Special” from the dialog box.

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

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

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

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

Similar Readings

• How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)
• Fill Blank Cells with Color in Excel (5 Methods)
• How to Skip Blank Rows Using Formula in Excel (8 Methods)
• Fill Blank Cells with Dash in Excel (3 Easy Ways)
• If Cell is Blank Then Show 0 in Excel (4 Ways)

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.

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

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

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

• 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 “⌃ + G” to 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

• How to Fill Blank Cells with 0 in Excel (3 Methods)
• Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
• Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
• How to Make Empty Cells Blank in Excel (3 Methods)
• Fill Blank Cells with N/A in Excel (3 Easy Methods)
• How to Perform If Zero Leave Blank Formula in Excel (3 Methods)