Spreadsheets and desktop databases (Excel, Access)
Excel calculates with formulas and charts in a grid of cells; Access stores related records in tables and answers questions with queries.
Spreadsheet basics
A worksheet is a grid of columns (A, B, C …) and rows (1, 2, 3 …). Each box is a cell, named by its address, such as C5. A cell holds a label (text), a value (number) or a formula, which always starts with =. When an input changes, every formula that uses it recalculates at once, which makes Excel ideal for budgets and what-if analysis.
Useful Excel functions
| Function | What it does | Example |
|---|---|---|
| =SUM(B2:B10) | Adds a range of cells | Total sales for the month |
| =AVERAGE(B2:B10) | Finds the mean | Average daily sales |
| =MAX(B2:B10) / =MIN(B2:B10) | Highest / lowest value | Best and worst day |
| =COUNT(B2:B10) | Counts cells that contain numbers | Number of days with sales |
| =IF(B2>=50000,"Bonus","No bonus") | Tests a condition and shows one of two results | Staff bonus check |
| =VLOOKUP(A2,PriceList,2,FALSE) | Finds a value in the first column of a table and returns a matching value | Look up a price by product code |
A sales sheet with relative and absolute references
A shop records sales in columns B (quantity) and C (unit price): tea packets 12 at Rs. 450, biscuits 8 at Rs. 1,250, soap 25 at Rs. 120. A 5% discount rate is in cell F1. Find each line value, the total, the average and the discount.
- D2: =B2*C2 → 12 × 450 = Rs. 5,400
- Copy down. D3: =B3*C3 → 8 × 1,250 = Rs. 10,000. D4: =B4*C4 → 25 × 120 = Rs. 3,000. The relative references change row by row.
- D5: =SUM(D2:D4) → 5,400 + 10,000 + 3,000 = Rs. 18,400
- D6: =AVERAGE(D2:D4) → 18,400 ÷ 3 = Rs. 6,133.33
- D7: =D5*$F$1 → 18,400 × 5% = Rs. 920. $F$1 is absolute, so it stays fixed if the formula is copied.
- D8: =D5-D7 → 18,400 − 920 = Rs. 17,480
Total Rs. 18,400; average Rs. 6,133.33; discount Rs. 920; net Rs. 17,480.
Relative vs absolute reference
The four main Access objects
| Object | What it does | Example |
|---|---|---|
| Table | Stores the data in rows (records) and columns (fields) | Customers table |
| Query | Asks a question: filters, sorts and joins tables | Customers in Colombo who owe more than Rs. 50,000 |
| Form | A screen for entering and viewing one record at a time | New customer entry form |
| Report | A formatted printout with groups and totals | Monthly sales report by area |
When to use Excel and when to use Access
A small Sri Lankan business can keep its monthly budget and sales analysis in Excel in rupees, and its customer, supplier and order records in Access, printing a monthly report of outstanding dues.
Short answer: “What is the difference between a relative and an absolute cell reference?” A relative reference (B2) changes when a formula is copied; an absolute reference ($B$2) stays fixed.
Structured question: “Explain the four main objects of a Microsoft Access database.” Tables store data, queries ask questions, forms enter and view data, reports print formatted summaries. Give one example of each.
Essay: “Explain how Excel and Access help a business manage and analyse its data.” Explain cells, formulas, functions, relative and absolute references, charts and what-if analysis in Excel; then tables, queries, forms and reports in Access, with examples.
Every formula starts with =. A $ freezes a reference. Access objects = T-Q-F-R: Tables store, Queries ask, Forms enter, Reports print.