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 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.
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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
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.
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
- Selecting Non-Adjacent or Non-Contiguous Cells in Excel (5 Simple Methods)
- How to Select Multiple Cells in Excel (7 Easy Ways)
- How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)
- Fill Blank Cells with Color in Excel (5 Methods)
- How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)
- Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)
- How to Fill Down Blanks in Excel (4 Quick Methods)