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

Get FREE Advanced Excel Exercises with Solutions!

In most large data sheets in Excel, we may find blank cells. But if we want to sort or filter or do any calculations, we have to fill those blank cells in Excel. This article will show you 3 easy methods to fill blank cells in Excel with a dash.

Table of Contents hide

How to Fill Bank Cells with Dash in Excel: 3 Simple Ways

1. Fill Blank Cells with Dash Using ‘Go To Special’ Command

2. Use the Find and Replace Tool

3. Use a VBA Code to Fill Blank Cells with Dashes

Conclusion

How to Fill Bank Cells with Dash in Excel: 3 Simple Ways

I have a customer data table where some cells are blank as some data is missing. However blank cells may cause errors in doing any sorting, filtering, or calculations. So I have decided to fill the blank cells with a dash in the Excel file. Here I will show you 3 easy methods to fill blank cells in Excel with a dash.

1. Fill Blank Cells with Dash Using ‘Go To Special’ Command

You can use the Go To Special tool of Excel to select the blank cells only. As selecting the blank cells one by one may become clumsy, it would be a great option.

STEPS:

First, select the table of cells from where you want to select the blank cells. Drag your mouse by pressing the right button to select the cells.

Then, go to the top ribbon and press on the ’Find & Select’ option. Here will open a menu, from where you will select the ‘Go To Special’ option.

After pressing the ‘Go To Special’ option, there will be a box named ‘Go To Special’. Select the ‘Blanks’ option from the box and press OK.

Then, you will see that all the blank cells are selected. Now write the Dash character from the keyboard in the first blank cell. Press Ctrl + Enter.

After pressing Ctrl + Enter, you will see all the blank cells are filled with a dash.

Note: While writing the dash in the first blank cell, all other blank cells will remain selected. If anyhow, the selection is gone, then you have to do the procedure from the beginning.

2. Use the Find and Replace Tool

You can use the Find and Replace option of Excel to replace the blank cells with Dash. The steps are shown below,

STEPS:

At first, select all the cells, from where you want to select the blank cells.

Then, go to the top ribbon, and press on the ‘Find & Select’ option. Here will open other options. From the press on the ‘Replace’ option.

There will open a box named ‘Find and Replace’. Here, leave the ‘Find what’ box empty.

And write the Dash character ( – ) in the ‘Replace with’ box. And press on the ‘Replace All’ option.

Now you will see that all the blank cells are filled with a Dash.

3. Use a VBA Code to Fill Blank Cells with Dashes

You can also write a VBA code to fill the blank cells with a dash. The VBA code will be useful if you have to do it in a regular manner. Here I am showing you all the steps how you can write and run a VBA macro to fill the blank cells with a dash:

STEPS:

For this, first, go to the top ribbon and press on the Developer option. And then press on the Visual Basic option from the menu. You can also use ALT + F11 to open the ‘Microsoft Visual Basic for Applications’ window if you don’t have the Developer tab added.

Now, a window named ‘Microsoft Visual Basic for Applications’ will appear. Here from the top menu bar, press on the ‘Insert’ option. And a menu will appear. From them select the ‘Module’ option.

Now, a new ‘Module’ window will appear. And Paste this VBA code into the box.

Sub FillBlankwithdash()
Dim Cell As Range
Dim WorkCell As Range
On Error Resume Next
xTitleId = " Fill Blank With Dash"
Set WorkCell = Application.Selection
Set WorkCell = Application.InputBox("Cell", xTitleId, WorkCell.Address, Type:=8)
For Each Cell In WorkCell
    If Cell.Value = "" Then
        Cell.Value = "-"
    End If
    Cell.HorizontalAlignment = xlCenter
Next
End Sub

To run the code go to the top menu, press on the ‘Run’ option, and here will open some other options and select the ‘Run Sub/UserForm’ also you can simply press F5 to run the code.

After running the code a prompt box will appear to select the data table. So, select the range of data that you will need and press OK.

After pressing OK, you will see all the blank cells are filled with dashes.

Download Practice Workbook

You can download the practice workbook from here:

Fill Blank Cells with Dashes.xlsm

Conclusion

In this article, I have tried to show you the 3 easy methods to fill blank cells in Excel with Dash. 1st two methods are very easy and you can learn them in a very short time. Using VBA code to fill blank cells in Excel with a dash, will help you if you have to do this regularly. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

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

How to Fill Bank Cells with Dash in Excel: 3 Simple Ways

1. Fill Blank Cells with Dash Using ‘Go To Special’ Command

2. Use the Find and Replace Tool

3. Use a VBA Code to Fill Blank Cells with Dashes

Conclusion

Related Articles

Leave a reply Cancel reply