NIELIT CCC — Revision 3

Chapter 4: Spreadsheet

Learn to create, organise, calculate, and visualise data using LibreOffice Calc / MS Excel. Master cells, formulas, functions, charts, and data tools.

4 hrs Theory + 8 hrs Lab Chapter 4 of 9 15 MCQs included
Notes
Formulas & functions
Shortcuts
MCQ Practice
Spreadsheet screen layout
C5 fx =SUM(C2:C4)
ABCDE
1StudentSubjectMarks
2RahulMaths85
3PriyaScience92
4AmitEnglish78
5Total=SUM(C2:C4)
6Average=AVERAGE(C2:C4)

▲ Cell C5 is selected — its formula =SUM(C2:C4) appears in the Formula Bar above the sheet. Rows are numbered, columns are lettered (A, B, C…).

Key concepts

Cells & addresses

  • A cell is the intersection of a row and column
  • Cell address = Column letter + Row number (e.g. B3)
  • Active cell is highlighted with a green border
  • A range is a group of cells (e.g. A1:D5)
  • Columns are letters (A–Z, then AA, AB…), rows are numbers

Data types in cells

  • Text — labels like names, headings
  • Number — integers, decimals
  • Date — 01/06/2024, June 1 2024
  • Formula — starts with = sign
  • By default: text aligns left, numbers align right

File operations

  • Default format: .ods (LibreOffice Calc)
  • Also save as .xlsx (Excel), .csv, .pdf
  • Page Setup: margins, orientation, paper size
  • Print: select sheet, range, or workbook

Cell formatting

  • Font, size, bold, italic, underline
  • Cell background & text colour
  • Alignment: left, right, centre, wrap text
  • Number format: currency, %, date, decimal
Cell manipulation tools

AutoFill

  • Type 1, 2 → select both → drag fill handle
  • Auto-completes series: Mon, Tue… or Jan, Feb…
  • Also copies formulas down/across a range

Sort & Filter

  • Sort: A→Z (ascending) or Z→A (descending)
  • Sort by multiple columns (primary, secondary key)
  • Filter: show only rows matching a condition
  • AutoFilter adds dropdown arrows to column headers
Sort example
Before sort (Marks)
85
92
78
95
67
→
After sort (ascending)
67
78
85
92
95
→
After sort (descending)
95
92
85
78
67
Freeze panes

Freeze Panes keeps selected rows or columns visible while you scroll through a large sheet. Useful when your data has many rows and you want the heading row always visible.

Menu: View → Freeze Rows and Columns

ABC
1NameDeptSalary
2RahulHR35000
3PriyaIT42000
4AmitSales29000

Row 1 (heading) is frozen — green line shows the freeze boundary

Charts
Bar / Column chart
Compare values across categories (e.g. sales by month)
Pie chart
Show parts of a whole (e.g. market share %)
Line chart
Show trends over time (e.g. temperature over a week)

Sample bar chart — marks of 4 students:

Step 1: Select data range → Step 2: Insert → Chart → Step 3: Choose chart type → Step 4: Click Finish
Using formulas

All formulas start with =. You can use cell addresses instead of fixed numbers so results update automatically.

ABCD
1Num1Num2OperationResult
25030Addition=A2+B2
35030Subtraction=A3-B3
45030Multiplication=A4*B4
55030Division=A5/B5

Operators: + Addition   - Subtraction   * Multiplication   / Division

Built-in functions
SUM
=SUM(A1:A5)
Adds all values in a range. AutoSum button (Σ) inserts SUM quickly.
AVERAGE
=AVERAGE(B1:B5)
Calculates the arithmetic mean of values in a range.
COUNT
=COUNT(C1:C10)
Counts the number of cells that contain numeric values.
MAX
=MAX(D1:D8)
Returns the largest (maximum) value in a range.
MIN
=MIN(D1:D8)
Returns the smallest (minimum) value in a range.
AutoSum
Σ button
Click the Σ button to automatically insert =SUM() for adjacent cells.
Live formula demo

Enter marks (1–100) to see functions calculated live:

Important shortcut keys
New spreadsheetCtrlN
Open fileCtrlO
SaveCtrlS
PrintCtrlP
CutCtrlX
CopyCtrlC
PasteCtrlV
UndoCtrlZ
RedoCtrlY
Select allCtrlA
BoldCtrlB
Find & ReplaceCtrlH
Go to cellCtrlG
AutoSumAlt=
Insert row/colCtrl+
Delete row/colCtrl-
Move to last cellCtrlEnd
Move to first cellCtrlHome

MCQ Practice — Chapter 4

15 questions based on NIELIT CCC exam pattern. Click an option to check your answer.