💎 Our top tip 💎
You have created a beautiful bid sheet template with cells or columns where only numbers should be added - only to find that a supplier has entered a comment instead of the price quote you actually expect. See below how you can ensure that the right input format is provided?
How to include data validation to require specific number formats
For columns which are inputs fields from suppliers and numeric data is filled in, it is highly recommended to include data validation. This means, that only numeric values can be accepted in those cells. So suppliers cannot insert 90 days, instead they can only insert 90.
Examples of numeric inputs are:
Price (Material Cost, Conversation Cost, Distribution Cost)
Delivery Time
Payment Terms
...
To include data validation follow those 5 steps:
Select all the cells, for which you want to include numeric data validation
Go to: Data / Data Validation and select: Data Validation...
In Settings allow decimal numbers, which are greater than or equal to 0
in Error Alert select the error alert Stop and title it as: please fill in numeric value. Now if a non-numeric value is typed this error message will appear.
Click OK to save the changes.
If you fill in a value that does is not allowed within the predefined data validation, the following Alert will appear:
How to include data validation to choose an option from a list
It's often easier and less error-prone to provide suppliers with a drop-down of options to fill in the bid sheet. Typical use cases are ISO Codes or some pre-defined product specifications your suppliers should choose from.
How does it look like for suppliers?
The supplier clicks into the cell and sees the arrow appear and can then choose an option from the drop-down list. If suppliers want to circumvent the list and enter an option that doesn't exist, they will see a warning message and their input is not accepted.
How to set up a drop-down list through data validation?
In the below example, you'll find a bid sheet template in one worksheet and another worksheet called "Bid helper" that we will hide before sending out to suppliers.
The worksheet Bid helper contains the list(s) that we will use for the drop-down options.
Select the cells in your bid sheet where the drop-down list should be selectable.
Go to the tab Data in your Excel file and click on the icon Data Validation.
In the Data Validation dialog window, stay in Settings. In the Validation criteria, choose to allow List.
Click into the Source field and go to the area in your Excel workbook that contains the list to choose from. In our case, the list is in a separate Worksheet called Bid helper.
Select all the options that should appear in your dropdown and confirm by clicking in OK.
To give more guidance to your suppliers, we recommend adding an Input Message and/or Error Alert. You can type in your own message or use on of our example messages
Use these example warning and error messages
Copy some of our typical warning and/or error messages to create bulletproof bid sheet templates quickly:
Whole number:
⚠️ Warning: Please insert whole number. Only whole number inputs are accepted in this column.
❌ Error: Your input is not a whole number. Please try again with a whole number.
Decimal number:
⚠️ Warning: Please insert a decimal number Only (decimal) number inputs are accepted in this column.
❌ Error: Your input is not a number. Please try again with a number.
Pre-defined options:
⚠️ Warning: Please use pre-defined option. Only an option from the drop-down is accepted in this column.
❌ Error: Your input is not one of the pre-defined options. Choose from available options in drop-down and add remarks in the comments section.