How to display warning/alert messages if cells are blank in Excel?
Let's say you have designed a questionnaire in Excel, as illustrated in the screenshot on the left.
Now, you would like Excel to provide a warning message or alert if there are any unanswered questions. How can you achieve this efficiently? This article demonstrates several practical methods to automatically display warning or alert messages if cells are left blank within a specified range in Excel. You can choose a method based on your intended use, ease of implementation, and specific needs.
Display warning/alert message if cells are blank with ISBLANK function
Display warning/alert message if cells are blank with a help column
Display warning/alert message IF cells are blank with VBA
Show an immediate warning if a cell is left blank using Data Validation
Display warning/alert message if cells are blank with ISBLANK function
To quickly monitor for unanswered questions in a specific range, you can create a dynamic warning message using the ISBLANK and COUNTBLANK functions, which provides a summary alert when any cells remain blank.
1. Click in an unused cell where you want to display the warning message, for example, C18, then enter the formula below:
="There are still "&COUNTBLANK(B1:B16)&" questions you have not answered!"2. Press the Enter key. The cell will now display a message updating the count of unanswered entries within your specified range.
Note: In this example, the range B1:B16 refers to the column containing your survey answers. You may adjust the range based on the actual location of your data.
3. To increase the visibility of your warning, select the output cell, then on the Ribbon go to Home > Font Color > Red, click Bold, and optionally adjust the font size. This will ensure the alert stands out for users completing the form.
Usage Scenarios & Tips: This approach is suitable for dashboards, forms, and reports where it is helpful to have a summary warning rather than alerting at every blank cell. Ensure that users can see and understand the summary warning; you may want to place it near the submit button or in a prominent area. For best results, lock the formula cell so recipients cannot accidentally delete it.
Quickly enter dash or certain text of NA into all blank cells of selected range in Excel
Kutools for Excel's Fill Blank Cells utility can help you quickly enter a certain text, such as "Warning", into all blank cells in the selected range with just a few clicks.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Display warning/alert message if cells are blank with a help column
Another approach to provide direct feedback is by adding a help column to your sheet. This method will show a warning next to each blank cell, which is particularly useful for emphasizing exactly which responses are missing in a list or table.
1. In the column immediately to the right of your answers (for example, cell C2 if your answers are in column B), enter the following formula:
=IF(ISBLANK(B2),"Please Answer This Question","")Then drag the fill handle down alongside your data to apply the formula to all relevant rows.
2. To make warnings more noticeable, select the new helper column range and format it (e.g., Home > Font Color > Red, click Bold).
Now, every blank entry in the Answer column will have a clear warning message in the helper column, making it easy to identify omissions at a glance. 
Usage Scenarios & Tips: Use this method for questionnaire forms, attendance lists, or anywhere you want a row-by-row indication of missing input. You can modify the warning text to fit your context. Remember, if you insert or remove rows, ensure the formula covers the new range as needed.
Display warning/alert message IF cells are blank with VBA
For scenarios where you want a dialog box alerting the user immediately about any blanks in a selected range, a VBA solution provides an interactive method. You can use this when distributing templates or automating tasks that require complete data entry before proceeding.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. Go to Insert > Module, and paste the following VBA code into the code area:
VBA: Warn if blank cells exist in specific range
Sub BlackCell()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeBlanks)
If Err = 0 Then
MsgBox "You don't answer all of questions!"
End If
End Sub3. Press F5 or click the Run button to execute the macro.
4. A Kutools for Excel dialog box will pop up. Specify the range you want to check, and click OK to proceed.
If any blank cells are detected in the selected range, a dialog box will appear to alert you:
Notes & Troubleshooting: Always save your workbook before running VBA code to avoid accidental loss of data. If nothing happens when you run the code, ensure macros are enabled in your Excel settings. If you encounter an error or the range selection dialog is not appearing, check that you have pasted all code correctly, and that no other macros are interfering. This technique is best for more advanced users comfortable with enabling and editing macros.
Show an immediate warning if a cell is left blank using Data Validation
Data Validation in Excel allows you to set rules so users must enter something in the required fields. You can configure it to show a warning or even prevent leaving a cell blank.
1. Select the range where you want to enforce non-empty input, for example, B1:B16 in your questionnaire.
2. Click Data > Data Validation > Data Validation from the Ribbon.
3. In the Data Validation dialog, on the Settings tab, set Allow to Custom.
4. In the Formula field, enter the following formula to prevent blanks:
=LEN(B1)>0If applying to multiple cells at once, Excel will automatically adjust the formula for each row.
5. Note to untick Ignore blank checkbox.
6. Switch to the Error Alert tab. Here, you can customize the alert. For example, set the Style to Stop for a strict rule, or to Warning or Information to display a softer prompt. Enter a relevant error message such as "This cell cannot be left blank. Please provide your answer."
6. Click OK to save the rule. When a user tries to leave a cell in the specified range blank and moves to another cell, your custom alert message is automatically shown.
Advantages & Limitations: Data Validation is excellent for forms or templates that will be filled out electronically and where completeness is critical. It offers flexibility between a hard requirement (Stop) or a user warning (Warning/Information). Note that Data Validation can be circumvented by pasting values, so it is not absolute protection. For more robust enforcement, combine with other methods or protect the sheet.
Tip: For extended forms, use Data Validation in combination with conditional formatting to both block blanks and visually flag them. Regularly review your validation rules if you change the structure of your worksheet.
Related Articles
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 enter/display text or message if cells are blank in Excel?
How to delete rows if cells are blank in a long list in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in