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