Data Clean-Up Techniques: Fill Blank Cells in Excel (4 Ways)

 

Sometimes we keep some blank cells to understand that they will be the first filled ones, have a look at our dataset to understand it. But sometimes it creates problems like while we are filtering or sorting data. So it’s better to fill them and there are easy ways to o it. This article will show you 4 easy ways to fill blank cells in Excel.

---

Table of Contents

Data Clean-Up Techniques: Fill Blank Cells in Excel; 4 Ways

Solution 1: Using Fill Command to Fill Blank Cells

Solution 2: Using Go to Special Tool to Fill Blank Cells

Solution 3: Using Excel Power Query Editor

Solution 4: Using Excel VBA to Fill Blank Cells

Fill Blank Cells in Excel with 0/Dash

Fill Blank Cells in Excel with Value from Left

Conclusion

Download Practice Workbook

Download this file to work with filling blank cells in Excel.

Fill Blank Cells.xlsm

---

Data Clean-Up Techniques: Fill Blank Cells in Excel; 4 Ways

Here’s our dataset that represents some sales representatives’ monthly sold units and amounts. And look, this data contains gaps in the Sales Representative column.

In our example, an entry in column B applies to several rows of data. This type of data arrangement is easy to understand. For example, you can find easily Jane’s Jan, Feb, and March months’ sales. But if you sort this type of list, the blank cells will mess things up, and you will not be able to tell who sold what. See this figure sorted according to Amount.

When data is sorted, you can’t find out the sales representative as there were gaps in this column. We shall explain here 4 ways to solve this problem.

---

Solution 1: Using Fill Command to Fill Blank Cells

If your data is small, you can enter the missing cell values from above manually or by using this command.

Steps:

• Select Cell B6 and choose: Home ➪ Editing ➪ Fill ➪ Down or press CTRL+ D, B6 will show “Jane”.

Now see the cell is filled.

• Then repeat the process after selecting the next empty cell to fill the blank cells by taking the value from above.

• Instead of selecting one by one, we can do it at once. Just select the filled cell and double-click the plus icon (+) of the cell.

Soon after all the blank cells will be filled with the same value.

Have a look at the final output.

---

Solution 2: Using Go to Special Tool to Fill Blank Cells

If your data is large, it is not possible to fill blanks manually. Here’s a better way. We’ll use the Go to Special tool and a simple formula to fill blank cells.

Steps:

• Select the range that has the blank cells (B5:B16, in our example).
• Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special. The “Go to Special” dialog box will appear.

• Select the “Blanks” option and click OK. This action selects the blank cells in the cell range B5:B16.

• Later, directly type an equal sign (=) and then the cell address of the first cell with data in the column-

=B5

• And press CTRL+ENTER.

Soon after, you will find the following result. The Blank cells are filled with data as you expected

If you select the recently filled blank cells, they will show “Formula” in the formula bar rather than data. But we want data in those cells. See the following figure.

• Reselect the original range and press CTRL+ C to copy the selection.
• Choose Home ➪ Clipboard ➪ Paste ➪ Paste Values to convert the formulas to values.

After you complete these steps, the gaps are filled in with the correct information.

---

Solution 3: Using Excel Power Query Editor

Another smart way for a large dataset is to use the Excel Power Query Editor.

Steps:

• Click on any data on the dataset.
• Then click as follows: Data ➪ From Table/Range.

• It will automatically select the data range, just press OK.

In a while, you will get a Power Query Editor like the image below.

• Right-click on the name column and choose Fill > Down from the context menu.

• Soon the cells will be filled like this. Now just click on Close & Load.

A few moments later, it will open a new sheet with the data.

---

Solution 4: Using Excel VBA to Fill Blank Cells

A simple macro can do the operation very first. If you are used to working with VBA then it’s the best method for you.

Steps:

• Press ALT + F11 to open the VBA window.
• Then click as follows to open a new module: Insert ➪ Module.

• Write the following codes in it-

Sub Fill_Blank_Cells()
Dim BCells As Range
For Each BCells In Selection.Cells
If BCells.Value = "" Then
BCells.FillDown
End If
Next BCells
End Sub

• Next, go back to your sheet.

Code Breakdown:

• First, I created a Sub procedure- Fill_Blank_Cells().
• Then declared a variable, BCells as Range.
• Next, used For loop and If statement to select the blank cells and fill them.

• After that, select the cell range and click Developer ➪ Macros.

• Finally, just select the macro name and press Run.

Here’s our expected output.

---

Fill Blank Cells in Excel with 0/Dash

To fill blank cells with 0/dash, using the Find and Replace tool is the best option. Just keep the Find what box blank and type 0/dash in the Replace with box.

Here, I’ve used 0, you similarly can use a dash instead of 0.

---

Fill Blank Cells in Excel with Value from Left

If we need to fill blank cells by taking value from the left then you can apply all the above four methods in the right direction. The steps will be the same, just we’ll have to select cells horizontally or in the right direction. Look, I applied the Fill command following the first method.

---

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to fill blank cells in Excel. Feel free to ask any questions in the comment section and give me feedback. Visit ExcelUntold to explore more. Happy Excelling 🙂