StudyOSStudyOS CBSE Study Material, Class 5–12

Spreadsheet

NCERT Class 12 · Accountancy Based on NCERT Class 12 Accountancy textbook · Free CBSE study kit

Everything you need to ace this chapter, in one place.
Notes · Mind Map · Quiz · Assertion-Reason · Case Studies · Question Bank · Past Papers

Get Full Study Kit →
Studying with friends? Send them this chapter.

Chapter Notes

Overview
This chapter introduces the concept of spreadsheets, a fundamental tool in computerised accounting systems, using MS-Excel 2007 as the reference software. It exists in the syllabus to equip students with the practical skills to record, calculate, and analyse numerical and financial data efficiently. The chapter covers the basic structure of a workbook and worksheet, data entry, cell referencing, and the use of formulas and functions. The one big idea a student must walk away with is that a spreadsheet is not just a data storage tool but a dynamic modelling environment where derived values automatically update based on changes to basic values, enabling powerful 'what-if' scenario analysis for business decision-making.
Key Concepts (23)
Spreadsheet
A configuration of rows and columns, also known as a worksheet, used to record, calculate, and compare numerical or financial data.
e.g. A sheet in MS-Excel used to prepare a student's mark sheet, where total marks and percentage are calculated automatically.
Workbook
A file in Excel that is a collection of a number of worksheets. By default, three sheets (Sheet 1, Sheet 2, Sheet 3) are available.
e.g. A company's annual financial report saved as 'Accounts_2024.xlsx' containing separate worksheets for each month.
Active Worksheet
The worksheet that is currently available to a user for carrying out operations. Its name is shown in bold letters in the Sheet Tab.
e.g. When you click on 'Sheet2' at the bottom of the Excel window, it becomes the active worksheet, and its name appears bold.
Row
A horizontal vector in a spreadsheet, numbered numerically from top to bottom (1 to 65,536 in Excel 2007).
e.g. Row 5 in a spreadsheet might contain all the data related to a specific product, like its name, price, and quantity.
Column
A vertical vector in a spreadsheet, identified by alpha characters from left to right (A, B, C,... AA... IV, total 256 in Excel).
e.g. Column C in a budget sheet might be dedicated entirely to 'Amount Paid' for various expense items.
Cell
The intersection of a row and a column. It is the basic unit where a value, function, or arithmetic expression is recorded.
e.g. The cell where row 3 and column B meet is called cell B3, and it might contain the price of a product.
Cell Address
A unique identification for a cell, formed by a combination of a column letter and a row number, also known as a cell reference.
e.g. The cell address 'G8' corresponds to the cell located at the 8th row under column G.
Range
A group of cells identified by the cell references of the upper left and lower right corners, separated by a colon (:).
e.g. The range A1:E2 tells Excel to include all cells from A1 to E2, forming a rectangular block of 10 cells.
Relative Cell Reference
The default type of cell reference which changes when a formula or function is copied and pasted to other cells, reflecting the new location.
e.g. If the formula in cell C1 is '=A1+B1' and you copy it to C2, it automatically becomes '=A2+B2'.
Absolute Cell Reference
A cell reference that stays fixed on a specific cell when a formula is copied, created by surrounding the column letter and row number with dollar ($) signs, e.g., $C$4.
e.g. A fixed interest rate in cell B1 is used in a formula as '$B$1' so that when the formula is dragged down a column, it always refers to the rate in B1.
Mixed Reference
A cell reference that holds either the row or the column constant when the formula is copied, e.g., $C4 or C$4.
e.g. In a multiplication table, using '=B$1*$A2' allows you to copy the formula across rows and columns while keeping one factor from the first row and the other from the first column.
Basic Value
An independent value entered directly into a cell, which does not depend on other values for its calculation.
e.g. The quantity (Q) of an item purchased and its price (P) are basic values entered manually into a spreadsheet.
Derived Value
The outcome of an arithmetic expression and/or a function, which is calculated based on the values of other variables.
e.g. The total value (V) of an item, calculated as V = Q × P, is a derived value that changes if Q or P is modified.
Formula
A mathematical calculation on a set of cells that must start with an '=' sign. It identifies the calculation needed to place the result in the cell it is contained within.
e.g. The formula '=D1+E1/F1*G1' in cell E3 performs a calculation based on the values in other cells and displays the result.
Function
A special, built-in keyword that can be entered into a cell to perform and process data appended within brackets, starting with an '=' sign.
e.g. The function '=SUM(D1:G1)' adds all the numbers in the range from cell D1 to G1.
Labels
Text or special characters used for rows, columns, or descriptive information. They cannot be treated mathematically.
e.g. Headings like 'Principal Amount', 'Rate of Interest', and 'Student Name' are labels that describe the data in adjacent cells.
What-if Scenario
A feature of spreadsheets used to generate a number of alternatives by changing one or more input values to examine the cause (if) and effect (what).
e.g. Changing the 'Rate of Interest' in a fixed deposit calculator to see how it affects the 'Maturity Amount' and 'Compound Interest'.
Value Rule
A principle by computer scientist Alan Kay stating that a cell's value relies solely on the formula the user has typed into it, with no 'side effects' to calculating a formula.
e.g. When a formula in cell E4 calculates a maturity amount, the only output is the result displayed in E4; it does not permanently modify the contents of any other cell.
Naming Ranges
The process of assigning a descriptive name to a cell or range of cells, which can then be used in formulas instead of cell references.
e.g. Naming the range B1:F1 as 'Numbers' allows you to write the formula '=SUM(Numbers)' instead of '=SUM(B1:F1)'.
IF Function
A logical function that returns one value if a specified condition evaluates to TRUE and another value if it evaluates to FALSE. Its format is IF(logical_test, value_if_true, value_if_false).
e.g. The function '=IF(A1<20, "Yes", "No")' will return 'Yes' if the value in cell A1 is less than 20, and 'No' otherwise.
Nested IF Function
An IF() function placed inside another IF() function to check for more than one condition sequentially.
e.g. A formula like '=IF(E2<96, IF(E2<91, IF(E2<55,"Fail","C Grade"), "B Grade"), "A Grade")' assigns a grade based on multiple mark thresholds.
Logical Operator
An operator used to perform a comparison between two values, producing a result of either TRUE or FALSE.
e.g. In the logical test 'A1 < 20', the symbol '<' is a logical operator meaning 'less than'.
Array
Used to build single formulas that produce multiple results or operate on a group of arguments arranged in rows and columns. An array formula is entered by pressing CTRL+SHIFT+ENTER.
e.g. Using an array formula to multiply a range of quantities by a range of prices and sum the results in a single step.
Formulas & Equations (14)
MA = PA * (1 + R / (100 * CP)) ^ (NY * CP)
Reads as: Maturity Amount equals Principal Amount multiplied by the quantity one plus Rate divided by the product of one hundred and Compounding Period, raised to the power of the product of Number of Years and Compounding Period.
Variables: MA = Maturity Amount, PA = Principal Amount, R = Rate of Interest, CP = Period of Compounding, NY = Period in years.
Use when: Used to compute the maturity amount of a fixed deposit when interest is compounded periodically.
⚠ Incorrectly applying the order of operations, especially not performing the division and addition within the parentheses before applying the exponent.
CI = MA – PA
Reads as: Compound Interest equals Maturity Amount minus Principal Amount.
Variables: CI = Compound Interest, MA = Maturity Amount, PA = Principal Amount.
Use when: Used to find the total interest earned on a principal amount over a period after compounding.
⚠ Forgetting that this formula is the final step and trying to calculate CI directly without first computing the Maturity Amount (MA).
=SUM(D1:G1)
Reads as: Equals SUM of the range from cell D1 to G1.
Variables: SUM is the function name; D1:G1 is the range of cells to be added.
Use when: Used to quickly add all numerical values in a contiguous range of cells.
⚠ Using a comma (,) instead of a colon (:) to specify the range, which would only add the two individual cells D1 and G1 instead of the entire range.
=IF(logical_test, value_if_true, value_if_false)
Reads as: Equals IF, open parenthesis, logical test, comma, value if true, comma, value if false, close parenthesis.
Variables: logical_test: the condition to evaluate (e.g., E4>10000). value_if_true: the result if the condition is met. value_if_false: the result if the condition is not met.
Use when: Used to make a logical comparison between a value and what you expect, returning one value for a TRUE result and another for FALSE.
⚠ Using a single equals sign '=' for comparison instead of the correct logical operators like '>', '<', or '<>'.
=SUMIF(range, criteria, sum_range)
Reads as: Equals SUMIF, open parenthesis, range to evaluate, comma, criteria, comma, actual cells to sum, close parenthesis.
Variables: range: the cells to evaluate against the criteria. criteria: the condition that defines which cells will be added (e.g., ">150000"). sum_range: the actual cells to sum if the corresponding range cells meet the criteria.
Use when: Used to add the values in a range that meet a specific, single criterion.
⚠ Mismatching the size of the 'range' and 'sum_range' arguments, leading to incorrect or unexpected results.
=ROUND(number, num_digits)
Reads as: Equals ROUND, open parenthesis, the number to round, comma, the number of digits to round to, close parenthesis.
Variables: number: the fractional or whole number to be rounded. num_digits: specifies the number of digits to which the number is rounded. If >0, rounds to decimal places; if 0, rounds to nearest integer; if <0, rounds to the left of the decimal.
Use when: Used to round a number to a specified number of digits for simplification or to remove insignificant decimals.
⚠ Confusing ROUND with cell formatting. Formatting only changes the display, while the ROUND function changes the actual stored value used in further calculations.
=COUNT(value1, [value2], ...)
Reads as: Equals COUNT, open parenthesis, a list of values or cell ranges, close parenthesis.
Variables: value1, value2, ...: 1 to 255 arguments that can be cell references or ranges. Only cells containing numbers are counted.
Use when: Used to count the number of cells in a range that contain numbers, which is useful for getting the number of entries in a numeric field.
⚠ Using COUNT to count all non-blank cells. COUNT only counts cells with numbers; COUNTA should be used to count cells with any type of data.
=COUNTIF(range, criteria)
Reads as: Equals COUNTIF, open parenthesis, the range of cells to count, comma, the criteria that defines which cells to count, close parenthesis.
Variables: range: one or more cells to count. criteria: the condition in the form of a number, expression, cell reference, or text (e.g., 32, ">32", "apples").
Use when: Used to count the number of cells within a range that meet a single specified condition.
⚠ Forgetting to enclose a text-based criteria or a logical operator in double quotes, e.g., writing COUNTIF(A1:A10, >10) instead of COUNTIF(A1:A10, ">10").
=TEXT(value, format_text)
Reads as: Equals TEXT, open parenthesis, a numeric value, comma, a format code in double quotes, close parenthesis.
Variables: value: a numeric value or a reference to a cell containing one. format_text: a numeric format as a text string, e.g., "Rs. 0.00".
Use when: Used to convert a numeric value to text in a specific number format, often for combining numbers with text or symbols in a readable way.
⚠ Trying to use the resulting text string in mathematical calculations, which will cause an error because the output is text, not a number.
=CONCATENATE(text1, [text2], ...)
Reads as: Equals CONCATENATE, open parenthesis, first text item, comma, second text item, and so on, close parenthesis.
Variables: text1, text2, ...: 2 to 255 text items to be joined. These can be text strings, numbers, or single-cell references.
Use when: Used to join two or more text strings from different cells into one single text string, such as combining a first name and last name.
⚠ Forgetting to add spaces between joined items, resulting in a combined word like 'JohnDoe' instead of 'John Doe'. A space must be included as a separate argument, e.g., CONCATENATE(A1, " ", B1).
=AND(logical1, [logical2], ...)
Reads as: Equals AND, open parenthesis, first logical condition, comma, second logical condition, and so on, close parenthesis.
Variables: logical1, logical2, ...: up to 255 conditions that can each evaluate to TRUE or FALSE.
Use when: Used to check if all given conditions are TRUE. It returns TRUE only if every argument is TRUE; otherwise, it returns FALSE.
⚠ Using AND when the logic requires OR. A student might write =AND(A1>10, A1<5) expecting it to be TRUE for any number outside 5-10, but it will always be FALSE because a number cannot be both >10 and <5 simultaneously.
=OR(logical1, [logical2], ...)
Reads as: Equals OR, open parenthesis, first logical condition, comma, second logical condition, and so on, close parenthesis.
Variables: logical1, logical2, ...: up to 255 conditions that can each evaluate to TRUE or FALSE.
Use when: Used to check if at least one of the given conditions is TRUE. It returns TRUE if any argument is TRUE; it returns FALSE only if all arguments are FALSE.
⚠ Confusing the logic with AND. A student might use OR when all conditions must be met, leading to a TRUE result when only one condition is satisfied, which can cause major errors in financial models.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Reads as: Equals VLOOKUP, open parenthesis, value to search for, comma, table range, comma, column index number, comma, optional range lookup, close parenthesis.
Variables: lookup_value: the value to find in the first column. table_array: the range of cells containing the data. col_index_num: the column number in the table from which to return a value. range_lookup: TRUE for approximate match, FALSE for exact match.
Use when: Used to search for a specific piece of information in a large data table vertically and return a corresponding value from the same row.
⚠ Omitting the fourth argument [range_lookup] or setting it to TRUE when an exact match is needed, which can return a wrong approximate match if the first column isn't sorted in ascending order.
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Reads as: Equals LOOKUP, open parenthesis, value to search for, comma, the one-row or one-column range to search in, comma, the corresponding range to return a value from, close parenthesis.
Variables: lookup_value: the value to search for. lookup_vector: a single row or column range containing the search values in ascending order. result_vector: a single row or column range of the same size from which to return the result.
Use when: Used to look up a value in a one-row or one-column range and return a value from the same position in a second range.
⚠ Not sorting the lookup_vector in ascending order. The LOOKUP function assumes this order and will return an incorrect result if the data is unsorted.
How It Works
Creating and Applying a Named Range
  1. Select the cell or range of cells you wish to name (e.g., B1:F1).
  2. Click on the 'Formulas' tab on the Ribbon.
  3. Click the 'Define Name' option in the 'Defined Names' group.
  4. In the 'New Name' dialogue box, type a descriptive name (e.g., 'Numbers') in the 'Name' field. Verify the correct range is in the 'Refers to' field.
  5. Click 'OK'. The name is now created and appears in the Name Box.
  6. To use the name, type a formula like '=SUM(Numbers)' in any cell, or use the 'Apply Name' option to replace existing cell references with the defined name.
💡 Named ranges are absolute by default. The 'Refers to' field will show absolute references like $B$1:$F$1. This is crucial for formulas that need to be copied without changing the reference.
Using the IF Function via the Formula Tab
  1. Select the cell where you want the IF function's result to appear (e.g., F4).
  2. Click on the 'Formulas' tab on the Ribbon, then click the 'Logical' button.
  3. Select 'IF' from the dropdown list. The 'Function Arguments' dialogue box will open.
  4. In the 'Logical_test' box, type the condition to evaluate (e.g., E4 > 10000).
  5. In the 'Value_if_true' box, type the value or expression to return if the condition is met (e.g., 10%).
  6. In the 'Value_if_false' box, type the value or expression to return if the condition is not met (e.g., 5%).
  7. Click 'OK'. The formula is created, and the result is displayed in the selected cell.
💡 Text values in the 'Value_if_true' and 'Value_if_false' arguments must be enclosed in double quotes. Numbers and cell references should not be in quotes.
Freezing a Formula's Result Using Paste Special
  1. Select the cell(s) that contain the formula(s) whose calculated value you want to keep permanently.
  2. Click on the 'Home' tab and click the 'Copy' icon (or press Ctrl+C). The cells will show a moving border.
  3. Click the dropdown arrow under the 'Paste' button in the 'Clipboard' group.
  4. Select 'Paste Special...' from the menu. A dialogue box will appear.
  5. In the 'Paste Special' box, under the 'Paste' section, select the radio button next to 'Values'.
  6. Click 'OK'. The formula in the cell is now permanently replaced by its last calculated value.
💡 This action is irreversible using Undo after the workbook is saved and reopened. It is used to prevent recalculation when source data changes or is deleted.
Navigating a Worksheet Using Keyboard Shortcuts
  1. To move one cell down, press the 'Down arrow' key or 'Enter'.
  2. To move one cell up, press the 'Up arrow' key.
  3. To move one cell left, press the 'Left arrow' key.
  4. To move one cell right, press the 'Right arrow' key or 'Tab'.
  5. To jump to the beginning of the worksheet (cell A1), press 'CTRL + HOME'.
  6. To jump to the last cell containing data, press 'CTRL + END'.
  7. To move to the first or last filled cell in a cluster within a row, press 'CTRL + Right/Left arrow'.
  8. To move to the first or last filled cell in a cluster within a column, press 'CTRL + Down/Up arrow'.
💡 The 'CTRL + arrow' keys navigate between clusters of filled cells. If the current cell is empty, it will jump to the next filled cell. If it is filled, it will jump to the last cell in the cluster before an empty cell.
Exam Traps — Don't Make These Mistakes
⚠ Confusing Relative and Absolute References
✗ Students often copy a formula like =A1*B1 down a column, expecting B1 to remain constant, and get incorrect results because they didn't use $B$1.
→ A relative reference like B1 automatically adjusts to B2, B3, etc., when copied. If a specific cell (like a tax rate or interest rate) must remain fixed, the reference must be made absolute using dollar signs.
✓ To keep the reference to cell B1 constant when copying the formula, you must write it as =A1*$B$1. This locks both the column and the row.
⚠ Misunderstanding the LOOKUP/VLOOKUP Range_Lookup Argument
✗ Students use =VLOOKUP(value, table, col, TRUE) or omit the fourth argument, expecting an exact match, and get a wrong result because the lookup column is not sorted.
→ When the range_lookup argument is TRUE or omitted, VLOOKUP performs an approximate match, which requires the first column of the table_array to be sorted in ascending order. If it's not sorted, the returned value is unpredictable and usually wrong.
✓ For an exact match, you must set the fourth argument to FALSE, like this: =VLOOKUP(value, table, col, FALSE). This does not require the data to be sorted and will return an #N/A error if no exact match is found, which is the correct behavior.
⚠ Incorrect Order of Operations in Formulas
✗ A student writes =5+2*3 expecting the result to be 21, because they calculate from left to right.
→ Excel follows the mathematical order of operations (BEMDAS/PEMDAS): Brackets, Exponents, Multiplication/Division (left to right), Addition/Subtraction (left to right). Multiplication has higher precedence than addition, so 2*3 is calculated first.
✓ To get the result 21, you must use brackets to change the order: =(5+2)*3. The correct result for =5+2*3 is 11. Always use brackets to explicitly define the intended calculation order.
⚠ Using COUNT Instead of COUNTA
✗ To count the total number of entries in a list containing text and numbers, a student uses the =COUNT(range) function and gets a count that is too low.
→ The COUNT function only counts cells that contain numbers, dates, or text representations of numbers. It completely ignores cells with text, logical values, or error values.
✓ To count all non-empty cells in a range regardless of their data type, you must use the =COUNTA(range) function. This counts cells with numbers, text, logical values, and error values.
⚠ Forgetting Quotes for Text in IF and COUNTIF Functions
✗ A student writes the formula =IF(A1=Yes, 1, 0) or =COUNTIF(B1:B10, >10) and gets an error.
→ In Excel formulas, text strings must be enclosed in double quotes. Similarly, when a logical operator is used as a criterion in functions like COUNTIF, SUMIF, etc., the entire expression including the operator must be a text string in quotes.
✓ The correct formulas are =IF(A1="Yes", 1, 0) and =COUNTIF(B1:B10, ">10"). The quotes tell Excel to interpret the content as a literal string or a criteria expression.
Compare & Contrast
Definition and Structure
Formula
Function
A formula is a user-defined mathematical expression that performs calculations using operators (+, -, *, /) and cell references, always starting with '='. A function is a predefined, built-in operation in Excel that performs a specific calculation using a keyword and arguments in parentheses, like SUM(), AVERAGE(), or IF().
Behavior When Copied
Relative Cell Reference
Absolute Cell Reference
A relative reference (e.g., A1) changes its row and column coordinates relative to the new location when the formula is copied to another cell. An absolute reference (e.g., $A$1) remains permanently fixed on a specific cell, regardless of where the formula is copied.
Type of Data Counted
COUNT
COUNTA
The COUNT function counts only the cells within a range that contain numbers, dates, or text representations of numbers. The COUNTA function counts all cells that are not empty, including those with text, numbers, logical values (TRUE/FALSE), and error values.
Logic for Returning TRUE
AND Function
OR Function
The AND function returns TRUE only if every single logical condition provided as an argument is TRUE. The OR function returns TRUE if at least one of the logical conditions provided as an argument is TRUE.
Exam Focus
Marks pattern: This chapter is part of the Computerised Accounting System unit. It typically appears as 2-3 MCQs (1 mark each), 1-2 Short Answer Questions (3-4 marks each) requiring steps or syntax, and 1 Long Answer Question (6 marks) involving a practical problem with formulas, functions, or scenario analysis.
Most asked: Differentiating between relative, absolute, and mixed cell references with examples. · Writing and interpreting formulas using arithmetic operators and the correct order of operations (BEMDAS). · Using logical functions like IF, nested IF, AND, and OR to solve conditional problems. · Applying the VLOOKUP function for vertical data retrieval in a table. · Explaining the concept and business application of 'what-if' scenario analysis. · Using mathematical functions like SUMIF, COUNTIF, and ROUND with correct syntax.
Don't skip: A 6-mark practical problem requiring the creation of a spreadsheet model with given data, writing specific formulas using absolute/relative references, and applying functions like IF, VLOOKUP, or SUMIF to generate a result.; A 4-mark question asking to write the syntax and explain the purpose of four different functions from various categories (e.g., Financial, Logical, Text).
Safe to skip: The specific historical detail about the number of rows (65,536) and columns (256) in Excel 2007 is low-weightage. The concept of a cell's address is crucial, but memorizing the exact maximum count is not typically tested in higher-mark questions.
One-Line Revision (read aloud before the exam)
  1. A spreadsheet is a grid of rows (horizontal, numbered) and columns (vertical, lettered) used to record, calculate, and compare data.
  2. A workbook is an Excel file containing one or more worksheets; the active worksheet's name is shown in bold on the Sheet Tab.
  3. A cell is the intersection of a row and a column, identified by a unique cell address like A1, B2, etc.
  4. A range is a group of cells referenced by its top-left and bottom-right cell addresses separated by a colon, e.g., A1:C5.
  5. A relative cell reference (A1) changes when a formula is copied; an absolute reference ($A$1) remains constant.
  6. A mixed reference locks either the row or the column, e.g., $A1 or A$1.
  7. Basic values are entered directly; derived values are calculated using formulas or functions based on other cells.
  8. All formulas and functions in Excel must begin with an equals sign (=).
  9. The order of operations in Excel is BEMDAS: Brackets, Exponents, Multiplication/Division, Addition/Subtraction.
  10. A function is a built-in formula like SUM(), AVERAGE(), or IF() that performs a specific calculation on arguments.
  11. The SUMIF function adds cells that meet a single criterion; COUNTIF counts cells that meet a single criterion.
  12. The IF function performs a logical test and returns one value if TRUE and another if FALSE.
  13. A nested IF function is an IF statement inside another, used to test multiple conditions in sequence.
  14. VLOOKUP searches for a value in the first column of a table and returns a value from a specified column in the same row.
  15. The TODAY() function returns the current date; NOW() returns the current date and time.
  16. The CONCATENATE function joins two or more text strings into one.
  17. The TEXT function converts a numeric value to text in a specified number format, like currency.
  18. A 'what-if' scenario involves changing input values to see how they affect calculated results, aiding in decision-making.
  19. The AND function returns TRUE only if all its arguments are TRUE; OR returns TRUE if any argument is TRUE.
  20. Paste Special -> Values is used to replace a formula with its calculated result, freezing the value permanently.

MCQs — 10 Questions with Answers

Q1. Which of the following correctly identifies a cell in Excel 2007?

  • A. Column number and row letter (e.g., 5C)
  • B. Column letter and row number (e.g., C5) ✓
  • C. Row letter and column number (e.g., C5)
  • D. Cell reference starting with dollar sign (e.g., $C5)

Answer: B — Cell address format is always Column letter + Row number (e.g., C5), where C is the column and 5 is the row.

Q2. How many rows and columns does Excel 2007 contain in a single worksheet?

  • A. 1,000 rows and 100 columns
  • B. 10,000 rows and 256 columns
  • C. 65,536 rows and 256 columns ✓
  • D. 65,536 rows and 1,024 columns

Answer: C — Excel 2007 has exactly 65,536 rows (numbered 1-65,536) and 256 columns (lettered A-IV), providing approximately 16.7 million cells per sheet.

Q3. In a spreadsheet, what is the difference between a basic value and a derived value?

  • A. Basic value is text; derived value is a number
  • B. Basic value is independent data entered directly; derived value is calculated from other values using a formula ✓
  • C. Basic value is always a price; derived value is always a quantity
  • D. Basic value is stored in column A; derived value is stored in column B

Answer: B — Basic values (e.g., Price, Quantity) are independent entries; derived values (e.g., Total = Price × Quantity) are calculated using formulas and functions.

Q4. A Workbook in Excel contains:

  • A. One fixed worksheet only
  • B. Multiple worksheets; by default three sheets named Sheet1, Sheet2, Sheet3 ✓
  • C. Unlimited worksheets but only one can be active at a time
  • D. Multiple workbooks inside, each with its own worksheets

Answer: B — An Excel Workbook is a file containing multiple Worksheets (sheets); the default is three sheets (Sheet1, Sheet2, Sheet3), and additional sheets can be added.

Q5. Which cell reference will remain fixed when a formula is copied to other cells?

  • A. Relative reference (e.g., C4)
  • B. Absolute reference (e.g., $C$4) ✓
  • C. Mixed reference (e.g., C$4)
  • D. Range reference (e.g., A1:E2)

Answer: B — Absolute reference ($C$4) locks both column and row with dollar signs, so when the formula is copied, this reference does not change; relative references (C4) do change.

Q6. What does the cell range A1:E2 include?

  • A. Only cells A1, E2
  • B. All cells in row 1 from A to E
  • C. All cells from top-left (A1) to bottom-right (E2) in a rectangular block ✓
  • D. Only cells in column A and column E, rows 1 and 2

Answer: C — A range with colon notation (A1:E2) includes all cells within the rectangle defined by the top-left corner (A1) and bottom-right corner (E2).

Q7. Which keyboard shortcut moves the cursor directly to cell A1 (top of the worksheet)?

  • A. Alt+Home
  • B. Ctrl+Home ✓
  • C. Shift+Home
  • D. Tab+Home

Answer: B — Ctrl+Home (holding Ctrl and pressing Home) jumps directly to cell A1, the starting cell of any worksheet.

Q8. In an accounting spreadsheet, if Quantity is in cell B2 and Price is in cell C2, which formula correctly calculates Total Value as a derived value?

  • A. = B2 + C2
  • B. = B2 × C2 ✓
  • C. = C2 ÷ B2
  • D. = B2 - C2

Answer: B — Total Value is derived by multiplying Quantity × Price; therefore, the formula is =B2×C2, which is a multiplication (multiplication symbol is ×).

Q9. Which of the following statements is NOT correct about cell references in spreadsheets?

  • A. Relative references change when a formula is copied to another location
  • B. Absolute references remain fixed when a formula is copied
  • C. A mixed reference locks both the column and the row simultaneously ✓
  • D. Mixed references can lock either the column or the row, but not both

Answer: C — A mixed reference locks either the column ($C4) or the row (C$4) — not both; if both are locked, it is an absolute reference ($C$4).

Q10. A company accountant enters Purchase Price = 500 in cell A1 and Quantity = 10 in cell A2, then writes the formula =A1*A2 in cell A3. If the formula in A3 is copied to cell B3, what formula will appear in B3, and why?

  • A. =A1*A2 (no change, because it is already absolute)
  • B. =B1*B2 (cells shift because the reference is relative) ✓
  • C. =$A$1*$A$2 (cells lock automatically)
  • D. =B1*A2 (only column changes, row stays fixed)

Answer: B — The formula =A1*A2 uses relative references; when copied to B3 (one column right), both column letters shift right to B1*B2, reflecting the new location.

Flashcards

What is the intersection of a row and column called?

A cell, identified by a unique cell address such as C5 (column letter + row number).

Define a basic value and a derived value in a spreadsheet.

Basic value is an independent entry (e.g., Price, Quantity); derived value is calculated from basic values using a formula (e.g., Value = Quantity × Price).

How many rows and columns are in Excel 2007?

Excel 2007 has 65,536 rows (numbered 1–65,536) and 256 columns (lettered A–IV), totaling approximately 16.7 million cells.

What is a Workbook in Excel?

A Workbook is a file in Excel that contains multiple Worksheets (sheets), and by default it has Sheet 1, Sheet 2, and Sheet 3.

Define an Active Worksheet.

The Active Worksheet is the single worksheet currently available for operations; its name appears in bold in the Sheet Tab at the bottom.

What does a cell range A1:E2 represent?

A cell range A1:E2 includes all cells from the top-left corner (A1) to the bottom-right corner (E2) in a rectangular block.

Distinguish between relative reference and absolute reference.

Relative reference (e.g., C4) changes when copied to another cell; absolute reference (e.g., $C$4) stays fixed and does not change when copied.

What is a mixed cell reference?

A mixed reference holds either the row or column constant (e.g., $C4 or C$4) so that only the unfixed part changes when copied.

How do you move to cell A1 (top of worksheet) using keyboard?

Press Ctrl+Home keys together to jump directly to cell A1.

Why are formulas and functions important in accounting spreadsheets?

Formulas and functions automate calculations (e.g., totalling expenses) and ensure consistency and accuracy across financial statements.

Important Board Questions

What is a cell? How is it identified in a spreadsheet? Give one example. [2 marks]

Define cell as intersection of row and column; cell address format is column letter + row number (e.g., D7); mention that it is the unit where data or formulas are entered.

Explain the difference between basic values and derived values in a spreadsheet with a suitable example from an accounting context. Show why derived values are important in financial calculations. [5 marks]

Basic value = independent entry (e.g., Unit Price = Rs 100, Quantity = 5); Derived value = formula result (e.g., Total Cost = Quantity × Unit Price = 500). Show that derived values ensure consistency and automate calculations in financial statements like trial balance and profit & loss statements.

Define and distinguish between relative reference, absolute reference, and mixed reference with examples. Explain how each behaves when a formula containing it is copied from one cell to another. Why is understanding these references critical for building financial templates in Excel? [6 marks]

Relative (C5): shifts when copied (C5→D6 if moved right-down); Absolute ($C$5): locks both row and column, never shifts; Mixed ($C5 or C$5): locks only column or row. Show examples of copying formulas. Explain that templates for Balance Sheets, P&L, and Ratios rely on correct references to prevent formula errors and ensure reusability across multiple periods or companies.

Assertion-Reason Questions for Spreadsheet

Q1. Assertion (A):

An absolute cell reference ($C$4) remains fixed when a formula is copied to other cells.

Reason (R):

Dollar signs ($) preceding both the column letter and row number prevent the cell reference from changing during a copy operation.

Answer: A — Both A and R are true, and R is the correct explanation of A — Both statements are true, and the reason correctly explains why absolute references remain fixed. The dollar signs are the mechanism that locks both row and column, preventing any change when the formula is copied to other locations.

Q2. Assertion (A):

A derived value in a spreadsheet cannot exist without at least one basic value.

Reason (R):

Derived values are calculated using arithmetic expressions or functions that operate on other cell values, which may themselves be basic or derived.

Answer: C — A is true but R is false — The assertion is true (derived values depend on other values through formulas), but the reason is partially misleading. A derived value requires input values, but these input values could themselves be derived. For example, if A = basic value, B = A*2 (derived), and C = B+5 (derived), then C is derived from B which is derived from A. The reason oversimplifies by suggesting all inputs must be basic.

Q3. Assertion (A):

A workbook in Excel is a file containing a single worksheet.

Reason (R):

In Excel 2007, a workbook can contain only one sheet due to memory limitations of the software.

Answer: D — A is false but R is true — Both statements are false. A workbook is a file that contains multiple worksheets (Sheet 1, Sheet 2, Sheet 3 by default), and additional sheets can be added as needed. Excel 2007 has no memory limitation preventing multiple worksheets; this is by design.

Unlock more Assertion-Reason questions in Premium — unlock →

Case-Based Questions (CBQ) for Spreadsheet

Case Study:

Raj Kumar, an accountant at TechCorp India Ltd., is preparing a quarterly expense analysis spreadsheet. He creates columns for Date, Department, Category, Amount (in ₹), and Tax (18% GST). The GST rate of 18% is stored in cell H1. Raj needs to calculate the tax amount for each expense by multiplying the Amount by the GST rate. He enters the formula =E2*(H1/100) in cell F2 for the first row and then copies this formula down to row 51 to cover all expenses. However, when he reviews row 10, he notices the formula shows =E10*(H10/100), which is incorrect because H10 is empty.

Q1. What error did Raj make when creating the formula?

  • A. He should have used an absolute reference for H1 as $H$1 so it remains fixed when copied ✓
  • B. He used too many parentheses in the formula, which causes errors
  • C. The GST rate cannot be expressed as a decimal in the formula
  • D. He should have divided by 100 instead of multiplying

Answer: A

Q2. Which corrected formula should Raj use in F2 to ensure the GST rate in H1 always applies to all rows?

  • A. =E2*($H$1/100) ✓
  • B. =E2*(H2/100)
  • C. =$E$2*($H$1/100)
  • D. =E2*H1

Answer: A

Q3. If Raj uses the corrected formula =E2*($H$1/100) and copies it to F51, what will the formula in F51 display?

  • A. =E51*($H$1/100) ✓
  • B. =E2*($H$2/100)
  • C. =E51*($H$51/100)
  • D. =E50*($H$1/100)

Answer: A

Q4. This scenario demonstrates the importance of using mixed or absolute references in financial spreadsheets because:

  • A. Relative references are slower and cause Excel to crash
  • B. Constants like tax rates or exchange rates must remain fixed across multiple rows of data ✓
  • C. Absolute references automatically prevent data entry errors
  • D. Formulas with absolute references can calculate twice as fast

Answer: B

Unlock more case studies in Premium — unlock →

Previous Year Questions (PYQ) for Spreadsheet

Real CBSE board exam questions from past papers — free to attempt.

CBSE 2026 · Set 67/3/1 · 1 mark

Identify what will be displayed on the screen when we specify an intersection of two areas that do not intersect.

  • Correct a ##### Error
  • Correct a #NULL! Error
  • Correct a #Name Error
  • Correct a #REF! Error

CBSE 2026 · Set 67/3/1 · 1 mark

Which financial function calculates the lump-sum amount for series of future payments?

  • NPER
  • FV
  • PV
  • PMT

CBSE 2026 · Set 67/3/1 · 1 mark

The process of comparing input data with some known data is known as:

  • Data Validation
  • Storage data
  • Data entry
  • Redundant data

CBSE 2026 · Set 67/3/1 · 3 marks

Explain Sequential codes and Block codes with the help of example.

CBSE 2026 · Set 67/3/1 · 6 marks

[Diagram/Map-based question — skipped]

CBSE 2026 · Set 67/3/2 · 1 mark

__________ provides additional information about a data marker to identify the details of data point in a data series.

  • Source data
  • Secondary data
  • Data series
  • Data label

CBSE 2026 · Set 67/3/2 · 6 marks

What is IF function in spreadsheet? Elaborate tests and why? Explain its various types with syntax.

CBSE 2026 · Set 67/4/1 · 1 mark

A column chart can be changed to a pie chart because : I. Only one data series is used to plot it. II. The plotted data values are positive. III. The data values are not equal to zero also. IV. More than seven categories of data can be represented. Identify the correct option :

  • All except point I are correct.
  • All except point II are correct.
  • All except point III are correct.
  • All except point IV are correct.

CBSE 2026 · Set 67/4/1 · 1 mark

A1 : E2 refers to :

  • Rows on Excel sheet
  • Cells between starting point A1 to ending point E2
  • Column on Excel sheet
  • Alphabetic arrangement between A to E

CBSE 2026 · Set 67/4/1 · 1 mark

Which of the following is not contained on formula tab on Excel ribbon ?

  • Page layout
  • Function library
  • Defined names
  • Calculations

CBSE 2026 · Set 67/4/1 · 3 marks

What are Pivot Tables?

CBSE 2026 · Set 67/4/1 · 4 marks

What is the use of 'IF' function in Excel? Explain with an example how to change conditional format ?

See all 74 board PYQs for this chapter — open PYQ tab →

Class 12 Accountancy Formula Sheet

Every formula, ratio and format from all 10 chapters on one page, checked against the NCERT textbook and the CBSE 2026-27 syllabus. Free PDF download.

View the formula sheet →

Class 12 Accountancy Previous Year Questions, Chapter-wise

All 1230 CBSE board questions from 2022–2026, sorted by chapter and question type. Free PDF download.

See all past board questions →

Next chapterUse of Spreadsheet in Business Applications →
Found this useful? Share it with your class group.

You've seen the free preview. Open the full interactive study kit — mind map, instant-graded quiz, progress tracking — free, no sign-in required.

Open Full Study Kit — Free →

Premium unlocks the full Question Bank, Daily Practice Papers, Chapter Quiz and Full Mock Papers for every chapter.