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.


Download Practice Workbook

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


4 Suitable Ways to Fill Blank Cells in Excel

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.

Fill Blank Cells in Excel

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

Using Fill Command to Fill Blank Cells

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.

Repeat Using Fill Command to Fill Blank Cells

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

Flash Fill Command to Fill Blank Cells

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

Have a look at the final output.

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


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.

Using Go to Special Tool to Fill Blank Cells

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

Select Blank Cells Using Go to Special Tool to Fill Blank Cells

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

Insert Formula to Fill Blank Cells

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

Output of Using Go to Special Tool to Fill Blank Cells

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.

Read More: Fill Blank Cells with Text in Excel (3 Effective Ways)


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.

Using Excel Power Query Editor

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

Choose Range Using Excel Power Query Editor

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

Opening the Power Query Editor

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

Applying Fill Command in Power Query Editor

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

Read More: How to Fill Empty Cells with Default Value in Excel


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.

Using Excel VBA to Fill Blank Cells

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

Insert Codes in VBA to Fill Blank Cells

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.

Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)


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.

Fill Blank Cells in Excel with 0 / Dash

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

Read More: Fill Blank Cells with Dash in Excel (3 Easy Ways)


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.

Fill Blank Cells in Excel with Value from Left


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 ExcelDemy to explore more. Happy Excelling 🙂


Further Readings

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo