Meaning: Computer program to store data in rows & columns grid for numerical/financial calculations.
Examples: Microsoft Excel, LibreOffice Calc, Lotus 1-2-3.
Features: - Easy calculations across sheets - What-If analysis - Database functions - Data sorting & filtering - Dynamic charts
COMPONENTS OF SPREADSHEET
Workbook: File containing worksheets.
Worksheet: Single page with rows & columns.
Cell: Intersection of row & column (e.g., A1).
Range: Group of selected cells (e.g., B2:D8).
Named Range: Meaningful name for a range (e.g., "Score" for C2:C15).
DATA TYPES
Value: Numerical data, right-aligned (2500, ₹5,000).
Label: Text data, left-aligned (Name, City).
Formula: Starts with = sign (=A1+B1).
CELL REFERENCING
Relative: Changes when copied (=C5+D5 becomes =C6+D6).
Absolute ($): Remains constant (=$A$1+$B$1).
Mixed: Partly absolute, partly relative (=A$1+$B1).
DATE & TIME FUNCTIONS
TODAY(): Current system date.
=TODAY()NOW(): Current date & time.
=NOW()YEAR(): Extracts year.
=YEAR("date")MONTH(): Extracts month (1-12).
=MONTH("date")DAY(): Extracts day (1-31).
=DAY("date")DATEVALUE(): Converts text date to serial number (base 30/12/1899).
=DATEVALUE("date_text")DATE(): Returns date from year, month, day.
=DATE(year, month, day)
MATHEMATICAL FUNCTIONS
SUM(): Adds numbers.
=SUM(range)SUMIF(): Sums cells meeting condition.
=SUMIF(range, criteria, sum_range)ROUND(): Rounds to specified digits.
=ROUND(number, count)ROUNDUP(): Rounds up away from zero.
=ROUNDUP(number, count)ROUNDDOWN(): Rounds down towards zero.
=ROUNDDOWN(number, count)
STATISTICAL FUNCTIONS
COUNT(): Counts cells with numbers only.
=COUNT(range)COUNTA(): Counts non-empty cells.
=COUNTA(range)COUNTBLANK(): Counts empty cells.
=COUNTBLANK(range)COUNTIF(): Counts cells meeting condition.
=COUNTIF(range, criteria)
LOGICAL FUNCTIONS
IF(): Returns value based on condition.
=IF(logical_test, value_if_true, value_if_false)AND(): TRUE if all arguments TRUE.
=AND(logical1, logical2, ...)OR(): TRUE if at least one TRUE.
=OR(logical1, logical2, ...)
TEXT & LOOKUP FUNCTIONS
TEXT(): Converts number to text format.
=TEXT(number, format)CONCATENATE(): Joins text strings.
=CONCATENATE(text1, text2, ...)VLOOKUP(): Searches first column, returns from same row.
=VLOOKUP(search_criterion, array, index, sort_order)HLOOKUP(): Searches first row, returns from same column.
=HLOOKUP(search_criterion, array, index, sort_order)
FINANCIAL FUNCTIONS
PMT(): Periodic payment (EMI).
=PMT(rate, nper, pv, [fv], [type])PV(): Present value.
=PV(rate, nper, pmt, [fv], [type])FV(): Future value.
=FV(rate, nper, pmt, [pv], [type])RATE(): Interest rate per period.
=RATE(nper, pmt, pv, [fv], [type], [guess])SLN(): Straight line depreciation.
=SLN(cost, salvage, life)DB(): Declining balance depreciation.
=DB(cost, salvage, life, period, [month])NPER(): Number of payments.
=NPER(rate, pmt, pv, [fv], [type])
DATA TOOLS
Auto Fill: Drag handle to copy/create series.
Fill Series: Linear, growth, date series (Edit → Fill → Series).
Data Validation: Restrict entries (list, whole number, date).
Conditional Formatting: Highlight cells based on rules.
Pivot Table: Interactive tool to summarise large datasets.
DATA TABLES
One-variable Data Table: Multiple results by changing one input.
Two-variable Data Table: Results by changing two variables.
ERROR CODES IN CALC
###: Column too narrow.
#DIV/0! (532): Division by zero.
#NAME? (525): Text not recognised.
#REF! (524): Invalid cell reference.
#VALUE! (519): Wrong argument type.
#NUM! (503): Number too large/small.
IMPORTANT EXAM QUESTIONS (PAST YEARS)
Q1 (2019 Mar): Argument for number of payments in financial function? Ans: NPER.
Q2 (2020 Say): DATEVALUE default base date? Ans: 30-12-1899.
Q3 (2021 Mar): Unique cell name? Ans: Cell address.
Q4 (2021 Say): Pre-defined formula? Ans: Function.
Q5 (2021 Say): Odd one: IF, AND, OR, DAY? Ans: DAY (Date function).
Q6 (2018 Mar): =ROUND(999.99,0)? Ans: 1000. =ROUNDDOWN(999.99,0)? 999. =ROUND(123.45,-1)? 120. =ROUNDUP(123.45,0)? 124.
Q7 (2018 Say): Absolute ref for 8th column, 15th row? Ans: $H$15. Range D5 to J20? $D$5:$J$20.
Q8 (2019 Say): Convert A5 to absolute & mixed? Ans: $A$5, A$5, $A5.
Q9 (2018 Say): SLN function use? Ans: Fixed installment method depreciation.
Q10 (2018 Mar): SLN, DB, PMT syntax? Ans: SLN(cost,salvage,life); DB(cost,salvage,life,period); PMT(rate,nper,pv).
Q11 (2018 Say): DA formula (14% of C4)? Ans: =C4*14%. HRA formula (>55500=2500 else 1500)? =IF(C4>55500,2500,1500). Gross pay? =C4+D4+E4.
Q12 (2019 Mar): SLN vs DB difference? Ans: SLN = equal depreciation each year; DB = reducing depreciation each year.