Unit 3: MS-Excel
Workshop on IT Tools for Business and E-Commerce notes · PTU syllabus (BCOMSEC 301-18)
On this page
Unit summary
Spreadsheets are the most widely used business analysis tool. This unit covers creating and formatting spreadsheets in MS Excel, charts and graphs, menus and toolbars, cell editing and protection, and financial and statistical functions.
After this unit you can
- Create and format worksheets
- Edit and protect cells and sheets
- Create charts and graphs from data
- Use financial and statistical functions in formulas
PTU syllabus topics
- Spreadsheet creation and formatting
- charts and graphs
- toolbars and menu commands
- cell editing and protection
- calculation of financial and statistical functions using formulas
Total
=SUM(B2:B20)
Condition
=IF(C2>=50000, "Target met", "Below")
Loan instalment
=PMT(rate/12, months, -loan)
Lookup
=VLOOKUP(A2, Prices, 2, FALSE)
Count with condition
=COUNTIF(D2:D50, "Paid")
Topic 1
Creating and formatting spreadsheets
A workbook contains worksheets; each cell has an address (column letter + row number), e.g. C5.
- Enter text, numbers, dates and formulas (starting with =).
- Format numbers (currency ₹, percentage, decimals), fonts, borders, fill colours and column widths.
- AutoFill copies formulas or continues series (Jan, Feb, …).
- Relative references (A1) change when copied; absolute references ($A$1) stay fixed.
Topic 2
Cell editing and protection
- Edit a cell with F2 or the formula bar; undo with Ctrl + Z.
- Insert or delete rows and columns; merge cells; wrap text.
- Protect cells: unlock input cells (Format Cells → Protection), then Review → Protect Sheet with a password so formulas cannot be changed.
Topic 3
Charts and graphs
Column/bar
Comparisons between categories
Sales by region
Line
Trends over time
Monthly revenue
Pie
Parts of a whole
Expense share
Scatter
Relationship between two variables
Advertising vs sales
Insert → Charts; add titles, axis labels and data labels for clarity.
Topic 4
Financial and statistical functions
| Function | Purpose | Example |
|---|---|---|
| SUM / AVERAGE | Total / mean | =SUM(B2:B10) |
| MAX / MIN / COUNT | Highest / lowest / count | =MAX(C2:C20) |
| IF | Conditional result | =IF(D2>=40,"Pass","Fail") |
| PMT | Loan instalment | =PMT(10%/12, 60, -500000) |
| FV / PV | Future / present value | =FV(8%, 5, -10000) |
| NPV / IRR | Investment appraisal | =NPV(10%, B2:B6) |
| STDEV / MEDIAN | Spread / middle value | =STDEV.S(E2:E50) |
Example
A ₹5,00,000 loan at 10% a year for 5 years: =PMT(10%/12, 60, −500000) gives an EMI of about ₹10,624.
Key terms
- Workbook
- An Excel file containing worksheets
- Absolute reference
- A cell reference fixed with $ signs
- Sheet protection
- Locking cells to prevent changes
- PMT
- Excel function for loan instalments
- NPV
- Net present value of a series of cash flows
Quick revision
- Formulas start with =; $ fixes references.
- Unlock input cells, then protect the sheet.
- Bar for comparison, line for trend, pie for share.
- PMT, FV, PV, NPV, IRR for finance.
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
- Q1.Differentiate between a workbook and a worksheet.
- Q2.What is an absolute cell reference?
- Q3.How do you protect a worksheet?
- Q4.Which chart shows a trend over time?
- Q5.What does the PMT function calculate?
Long-answer questions
- Q1.Explain how to create and format a worksheet in Excel.
- Q2.Explain the types of charts in Excel and when to use each.
- Q3.Explain financial and statistical functions in Excel with examples.
Stuck on this unit?
Message SBS on WhatsApp for help with Workshop on IT Tools for Business and E-Commerce, or to ask about studying B.Com at Synetic.