Advanced Spreadsheets Tools is a 2-credit SEC (30 hours) that DU marks entirely on practical files and viva, not theory. The syllabus lists functions, but the exam asks you to make a sheet. Students who try to learn this by reading PDFs score 4/10; those who open Excel score 8/10.
What DU asks — unit by unit
- Unit 1: Basic functions — VLOOKUP, HLOOKUP, IF, COUNTIF, SUMIF. Expect: “Create a student table with 50 rows and find toppers using VLOOKUP.”
- Unit 2: Data tools — Sort, Filter, Conditional Formatting, Pivot Tables. Pivot Table is the 15-mark question every year.
- Unit 3: Charts — Column, Pie, Line with trendline. One chart with correct labels = 10 marks.
- Unit 4: What-if, Goal Seek, basic macros — the viva part.
2-day crash plan
- Day 1 morning: Make one workbook with 3 sheets — “Marks”, “Sales”, “Attendance”. Practise VLOOKUP across sheets and Conditional Formatting for >90%.
- Day 1 evening: Make a Pivot Table from the same data, add a chart. Save as PDF — that’s your practical file draft.
- Day 2: Do Goal Seek (“what marks needed for 60%”) and record a 3-step macro. Then open SEC → Advanced Spreadsheets → PYQs here — most colleges reuse the same viva questions: “Difference between VLOOKUP and XLOOKUP?”
Sulaksh keeps the Syllabus and PYQs for this SEC semester-wise; notes are not theory pages but step lists with screenshots — vanished once the verified PDF is uploaded.
Function drills: the fifteen formulas that matter
Examiners reuse the same fifteen functions, so drill them as muscle memory rather than reading about them. Lookup family first: VLOOKUP (exact vs approximate match — the fourth argument is the most-tested single character in this paper), HLOOKUP, and XLOOKUP as the modern replacement students should mention in viva to sound current. Conditional aggregation: SUMIF, COUNTIF, AVERAGEIF, then their plural siblings SUMIFS and COUNTIFS for multi-condition problems. Logic: IF nested two deep (three deep is where exam questions stop), AND/OR wrappers, IFERROR for clean outputs. Text and date: LEFT/RIGHT/MID, CONCAT, TODAY, DATEDIF for age/service calculations. The drill method: one workbook, one sheet per function family, each sheet solving the same student-marks dataset a different way — toppers via LARGE, pass-fail via IF, grade bands via nested IF, department averages via AVERAGEIF. When every function has touched the same data, the viva question “which function where?” answers itself. And always show formulas (Ctrl+~) in screenshots for your file — a value without its visible formula earns nothing.
The Pivot Table masterclass (fifteen marks, one sitting)
The Pivot question arrives every year wearing different data, so learn the moves, not the example. Insert PivotTable from a clean table (no blank rows, headers in row one — dirty data is how students lose the first five marks before starting). Drag fields deliberately: rows for categories, values as Sum/Count/Average (right-click to change aggregation — the examiner checks you know Count is default for text), filters for years or departments, slicers for presentation flair. Group dates by month and quarter (right-click grouping is a two-mark move students miss). Add a calculated field for one derived metric. Then the chart: clustered column for comparisons, pie only for shares of a whole, line with trendline for time data — each with titled axes, data labels, and a sourced title. Finish by refreshing after editing source data (proves the Pivot is live, not pasted values). Rehearse this entire sequence on three different datasets and the fifteen marks become procedural — insert, drag, group, chart, refresh — five verbs, full marks.
Viva Q-bank and file craft
The viva draws from a stable bank — rehearse answers aloud, not in your head. Difference between VLOOKUP and XLOOKUP (exact match default, leftward lookup, single function replacing INDEX-MATCH). Absolute vs relative references ($ signs — demonstrate by dragging a formula). Pivot vs formulas (dynamic summarisation vs static calculation). Chart choice rules (comparison, composition, trend). What Goal Seek does versus Solver. How to record a macro and where it is stored. One-line answers, each with a ten-second demo on your laptop if allowed — showing beats telling in practical vivas. Your file carries 25 marks before you speak: title page with paper code, index, one experiment per page (aim, steps numbered, screenshot with formulas visible, output, one-line conclusion), printed cleanly or saved as PDF. Examiners flip files fast; clear headings and visible formulas decide the internal marks in ninety seconds. Sulaksh keeps the Syllabus and PYQs for this SEC semester-wise — most colleges recycle viva questions, so the PYQ file is effectively the viva preview.
Lab assistants get asked
Excel, Sheets, or LibreOffice? Whichever your college lab uses — functions transfer, but menu paths and Pivot dialogs differ. Practise on the exam software exclusively in the final week; interface surprises cost more marks than formula gaps.
Can theory alone pass this SEC? No — the paper is marked on files made and tasks performed. Reading about PivotTables without building three scores what reading about swimming scores in a pool test. Open the application.
Where are the PYQs? On Sulaksh — SEC → Advanced Spreadsheets → PYQs, or All PYQs. Viva questions repeat most, so read those first.