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 with the same text or message, Kutools for Excel offers a dedicated Fill Blank Cells feature. Instead of locating blank cells separately or creating helper formulas, you can select the target range, specify the text once, and fill all current blanks directly.

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, and click OK.

Kutools fills all current blank cells in the selected range with your specified text at once, without requiring Go To Special, Ctrl + Enter, helper formulas, or VBA code. If new blank cells appear later, simply reapply the feature when 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 dynamic visual way to identify blank cells without altering the actual cell values. This approach is useful when your goal is to flag missing data rather than insert text into the blank cells themselves.

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 or font color, then click OK.

Note: Conditional Formatting does not enter a message into the blank cells. Instead, it dynamically highlights or formats them so missing values are easier to identify. If the cell later receives a value, the visual indicator updates automatically.

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 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 the chosen range will be filled with the specified text.

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 recurring processes. Limitation: Requires VBA knowledge and macro permissions, so it may not be suitable in environments where macros are disabled.

Choose the method according to the result you actually need. Go To Special provides a built-in option for one-time direct filling, while the IF function is better when the message needs to update dynamically and Conditional Formatting is appropriate when you only want to visually flag missing values. VBA provides programmable automation for advanced workflows. For regular direct filling of blank cells with consistent text, Kutools for Excel offers the simplest point-and-click workflow without blank-cell selection steps, helper formulas, or macro code.

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?