In some financial models, such as cash payments spreadsheet, there is an inherent need to safeguard unique invoice number inputs.

Put simply, the spreadsheet must be prevented from inputting duplicate cell values. Excel Data Validation is a fast, effective and macro-free solution, which will improve the financial model and prevent erroneous, duplicate input cells.

Strategize Financial Modelling – Excel Data Validation to prevent duplicate cells.xls

Value of Data Validation in Financial Modelling

Excel Data Validation is a fast, effective and macro-free solution, which will improve the financial model’s

• Corporate Governance and Internal Controls – prevent erroneous, duplicate input values being entered into a spreadsheet. Thereby ensuring greater reliability and credibility of the spreadsheet tool, and
• Productivity – eliminate spreadsheet review time of inputs into the financial model, because of the prevalence of duplicate input values

Duplicate input cells in a Financial Model

In a standard financial model, unless users remember to use the Remove Duplicates Excel function, there is no way to safeguard duplicate inputs creeping into a spreadsheet.

Excel Data Validation is one great solution.

Establishing the Data Validation Settings

Select the Data heading at the top of the page, choose Data Validation and under Settings – Validation criteria, Allow – select Custom from the drop-down list.

In the Formula window type the following formula.

=COUNTIF($B$5:$B5,$B5)<=1

This must be 100% correct – otherwise the Data Validation may not work. Then select the Error Alert tab, under Title type the following:

“Only Unique Invoice numbers”

Then type in the Error message window:

“Please re-type because this Invoice number has already been used.”

Copy this formatted cell B5. Paste Special Validation.

Testing the Data Validation – formatted cells

We will now test drive these formatted, validated cells. When we now type an input value again, the Excel Data Validation will throw up an error message, which will force the user to re-enter the input value.

Data Validation COUNTIF formatting on another sheet

On this second sheet, let’s again apply Excel Data Validation to mitigate duplicate input values – by applying the COUNTIF formula. Again strong attention to detail is paramount; thus take your time to effect the Excel Data Validation across this data range.

Testing the spreadsheet, we can again observe how Excel Data Validation prevents the duplication of input cells.