Synetic College Logo
Unit 2 of 4 · B.Sc Textile Sem 1

Unit 2: MS Excel basics

Basics of Computer notes · PTU syllabus (BSTD 105-20)

3 min read3 topics7 exam questions
On this page
  1. Unit summary
  2. The Excel interface
  3. Formulas and formatting
  4. Sort, filter, validation and subtotals
  5. Key terms
  6. Quick revision
  7. Important questions

Unit summary

Excel is used for costing, measurements and sales data. This unit covers spreadsheet basics, formulas, formatting, sorting, filtering, validation and subtotals.

After this unit you can

  • Identify the parts of an Excel worksheet
  • Write and copy formulas
  • Sort, filter and validate data
  • Create subtotals and consolidate data

PTU syllabus topics

  • Introduction to spreadsheets
  • menus and toolbars
  • computing data
  • formatting spreadsheets
  • sorting
  • filtering
  • validation
  • consolidation
  • subtotals
ClassificationExcel data tools
Working with data

  • Sort

    Order rows by a column

  • Filter

    Show only matching rows

  • Validation

    Restrict what can be typed

  • Subtotals

    Totals by group

  • Consolidation

    Combine data from several sheets

1

Topic 1

The Excel interface

A workbook has many worksheets; each is a grid of rows (1, 2, 3…) and columns (A, B, C…). A cell such as B3 is where a row and column meet.

Key termsExcel terms
Cell
Intersection of a row and a column
Range
A group of cells such as A1:A10
Formula
Entry beginning with =, for example =B2*C2
Function
Built-in formula such as SUM, AVERAGE, IF
Relative reference
Changes when copied (A1)
Absolute reference
Fixed when copied ($A$1)
2

Topic 2

Formulas and formatting

FunctionUseExample
SUMTotal of a range=SUM(B2:B10)
AVERAGEMean=AVERAGE(B2:B10)
MAX / MINLargest / smallest=MAX(B2:B10)
COUNTCount numbers=COUNT(B2:B10)
IFCondition=IF(B2>50,"Pass","Fail")

Example

Fabric cost sheet: metres in B2 = 12, rate per metre in C2 = ₹180. Cost in D2 = B2*C2 = ₹2,160. Fill down to cost every fabric.

3

Topic 3

Sort, filter, validation and subtotals

Sorting orders rows (A–Z or smallest to largest). Filter shows only rows that meet a condition. Data validation restricts what can be typed in a cell (for example only whole numbers from 1 to 100). Subtotals and consolidation summarise data by group or combine ranges from several sheets.

GraphBar chart of monthly sales (typical Excel chart)
MonthSales (₹ thousand)OJanFebMarAprMay

Select the data and use Insert → Chart; add a title and axis labels

Key terms

Cell
One box in a worksheet
Formula
Entry that starts with =
Function
Ready-made formula
Filter
Display rows meeting a condition
Validation
Rule limiting cell entries

Quick revision

  • Formulas start with =.
  • SUM, AVERAGE, MAX, MIN, COUNT, IF.
  • Absolute reference uses $.
  • Sort, filter, validate, subtotal.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.What is a cell reference?
  2. Q2.Write a formula to add B2 to B10.
  3. Q3.What is data validation?
  4. Q4.Differentiate relative and absolute reference.
  5. Q5.What does COUNT do?

Long-answer questions

  1. Q1.Explain spreadsheet features with a costing example.
  2. Q2.Describe sorting, filtering and subtotals with uses.

Stuck on this unit?

Message SBS on WhatsApp for help with Basics of Computer, or to ask about studying B.Sc Textile at Synetic.

WhatsApp us