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.


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.

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:

  • 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 be a box named ‘Go To Special’. 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. 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 procedure from the beginning.

Read More: How to Fill Blank Cells in Excel with Go To Special


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

Read More: How to Find and Replace Blank Cells in Excel


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

Read More: How to Fill Blank Cells with Value Above in Excel VBA


Download Practice Workbook

You can download the practice workbook from here:


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.


Related Articles

Osman Goni Ridwan
Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo