Spreadsheet Functions and Charts ICSE Class 8: Formulae, Ranges, Cell References, Functions and Charts

What Spreadsheet: Functions and Charts is about

Weightage: CISCE publishes no question paper, marking scheme or chapter weightage for Class VIII Computer Studies: your school sets and marks the exam. Spreadsheet: Functions and Charts sits in the Theme: Spreadsheet - Functions and Charts theme of the CISCE Curriculum for Upper Primary Classes (VI-VIII).Source: CISCE Curriculum for Upper Primary Classes (VI-VIII), RDCD 2016

Key concepts

Entering a formula

A formula is an instruction that calculates a value. Rules:

• It always begins with an equals sign =; without it, the spreadsheet treats the entry as text.
• It uses cell references (not just numbers) so the result updates when data changes: =B2+C2.
• Press Enter to accept it. The cell shows the result; the formula bar shows the formula.

Arithmetic operators:

Operator	Meaning	Example	Result (B2 = 12, C2 = 4)
+	addition	=B2+C2	16
-	subtraction	=B2-C2	8
*	multiplication	=B2*C2	48
/	division	=B2/C2	3
^	power	=C2^2	16

Order of operations (like BODMAS): brackets first, then powers, then * and / (left to right), then + and -.

• =10+4*3-6/2 gives 19 (4 × 3 = 12, 6 ÷ 2 = 3, 10 + 12 − 3 = 19).
• =(10+4)*3 gives 42.

Common error messages: #DIV/0! (dividing by zero or a blank cell), #NAME? (misspelt function name), ##### (column too narrow to show the number).

Worked example

What will each formula display? (i) =10+4*3-6/2 (ii) =2^3*2 (iii) B2+C2 typed without the equals sign.

Try it first, then show the solutionSolution

1. (i) Multiplication and division first: 10 + 12 − 3 = 19. (ii) Power first: 8 × 2 = 16.
2. (iii) Without = the entry is treated as text, so it shows B2+C2 exactly as typed.

Answer: (i) 19 (ii) 16 (iii) the text B2+C2

Range: meaning, selecting and naming

A range is a group of adjacent cells forming a rectangle, written as first cell : last cell.

• B2:B6 is five cells in column B.
• A1:C4 is 3 columns × 4 rows = 12 cells.

Selecting a range:

• Drag the mouse from the first cell to the last; or
• click the first cell, hold Shift and click the last cell (or use Shift + arrow keys);
• hold Ctrl while dragging to select non-adjacent ranges;
• click a column letter or row number to select a whole column or row.

Naming a range: select the range, then type a name in the Name Box (left of the formula bar) and press Enter, or use Formulas → Define Name (in Calc: Sheet → Named Ranges and Expressions → Define). Then you can write =SUM(Marks) instead of =SUM(B2:B6).

Rules for range names: start with a letter or underscore; no spaces (use Total_Marks); must not look like a cell address (such as A1); keep them short and meaningful.

Worked example

How many cells are there in the range B2:D10? Suggest a valid name for it if it holds monthly sales.

Try it first, then show the solutionSolution

1. Columns B, C, D = 3; rows 2 to 10 = 9; cells = 3 × 9 = 27.
2. A valid name: Monthly_Sales (no spaces, starts with a letter).

Answer: 27 cells; Monthly_Sales

Relative, absolute and mixed references

When a formula is copied to other cells, its cell references may change. How they change depends on the type of reference.

Type	Written as	When copied	Use
Relative	B2	changes with the new position (row and column)	same calculation on each row, like totals
Absolute	$B$2	never changes	a fixed value used by every row, like a tax rate
Mixed	$B2 or B$2	the part with $ stays fixed; the other part changes	tables such as multiplication grids

The $ sign locks the letter or number just after it. Pressing F4 after typing a reference cycles through B2 → $B$2 → B$2 → $B2.

Examples of copying:

• =B2+C2 in D2, copied down to D3, becomes =B3+C3 (relative).
• =B2*$E$1 in C2, copied to C3, becomes =B3*$E$1: the rate in E1 stays fixed (absolute).
• =$B2 copied 2 rows down and 1 column right becomes =$B4 (column locked, row changes).
• =B$2 copied 2 rows down and 1 column right becomes =C$2 (row locked, column changes).

Typical mistake: copying =B2*E1 down a column changes it to =B3*E2, =B4*E3..., which point at empty cells, so the answers become 0. Fix: =B2*$E$1.

Worked example

The formula =A1 is copied from cell C1 to cell E4 (3 rows down, 2 columns right). What does it become? What would =$A$1 become?

Try it first, then show the solutionSolution

1. Relative: column A moves 2 to the right (C) and row 1 moves 3 down (4): =C4.
2. Absolute: =$A$1 stays =$A$1.

Answer: =C4; =$A$1 unchanged

Working with sheet tabs

The sheet tabs at the bottom of the window show the worksheets in a workbook (Sheet1, Sheet2...).

• Insert a sheet: click the + button next to the tabs, or right-click a tab → Insert.
• Rename a sheet: double-click the tab, or right-click → Rename, type the new name, press Enter.
• Delete a sheet: right-click the tab → Delete (confirm). Deleting a sheet cannot usually be undone, so check first.
• Move or copy: drag the tab, or right-click → Move or Copy.
• Tab colour: right-click → Tab Colour, to organise sheets.

Sheet name rules (in Excel): up to 31 characters; cannot be blank; cannot contain : \ / ? * [ ]; two sheets in a workbook cannot have the same name.

Good names, such as Term1, Term2 and Summary, make a workbook easy to understand.

Worked example

Give the steps to rename Sheet1 as Term1 and to delete Sheet3.

Try it first, then show the solutionSolution

1. Rename: double-click the Sheet1 tab (or right-click → Rename), type Term1, press Enter.
2. Delete: right-click the Sheet3 tab → Delete, then confirm.

Answer: Double-click (or Rename) and type; right-click → Delete.

Functions and the rules to enter them

A function is a ready-made (built-in) formula that performs a calculation on values called arguments.

Rules to enter a function:
1. Begin with =.
2. Type the function name correctly (not case-sensitive).
3. Put the arguments in brackets immediately after the name, with no space: =SUM(B2:B6).
4. Separate arguments with commas (in some regional settings, semicolons): =SUM(B2:B6,D2).
5. Use a colon for a range.
6. Close every bracket; press Enter.

Function	Does	Example (B2:B6 = 78, 85, 62, 90, 70)	Result
SUM	adds	=SUM(B2:B6)	385
AVERAGE	arithmetic mean	=AVERAGE(B2:B6)	77
MAX	largest value	=MAX(B2:B6)	90
MIN	smallest value	=MIN(B2:B6)	62
COUNT	counts cells holding numbers	=COUNT(B2:B6)	5

COUNT ignores blank cells and text. If B3 held the text AB (absent) and B5 were blank, =COUNT(B2:B6) would give 3, and =AVERAGE(B2:B6) would average only the three numbers: (78 + 62 + 70) ÷ 3 = 70.

The AutoSum button (Σ) inserts =SUM() for the nearby range in one click.

Worked example

Write functions to find the total, average, highest and lowest of marks in C2:C31 and the number of students who have marks.

Try it first, then show the solutionSolution

1. =SUM(C2:C31), =AVERAGE(C2:C31).
2. =MAX(C2:C31), =MIN(C2:C31), =COUNT(C2:C31).

Answer: SUM, AVERAGE, MAX, MIN and COUNT on C2:C31

Creating a chart

A chart is a picture of data that makes comparisons and trends easy to see.

Steps to create a chart:
1. Enter the data with headings, for example subjects in column A and marks in column B.
2. Select the data range including the headings (A1:B6).
3. Go to Insert → Chart (choose a type such as Column).
4. Add or edit the chart title, axis titles, legend and data labels.
5. Move or resize the chart; it updates automatically when the data change.

Common chart types and when to use them:

Chart	Best for
Column (vertical bars) / Bar (horizontal bars)	comparing values (marks in subjects)
Line	change over time (temperature each month)
Pie	parts of a whole (how pocket money is spent); shows each part as a share of 360°

Parts of a chart: chart title, plot area, x-axis (categories) and y-axis (values) with titles, legend (which colour is which series), data labels, gridlines.

Worked example

A survey of 30 students shows the sport each student likes best: cricket 12, football 8, badminton 6, chess 4. Which chart shows each sport's share best, and what angle will the cricket slice have?

Try it first, then show the solutionSolution

1. Parts of a whole: a pie chart.
2. Cricket: 12 ÷ 30 × 360° = 144°.

Answer: Pie chart; 144°

Spreadsheet: Functions and Charts in real life

Each concept, met in an everyday situation. Read the situation, answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Topics in this chapter

• Formulas, Ranges and Relative, Absolute and Mixed References
• SUM, AVERAGE, MAX, MIN, COUNT and Creating Charts

Spreadsheet: Functions and Charts: syntax and key facts

Formula rule

= (equals sign) first, then references and operators: =B2+C2

Without = the entry is text.

Function syntax

=FUNCTION(argument1, argument2, ...), e.g. =SUM(B2:B6)

No space before the bracket; colon for a range.

Range size

Cells in a range = columns × rows (A1:C4 has 3 × 4 = 12)

A1:C4 has 3 × 4 = 12 cells.

Reference types

Relative B2; absolute $B$2; mixed $B2 or B$2

The dollar sign locks what follows it; F4 cycles.

Pie angle

Pie slice angle = value ÷ total × 360°

For a pie chart slice.

Spreadsheet: Functions and Charts: diagrams to practise

Practise each one on paper: draw it from the checklist, then tick off every label.

How the school exam asks Spreadsheet: Functions and Charts

The question patterns to expect, and the method that works for each.

Tricks to remember Spreadsheet: Functions and Charts

Short memory aids. Say each one aloud once, then use it on a question the same day.

• Equals first, always

  No = means no maths: the cell just shows text.

  Use it when: Entering formulae.

• Dollar = lock

  A dollar sign locks the letter or number right after it, like a padlock.

  Use it when: Absolute and mixed references.

• SAM-MC

  Sum, Average, Max, Min, Count: the five basic functions.

  Use it when: Remembering the functions in the syllabus.

• Pie for parts, Line for time, Column to compare

  Choose the chart by the question you are answering.

  Use it when: Choosing a chart type.

• COUNT counts numbers

  Text and blanks are invisible to COUNT.

  Use it when: Predicting COUNT results.

Spreadsheet: Functions and Charts: exam-level solved questions

Ordered from exam level to HOTS. Cover the solution, write your own, then compare step by step.

Case-study practice (Section B style)

Read the situation, then answer every part. In a school exam set on the board pattern this is a 6-mark question split into 2 + 2 + 2 marks.

Where the marks are

How a typical answer is split into step marks. Show every step: a right final answer alone rarely earns full marks.

Where students lose marks in Spreadsheet: Functions and Charts

The slips examiners see most often, and what to write instead.

• ✗Mistake: Forgetting the equals sign

  Without =, the entry is stored as text and not calculated.

  Wrong working

  SUM(B2:B6)

  Right working

  =SUM(B2:B6)

  ✓Do this: Type = first, every time.

  Usually costs: The whole formula mark.

• ✗Mistake: Using a relative reference for a fixed rate

  When copied, the reference moves to empty cells and results become wrong.

  Wrong working

  =B2*E1 copied down

  Right working

  =B2*$E$1 copied down

  ✓Do this: Lock fixed values with dollar signs.

  Usually costs: 1-2 marks.

• ✗Mistake: Thinking COUNT counts all filled cells

  COUNT counts only numbers; text and blanks are ignored.

  ✓Do this: Check each cell's type before predicting COUNT.

  Usually costs: 1 mark.

• ✗Mistake: Selecting data without headings for a chart

  The chart then has no category names or legend labels.

  ✓Do this: Include the heading row when selecting.

  Usually costs: 1 mark in step questions.

• ✗Mistake: Writing a range with a dash or comma

  A range uses a colon: B2:B6; B2,B6 means only two cells.

  ✓Do this: Colon for 'to'.

  Usually costs: 1 mark.

Practice questions on Spreadsheet: Functions and Charts

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQExam level

   Which function counts the number of cells containing numbers in a range?
   (a) SUM
   (b) COUNT
   (c) MAX
   (d) AVERAGE

   Hint

   Counting.

   Answer

   (b)

2. Q2 · 1 mark · Very short answerExam level

   Which symbol is used to make a reference absolute?

   Hint

   Currency sign.

   Answer

   The dollar sign.

3. Q3 · 2 marks · Short answerExam level

   Give the steps to delete a worksheet.

   Hint

   Right-click the tab.

   Answer

   Right-click the sheet tab, choose Delete, and confirm.

4. Q4 · 3 marks · Short answerExam level

   Name three types of charts and the kind of data each suits.

   Hint

   Compare, time, parts.

   Answer

   Column or bar: comparing values; line: change over time; pie: parts of a whole.

5. Q5 · 1 mark · Assertion-reasonHarder exam level

   Assertion (A): =B2*$E$1 copied from C2 to C5 becomes =B5*$E$1.
   Reason (R): A relative reference changes when copied, but an absolute reference does not.
   (a) (A) is true and (R) is false.
   (b) (A) is false and (R) is true.
   (c) Both (A) and (R) are true, and (R) is the correct explanation for (A).
   (d) Both (A) and (R) are true, but (R) is not the correct explanation for (A).

   Hint

   Three rows down.

   Answer

   (c)

6. Q6 · 1 mark · Very short answerHarder exam level

   What will =(15-3)*2+12/4 display?

   Hint

   Brackets, then × and ÷.

   Answer

   12 × 2 + 3 = 27.

7. Q7 · 1 mark · Very short answerHarder exam level

   How many cells are in the range C3:F12?

   Hint

   Columns × rows.

   Answer

   4 columns (C to F) × 10 rows (3 to 12) = 40 cells.

8. Q8 · 2 marks · Short answerHarder exam level

   Distinguish between relative and absolute references with an example of each.

   Hint

   What happens on copying.

   Answer

   A relative reference (B2) changes according to the new position when copied (B2 becomes B3 one row down). An absolute reference ($B$2) stays the same wherever it is copied, used for fixed values such as a rate.

9. Q9 · 2 marks · Short answerHarder exam level

   The formula =$A1+B$1 in C2 is copied to E5. Write the new formula.

   Hint

   2 columns right, 3 rows down.

   Answer

   $A1 keeps column A, row 1 → 4: $A4. B$1 keeps row 1, column B → D: D$1. New formula: =$A4+D$1.

10. Q10 · 3 marks · Short answerHarder exam level

    Cells B2:B7 hold 45, 60, (blank), 75, Absent, 50. Find =SUM(B2:B7), =COUNT(B2:B7) and =AVERAGE(B2:B7).

    Hint

    Ignore text and blanks.

    Answer

    SUM = 45 + 60 + 75 + 50 = 230; COUNT = 4; AVERAGE = 230 ÷ 4 = 57.5.

11. Q11 · 2 marks · Short answerHarder exam level

    Give three rules for naming a range and one advantage of using range names.

    Hint

    Letters, spaces, addresses.

    Answer

    Start with a letter or underscore; no spaces; must not look like a cell address. Advantage: formulas such as =SUM(Marks) are easier to read and less error-prone.

12. Q12 · 3 marks · Short answerHOTS / competency

    A pie chart of a family's ₹20 000 monthly budget shows food ₹8000, rent ₹6000, travel ₹3000 and savings ₹3000. Find the angle of each slice and explain why a pie chart suits this data better than a line chart.

    Hint

    Share × 360°.

    Answer

    Food 8000/20000 × 360 = 144°, rent 108°, travel 54°, savings 54° (total 360°). The data are parts of one whole budget at one time, not changes over time, so a pie chart shows the shares clearly.

13. Q13 · 3 marks · Short answerHOTS / competency

    A teacher has 40 students' marks in Term1!B2:B41 and Term2!B2:B41 on two sheets. Suggest how to find each student's total on a Summary sheet and the class topper's total. Explain the referencing you use.

    Hint

    Sheet name before the cell.

    Answer

    On Summary, in B2 write =Term1!B2+Term2!B2 and copy it down to B41; the references are relative, so each row picks the same student on both sheets. Then =MAX(B2:B41) gives the topper's total.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.What must every formula begin with?Show answer

   An equals sign (=).

2. 2.Write an absolute reference to cell C4.Show answer

   $C$4.

3. 3.Which key cycles through reference types?Show answer

   F4.

4. 4.Which function finds the largest value?Show answer

   MAX.

5. 5.Does COUNT count text?Show answer

   No, only numbers.

6. 6.How do you rename a sheet?Show answer

   Double-click its tab (or right-click → Rename) and type the name.

7. 7.Which chart shows parts of a whole?Show answer

   Pie chart.

Quick revision checklist

The night before the exam: can you do each of these without looking?

• ☐Can you predict the result of a formula with mixed operators?
• ☐Can you count the cells in any range?
• ☐Can you say what a formula becomes when copied?
• ☐Can you rename and delete a sheet?
• ☐Can you write the five basic functions correctly?
• ☐Can you choose and create the right chart?

Spreadsheet: Functions and Charts: frequently asked questions