Master MS-Excel and OpenOffice Calc — from entering data and writing formulas to creating charts, pivot tables, and professional worksheets.
A Spreadsheet is an electronic document organised into rows and columns for storing, organising, and analysing data. Each intersection of a row and column is called a Cell. Cells can contain text, numbers, dates, or formulas that automatically calculate results.
MS-Excel (Microsoft) and OpenOffice Calc (Apache) are the two spreadsheet applications covered in COA. They are used for payroll, accounting, data analysis, inventory, grade sheets, budgets, and generating graphs and charts.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee Name | Basic Pay | DA (90%) | HRA (5%) | Gross Pay |
| 2 | Arjun Kumar | 18000 | =B2*0.9 | =B2*0.05 | =B2+C2+D2 |
| 3 | Priya Devi | 22000 | =B3*0.9 | =B3*0.05 | =B3+C3+D3 |
| 4 | Suresh Raja | 15500 | =B4*0.9 | =B4*0.05 | =B4+C4+D4 |
| Term | Meaning | Example |
|---|---|---|
| Workbook | The entire Excel file. Contains one or more worksheets. | Salary_2026.xlsx |
| Worksheet (Sheet) | A single tab/page within a workbook. Contains rows and columns. | Sheet1, Sheet2 |
| Row | Horizontal line of cells. Numbered 1, 2, 3 … 1,048,576. | Row 3 |
| Column | Vertical line of cells. Labelled A, B, C … XFD (16,384 columns). | Column B |
| Cell | The single box at the intersection of a row and column. | Cell B3 |
| Cell Reference | The address of a cell: column letter + row number. | A1, C5, D10 |
| Cell Range | A rectangular block of cells. Colon (:) separates start and end. | A1:D10, B2:B12 |
| Active Cell | The currently selected cell. Shown by a green/blue border and in the Name Box. | Cell B3 (shown in Name Box) |
| Formula Bar | Bar above the grid that shows/edits the content or formula of the active cell. | =SUM(B2:B12) |
| Name Box | Shows the cell reference of the active cell. Type a cell address to navigate there. | B3 |
| Formula | An expression starting with = that performs a calculation. | =A1+B1, =SUM(A1:A10) |
Microsoft's spreadsheet. Part of MS Office. Format: .xlsx. Most widely used in offices and businesses.
Free open-source spreadsheet. Format: .ods. Compatible with Excel. Part of Apache OpenOffice suite.
Fork of OpenOffice. Free and well-maintained. Excellent Excel compatibility. Preferred on Linux.
Cloud-based, free spreadsheet in browser. Real-time collaboration. Saves automatically to Google Drive.
File → New → Blank Workbook or Ctrl+N.
Ctrl+O → browse for .xlsx, .xls, .ods, .csv files.
Ctrl+S to save. F12 to save as different name or format (PDF, CSV, ODS).
Page Layout → Margins → choose Normal, Wide, Narrow or Custom.
File → Save As → choose .pdf, .csv, .ods, .txt to export in different formats.
File → Share → Email as attachment or save to OneDrive for collaboration.
| Navigation Action | Keyboard / Method |
|---|---|
| Move one cell (any direction) | Arrow keys |
| Move to next cell to the right | Tab |
| Move to next row down | Enter |
| Move to cell A1 (start) | Ctrl+Home |
| Move to last used cell | Ctrl+End |
| Move to first cell in row | Home |
| Go to specific cell | Click Name Box → type address (e.g., D15) → Enter |
| Go To dialog box | Ctrl+G or F5 |
| Select entire column | Click column header (A, B, C…) |
| Select entire row | Click row header (1, 2, 3…) |
| Select multiple cells | Click and drag OR hold Shift + click |
| Select non-adjacent cells | Hold Ctrl while clicking cells |
Click any cell and start typing to enter data. Press Enter to confirm and move down, or Tab to confirm and move right. To edit existing data, double-click the cell or press F2.
| Operation | How to Perform | Note |
|---|---|---|
| Enter Data | Click cell → type → press Enter / Tab | Numbers right-align; text left-aligns by default |
| Edit Data | Double-click cell or press F2 | Cursor appears inside cell for editing |
| Delete Data | Select cell(s) → press Delete | Clears content but keeps formatting |
| Cut | Ctrl+X | Moving cells — removes from source after paste |
| Copy | Ctrl+C | Marching ants border shows copied range |
| Paste | Ctrl+V | Paste Special (Ctrl+Alt+V) — paste values only |
| Undo / Redo | Ctrl+Z / Ctrl+Y | Up to 100 undo steps available |
| Find | Ctrl+F | Search for text or values in the sheet |
| Find & Replace | Ctrl+H | Replace one value with another throughout |
| Insert Cell/Row/Column | Right-click row/column header → Insert | Existing data shifts down or right |
AutoFill automatically completes a series of values when you drag the Fill Handle (small green square at the bottom-right corner of the selected cell) across adjacent cells.
| Starting Value | AutoFill Result |
|---|---|
| 1, 2 (select both) | 3, 4, 5, 6, 7 … |
| January | February, March, April … |
| Mon | Tue, Wed, Thu, Fri … |
| Q1 | Q2, Q3, Q4 … |
| 10, 20 (select both) | 30, 40, 50, 60 … |
| 2026-01-01 | 2026-01-02, 2026-01-03 … |
| =A1*2 (formula) | =A2*2, =A3*2, =A4*2 … (copies formula) |
Tip: To fill continuous S.No. (1, 2, 3…), type 1 in a cell and 2 below it, select both cells, then drag the fill handle downward. Alternatively, use Fill → Series (Home → Fill → Series) for large ranges.
=Sheet2!B5 pulls data from Sheet2, cell B5.Ctrl+Home to quickly jump back to cell A1 from anywhere in the sheet.=SheetName!CellAddress — e.g., =Summary!D5.Ctrl+Shift+End to select from the current cell to the last used cell — useful for selecting all data.A Formula is an expression that starts with = and performs calculations using cell references, operators, numbers, and functions. Excel recalculates automatically whenever input data changes.
| Operator | Symbol | Example | Result |
|---|---|---|---|
| Addition | + | =A1+B1 | Sum of A1 and B1 |
| Subtraction | - | =C1-D1 | C1 minus D1 |
| Multiplication | * | =B2*0.9 | 90% of B2 (used for DA) |
| Division | / | =E5/12 | E5 divided by 12 |
| Exponentiation | ^ | =A1^2 | A1 squared (A1²) |
| Percentage | % | =B2*5% | 5% of B2 (same as B2*0.05) |
| Type | Syntax | Behaviour when Copied | Use When |
|---|---|---|---|
| Relative | =A1+B1 | Row and column adjust: =A2+B2, =A3+B3… | Repeating same calculation for each row |
| Absolute | =$A$1+B1 | $A$1 stays fixed; B1 adjusts | Tax rate, exchange rate in one fixed cell |
| Mixed Row | =A$1 | Row fixed, column adjusts | Multiplication table headers |
| Mixed Col | =$A1 | Column fixed, row adjusts | Fixed column with varying rows |
Tip: Press F4 while editing a cell reference to cycle through all four reference types (A1 → $A$1 → A$1 → $A1 → A1). This is the fastest way to add dollar signs.
Mathematical Functions
Logical Function — IF
Nested IF explained: The formula checks marks in F2. If ≥75 → "Distinction". Else if ≥60 → "I Class". Else if ≥50 → "II Class". Otherwise → "Fail". This is the exact formula used in COA Practical Exercise 16.
Text and Lookup Functions
=A1&" "&B1
=A1&" "&B1 → "Arjun Kumar"
=$B$1 stays fixed when copied.F4 while editing to toggle between relative and absolute references.=SUM(B2:B12) is faster and more reliable than =B2+B3+B4+…+B12.=TODAY() auto-updates every day — perfect for invoices and reports.Formatting controls how data appears in cells — number format, font, colour, borders, and alignment. Select cells, then use Home tab or right-click → Format Cells (Ctrl+1) to open the Format Cells dialog.
| Format Tab | Options Available |
|---|---|
| Number | General, Number (decimal places), Currency (₹), Percentage (%), Date, Time, Text, Custom (#,##0.00) |
| Alignment | Horizontal (Left/Centre/Right/Justify), Vertical (Top/Middle/Bottom), Wrap Text, Merge Cells, Indent, Text Orientation/Angle |
| Font | Font family, Style (Bold/Italic), Size, Colour, Underline, Strikethrough, Superscript, Subscript |
| Border | Outline border, inside gridlines, custom borders (top/bottom/left/right), border style, colour |
| Fill (Shading) | Background colour, pattern colour, pattern style for cells |
| Protection | Lock cells from editing, hide formulas — requires sheet to be protected to take effect |
| Protection Feature | How to Apply |
|---|---|
| Protect Sheet | Review → Protect Sheet → set password. Prevents others from editing cells, inserting rows, or changing formatting. |
| Protect Workbook | Review → Protect Workbook. Prevents adding, deleting, or renaming sheets. |
| Lock Specific Cells | Select cells to lock → Format Cells → Protection → check Locked → then protect the sheet. |
| Hide Formulas | Format Cells → Protection → check Hidden. Formulas are invisible in formula bar when sheet is protected. |
| Password to Open | File → Info → Protect Workbook → Encrypt with Password. |
Ctrl+1 to quickly open the Format Cells dialog from any selected cell.Sorting rearranges rows based on the values in one or more columns. Ascending (A→Z or 0→9) or Descending (Z→A or 9→0).
No need to select the entire table — Excel detects the range automatically.
Data → Sort. Or click the AZ↑ button for quick ascending sort on the selected column.
Select the column to sort by (e.g., "Name" or "Total Marks"). Choose Ascending or Descending. Add additional levels for multi-column sort.
Filter temporarily hides rows that don't match certain criteria, so you can view only the relevant data. The original data is not deleted.
Click anywhere in data → Data → Filter (or Ctrl+Shift+L). Drop-down arrows appear in each column header.
Click the drop-down arrow in a column → uncheck items to hide, or use "Text Filters" / "Number Filters" for custom conditions.
Data → Clear (to remove all filters) or click the drop-down → Select All to restore all rows.
Data Consolidation combines data from multiple ranges (across sheets or workbooks) into a single summary. Useful for combining departmental budget data, quarterly reports, or branch-wise totals.
Select the cell where the consolidated result should appear.
Data → Consolidate. Choose function (Sum, Average, Max, Min, Count).
Click in Reference box → select data range from Sheet1, Sheet2, etc. → click Add for each range.
Excel combines all selected ranges using the chosen function and places the result in the target area.
Subtotal (Data → Subtotal) automatically inserts subtotal rows for groups of data. For example, in a sales sheet sorted by Region, it can insert a sum for each region group automatically. Use =SUBTOTAL(9, range) in formulas — function 9 = SUM, 1 = AVERAGE, 2 = COUNT.
Data Validation (Data → Data Validation) restricts what users can enter in a cell. For example, only allow whole numbers between 0–100 in a marks column, or only allow dates after 01/01/2026. If the user enters an invalid value, Excel shows an error message.
Pivot Table is a powerful tool that summarises, analyses, and presents large data sets in a compact, interactive table. You can drag and drop fields to change the view instantly — no formulas needed.
Click anywhere in your data table (must have column headers).
Insert → PivotTable → choose New Worksheet. Click OK.
In the PivotTable Fields panel: drag "Region" to Rows, "Product" to Columns, "Sales" to Values (set to Sum). The summary table builds automatically.
Right-click the pivot table → Refresh. Or Data → Refresh All.
Ctrl+P opens print settings with a live preview on the right side.Ctrl+Shift+L to quickly toggle AutoFilter on and off.A Chart (also called a Graph) is a visual representation of spreadsheet data. Charts make it easy to spot trends, compare values, and present data in a compelling way.
Fig 4.1 — Clustered Bar Chart (as in Practical Exercise 18)
Compare values across categories. Best for sales by region, marks by subject.
Show trends over time. Best for monthly sales, temperature over weeks.
Show proportion of parts to whole. Best for market share, budget allocation.
Like a line chart but filled below — shows magnitude of change over time.
Show correlation between two variables. Used in scientific data analysis.
Like a pie chart with a hole in the middle. Can show multiple data series.
Select the cell range including column headers and all data rows. For branch sales: select branch names and their values (including the header row).
Insert → Charts group → click the chart type (Column, Line, Pie, etc.). A preview chart appears in the worksheet.
In Chart Design tab → Chart Styles group → pick a pre-built style. Or use Change Chart Type to switch between chart types.
Chart Design → Add Chart Element → add Title, Data Labels, Legend, Gridlines, Axis Titles.
Drag chart to desired location. Resize by dragging corners. Right-click chart → Format Chart Area for colours, borders, effects.
F7). Checks text labels in cells — does not check numbers or formulas.File & General
Navigation
Selection & Data
Formulas & Formatting
1. In MS-Excel, cell address B5 means:
2. Which of the following is the correct formula to find the sum of cells A1 to A10?
3. In MS-Excel, AutoFill is used to:
4. Which function returns the highest value in a range?
5. The keyboard shortcut Alt+= in Excel is used to:
6. What does $B$3 represent in a formula?
7. A Pivot Table is used to:
8. The Excel error #DIV/0! means:
9. Match — Data Consolidation in Excel is used to:
10. True or False: Filter in Excel permanently removes the rows that do not match the filter criteria.