Complete step-by-step guides for all 5 Excel exercises — salary statements, result sheets with IF formulas, charts, data consolidation, sorting, filtering, and pivot tables.
SUM, AVERAGE, Nested IF for Distinction / I Class / II Class / Fail
Objective: Prepare (A) a monthly sales worksheet showing Total and Average Sales per branch, and (B) a student result sheet using nested IF to calculate grades (Distinction / I Class / II Class / Fail).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Branch | Jan | Feb | Mar | Total Sales | Average Sales |
| 2 | Chennai | 45000 | 52000 | 48000 | =SUM(B2:D2) | =AVERAGE(B2:D2) |
| 3 | Coimbatore | 38000 | 41000 | 39000 | =SUM(B3:D3) | =AVERAGE(B3:D3) |
| 4 | Madurai | 52000 | 58000 | 55000 | =SUM(B4:D4) | =AVERAGE(B4:D4) |
| 5 | Salem | 29000 | 33000 | 31000 | =SUM(B5:D5) | =AVERAGE(B5:D5) |
| 6 | Trichy | 61000 | 65000 | 62000 | =SUM(B6:D6) | =AVERAGE(B6:D6) |
| 7 | TOTAL | =SUM(B2:B6) | =SUM(C2:C6) | =SUM(D2:D6) | =SUM(E2:E6) | =AVERAGE(F2:F6) |
Open Excel → rename Sheet1 to Sales (double-click the tab). Enter headers in Row 1: Branch, Jan, Feb, Mar, Total Sales, Average Sales. Bold + fill colour (teal) for headers.
Enter 5 branch names in A2:A6. Enter monthly figures for Jan, Feb, Mar in columns B, C, D. Format numbers as Currency (₹): select B2:D7 → Ctrl+1 → Number → Currency → ₹ symbol → 0 decimal places → OK.
Click E2 → type =SUM(B2:D2) → Enter. Now drag the fill handle from E2 down to E6 (AutoFill). This copies the formula adjusting row numbers automatically.
Click F2 → type =AVERAGE(B2:D2) → Enter → drag down to F6. Average = (Jan + Feb + Mar) / 3.
In Row 7, type "TOTAL" in A7. In B7: =SUM(B2:B6) → drag right to D7. In E7: =SUM(E2:E6). In F7: =AVERAGE(F2:F6). Bold Row 7, apply yellow fill.
Select A1:F7 → apply All Borders (Home → Borders → All Borders). AutoFit column widths: select all columns → right-click column header → Column Width → Auto Fit Selection.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Reg. No. | Name | Tamil | English | Maths | Science | Total | Result |
| 2 | 2601 | Arjun | 82 | 76 | 91 | 88 | =SUM(C2:F2) | =IF(G2/4>=75,"Distinction",IF(G2/4>=60,"I Class",IF(G2/4>=50,"II Class","Fail"))) |
| 3 | 2602 | Priya | 65 | 70 | 68 | 72 | =SUM(C3:F3) | [IF formula] |
| 4 | 2603 | Suresh | 48 | 52 | 45 | 50 | =SUM(C4:F4) | [IF formula] |
Set up columns: Reg. No. | Name | Tamil | English | Maths | Science | Social | Total | Average | Result. Enter data for 10 students.
In G2: =SUM(C2:F2) → drag down to G11. This adds all 4 subject marks.
In H2: =G2/4 or =AVERAGE(C2:F2) → drag down. Format as Number with 1 decimal place.
In the Result column (e.g., I2): type the full nested IF formula as shown above. The formula checks the average mark and returns the appropriate grade text. Press Enter.
Click I2 → drag the fill handle down to I11. Excel automatically adjusts G2 to G3, G4, etc. for each student row.
Select the Result column → Home → Conditional Formatting → Highlight Cells Rules → Text that Contains → type "Fail" → choose Red fill → OK. Repeat for "Distinction" → Green fill.
Exam Tip: The nested IF formula is one of the most important exam questions in COA Practical Exercise 16. Memorise the logic: =IF(avg≥75,"Distinction",IF(avg≥60,"I Class",IF(avg≥50,"II Class","Fail"))). Practice typing it correctly without errors.
=SUM(B2:D2) adds all values from B2 to D2. =AVERAGE(B2:D2) divides the sum by the count.Sheet-level operations and cross-sheet referencing
Press Ctrl+F → type the value to find (e.g., "Chennai") → click Find All. All matching cells are listed below with their addresses. Click any result to jump to that cell.
Click the row number below where you want to insert → right-click → Insert. A blank row appears. OR: click the last data row's next empty row and simply type new data.
Click the row number to select the entire row → right-click → Delete. All rows below shift up automatically. If formulas reference that row, they may show #REF! — check and fix.
Ctrl+H → Find: old value → Replace with: new value → Replace All. Useful for updating all occurrences of a branch name or product code across the sheet.
Cross-sheet referencing lets one worksheet pull data from another. The format is: =SheetName!CellAddress. For example, =Sheet2!B5 reads the value from cell B5 on Sheet2.
Right-click the Sheet1 tab → Insert → Worksheet → name it "BranchData". Rename Sheet1 to "Summary" (double-click tab → type name → Enter).
Click the BranchData tab → enter branch names and figures (Jan, Feb, Mar sales) in columns A–D.
Click the Summary tab → click cell B2 → type = → click the BranchData tab → click cell B2 → press Enter. The formula bar shows: =BranchData!B2. The value from BranchData appears in Summary.
In Summary, click cell B8 → type: =SUM(BranchData!B2:B6) → Enter. This sums the Jan column from BranchData directly in the Summary sheet.
Go back to BranchData → change a sales figure → switch to Summary — the total updates automatically. This is the power of cross-sheet linking.
=SheetName!CellAddress — the sheet name and ! are added automatically when you click.Clustered column/bar chart with titles, legend, and data labels
Fig 10.1 — Clustered Column Chart: Branch-wise January Sales
Open the sales worksheet from Exercise 16. Select A1:D6 (Branch names + Jan, Feb, Mar columns — include the header row). Do NOT include the Total/Average columns.
Insert tab → Charts group → click Column Chart icon → choose Clustered Column (first option). A chart appears on the worksheet.
Click the chart → Chart Design tab → Add Chart Element → Chart Title → Above Chart → type: "Monthly Branch Sales Comparison". Click outside the title to confirm.
Chart Design → Add Chart Element → Axis Titles → Primary Horizontal: type "Branch Office". Primary Vertical: type "Sales Amount (₹)".
Chart Design → Add Chart Element → Data Labels → Outside End. The exact value (e.g., 45000) now appears on top of each bar.
Chart Design → Change Colors → choose a colourful palette. Click individual bars to select a series → right-click → Format Data Series → change fill colour. Apply a chart style from the gallery.
Click inside the chart → drag to position it below the data table. Drag the corner handles to resize. To move to a new sheet: Chart Design → Move Chart → New sheet → name it "SalesChart" → OK.
Chart Design → Change Chart Type → choose Bar (horizontal bars), Line, or Pie to see different views. For this exercise, Clustered Column (vertical bars) is the correct choice.
| Chart Type | Best Used For | Syllabus Requirement |
|---|---|---|
| Clustered Column | Comparing values across categories side by side | ✅ Exercise 18 — Branch sales comparison |
| Bar (Horizontal) | Same as column but horizontal — good for long labels | Alternative to column |
| Line | Trends over time — monthly/yearly progression | Useful for showing sales growth trend |
| Pie | Each branch's share of total sales as a percentage | Good for single series data |
Combining data from multiple ranges or sheets into a single summary
Data Consolidation combines values from multiple ranges (which may be on different sheets or workbooks) using a summary function (Sum, Average, Count, Max, Min). It is ideal for creating budget summaries from departmental data.
| Category | Budget (₹) |
| Wages | 120000 |
| Travel | 25000 |
| Supplies | 18000 |
| Category | Budget (₹) |
| Wages | 95000 |
| Travel | 30000 |
| Supplies | 22000 |
| Category | Total Budget |
| Wages | 215000 |
| Travel | 55000 |
| Supplies | 40000 |
Create 3 sheets: Dept_A, Dept_B, Dept_C. Each has the same structure: Column A = Category (Wages, Travel, Supplies, Entertainment, Office), Column B = Budget Amount. Labels must be identical across sheets for consolidation to work correctly.
Right-click a tab → Insert → Worksheet → rename to "Consolidated_Summary". Click cell A1 of this sheet — this is where the consolidated result will be placed.
Data tab → Data Tools group → Consolidate. The Consolidate dialog opens.
In the Function dropdown: choose Sum (to total all department budgets). For average budget: choose Average.
Click in the Reference box → click the Dept_A tab → select A1:B6 (include headers) → click Add. Repeat for Dept_B (A1:B6 → Add) and Dept_C (A1:B6 → Add). All three ranges appear in the "All references" list.
Check "Top row" and "Left column" so Excel uses the category names and column headers as labels in the output table.
The consolidated summary appears in the Summary sheet with each category's total (or average) budget from all three departments combined.
Apply Currency format (₹) to the amount column. Add borders and header shading. Add a title row: "Consolidated Department Budget Summary".
Practical Use: The syllabus specifically mentions using Consolidate to calculate the "total amount budgeted for all departments (wages, travel, entertainment, office supplies)" and also the "average amount budgeted for department office expenses." Practice both — once with Sum function, once with Average function.
Three powerful data analysis tools in one exercise
Sorting rearranges rows in a table based on values in one or more columns — either in Ascending (A→Z, 0→9) or Descending (Z→A, 9→0) order. The original data is rearranged (not copied).
Click any cell inside your data table → Data tab → click A→Z button (Sort Ascending) or Z→A (Sort Descending). Excel sorts by the column your cursor is in.
Data → Sort (opens the Sort dialog). Sort by: choose "Total Marks" → Order: Largest to Smallest. Click Add Level → Then by: choose "Name" → Order: A to Z. This sorts primarily by marks (descending) and breaks ties alphabetically.
Open the result sheet from Ex. 16 → click inside the data → Data → Sort → Sort by: Total → Largest to Smallest → OK. Students now appear ranked from highest to lowest marks.
Data → Sort → Order dropdown → Custom List → you can sort by month names (Jan, Feb, Mar…) or day names (Mon, Tue, Wed…) which don't sort correctly alphabetically.
AutoFilter temporarily hides rows that don't match selected criteria. The data is NOT deleted — only hidden. Remove the filter to see all rows again.
Click any cell inside the data table → Data → Filter (or press Ctrl+Shift+L). Drop-down arrows (▼) appear in each column header.
Click the ▼ on the "Result" column → uncheck "Select All" → check only "Distinction" → OK. Only the Distinction students are shown; others are hidden (row numbers turn blue).
Click ▼ on "Total" column → Number Filters → Greater Than → type 200 → OK. Shows only students with total marks above 200.
Click ▼ on "Branch" column → Text Filters → Contains → type "Chennai" → OK. Shows only Chennai branch rows.
Data → Clear (removes all active filters and shows all rows again). OR click the ▼ arrow → "Clear Filter from [column name]" to clear just that column's filter.
Pivot Table is an interactive summary table that lets you analyse large datasets by dragging and dropping fields. It can instantly show totals, averages, counts, and more — grouped by any category.
| Region | Product | Sales |
| North | Pen | 1200 |
| South | Book | 3400 |
| North | Book | 2100 |
| South | Pen | 950 |
| East | Pen | 1800 |
| Row Labels | Sum of Sales |
| East | 1800 |
| North | 3300 |
| South | 4350 |
| Grand Total | 9450 |
Ensure data has clear column headers in Row 1 (e.g., Region, Product, Salesperson, Month, Sales Amount). No blank rows or columns within the data range.
Click any cell inside data → Insert → PivotTable → "From Table/Range" → verify the range → choose New Worksheet → OK. A new sheet opens with the PivotTable Fields pane on the right.
In the PivotTable Fields pane, drag Region from the field list down to the Rows area. The Pivot Table now shows each unique region as a row.
Drag Sales to the Values area. Excel automatically applies Sum. The total sales for each region appear instantly.
Drag Product to the Columns area to show a cross-tab: rows = regions, columns = products, values = total sales. A 2D summary table appears.
In Values area → click "Sum of Sales" → Value Field Settings → choose Average, Count, Max, or Min instead of Sum.
PivotTable Analyze → Insert Slicer → choose a field (e.g., Region) → OK. A visual filter panel appears — click any region to instantly filter the pivot table.
If source data changes: right-click inside the pivot table → Refresh. Or PivotTable Analyze → Refresh All.
| Exercise | Topic | Key Functions / Features | Important Shortcut |
|---|---|---|---|
| Ex. 16 | Formulas & IF | =SUM, =AVERAGE, Nested IF (Distinction/I/II/Fail), AutoFill, Conditional Formatting | Alt+= for AutoSum |
| Ex. 17 | Sheet Operations | Find, Add/Delete Rows, Merge Cells, Row Height, Column Width, Cross-sheet =SheetName!Cell | Ctrl+F to find |
| Ex. 18 | Charts | Clustered Column, Chart Title, Axis Titles, Data Labels, Move to New Sheet, Change Type | Insert → Column Chart |
| Ex. 19 | Data Consolidation | Data → Consolidate, Sum/Average function, Multiple source ranges, Top row + Left column labels | Data → Consolidate |
| Ex. 20 | Sort, Filter & Pivot | A→Z / Z→A Sort, Multi-level Sort, AutoFilter, Number/Text Filters, Pivot Table (Rows/Columns/Values), Slicer, Refresh | Ctrl+Shift+L (Filter) |