How to use Excel Data Validation to protect cells

Excel’s Data Validation is one of the more underutilised and understated features in Microsoft’s spreadsheet application. Surprisingly, it is underemployed even by many expert Excel and financial model users, as a means to restrict data inputs into cells and improve the viability of a financial spreadsheet.

Quite simply it is a great, VBA macro-free tool, which can improve the internal controls and corporate governance of a financial model.

The following Excel spreadsheet is used in the following video:

data validation Cell protection with Data Validation example

 

Practical Business Planning uses

Here are some common and obvious practical users of Data Validation in financial models:

  • Restrict data inputs to a confined data range, such as percentages (0% to 100%); i.e. for a company’s interest in a joint venture entity or equity accounting investment
  • Dependant drop-down lists, such as selecting US states versus Canadian provinces, based on a precedent drop-down list of USA and Canada; for the application of applicable statutory corporate tax rates.
  • Preventing users from entering non-integer values, such as text or percentages, when selecting the number of whole units for the purposes of an activity-based costing exercise.

 

Example 1 – Protect cells using Data Validation

The above video demonstrates the subtle power of Data Validation to protect a cell, based on a Custom type of Validation Criteria to specific via a formula in cell I5, such as the following:

=AND(P5<>”OK”,Q5<>”OK”,R5<>”OK”)

This means, if P5, Q5 and R5 all contain “OK” as a cell value, then cell I5 will reject all changes to the value in its cell. Otherwise, users would be able to edit the cell value in cell I5.

data validation

 

Example 2 – Protect adjacent cells using Data Validation

Expanding on Example 1, the above formula (once it was amended to ensure absolute column referencing) was copied across to the left-hand cells from B5 to H5; applying the following formula and Paste Special -> Validation:

=AND($P5<>”OK”,$Q5<>”OK”,$R5<>”OK”)

Hence, if the corresponding “OK” cells in columns P, Q and R all contain “OK” cell values; then cells from column B to column I will be locked from editing.

 

Practical benefits of Data Validation for Cell Protection

Unlike the discriminatory and crude approaches of cell locking, worksheet and workbook protection, and not to mention customised VBA macros; Data Validation provides financial and business planning professionals greater flexibility in the following instances:

  • Internal controls. This example was based on a company applying controls on non-executives and third parties from editing accounts payable amounts; once an invoice had been authorised and paid by executives.
  • Corporate Governance. A contract pricing tool for the supply of a product, which is based on an authorised price, volume, operating expenses and capital expenditure and requires executive sign off; would benefit from cell protection of precedent inputs.

 

Important practical considerations with Data Validation

Attention to detail is paramount if applying Data Validation across an array of data – particularly in Example 2 – Protect adjacent cells using Data Validation. Remember cell referencing in a cell with Data Validation is no different than a formula in a cell; if the cell’s Validation properties are pasted across a data array, the cell referencing in the Validation will change accordingly.