Study guide
13.4 MS Excel — Formulas, Functions & Charts
Karnataka Village Administrative Officer VAO
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
About the Karnataka VAO Exam Selection Process & How to Use This Guide
2 Chapter 1: Eligibility, Exam Pattern & Marking
1.1 Eligibility, Age Limits & Application Fee 1.2 Exam Pattern, Negative Marking & OMR Rules
3 Chapter 2: Current Events (Paper 1)
2.1 Current Affairs — National & International 2.2 Karnataka Current Affairs, Schemes & State Institutions
4 Chapter 3: General Science & Daily Comprehension (Paper 1)
3.1 General Science — Physics & Everyday Phenomena 3.2 General Science — Chemistry & Everyday Materials 3.3 General Science — Biology, Health & Nutrition
5 Chapter 4: The Constitution of India (Paper 1)
4.1 The Constitution — Making, Preamble, Citizenship & Salient Features 4.2 Fundamental Rights, Fundamental Duties & Directive Principles 4.3 Union Executive, Parliament & the Law-Making Process 4.4 Judiciary, Centre–State Relations, Amendments & Constitutional Bodies
6 Chapter 5: History of India (Paper 1)
5.1 Ancient India — Indus Valley to the Guptas 5.2 Medieval India — Delhi Sultanate, Mughals, Bhakti & the Marathas 5.3 Modern India, Colonial Rule & the Freedom Struggle
7 Chapter 6: History of Karnataka (Paper 1)
6.1 Ancient & Early Medieval Karnataka — Kadambas to Hoysalas 6.2 Vijayanagara, the Deccan Sultanates & the Mysore Kingdom 6.3 Karnataka Personalities, Freedom Fighters & the Unification Movement
8 Chapter 7: Geography of India & Karnataka (Paper 1)
7.1 Indian Geography — Location, Extent & Physical Divisions 7.2 Indian Geography — Rivers, Climate & the Monsoon 7.3 Indian Geography — Soils, Agriculture, Minerals & Population 7.4 Karnataka Geography — Physiography, Western Ghats & Rivers 7.5 Karnataka Geography — Divisions, Districts, Climate, Soils & Wildlife
9 Chapter 8: State & Territorial Administration (Paper 1)
8.1 Karnataka State & District Administration 8.2 Revenue Administration & the Record of Rights (RTC / Pahani) 8.3 Survey, Settlement & Karnataka's Digital Land-Record Systems 8.4 VAO Role, Village Records & Document Procedures 8.5 Karnataka Land Revenue Act, Land Reforms & Revenue Courts
10 Chapter 9: Karnataka Economy, Rural Development, Panchayat Raj & Co-operatives (Paper 1)
9.1 Karnataka Economy — Sectors, Agriculture, Industry & Budget 9.2 Rural Development Schemes & the Employment Guarantee 9.3 Rural Co-operative Societies & Agricultural Credit 9.4 Panchayat Raj — 73rd Amendment & the Karnataka Grama Swaraj Act 9.5 Panchayat Functions, Finances & the Revenue–Panchayat Interface 9.6 Social Welfare, Food Security & Karnataka's Guarantee Schemes
11 Chapter 10: Environmental Issues & Development of Karnataka (Paper 1)
10.1 Environmental Issues & Development of Karnataka 10.2 The Western Ghats, Biodiversity & Conservation in Karnataka 10.3 Pollution, Climate Change, Water Disputes & Renewable Energy
12 Chapter 11: General Kannada (Paper 2)
11.1 Kannada Grammar I — Varnamale & Sandhi 11.2 Kannada Grammar II — Samasa, Linga-Vachana & Vibhakti 11.3 Kannada Grammar III — Karaka, Kriyapada, Alankara & Chandas 11.4 Kannada Vocabulary, Idioms, Proverbs & Comprehension 11.5 Kannada Literature — Poets, Movements & Jnanpith Awardees
13 Chapter 12: General English (Paper 2)
12.1 English Grammar I — Tenses, Articles, Prepositions & Agreement 12.2 English Grammar II — Voice, Narration, Conditionals & Transformation 12.3 English Vocabulary, Idioms & Commonly Confused Words 12.4 Reading Comprehension, Error Spotting & Sentence Improvement
14 Chapter 13: Computer Knowledge (Paper 2)
13.1 Computer Fundamentals, Generations & Hardware 13.2 Software, Operating Systems & File Management 13.3 MS Word & MS PowerPoint 13.4 MS Excel — Formulas, Functions & Charts 13.5 Internet, Networking, Cybersecurity & e-Governance
15 Chapter 14: The Compulsory Kannada Language Test
14.1 Compulsory Kannada Language Test — Rules & Preparation 14.2 Optional Kannada Practice — Reading, Writing & Translation
16 Chapter 15: Exam Strategy, Time Management & Mock Practice
15.1 Exam Strategy, Time Management & Mock Practice
13.4 MS Excel — Formulas, Functions & Charts
Key Takeaways Explain This Topic AI
A worksheet is a grid of cells addressed by column letter and row number, and a modern worksheet has 1,048,576 rows and 16,384 columns.
A relative reference such as B2 shifts when a formula is copied, an absolute reference such as $B$2 stays fixed, and a mixed reference such as $B2 locks only the column.
Every formula begins with an equals sign, and the order of evaluation follows brackets, then exponent, then multiplication and division, then addition and subtraction.
Useful functions to practise include SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, IF, COUNTIF, SUMIF and VLOOKUP.
A column chart compares values, a pie chart shows proportions of a whole and a line chart shows a trend over time.
Last updated: October 2026
Microsoft Excel
Excel organises data in a workbook made of worksheets . Each worksheet is a grid of cells addressed by column letter and row number (for example, B3 ). A modern Excel worksheet has 1,048,576 rows and 16,384 columns (columns run up to XFD) — far more than any revenue office will ever fill.
Referencing, Ranges and Operators
- Concept Notation Behaviour when copied
- Relative reference B2 Unfixed row/column adjust by the copy offset
- Absolute reference $B$2 Neither shifts
- Mixed reference $B2 Column fixed; row adjusts by vertical offset
- Mixed reference B$2 Row fixed; column adjusts by horizontal offset
- Range B2:B10 A contiguous block
- Whole column / row B:B , 2:2 Entire column or row
- Another sheet Sheet2!B2 Reference across sheets
Operators and precedence: brackets first, then exponent ^ , then * and / , then + and - , then comparison operators. So =2+3*4 is 14 , not 20; =(2+3)*4 is 20 .
Common error values, and what each tells you:
- Error Meaning
- #DIV/0! Division by zero or by an empty cell
- #VALUE! Wrong data type — text where a number is expected
- #REF! The referenced cell has been deleted
- #NAME? A misspelt function name or an undefined name
- #N/A A lookup found no match
- ##### The column is too narrow to display the value — widen it
- The Function Set to Learn
- Function Purpose Example
- SUM Total =SUM(C2:C31)
- AVERAGE Mean =AVERAGE(C2:C31)
- COUNT / COUNTA Count numeric cells / count non-empty cells =COUNTA(A2:A31)
- MAX / MIN Largest / smallest =MAX(C2:C31)
- IF Conditional result =IF(D2>=35,"Qualified","Not qualified")
- COUNTIF / SUMIF Count or total the cells meeting a condition =COUNTIF(E2:E31,"Dry")
- ROUND Round to a number of decimals =ROUND(F2,2)
VLOOKUP Find a value in the first column of a table and return a value from another column =VLOOKUP(A2,Villages!$A$2:$C$50,3,FALSE)
- CONCATENATE / & Join text =A2&" "&B2
- LEN , LEFT , RIGHT , MID , TRIM , UPPER , LOWER Text handling =LEFT(A2,3)
- TODAY / NOW Current date / date and time =TODAY()
- SORT , FILTER (newer versions) Arrange and select rows —
- Worked Calculations from a Village Office
1. Total extent held. A holder's parcels are listed in acres in C2:C6. =SUM(C2:C6) gives the total; =ROUND(SUM(C2:C6),2) presents it to two decimals.
2. Acres and guntas. Column C holds extents in guntas. Acres = =INT(C2/40) and the remaining guntas = =MOD(C2,40) . For 180 guntas this returns 4 acres and 20 guntas — the same partition arithmetic as in Chapter 8, done for a whole village at once.
3. Water rate at a fixed rate. If the rate per acre is in $B$1 and each holder's irrigated extent is in column D, then =D2*$B$1 copied down the column applies the same rate to every row. This is exactly why the absolute reference matters: written as =D2*B1 , copying the formula would shift the rate cell down and every row after the first would be wrong.
4. Demand, collection and balance. With demand in column E and collection in column F, balance is =E2-F2 , total collection =SUM(F2:F100) , and the collection percentage =ROUND(SUM(F2:F100)/SUM(E2:E100)*100,1) .
5. Conditional counting. To count how many parcels are dry land, where classification is in column G: =COUNTIF(G2:G100,"Dry") . To total the extent of only the dry parcels: =SUMIF(G2:G100,"Dry",C2:C100) .
Sorting, Filtering and Presentation
Sort rearranges rows by one or more columns, ascending or descending — always select the whole data block, or the rows will be scrambled relative to each other.
Filter hides rows that do not meet a criterion, without deleting anything.
Freeze Panes keeps the header row visible while scrolling a long village list.
Conditional formatting colours cells meeting a rule — for instance highlighting every unpaid balance.
Pivot table summarises a large table by category, such as total extent by land classification.
Data validation restricts what may be entered in a cell, preventing typing errors in a long data-entry job.
Charts: a column or bar chart compares values across categories, a pie chart shows the parts of a whole (use it only when the parts genuinely sum to 100%), a line chart shows change over time, and a scatter chart shows the relationship between two variables. Practise matching a chart type to the data: monthly collection over a year is a line chart; the share of dry, wet and garden land is a pie chart.
Exam Angle for Section 13.4
Four useful study topics are the relative versus absolute reference distinction, the SUM / AVERAGE / COUNT / IF function set, the error values and what each means, and chart-type selection . Do the acres-and-guntas and the water-rate examples above by hand once; they practise both arithmetic and the referencing rule in a village-office setting.