Spreadsheets are used in every office for accounts, salary sheets, stock records and reports. Lok Sewa exams ask about cells, formulas, functions, cell references and error messages, and the Computer Operator practical test has Excel tasks. This note covers all of it in simple language.
1. Spreadsheet basics
A spreadsheet is application software that arranges data in rows and columns and calculates automatically. Microsoft Excel is the most common; others are LibreOffice Calc and Google Sheets. VisiCalc (1979) was the first electronic spreadsheet and Lotus 1-2-3 was popular before Excel.
| Term | Meaning |
|---|---|
| Workbook | The whole Excel file; extension .xlsx (2007 onwards), older .xls |
| Worksheet | One sheet (tab) inside a workbook |
| Row | Horizontal line of cells, numbered 1, 2, 3 … |
| Column | Vertical line of cells, lettered A, B, C … Z, AA, AB … |
| Cell | The box where a row and column meet; its name is the cell address, for example B5 |
| Active cell | The selected cell, shown with a thick border |
| Range | A group of cells, for example A1:A10 |
| Name Box | Shows the address of the active cell |
| Formula bar | Shows and edits the content or formula of the active cell |
From Excel 2007 onwards a worksheet has 1,048,576 rows and 16,384 columns (the last column is XFD). By default, text is aligned left and numbers are aligned right in a cell.
2. Formulas
Every formula in Excel starts with the equal sign (=). Example: =A1+B1. Operators are + (add), – (subtract), * (multiply), / (divide) and ^ (power). Excel follows the normal order of operations: brackets first, then power, then multiply and divide, then add and subtract. So =2+3*4 gives 14, while =(2+3)*4 gives 20.
3. Most asked functions
| Function | What it does | Example |
|---|---|---|
| SUM | Adds numbers | =SUM(A1:A10) |
| AVERAGE | Finds the average (mean) | =AVERAGE(B2:B20) |
| MAX / MIN | Largest / smallest value | =MAX(C1:C30) |
| COUNT | Counts cells that contain numbers | =COUNT(A1:A50) |
| COUNTA | Counts cells that are not empty | =COUNTA(A1:A50) |
| COUNTIF | Counts cells that meet a condition | =COUNTIF(D2:D40,”Pass”) |
| SUMIF | Adds cells that meet a condition | =SUMIF(B2:B9,”>500″) |
| IF | Gives one result if a condition is true and another if false | =IF(C2>=40,”Pass”,”Fail”) |
| ROUND | Rounds to a set number of digits | =ROUND(12.567,1) gives 12.6 |
| TODAY / NOW | Current date / current date and time | =TODAY() |
| VLOOKUP | Looks for a value in the first column of a table and returns a value from another column | =VLOOKUP(101,A2:D50,3,FALSE) |
| LEN / UPPER / LOWER | Length of text / capital letters / small letters | =LEN(A2) |
| CONCAT (CONCATENATE) | Joins text from cells | =CONCAT(A2,” “,B2) |
AutoSum (Σ) adds a column quickly; its shortcut is Alt + =.
4. Cell references: relative, absolute and mixed
- Relative reference (A1): changes when the formula is copied to another cell. This is the default.
- Absolute reference ($A$1): does not change when copied. The $ sign fixes the column and the row.
- Mixed reference ($A1 or A$1): only the column or only the row is fixed.
- Press F4 while editing a formula to switch between these reference types.
5. Error values
| Error | Cause |
|---|---|
| #DIV/0! | A number is divided by zero or an empty cell |
| #VALUE! | Wrong type of data, for example text used in a sum |
| #NAME? | Excel does not recognise a function name or text in the formula |
| #REF! | The formula refers to a cell that has been deleted |
| #N/A | A value is not available, often from VLOOKUP |
| #NUM! | An invalid number, for example the square root of a negative number |
| ##### | The column is too narrow to show the number or date |
6. Useful tools
- Fill handle and AutoFill: drag the small square at the corner of a cell to copy a formula or continue a series such as 1, 2, 3 or Jan, Feb, Mar.
- Sort and Filter: arrange data in order or show only rows that match a condition (Data tab).
- Freeze Panes: keeps headings visible while scrolling (View tab).
- Merge and Center: joins cells and centres the text, used for titles.
- Conditional Formatting: colours cells automatically by rule, for example marks below 40 in red.
- Data Validation: allows only correct entries, such as a list or a number range.
- Pivot Table: summarises a large table quickly (Insert tab).
7. Charts
Charts are inserted from the Insert tab. A column or bar chart compares values, a line chart shows change over time, a pie chart shows parts of a whole (only one data series), and a scatter (XY) chart shows the relation between two sets of numbers. Shortcut F11 creates a chart on a new sheet; Alt + F1 creates it on the same sheet.
8. Excel shortcuts
| Shortcut | Action |
|---|---|
| F2 | Edit the active cell |
| F4 | Toggle absolute and relative reference (or repeat last action) |
| Ctrl + ; | Insert today’s date |
| Ctrl + Shift + ; | Insert the current time |
| Alt + Enter | New line inside the same cell |
| Ctrl + 1 | Format Cells dialog |
| Ctrl + Home | Go to cell A1 |
| Ctrl + Arrow key | Jump to the edge of the data |
| Shift + F11 | Insert a new worksheet |
| Ctrl + Page Down / Page Up | Next / previous worksheet |
| Ctrl + Space / Shift + Space | Select the whole column / whole row |
📌 Key points to remember
- A workbook (.xlsx) contains worksheets; a cell is named by column letter and row number, such as B5.
- Excel 2007 onwards: 1,048,576 rows and 16,384 columns (last column XFD).
- Every formula starts with =; =2+3*4 gives 14.
- COUNT counts numbers only; COUNTA counts all non-empty cells.
- $A$1 is absolute, A1 is relative, $A1 and A$1 are mixed; F4 switches between them.
- #DIV/0! means division by zero; ##### means the column is too narrow.
- A pie chart shows parts of a whole; a line chart shows a trend over time.
- Ctrl + ; inserts the date, Alt + Enter gives a new line in a cell, Alt + = is AutoSum.
Test yourself: MS Excel Notes
50 questions check what you read about Spreadsheet basics, Error values and Excel shortcuts. Each attempt picks 25 at random.