AccountancyCBSE Class 12 · Spreadsheet

Data Validation in a Spreadsheet: Class 12 Steps, Tabs and Error Alert Styles

Q: What is data validation in Class 12 Accountancy?

A spreadsheet feature that restricts what can be entered in a cell, using rules set in the Settings tab, with optional input messages and error alerts.

Q: What is the difference between Stop and Warning?

Stop rejects invalid data; Warning shows a message but lets the user accept the value by choosing Yes.

Q: Can validation create a drop-down list?

Yes. Choose Allow: List and give the items or a range as the source.

Q: Does validation check existing data?

No, only new entries; use Circle Invalid Data to find existing violations.

Updated 15 October 2026 · Fact-checked

Data validation restricts what can be typed into a cell so that data stay accurate. Data → Data Validation has three tabs: Settings (the rule, such as a number range or a drop-down list), Input Message (a hint when the cell is selected) and Error Alert (the message and style, Stop, Warning or Information, for invalid entries).

Understand Data Validation in a Spreadsheet

Why validate? Accounting sheets are only as good as the data typed in. Validation prevents errors such as negative quantities, dates outside the accounting year, or misspelt branch names, before they enter calculations.

Steps. Select the cells → Data tab → Data Validation → set the three tabs → OK.

1. Settings tab (the rule). 'Allow' options: Any value, Whole number, Decimal, List, Date, Time, Text length, Custom (a formula that returns TRUE or FALSE). Choose a condition (between, not between, equal to, greater than ...) and limits. A List creates a drop-down in the cell from typed items (North, South, East, West), a cell range, or a named range. 'Ignore blank' allows empty cells.

2. Input Message tab. A title and message that appear when the cell is selected, telling the user what to enter (for example, 'Enter quantity between 1 and 500').

3. Error Alert tab. The message and style shown when invalid data are entered:

Stop: blocks the entry; the user must retry or cancel.
Warning: warns and asks 'Continue?'; Yes accepts the invalid value.
Information: only informs; OK accepts the value.

Circle Invalid Data. Data → Data Validation → Circle Invalid Data marks cells that were filled before the rule was set, or pasted in, and break the rule.

Limits. Validation checks typed entries; values pasted over a validated cell can remove the rule, so protect sheets where needed.

Data Validation in a Spreadsheet: key facts

Path: 1.Data tab
2.Data Validation
3.Settings / Input Message / Error Alert
Error alert styles: 1.Stop (blocks)
2.Warning (asks to continue)
3.Information (informs); Strictest to mildest.
Drop-down list: Allow: List; Source: North,South,East,West (or a range)

How to answer Data Validation in a Spreadsheet questions

Validation questions ask for steps, the right 'Allow' option, or the right error style for a situation.

1Identify the data: whole number, decimal, date, text length or a fixed list.
2Set the condition and limits in the Settings tab.
3Add an input message so users know the rule.
4Pick the error style: Stop if the value must never be wrong; Warning if exceptions are possible; Information if you only want to alert.
5Use Circle Invalid Data to find old entries that break the rule.
6Explain how the rule protects the accounting output.

Data Validation in a Spreadsheet in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Choosing the error alert style

Suppose a school canteen keeps a daily sales sheet. Quantities must be whole numbers from 1 to 500; the date must fall in the current academic year; occasionally, on fete days, sales above ₹20,000 per item are genuine but unusual.

Which validation rule and error style would you use for (i) quantity and (ii) item sales value?

Try it first, then show the solutionSolution

(i) Quantity: Whole number between 1 and 500, Stop style, because an invalid quantity is always an error.
(ii) Sales value: Decimal less than or equal to 20,000, Warning style, because larger amounts are rare but possible.
Add input messages explaining both rules.

Answer: Quantity: whole number 1-500 with a Stop alert. Sales value: decimal up to 20,000 with a Warning alert, so a genuine fete-day figure can still be accepted after confirmation.

Memory hook: Stop for never, Warning for rarely, Information for just so you know.

Tricks to remember Data Validation in a Spreadsheet

Short memory aids. Say each one aloud once, then use it on a question the same day.

S-I-E tabs
Settings, Input Message, Error Alert: 'Set It Early'.
Use it when: Naming the three tabs.
Stop, Warning, Info = red, yellow, blue
Strictest to mildest, like traffic signals.
Use it when: Error alert styles.
List = drop-down
Choose 'List' to give the user a drop-down.
Use it when: Fixed choices.

Data Validation in a Spreadsheet: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Strictest error style · 1 mark · MCQBoard level

Which error alert style does not allow an invalid entry to be accepted?
(a) Warning
(b) Information
(c) Stop
(d) Input message

Try it first, then show the solutionSolution

Stop blocks the entry; Warning and Information allow the user to accept it.

Answer: (c) Stop

Example 2: Steps for a quantity rule · 3 marks · Short answerBoard level

Write the steps to allow only whole numbers from 1 to 500 in cells B2:B100 with an input message and a Stop alert. (3 marks)

Try it first, then show the solutionSolution

Select B2:B100; Data tab → Data Validation.
Settings: Allow Whole number; Data between; Minimum 1; Maximum 500.
Input Message: title 'Quantity', message 'Enter 1 to 500'. Error Alert: Style Stop, with a title and message. OK.

Answer: As listed.

Example 3: Three error styles compared · 3 marks · Short answerHarder board level

Explain the three styles of error alert in data validation and what happens when a user enters invalid data under each. (3 marks)

Try it first, then show the solutionSolution

Stop: the entry is rejected; the user must Retry or Cancel.
Warning: a message asks 'Continue?'; Yes accepts the value, No returns to edit.
Information: a message is shown; OK accepts the value.

Answer: As explained.

Example 4: Drop-down list for regions · 3 marks · Short answerHarder board level

A sales sheet must accept only North, South, East or West in column C. Describe how to set this up and one advantage over typing. (3 marks)

Try it first, then show the solutionSolution

Select column C cells → Data → Data Validation → Settings: Allow List; Source: North,South,East,West; tick In-cell dropdown.
Users choose from the drop-down instead of typing.
Advantage: no spelling variants, so SUMIF and pivot tables group regions correctly.

Answer: List validation with the four items; it ensures consistent entries for later analysis.

Example 5: Old data and new rules · 1 mark · Assertion-reasonHOTS / competency

Assertion (A): Applying a data validation rule automatically corrects invalid values already present in the cells.
Reason (R): The 'Circle Invalid Data' command can be used to highlight existing entries that break a rule.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.

Try it first, then show the solutionSolution

Validation checks new entries; it does not change existing ones, which must be found (Circle Invalid Data) and fixed.

Answer: (d) A is false, but R is true.

Where students lose marks in Data Validation in a Spreadsheet

The slips examiners see most often, and what to write instead.

Mistake: Naming 'Input Message' as an error style
Mixing tabs and styles.
Wrong working
Styles: Stop, Warning, Input Message.
Right working
Styles: Stop, Warning, Information.
Do this: Input Message is a tab, not a style.
Usually costs: 1 mark.
Mistake: Wrong menu path
Guessing the tab.
Wrong working
Home tab → Validation.
Right working
Data tab → Data Validation.
Do this: Validation lives on the Data tab.
Usually costs: 1/2 mark.
Mistake: Choosing Warning where data must never be wrong
Not thinking about consequences.
Wrong working
Warning for invoice numbers that must be unique.
Right working
Stop for rules that must always hold.
Do this: Match the style to the risk.
Usually costs: 1 mark.
Mistake: Using Decimal for counts
Not choosing the tightest rule.
Wrong working
Allow Decimal for number of units.
Right working
Allow Whole number.
Do this: Pick the data type that fits.
Usually costs: 1/2 mark.

Exam tips

Name the three tabs and the three styles exactly.
In 'steps' questions, include selecting the range first.
Give a reason for the style you choose in case questions.
Data validation and error alerts are frequent 1-mark and 4-mark items in Part B Option II.

Practice questions on Data Validation in a Spreadsheet

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
Data validation is found under the:
(a) Home tab
(b) Insert tab
(c) Data tab
(d) View tab
Hint
Data.
Answer
(c) Data tab
Q2 · 1 mark · MCQBoard level
Which tab of the Data Validation dialog shows a hint when the cell is selected?
(a) Settings
(b) Input Message
(c) Error Alert
(d) Format Cells
Hint
Before entry.
Answer
(b) Input Message
Q3 · 3 marks · Short answerHarder board level
Name the 'Allow' option you would choose for: (i) invoice date within the year; (ii) PAN of 10 characters; (iii) unit price with paise. (3 marks)
Hint
Date, text length, decimal.
Answer
(i) Date (between the first and last day of the year); (ii) Text length equal to 10; (iii) Decimal (greater than 0).
Q4 · 3 marks · Short answerHarder board level
State three benefits of data validation in accounting spreadsheets. (3 marks)
Hint
Accuracy, consistency, guidance.
Answer
It prevents invalid entries (accuracy); it keeps entries consistent, as with drop-down lists (consistency for analysis); input messages guide users, reducing training and errors.
Q5 · 3 marks · Short answerHarder board level
What does 'Circle Invalid Data' do and when is it useful? (3 marks)
Hint
Existing data.
Answer
It draws red circles around cells whose existing values break the validation rule. It is useful when the rule is added to a sheet that already has data, or after values were pasted in, so errors can be found and corrected.
Q6 · 1 mark · Assertion-reasonHarder board level
Assertion (A): Under the Warning style, a user can still accept an invalid entry.
Reason (R): The Warning style asks whether to continue, and Yes keeps the value.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.
Hint
Continue?
Answer
(a) Both A and R are true, and R is the correct explanation of A.
Q7 · 4 marks · Short answerHOTS / competency
Suppose discount given to customers must not exceed 10% of the bill in column B, with the discount in column C. Write a Custom validation formula for C2 and choose an error style. (4 marks)
Hint
Custom formula returns TRUE or FALSE.
Answer
Select C2:C100 → Data Validation → Allow: Custom; Formula: =C2<=B2*10%. Error Alert: Stop, message 'Discount cannot exceed 10% of the bill'. Stop suits a policy that must never be broken.
Q8 · 3 marks · Short answerHOTS / competency
A clerk copies values from another file and pastes them over validated cells, and some invalid values get in. Explain why and suggest two safeguards. (3 marks)
Hint
Paste overrides.
Answer
Pasting replaces cell contents and can overwrite the validation rule, so the check is bypassed. Safeguards: use Paste Special → Values carefully and then Circle Invalid Data to find problems; protect the sheet so only input cells can be edited.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.Name the three tabs of the Data Validation dialog.Show answer
Settings, Input Message, Error Alert.
2.Name the three error alert styles.Show answer
Stop, Warning, Information.
3.Which Allow option creates a drop-down?Show answer
List.
4.Which style blocks invalid entries completely?Show answer
Stop.
5.Which command highlights existing invalid entries?Show answer
Circle Invalid Data.

Data Validation in a Spreadsheet: frequently asked questions

What is data validation in Class 12 Accountancy?

A spreadsheet feature that restricts what can be entered in a cell, using rules set in the Settings tab, with optional input messages and error alerts.

What is the difference between Stop and Warning?

Stop rejects invalid data; Warning shows a message but lets the user accept the value by choosing Yes.

Can validation create a drop-down list?

Yes. Choose Allow: List and give the items or a range as the source.

Does validation check existing data?

No, only new entries; use Circle Invalid Data to find existing violations.