How to enter/display text or message if cells are blank in Excel?

This method demonstrates how to quickly locate and select all blank cells within a specified range, so you can enter custom text into them in one go. This is ideal for situations where you need a one-time replacement—such as marking missing data before sending reports. However, this approach is static: if new blank cells appear later, you'll need to repeat the process.

1. Select the range where you want to identify blank cells and display a message or text in them. It's recommended to avoid selecting extra rows or columns beyond your actual dataset, as that could result in unnecessary changes.

2. Go to the Home tab, then click Find & Select > Go to Special.

3. In the "Go To Special" dialog, check the Blanks option and click OK.

Now, all blank cells within your selected range are highlighted and ready for editing.

4. Type the text you want to display in all the blank cells—for example, "NO DATA". Then press Ctrl + Enter at the same time. This action inputs your text into every selected blank cell simultaneously.

All targeted blank cells will now show the specified text. Note: This method overwrites the selected blank cells, so it does not display dynamic messages if further data is deleted to create new blanks. For ongoing needs, consider a formula or VBA-based method.

If you regularly need to fill blank cells across large datasets or need to apply consistent values, Kutools for Excel offers a dedicated Fill Blank Cells feature that simplifies and accelerates the process, minimizing manual effort and helping avoid mistakes common with manual methods.

1. Highlight the data range where you want to show custom text or messages in all blank cells. This is especially useful for large tables or reports where consistency is needed.

2. Navigate to the Kutools tab, then choose Insert Tools > Fill Blank Cells.

3. In the Fill Blank Cells dialog box, select the Fixed value option. Enter the desired text (such as "NO DATA" or "Missing") in the Filled value field. Click Ok.

Kutools fills all blank cells with your specified value at once. This feature reduces the risk of skipping cells or making typos typical of manual processes. If your dataset updates frequently, remember that this method directly fills the current blanks; new empty cells will require you to reapply the tool as needed.

Demo

If you prefer a dynamic display, where cells automatically show your chosen message whenever they are empty, you can use the IF function. This is especially useful when data entry is ongoing or frequently updated—formulas will automatically adjust the display as values are added or removed.

Select a blank cell where you want the output to appear (this should be the cell corresponding to the first item in your original range). Enter the following formula:

=IF(A1="","NO DATA",A1)

Drag the fill handle (a small square at the bottom right of the cell) down or across to fill the rest of the range where output is needed. This will create a new range that mirrors your original data, displaying "NO DATA" for blanks and the actual value for filled cells.

Note: In this formula, A1 refers to the original cell being checked, and "NO DATA" is the message displayed for blanks. Adjust these as needed for your specific data.

Tip: You can copy this formula to a new worksheet or an adjacent column to avoid overwriting your original data, and use additional formatting as necessary. Be mindful that formulas will not literally fill actual data blanks but display specified text based on the corresponding cell's value.

Conditional formatting provides a quick and visual way to identify blank cells by automatically highlighting them or displaying custom text or icons. This approach is well-suited when you want to visually flag missing data for yourself or others without altering any actual cell values. It works dynamically—if the data changes, the formatting updates automatically.

1. Select the range where you want to identify or visually indicate blank cells.

2. Go to the Home tab, click Conditional Formatting > New Rule.

3. In the New Formatting Rule dialog, select Use a formula to determine which cells to format.

4. Enter this formula, adjusting the cell reference as needed (assuming your selection starts at A1):

=ISBLANK(A1)

5. Click Format… and set your preferred formatting—such as a fill color, font color, then click OK.

Note: Conditional formatting does not fill in data but provides strong visual cues or can display text overlays, helping users spot missing values quickly and efficiently. For data validation or summary reports, this method is particularly valuable when you don't want to alter the actual content of the worksheet.

If you need to automate the process of filling blank cells or repeatedly apply it to different ranges, using a VBA macro is a robust option. VBA allows you to efficiently process large datasets, standardize the process across different sheets, or apply specific message criteria with minimal manual effort. This approach is especially beneficial when other users will perform the same task repeatedly or when integrating into batch reports.

1. Click Developer > Visual Basic to open the VBA editor. In the new Microsoft Visual Basic for Applications window, click Insert > Module. Paste the following code into the module:

Sub FillBlanksWithMessage()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Sigh As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to fill blanks with message", xTitleId, WorkRng.Address, Type:=8)
    
    Sigh = Application.InputBox("Enter the message/text to fill in blank cells", xTitleId, "NO DATA", Type:=2)
    
    For Each Rng In WorkRng
        If IsEmpty(Rng.Value) Then
            Rng.Value = Sigh
        End If
    Next
End Sub

2. To run the macro, press F5 key or click Run. The macro will prompt you to select a range, and then ask for the custom message. After confirming, all blank cells in your chosen range will be filled with the message you specified.

Tip: Remember to save your work before running macros, as changes cannot be easily undone. This method overwrites existing blank cells, so use caution if you might need to keep any cells empty for later data entry.

Advantage: Highly efficient for large sets of data or for recurring processes. Limitation: Not suitable for users unfamiliar with enabling macros or in restricted environments where VBA is disabled.

It is important to select the appropriate method depending on your workflow and needs. For static, one-time data marking, built-in options like Go To Special or Kutools provide quick solutions. For ongoing or dynamic needs, use the IF function or conditional formatting for automatic updates. VBA is ideal for automation and recurring processes, particularly in large or complex workbooks. Always back up original data before applying bulk changes, and test each method on a small range first to avoid unwanted results. If you encounter issues—such as formulas not updating, macros being disabled, or conditional formatting not displaying as expected—double-check your data range references, macro security settings, and formatting rules. With these solutions, you can efficiently enhance data clarity and reporting precision in your worksheets.

How to prevent saving if specific cell is blank in Excel?

How to highlight row if cell contains text/value/blank in Excel?

How to not calculate (ignore formula) if cell is blank in Excel?

How to use IF function with AND, OR, and NOT in Excel?

How to display warning/alert messages if cells are blank in Excel?

How to delete rows if cells are blank in a long list in Excel?