How to fill a default value if cell is blank in Excel?

In many Excel worksheets, you may have to ensure that blank cells are not left empty—but instead display a default or placeholder value. This is a common need when preparing data for reports, ensuring data consistency, or when sharing spreadsheets to avoid confusion or incomplete records. In this article, you’ll find a range of practical methods for filling blank cells with a default value—whether you want to do it for a single cell, an entire range, or across large datasets. Consider each method’s advantages and applicable scenarios to select the best for your specific Excel task.

Fill a default value if the reference cell is blank with formula

Fill a default value if the cell is blank with Find and Replace

Fill a default value if the cell is blank with Go To Special

Fill value above/below or default value if the cell is blank with Kutools for Excel

Use VBA macro to fill blank cells with a specified default value

Fill a default value if the reference cell is blank with formula

When you need to display a specific placeholder or default value in a cell if another referenced cell is empty, you can conveniently use an Excel formula. For example, if you have two columns—say, column A contains responses and column B is set up to mirror those responses but should show “no response” if column A is blank—this formula approach is appropriate.

Applicable scenarios: This technique is ideal when you want to dynamically generate a result column based on the content of another column, especially helpful in forms, feedback lists, or checklists to indicate missing information. The key benefit is that it updates automatically as you change the data in the referenced column. However, remember that formulas do not overwrite the blank cells themselves—they display an alternate value based on another cell’s status.

Enter the following formula in cell B1 (assuming you want to check cell A1):

=IF(A1="","no response",A1)

After typing the formula in B1, press Enter to confirm it. Next, drag the fill handle (a small square at the bottom right corner of cell B1) down to copy the formula to other cells in column B. This extends the logic to the rest of your dataset.

Fill a default value if the cell is blank with Find and Replace

Excel's Find and Replace function provides a quick way to replace all blank cells in a selected range with a prespecified value. This method overwrites any actual blank cells in-place, making it useful for permanently substituting empty cells with data.

Applicable scenarios: It is best used when you want to fill blanks in a static dataset, such as finalizing a data sheet before distribution or printing. Its advantage lies in efficiency for medium-sized tables, but it may not distinguish between cells that look empty but actually contain invisible characters or formulas returning empty results.

1. Select the range of cells where you want to fill blanks. Press Ctrl + H to open the Find and Replace dialog box.

2. Leave the Find what box empty, and enter your desired default value (for example, "N/A" or "0") in the Replace with box.

3. Click Replace All. When prompted, confirm the replacement by clicking OK. All blank cells in your selection are now filled with your default value.

Fill a default value if the cell is blank with Go To Special

Excel's Go To Special feature allows you to select all blank cells in a range at once, making it simple to fill them with any value or even a formula. This method is efficient when working with large or irregularly shaped data ranges.

Applicable scenarios: Use this approach when you want to fill only the truly empty cells (excluding those with formulas that may return blanks), and when you want to enter the same value into all selected cells simultaneously. This is especially suitable for cleaning imported data or prepping a worksheet for further calculations.

1. Select the target data range. Press Ctrl + G to bring up the Go To dialog, and then click Special.

2. In the Go To Special window, select the Blanks option and click OK.

3. Excel now highlights all blank cells in your range. Simply type your default value (for example, "Miss") and then press Ctrl + Enter. This action fills every selected blank cell at once.

Fill value above/below or default value if the cell is blank with Kutools for Excel

If you want the flexibility to fill blank cells with the value from above, below, left, right, a fixed value, or linear values, the Fill Blank Cells feature in Kutools for Excel can simplify the process. It's especially useful when you need to quickly standardize large tables or import data where some cells may be missing values.

1. Highlight the range to process, then go to Kutools > Insert > Fill Blank Cells.

2. In the Fill Blank Cells dialog, choose your desired options depending on whether you want to fill blanks with neighboring cell values or a fixed number/text.

Fill with above, below, left, or right values:

Fill blanks with above value (choose "Based on values" and "Down" options):

Fill blanks with left value (choose "Based on values" and "Right" options):

Fill blanks based on a fixed value:

Fill blanks with linear values:

Use VBA macro to fill blank cells with a specified default value

For Excel users who need to repeatedly fill blank cells with a consistent default value—potentially across very large ranges or in automated workflow scenarios—a simple VBA macro can be an efficient solution. With just a few clicks, you can replace all genuinely empty cells in your selected range with any value you specify, saving time compared to manual operations when dealing with repetitive data management tasks.

Applicable scenarios: Use a VBA macro when you have to perform this operation regularly, handle dynamic or large datasets, or include blank cell-filling in a larger Excel automation process. Not only does this approach offer speed, but it also allows you to customize or extend the VBA logic for more advanced data-cleansing needs. This method is especially handy if the built-in Excel functions or add-ins don’t offer the required flexibility, or if you want to batch process large workbooks.

1. Click Developer Tools > Visual Basic. In the VBA window, click Insert > Module, then copy and paste the code below into the module area:

Sub FillBlanksWithDefaultValue()
    Dim WorkRng As Range
    Dim Cell As Range
    Dim DefaultValue As Variant
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select range to fill blanks", xTitleId, WorkRng.Address, Type:=8)
    DefaultValue = Application.InputBox("Enter the default value to fill blank cells", xTitleId, "", Type:=2)
    Application.ScreenUpdating = False
    For Each Cell In WorkRng
        If IsEmpty(Cell.Value) Then
            Cell.Value = DefaultValue
        End If
    Next
    Application.ScreenUpdating = True
End Sub

2. To run the macro, press F5 or click the "Run" button in the VBA window. A dialog will prompt you to select the cell range to process, and then ask you to enter your desired default value. After confirmation, all blank cells in the specified range will be filled with the value you provided.

Demo: Fill value above/below or default value if the cell is blank with Kutools for Excel