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

In most large data sheets of 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.


Download Practice Workbook

You can download the practice workbook from here:


3 Simple Ways to Fill Bank Cells with Dash in Excel

I have a customer data table where some cells are blank as some data is missing. But 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.

Fill Blank Cells in Excel with 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:

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

Fill Blank Cells in Excel with Dash

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

Fill Blank Cells in Excel with Dash

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

Fill Blank Cells with Dash Using Go To Special Command

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

Fill Blank Cells in Excel with Dash

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

Fill Blank Cells in Excel with 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


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.

Use the Find and Replace Tool

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

Use the Find and Replace Tool

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

Use the Find and Replace Tool

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

Use the Find and Replace Tool

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

Use the Find and Replace Tool


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.

Use a VBA Code to  Fill Blank Cells with Dashes

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

Use a VBA Code to  Fill Blank Cells with Dashes

  • 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

Use a VBA Code to  Fill Blank Cells with Dashes

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

Use a VBA Code to  Fill Blank Cells with Dashes

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

Use a VBA Code to  Fill Blank Cells with Dashes

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

Use a VBA Code to  Fill Blank Cells with Dashes


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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Osman Goni Ridwan
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo