Skip to main content

Data Validation and Controls

Professional spreadsheet models require safeguards against incorrect inputs. Data validation rules—dropdown lists, numeric constraints, date ranges, and custom formulas—prevent errors before they propagate through your entire valuation. When you lock input cells with defined parameters, you create a controlled environment where only sensible assumptions reach your calculations.

Quick definition: Data validation restricts cell entries to predefined formats, ranges, or lists, preventing invalid data from entering your model and corrupting downstream calculations.

Key takeaways​

  • Input validation reduces errors by restricting entries to valid options, dates, or numeric ranges before data reaches formulas
  • Dropdown lists enforce consistency across multiple worksheets and users, eliminating typos and interpretation inconsistencies
  • Custom formulas enable context-aware validation that checks values against other cells or business logic rules
  • Locked inputs with protection prevent accidental overwrites of critical cell references or formula cells
  • Circular reference detection and error alerts catch structural problems during model development
  • User-friendly error messages guide input corrections without requiring formula debugging skills

Setting up input cells and ranges​

The foundation of validation is identifying which cells accept user inputs. In a DCF model, typical input zones include:

  • Revenue assumptions (growth rates, base year revenue)
  • Margin projections (gross margin, operating margin, tax rate)
  • Terminal growth rate and discount rate (WACC)
  • Working capital changes as percentage of sales
  • Capital expenditure (CapEx) projections

Create a dedicated "Inputs" or "Assumptions" worksheet where all user-provided values live. Color-code these cells distinctly (light blue background is standard) to visually separate inputs from calculations. This single source of truth prevents scattered assumptions and makes audits straightforward.

A1: Revenue (Base Year) | Format: Currency
A2: Revenue Growth Year 1 | Format: Percentage
A3: Revenue Growth Year 2 | Format: Percentage
A4: Terminal Growth Rate | Format: Percentage
A5: WACC (Discount Rate) | Format: Percentage

Numeric range validation​

Restrict numeric inputs to realistic ranges based on business logic. For a revenue growth rate, you might allow only 0–40% annually. For terminal growth, 0–3% makes sense for mature companies. To apply range validation in Excel:

  1. Select the input cell (e.g., A4 for terminal growth)
  2. Go to Data > Data Validation
  3. Set type to "Whole Number" or "Decimal"
  4. Choose "between" and specify minimum (0.0%) and maximum (3.0%)
  5. Enter an error message: "Terminal growth must be 0–3% for stable-state assumptions"

This prevents an analyst from accidentally entering 50% terminal growth, which would inflate intrinsic value absurdly. The validation triggers immediately when the cell is edited, catching the error in real time.

For margin inputs, set ranges aligned with industry benchmarks. If a company's gross margin typically ranges 35–55%, configure validation to reject 0% or 95%. This forces the user to reconsider unusual assumptions and justify them explicitly in documentation.

Dropdown validation eliminates ambiguity when multiple interpretations exist. Common use cases include:

  • Market scenario selection: Recession, Base Case, Growth
  • Currency selection: USD, EUR, GBP (if valuing multi-currency cash flows)
  • Tax treatment: Standard Rate, Preferential Rate, Tax-Loss Carryforward
  • Industry classification: Tech, Healthcare, Financials, Energy

To create a dropdown in Excel:

  1. Create a hidden worksheet named "Lists"
  2. Add a column with your options (e.g., B1: Recession, B2: Base Case, B3: Growth)
  3. Select your input cell
  4. Data > Data Validation > List
  5. Under Source, reference the named range: =Lists.B1:B3
  6. Check "Show dropdown arrow" and "Show input message"

Users now click a down arrow and select from predefined options. This prevents typos ("Recesion" instead of "Recession") and ensures consistency across worksheets. If multiple analysts work on the model, everyone uses identical terminology.

Custom formula validation​

For advanced scenarios, create validation rules using formulas that reference other cells. Example: the terminal growth rate should never exceed average GDP growth forecasts. If GDP growth (G3) is 2.5%, set validation on terminal growth (A4) to reject values exceeding G3.

In the Data Validation dialog:

  • Type: Custom
  • Formula: =A4<=G3
  • Error message: "Terminal growth cannot exceed GDP growth forecast"

Another example: total debt plus equity must equal total capitalization. Validate that inputs sum correctly before the model runs calculations:

Formula: =A10+A11=A12
(Total Debt + Total Equity = Total Cap)

Custom validation also flags inconsistencies across scenarios. If pessimistic-case revenue growth is higher than base-case growth, a formula catches it:

Formula: =PessimisticCase!A2<=BaseCase!A2
Error: "Pessimistic case growth exceeds base case"

Input protection and worksheets locking​

After validation is configured, lock the worksheet to prevent users from accidentally deleting or modifying validation rules or formulas. In Excel:

  1. Select the input cells you want unlocked (e.g., A1:A10)
  2. Right-click > Format Cells > Protection
  3. Uncheck "Locked"
  4. Select all other cells and check "Locked"
  5. Go to Review > Protect Sheet
  6. Set a password and choose permissions (allow: select cells, sort, auto-filter only)

Now only the designated input cells can be edited. Users cannot accidentally delete formulas or modify the model structure. Protected sheets also prevent viewing hidden columns or sheets without the password.

For sensitive models, protect the entire workbook (File > Info > Protect Workbook > Encrypt with Password) to prevent opening the file without authorization.

Error alerts and input prompts​

Configure input messages that appear when a user clicks a cell, guiding them on expected values:

Title: Revenue Growth Assumption
Input Message: "Enter annual revenue growth as a percentage (0–40%).
Use historical 5-year CAGR as a baseline, adjusted for market outlook."

Similarly, set error alerts when validation fails:

Error Style: Warning (allows override) or Stop (blocks entry)
Title: Invalid Input
Message: "Growth rate exceeds the 40% limit. Enter a value ≤40% or 
update the validation rule if justified by new market conditions."

A "Warning" style lets users override intentionally (with a yes/no prompt) if they have a defensible reason. A "Stop" style blocks entry completely, useful for truly non-negotiable constraints like negative discount rates.

Real-world example: Building a scenario model​

Suppose you're valuing a tech company under three scenarios: Recession, Base, and Optimistic. Create separate input worksheets for each, with identical validation rules but different assumption values.

Scenario selection dropdown (C1): ={"Recession", "Base", "Optimistic"}

Once a scenario is selected, formulas reference the appropriate worksheet:

=INDIRECT(C1&"!A5")
(Returns the revenue growth rate from the selected scenario sheet)

Validation ensures each scenario's assumptions are internally consistent (e.g., high growth scenarios don't have negative margin trends). If an analyst tries to model recession conditions with 50% revenue growth, validation rejects the conflicting pair.

Common mistakes​

Mistake 1: Validation rules that are too strict Setting a revenue growth cap at exactly 10% leaves no room for outlier companies or market shifts. Use ranges with buffer zones (0–40%) rather than hard limits. Document why you chose those bounds so future users understand the rationale.

Mistake 2: Validation on calculated cells Apply validation only to input cells, not formulas. If you validate a cell containing =SUM(A1:A5), the validation will fire when the formula evaluates—counterintuitive and unhelpful. Keep inputs and calculations separate.

Mistake 3: Forgetting to unlock input cells before protecting the sheet A common error: protect the sheet without first unlocking input cells, making the entire spreadsheet read-only. Always unlock inputs first, then protect.

Mistake 4: Error messages that are too technical "Error: Invalid entry per constraint formula CustomRule_Revenue" confuses users. Instead: "Enter annual revenue growth 0–40%. Higher values require written justification."

Mistake 5: Inconsistent validation across linked models If you have a summary workbook that pulls data from detailed models, validate both consistently. A base-case assumption of 15% growth in the detail model but 5% in the summary creates reconciliation chaos.

FAQ​

Q: Should I validate every cell in my model? A: No. Validate only input cells where users provide assumptions. Calculated cells (formulas) should never be validated; validation on them creates false errors when formulas evaluate. Keep validation focused on the inputs worksheet.

Q: Can I create a dropdown list with 200+ options? A: Yes, but avoid it. Long lists become unwieldy. Instead, use 2–3 levels: a category dropdown (e.g., "Healthcare") that then enables subcategory dropdowns (e.g., "Pharmaceutical" or "Medical Device"). This cascading approach is cleaner.

Q: What if my validation rule conflicts with another analyst's assumption? A: Document your validation choices in a separate reference sheet. Include the rule, the reason, and who approved it. If assumptions change (e.g., new market data), update validation rules company-wide and communicate the change.

Q: Can I validate across multiple worksheets simultaneously? A: Not directly in Excel. Instead, create a "master inputs" worksheet and reference it from other sheets using =INDIRECT(). This ensures all worksheets pull validated assumptions from the same source.

Q: How do I handle validation for dates (e.g., financial report dates)? A: Use Data Validation > Date > between and set start date (earliest quarterly report) and end date (today). This prevents users from entering future dates or typos like "2015" when they meant "2025."

Q: Should I protect sheets during development or only before sharing? A: Protect sheets early during development. It forces you to think critically about inputs vs. calculations and prevents accidental breaks. Remove protection temporarily if you need to restructure, then reapply.

  • Scenario analysis builds on validated inputs, ensuring each scenario uses a consistent set of assumptions. See Chapter 15 for multi-scenario modeling.
  • Sensitivity analysis tests how outputs respond to changes in validated inputs, identifying which assumptions drive value most.
  • Audit trails and version control track who changed assumptions and when, essential for compliance and model governance.
  • Input documentation captures the source, methodology, and approval date for each assumption, creating a paper trail for external auditors.
  • Template standardization applies validation rules across all valuation models in your organization, reducing training time and errors.
  • Conditional formatting pairs with validation to highlight inputs that fall outside typical ranges, providing visual alerts before calculations run.

Summary​

Data validation and input controls transform a loose spreadsheet into a disciplined tool. By restricting entries to predefined ranges, lists, and custom formulas, you prevent errors before they cascade through your model. Lock worksheets to protect formulas, use error messages that guide users, and validate consistently across all scenario sheets. Professional models are not just accurate—they're error-resistant by design.

Next​

Scenario Tables in Excel →