Fill Blank Cells with Dash (-) in Excel

by

Blank cells are often a part of the data that you download from databases or from the web. Or it could be a result of the data not being available/entered while the data entry was being done.

While blank cells are harmless in general, you may need to treat these if you want to sort your data or use it in formulas. Sometimes, you just need to replace these blank cells with a dash as it looks better when printed.

In this tutorial, I will show you three simple ways you can use to fill all the blank cells with a dash (-).

Find and Replace to Fill Blank Cells with Dash

The easiest way to fill all the blank cells in your data set with a dash would be by using the Find & Replace functionality.

Below I have a data set, where I have some blank cells that I want to fill these blank cells with a dash instead.

Dataset with blank cells

Here are the steps to do this using Find and Replace:

  1. Select the data set that has the blank cells that you want to be filled with the dash
  2. Click the Home tab
Click the Home tab
  1. In the Editing group, click on Find & Select
  2. In the options in the drop-down, click on the Replace option. This will open the Find & Replace dialog box
Click the Replace Option
  1. Leave the ‘Find what’ field empty (as we need to find all blank cells)
Leave find what empty
  1. In the ‘Replace with’ field, enter – (a dash character)
Enter dash in replace with
  1. Click on ‘Replace All’
Click on Replace all button

The above steps would instantly fill all the blank cells with the dash symbol.

Dataset with dash

There is a possibility that the dash character inserted in these blank cells is not aligned to the center. So you may have to select the entire data set and fix the alignment.

Important Note: For this method to work, it is important that the cells that appear blank are actually empty (i.e., there are no space characters in them). If there are any space characters, these would not be considered blank, and Find and Replace would not fill these cells with a dash

Also read: How To Add Text To The End Of A Cell In Excel

Fill Blank Cells with Dash using the ‘Go To Special’ Technique

Another way to quickly fill blank cells with dash is by using the Go To Special method.

With this method, we first select all the cells that are blank, and then manually enter a dash (-) in them.

Below I have a data set where I have some cells that are blank, and I want to fill these with a dash.

Dataset with blank cells

Here are the steps to do this using the ‘Go To Special’ technique:

  1. Select the data set where you have the blank cells
  2. Press the F5 key on the keyboard (this will open the Go To dialog box). You can also get the same option when you go to Home –> Editing –> Find & Select –> Go To
  3. Click on the Special button. This will open the ‘Go-To Special’ dialog box
Click the Special button
  1. In the Go To Special dialog box, select ‘Blanks’
Select Blanks in the Go To Special dialog box
  1. Click OK. Doing this would select only blank cells in your selection (and all the other cells in the data set would be deselected)
All blank cells are selected
  1. With all the blank cells selected, type dash from your keyboard. Note that this would only enter the dash symbol in the active cell (while the other blank cells would stay selected)
Enter dash in the active cell
  1. Hold the Control key and press the Enter key

The above steps would enter the dash (-) in all the blank cells in the data set.

Dash inserted in all the cells

The trick here is in step 7, where you hold the Control key and press the Enter key.

When you do that, the content of the active cell is entered in all the selected cells.

Since we entered the dash character in the active cell in step 6, the same is entered in all the blank cells (which were the only selected cells after step 6).

Note: In step 6, when you’re entering the dash character in the active cell, it is important that all the other blank cells stay selected. If by mistake you have clicked anywhere in your worksheet and all these cells are now not selected, you will have to repeat the steps again.

One benefit of using the Go To Special technique over the Find and Replace method is that with the Go To special method, all the blank cells get selected.

While we used it to enter a dash in these blank cells in the steps above, you can do anything you want. You can color these cells, delete the rows that have blank cells, or enter anything else other than a dash.

Also read: How to Replace Zero with Dash in Excel?

VBA Code to Enter Dash in Blank/Empty Cells

And finally, you can use a simple VBA code that would allow you to first select a range of cells in which you want to fill the blank cells with the dash, and then do it as soon as you run the code.

Below is the VBA code that you can use:

'Code developed by Steve Scott from https://spreadsheetplanet.com/
Sub Fill_Blank_Cells_with_Dash()
Dim myrng As Range
Set myrng = Application.InputBox("Select the Range to fill blank cells with dash", "Fill Blanks with Dash", , , , , , 8)
For Each Cell In myrng
    If Cell.Value = "" Then
        Cell.Value = "-"
    End If
Next
End Sub

When you run the above code, it’ll show you an input box where you can manually select the range of cells that have blank cells that you want to fill with the dash.

Input box to select the range

When you specify the range in the input box and then click OK, it would instantly fill all the blank cells with a dash character.

The above VBA code goes through each cell in the selection and checks whether the value is blank or not (this is done using the if statement used in the code).

In case the cell is not blank, nothing happens and the code moves on, and in case the cell is blank it is filled with the dash (-).

This VBA method is best used in scenarios where you need to do this quite often. so you can keep this VBA code handy, and then run it by adding it to a module in the VBA editor.

Or even better, you can save this VBA code in the Personal Macro Workbook, which would make this available in all the Excel files in your system.

You can then run this code by using a keyboard shortcut, or adding it to the Quick Access Toolbar and running it with a single click.

Below is a video that covers everything you need to know about the Personal Macro Workbook, and how to make a VBA code available in all the Excel files.

So these are three simple methods you can use to quickly fill blank cells with a dash character.

The fastest would be by using Find and Replace, which quickly finds all the blank cells and then replace the content of these cells with a dash.

In case you want to do more than insert a dash in the blank cell, such as color/highlight the cell, you can use the Go-To Special method.

And if this is something you need to do quite often, then you can use the VBA code by adding it to the Personal Macro Workbook.

I hope you found this excel tutorial useful.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment