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.
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.
- 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.
- 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 open a box named ‘Go To Special’ and 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. And 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
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.
- How to Return Value if Cell is Blank (12 Ways)
- Find, Count and Apply Formula If a Cell is Not Blank (With Examples)
- How to Highlight Blank Cells in Excel (4 Fruitful Ways)
- Null vs Blank in Excel
- How to Delete Blank Cells in Excel and Shift Data Up
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.
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.
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
- Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- How to Ignore Blank Cells in Range in Excel (8 Ways)
- If Cell is Blank Then Show 0 in Excel (4 Ways)
- How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas