Why make columns required in Excel?
When users submit spreadsheets for data imports, missing fields like customer emails, product IDs, or quantities cause import pipelines to reject entire batches. Enforcing required fields at the spreadsheet level stops errors before export.
Eliminate missing data
Ensure critical database identifiers are never submitted as blank cells.
Stop broken imports
Prevent downstream system rejections caused by unexpected empty rows.
Enforce data integrity
Guide contributors to complete mandatory forms accurately on the first pass.
How to set up mandatory fields in Excel
Data Validation only checks a cell when someone types in it. A cell nobody touches is never checked, so a validation rule on its own can't stop a required column being left blank. Instead, combine three things: highlight required cells that are still empty, count them, and stop entries made only of spaces.
Highlight required cells left blank
Use conditional formatting to colour a required cell red once the rest of its row has been started.
Select the required column from row 2 down, for example A2:A1000. On the Home tab, choose Conditional Formatting, then New Rule, then 'Use a formula to determine which cells to format'. Enter =AND(COUNTA($A2:$H2)>0,LEN(TRIM(A2))=0), where A to H are the columns of your table, and choose a red fill. Empty rows below the data stay clear, and a row someone has started shows its missing values at once.
Count the missing values
Put a counter above the table or on an instructions tab, so whoever fills it in can see when they're done.
If column B is one every real row has, such as Name, enter =COUNTIFS(B2:B1000,"<>",A2:A1000,"") with the label "Missing required values". It counts rows that have a name but no value in column A. Ask people to check it shows 0 before they send the file.
Stop entries made only of spaces
A cell holding a space looks filled in but imports as blank. Data Validation can catch this.
Select A2:A1000 again. On the Data tab, click Data Validation, choose Custom under Allow and enter =LEN(TRIM(A2))>0. Untick Ignore blank. Unticking it also stops someone clearing a value by editing the cell, though pressing Delete still gets past it, which is why steps 1 and 2 matter.
Set a clear error message
On the Error Alert tab, choose the Stop style and say what's needed.
For example, title "Customer ID required" and message "Enter the customer ID. It cannot be left blank or contain only spaces." A message that names the column is fixed far faster than "Invalid entry".
Understanding the highlight formula
COUNTA($A2:$H2)>0 - Something on this row has been filled in, so it's a real record
LEN(TRIM(A2))=0 - The required cell is empty, or holds only spaces
$A2 rather than $A$2 - The row number isn't fixed, so each row checks itself
Common validation mistakes to avoid
These are the mistakes that let blank required values through:
Expecting Data Validation to catch empty cells
Validation runs when a value is typed or edited. Cells nobody visits are never checked, whatever the Ignore blank setting, so use the highlight and the counter to find them.
Applying rules to the header row
If you select A1:A1000 instead of A2:A1000, the rules apply to the header too. Always start from row 2.
Fixing the row in the formula
Written as $A$2, the rule checks cell A2 on every row. Leave the row number unfixed (A2 or $A2) so each row checks itself.
Unclear error messages
'Error' or 'Invalid entry' doesn't say what to do. Name the column and what's needed: 'Customer ID is required.'
Not covering enough rows
Rules on A2:A50 do nothing for row 51. Cover more rows than anyone will need, such as A2:A1000.
Pasting over the rules
A normal paste brings the copied cells' formatting and validation with it, replacing yours, and pasted values aren't checked. Ask people to use Paste Special, then Values, and keep the counter as the final check.
What Excel can't do
- Block saving: Excel will save a file with blank required cells. A macro can block it, but files with macros are often stopped by email filters and security settings, so it's rarely worth it.
- Survive determined editing: Anyone can remove the rules, and sheet protection isn't security-grade.
- Travel with the CSV: Formatting and validation aren't part of a CSV file, so they're gone once it's saved.
Required field best practices
Mark required columns visually
Add an asterisk (*) to mandatory column headers. Use a different background color (light red, yellow) for required columns. Users should instantly see which fields must be filled.
Write clear, specific error messages
Instead of 'Invalid entry', say 'First Name is required. Please enter the customer's first name before proceeding.' Users need to know WHAT is wrong and WHY.
Test it like someone filling it in
Fill in a row but leave the required cell blank, type only spaces, and paste a few rows. Check the highlight, the counter and the error message all behave as expected.
Cover enough rows for future growth
Apply the highlight and validation to at least rows 2-1000, and point the counter at the same range, so later rows are covered too.
Combine validation with sheet protection
Unlock the data entry cells (Format Cells, Protection, untick Locked), then protect the sheet (Review → Protect Sheet) so the header row and the counter can't be changed by accident.
Include instructions on the worksheet
Add a note near the counter: 'Columns marked * are required. Check Missing required values shows 0 before sending this file.'
Test with actual users before deployment
Let someone unfamiliar with the spreadsheet try filling it out. See if they understand which fields are required and what the error messages mean.
Consider the alternative: web forms
When the data matters, a form that checks every row is more reliable than a spreadsheet: it can refuse to produce the file until every required value is there.
Tired of fixing blank spreadsheet cells?
Send a form instead of a spreadsheet. Required columns are checked on every row, including ones nobody touched, and the CSV can't be downloaded until they're filled in.
Start building free