Spreadsheet Class 12 Computerised Accounting: Concepts, Functions and Solved Questions

What Spreadsheet is about

Weightage: This chapter is in Part B Option II: Computerised Accounting, which a student takes instead of Option I (Analysis of Financial Statements). Computerised Accounting carries 20 of the 80 theory marks together with Overview of Computerised Accounting System, Use of Spreadsheet in Business Applications and Graphs and Charts for Business Data.Source: CBSE Accountancy (055) syllabus 2026-27, Part B Option II: Computerised Accounting

In real board papers: asked in 2 of the 2 board papers we hold (2026 to 2026), on average 8.5 marks per paper. See the questions

Key concepts

Concept and features of an electronic spreadsheet

NCERT section 2.1

A file is a workbook; it holds worksheets (Excel 2007 opens with Sheet1, Sheet2 and Sheet3; more can be inserted, renamed or deleted). Rows are numbered (1, 2, 3 ...) and columns lettered (A, B ... Z, AA ...). Their intersection is a cell, identified by its cell address (G8 = column G, row 8). The selected cell is the active cell. A range is written with a colon: A1:E2 means every cell from A1 to E2.

NCERT gives 65,536 rows and 256 columns per sheet (columns up to IV). That is the limit of Excel versions before 2007; Excel 2007 and later versions allow 1,048,576 rows and 16,384 columns (up to XFD). Know both, and answer from the NCERT book when a question follows it.

Features: automatic recalculation; built-in functions; copying formulas across rows and columns; data validation; sorting and filtering; formatting; charts; what-if analysis with data tables; pivot tables; printing and sharing reports; importing data from other sources.

Worked example

Suppose quantity is in B2 and price in C2. What do you type in D2 to get the value, and what happens if the price changes? [1 mark]

Try it first, then show the solutionSolution

1. Formula: =B2*C2. Quantity and price are basic values; value is a derived value.
2. Changing C2 recalculates D2 automatically.

Answer: =B2*C2; it recalculates automatically.

Relative, absolute and mixed references

NCERT section 2.1

By default a reference is relative: when you copy the formula, the reference shifts with it. An absolute reference has a dollar sign before both column letter and row number and stays fixed. A mixed reference fixes only one of them. Pressing F4 while editing a reference cycles through these forms.

Reference	Written as	On copying the formula
Relative	C4	Both column and row change
Absolute	＄C＄4	Neither column nor row changes
Mixed (column fixed)	＄C4	Only the row changes
Mixed (row fixed)	C＄4	Only the column changes

Worked example

Suppose B1 holds a GST rate of 18% and A3:A10 hold prices. You want B3 to show tax on A3 and then copy it down to B10. Which formula should you type in B3? [1 mark]

Try it first, then show the solutionSolution

1. The rate cell must stay fixed while the price row changes.
2. Use =A3*＄B＄1 (or =A3*B＄1, fixing the row).

Answer: =A3*＄B＄1

Formulas and common functions

NCERT section 2.1.2, 2.1.3, 2.1.4

A formula starts with = and uses operators (+, −, *, /, ^) and functions. Excel follows the usual order: brackets, exponent, multiplication and division, then addition and subtraction.

• Mathematical/statistical: SUM(range), AVERAGE(range), COUNT(range), ROUND(number, digits), SUMIF(range, criteria, sum_range), COUNTIF(range, criteria).
• Logical: IF(condition, value_if_true, value_if_false); AND(...) is TRUE only if all conditions are true; OR(...) is TRUE if any is true. The condition must give TRUE or FALSE, otherwise #VALUE! appears.
• Date and time: TODAY() gives the current date; NOW() gives date and time; DATE(year, month, day); DAY, MONTH and YEAR extract parts of a date.
• Text: CONCATENATE joins text from several cells.

The Formulas tab on the ribbon holds the function library.

Worked example

Suppose marks are in B2:B4 (72, 28, 91) and the pass mark is 33. Write a formula for the result in C2 and show the results. [3 marks]

Try it first, then show the solutionSolution

1. Marks sheet

   	A	B	C
   1	Name	Marks	Result
   2	Anu	72	=IF(B2>=33,"Pass","Fail") → Pass
   3	Bilal	28	=IF(B3>=33,"Pass","Fail") → Fail
   4	Chitra	91	=IF(B4>=33,"Pass","Fail") → Pass

2. Copy C2 down: the relative reference B2 becomes B3 and B4.

Answer: Pass, Fail, Pass.

Lookup functions: VLOOKUP and HLOOKUP

NCERT section 2.1.4.5

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) searches the first column of a table for a value and returns the value in the same row from the column you name. HLOOKUP does the same along the first row, returning a value from a named row.

range_lookup: TRUE (or omitted) gives an exact or the nearest smaller (approximate) match, and needs the first column sorted in ascending order; FALSE returns only an exact match, and the first column need not be sorted. If no exact match exists, #N/A is returned.

If col_index_num is less than 1 the result is #VALUE!; if it is more than the number of columns in the table, #REF!.

Worked example

Suppose A2:C50 holds employee code, name and basic pay. Write a formula to show the basic pay of the employee whose code is in F2, matching the code exactly. [1 mark]

Try it first, then show the solutionSolution

1. Search the first column (codes) for F2; return column 3 (basic pay); exact match needs FALSE.

Answer: =VLOOKUP(F2, A2:C50, 3, FALSE)

Financial functions

NCERT section 2.1.2.6

• PMT(rate, nper, pv): the equal periodic payment for a loan.
• PV(rate, nper, pmt): present value of a series of equal payments.
• FV(rate, nper, pmt): future value of equal periodic payments (deposits).
• RATE(nper, pmt, pv): the interest rate per period.

The rate and the number of periods must match: a monthly loan at 12% p.a. uses 12%/12 = 1% per month. Excel treats money paid out as negative, so =PMT(1%,12,100000) shows a negative result; put a minus sign before pv (or before the function) to show a positive payment.

Worked example

Suppose a loan of ₹1,00,000 is to be repaid in 12 equal monthly instalments at 12% p.a. Write the formula and find the instalment. [3 marks]

Try it first, then show the solutionSolution

1. Monthly rate = 1%; periods = 12.
2. =PMT(1%, 12, −100000).
3. Instalment = 1,00,000 × 0.01 ÷ (1 − 1.01^(−12)) = ₹8,884.88.

Answer: ₹8,884.88 per month.

Data entry and data validation

NCERT section 2.2, 2.2.1, 2.2.2, 2.2.3

Data entry can be typed, filled (Fill handle and Fill Series for numbers, dates and patterns) or imported (copied from another file, text file or database).

Data validation (Data tab → Data Validation) restricts what can be entered in a cell, so data stay accurate and consistent. It has three tabs:

• Settings: the validation criteria, such as whole numbers between limits, dates in a range, text length, or a drop-down list (typed list like Male, Female; a range of cells; or a named range).
• Input Message: a message shown when the cell is selected, telling the user what to enter.
• Error Alert: the message and style (Stop, Warning or Information) shown when invalid data are typed. A Stop alert does not allow the entry at all.

Worked example

Suppose a payroll sheet must accept only the departments Sales, Finance, HR and IT in column D. How would you set this up, and what will the user see if he types "Admin"? [3 marks]

Try it first, then show the solutionSolution

1. Select column D → Data Validation → Settings → Allow: List → Source: Sales, Finance, HR, IT.
2. Input Message: "Choose a department from the list."
3. Error Alert (Stop): "Invalid department." Typing "Admin" is rejected with this message.

Answer: A drop-down list with a Stop error alert; "Admin" is not accepted.

Formatting, alignment and output

NCERT section 2.3, 2.4

Number formats: currency, percentage, date and so on. Alignment (Home tab → Alignment group): horizontal (left, centre, right, justify), vertical (top, middle, bottom), orientation (angled text), wrap text, shrink to fit, merge and centre (merged cells take the address of the upper-left cell), and indent. Conditional formatting colours cells that meet a rule (data bars, colour scales, icon sets, or a formula that returns TRUE or FALSE). Internal margin (for text in chart elements and shapes) is the distance between the text and the element's border, set on the left, right, top and bottom.

Output: define a print area, set page layout, headers and footers, and print or save reports.

Worked example

Suppose a long heading "Statement of Debtors as at 31st March" must sit centred over columns A to E, and long customer names must fit inside column B without widening it. Which options would you use? [2 marks]

Try it first, then show the solutionSolution

1. Merge and Centre across A1:E1 for the heading.
2. Wrap Text on column B so names break into lines.

Answer: Merge and Centre; Wrap Text.

Error values in formulas

NCERT section 2.4 (formula errors)

When a formula cannot be evaluated, Excel shows an error value instead of a result. Each has a cause and a fix:

Error	Meaning	Typical fix
#####	Column too narrow for the value (or a negative date/time)	Widen the column (AutoFit) or change the number format
#DIV/0!	Division by zero or by a blank cell	Correct the divisor; or use IF to test it first
#N/A	Value not available to a function (e.g. lookup value not found)	Check the lookup value; use an exact-match lookup on correct data
#NAME?	Excel does not recognise text in a formula (misspelt function, missing colon in a range)	Correct the spelling or the range
#NULL!	Intersection of two ranges that do not intersect (space used as operator)	Use a colon for a range and a comma for separate ranges
#NUM!	Invalid numeric value in a formula or function	Check the arguments
#REF!	Cell reference is not valid (e.g. a referenced cell was deleted)	Restore or retype the reference
#VALUE!	Wrong type of argument or operand (e.g. text in arithmetic)	Enter numbers where numbers are needed

Worked example

Suppose a student types =SUM(B2B3). What error appears and how is it corrected? [3 marks]

Try it first, then show the solutionSolution

1. Without a colon, Excel reads B2B3 as an unknown name.
2. It shows #NAME?.
3. Correct it to =SUM(B2:B3) (or =SUM(B2,B3)).

Answer: #NAME?; insert the colon: =SUM(B2:B3).

Data tables and pivot tables

NCERT section 2.5, 2.5.1-2.5.3

A data table (Data tab → What-If Analysis → Data Table) shows the results of a formula for several input values. A one-variable table changes one input cell (for example, budget for several possible cost increases); a two-variable table changes two input cells with one formula.

A pivot table (Insert tab → PivotTable) builds a cross-tabulated summary report from a list of data. You drag fields into rows, columns, values and filters, and can move them around to see the data from different angles, with totals and sub-totals calculated automatically.

Advantages of pivot tables: summarise large data quickly; present it in a readable, formatted report; let you regroup and filter data interactively without formulas; highlight trends for decisions.

Worked example

Suppose a sheet lists daily sales of three products in four cities for a month. What would a pivot table with cities as rows and products as columns show? [1 mark]

Try it first, then show the solutionSolution

1. The values area sums sales for each city-product pair.
2. Row and column totals appear automatically.

Answer: A summary of total sales by city and product, with totals.

Spreadsheet in real life

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

Topics in this chapter

• Formulas, Functions and Cell References
• Data Validation in a Spreadsheet
• Conditional Formatting

Spreadsheet: key facts

IF

=IF(condition, value_if_true, value_if_false)

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

FALSE for an exact match only.

SUMIF and COUNTIF

=SUMIF(range, criteria, sum_range); =COUNTIF(range, criteria)

PMT

=PMT(rate, nper, pv)

Match rate and periods: monthly rate for monthly payments.

FV and PV

=FV(rate, nper, pmt); =PV(rate, nper, pmt)

Date functions

=TODAY(); =NOW(); =DATE(year, month, day)

Absolute and mixed references

＄C＄4 (both fixed); ＄C4 (column fixed); C＄4 (row fixed)

Type the ordinary dollar sign in Excel; F4 cycles the forms.

How the board asks Spreadsheet

The question patterns to expect, and the method that works for each.

Tricks to remember Spreadsheet

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

• Dollar fixes what follows

  ＄C fixes the column, ＄4 fixes the row, ＄C＄4 fixes both.

  Use it when: References.

• FALSE finds the exact

  VLOOKUP FALSE = exact match only.

  Use it when: Lookups.

• NAME is a spelling problem, REF is a missing cell

  #NAME? = Excel cannot read it; #REF! = the cell it points to is gone.

  Use it when: Errors.

• Match the clock

  Monthly payments: monthly rate and months.

  Use it when: PMT, FV, PV.

Spreadsheet: board-level solved questions

Ordered from board level to HOTS. Cover the solution, write your own, then compare step by step.

Case-study practice (Section E style)

Read the situation, then answer every part. In the board paper this is a 4-mark question split into 1 + 1 + 2 marks.

Where the marks are

How a typical answer is split into step marks. Show every step: a right final answer alone rarely earns full marks.

Where students lose marks in Spreadsheet

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

• ✗Mistake: Writing a range without a colon

  Typing quickly.

  Wrong working

  =SUM(B2B9)

  Right working

  =SUM(B2:B9)

  ✓Do this: Ranges use a colon; separate ranges use a comma.

  Usually costs: 1 mark (and a #NAME? error).

• ✗Mistake: Using an annual rate in PMT for monthly payments

  Copying the rate as given.

  Wrong working

  =PMT(12%,12,−100000)

  Right working

  =PMT(1%,12,−100000)

  ✓Do this: Divide the annual rate by 12 and use months for nper.

  Usually costs: 1-2 marks.

• ✗Mistake: Forgetting FALSE in VLOOKUP for codes

  Leaving range_lookup out.

  ✓Do this: Omitted means TRUE (approximate); use FALSE for exact codes.

  Usually costs: 1 mark.

• ✗Mistake: Mixing up #N/A and #REF!

  Both look like lookup errors.

  ✓Do this: #N/A: value not found. #REF!: invalid reference (deleted cell or column index beyond the table).

  Usually costs: 1 mark.

• ✗Mistake: Not starting a formula with =

  Typing as in a notebook.

  ✓Do this: Every formula begins with an equals sign; otherwise it is stored as text.

  Usually costs: ½-1 mark.

NCERT exercise guide: Spreadsheet

What each exercise in the NCERT chapter tests, how to approach it, and a question of the same kind solved step by step. Questions here are written in our own words.

Spreadsheet: previous year questions

• Asked in 2 of the 2 board papers we hold (2026 to 2026), on average 8.5 marks per paper; 9 questions in all, counting OR alternatives. In 4 of 4 sample papers.

Counted from the real papers on our previous year papers page, not estimated. Average marks are over every board paper we hold, with an OR pair counted once. Chapter tags are ours.

Solved board questions from Spreadsheet

Real questions from recent papers. Try each one first, then open our step-by-step solution with marks per step.

Every Spreadsheet question, by year

Practice questions on Spreadsheet

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

1. Q1 · 1 mark · MCQBoard level

   The intersection of a row and a column is called a:
   (a) range
   (b) cell
   (c) workbook
   (d) label

   Hint

   Smallest unit.

   Answer

   (b) cell

2. Q2 · 1 mark · MCQBoard level

   Which error appears when a column is too narrow to display a number?
   (a) #VALUE!
   (b) #NUM!
   (c) #####
   (d) #REF!

   Hint

   Hashes.

   Answer

   (c) #####

3. Q3 · 3 marks · Short answerBoard level

   What is data validation? What does the Error Alert tab do?

   Hint

   Restrictions and messages.

   Answer

   Data validation restricts the type or values of data that can be entered in a cell. The Error Alert tab sets the message and style (Stop, Warning, Information) shown when invalid data is entered.

4. Q4 · 3 marks · Short answerBoard level

   State any three features of an electronic spreadsheet.

   Hint

   Recalculation, functions, charts.

   Answer

   Automatic recalculation; library of built-in functions; charts and pivot tables (also validation, sorting and filtering, formatting).

5. Q5 · 3 marks · Short answerHarder board level

   Explain the options available to change the alignment of text in cells.

   Hint

   Home → Alignment.

   Answer

   Horizontal alignment (left, centre, right, justify); vertical alignment (top, middle, bottom); orientation (angle); wrap text; shrink to fit; merge and centre; indent.

6. Q6 · 3 marks · Short answerHarder board level

   How are absolute and mixed references written? Give the result of copying =A2*＄B＄1 from C2 to C3, and =A2*B＄1 from C2 to D2.

   Hint

   Dollar sign fixes what follows it.

   Answer

   Absolute: ＄B＄1; mixed: ＄B1 or B＄1. C3 gets =A3*＄B＄1. D2 gets =B2*C＄1 (column shifts, row 1 stays).

7. Q7 · 3 marks · Short answerHarder board level

   Suppose a lookup with =VLOOKUP(F2, A2:C50, 4, FALSE) returns an error. Which error and why? How would you fix it?

   Hint

   Only three columns in the table.

   Answer

   #REF!, because col_index_num 4 is more than the 3 columns in A2:C50. Use a column number from 1 to 3 or widen the table array.

8. Q8 · 4 marks · Short answerHOTS / competency

   Suppose a manager wants a report of total sales by region and by month from 5,000 rows of daily sales. Which spreadsheet tool would you use, and state three advantages of it.

   Hint

   Cross-tabulation.

   Answer

   A pivot table. Advantages: summarises large data quickly; produces a readable formatted report with totals; lets fields be rearranged or filtered interactively without formulas; helps spot trends.

9. Q9 · 1 mark · Assertion-reasonHOTS / competency

   Assertion (A): VLOOKUP with range_lookup TRUE may return a wrong value if the first column is not sorted.
   Reason (R): With TRUE, VLOOKUP returns the next largest value smaller than the lookup value when there is no exact match.
   (a) Both true and R explains A (b) Both true, R does not explain A (c) A true, R false (d) A false, R true

   Hint

   Approximate match needs order.

   Answer

   (a)

10. Q10 · 1 mark · Very short answerHarder board level

    Suppose a loan of ₹2,40,000 is repaid in 24 monthly instalments at 12% p.a. Write the PMT formula with the correct rate and periods.

    Hint

    Monthly rate.

    Answer

    =PMT(1%, 24, −240000)

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. 1.What is a workbook?Show answer

   A spreadsheet file containing one or more worksheets.

2. 2.What does #VALUE! mean?Show answer

   A wrong type of argument or operand, such as text used in arithmetic.

3. 3.What does HLOOKUP search?Show answer

   The first row of a table, returning a value from a specified row.

4. 4.What are the three tabs of data validation?Show answer

   Settings, Input Message and Error Alert.

5. 5.What is a mixed reference?Show answer

   A reference with only the row or only the column fixed.

6. 6.What does TODAY() return?Show answer

   The current date.

7. 7.What is a one-variable data table?Show answer

   A table showing a formula's results for different values of one input cell.

8. 8.What does merging cells do to the cell address?Show answer

   The merged cell takes the address of its upper-left cell.

Quick revision checklist

The night before the exam: can you do each of these without looking?

• ☐Can you explain workbook, worksheet, cell, range and active cell?
• ☐Can you write absolute and mixed references?
• ☐Can you write IF with AND or OR?
• ☐Can you write SUMIF and COUNTIF?
• ☐Can you write VLOOKUP with an exact match?
• ☐Can you set PMT for monthly instalments?
• ☐Can you set up a drop-down validation list with an error alert?
• ☐Can you name and fix the eight error values?
• ☐Can you explain alignment options and merge and centre?
• ☐Can you state the advantages of a pivot table?

Spreadsheet: frequently asked questions