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.
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.
- 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,
- 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.
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:
- 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:
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.
- How to Fill Blank Cells with Formula in Excel
- How to Fill Blank Cells with Value Below in Excel
- How to Fill Blank Cells with Value Above in Excel
- How to Fill Blank Cells with 0 in Excel
- How to Fill Blank Cells with N/A in Excel
- Fill Blank Cells with Text in Excel
- How to Fill Blank Cells with Color in Excel
- How to Fill Blank Cells with Value from Left in Excel
- How to Fill Empty Cells with Default Value in Excel
- How to Fill Empty Cells with Last Value in Excel