Objectives

  • Enter data, use SUM, AVERAGE, and a simple IF.
  • Format currency and create a column chart.

Theory: Application software, business information systems.

Tasks (55 minutes)

In a workbook named Surname_IT231_Lab4.xlsx:

  1. Sheet Sales: products A–E, columns Quantity, Unit Price, Revenue (=Quantity*UnitPrice).
  2. Totals row with SUM for Quantity and Revenue; AVERAGE of Unit Price.
  3. Status column: IF Revenue ≥ 10000 then "High" else "Normal".
  4. Format Revenue as currency (NPR or local).
  5. Insert a column chart of Product vs Revenue; title the chart.
  6. Freeze the header row.

Use at least five product rows with invented but realistic numbers.

Before You Start

  • Open a blank workbook in Excel (LibreOffice Calc or Google Sheets also work; formulas are the same).
  • Rename the first sheet: double-click the tab and type Sales.
  • Every formula starts with =. After typing one, press Enter and check the result before copying it down.

Step-by-Step Guide

  1. Headers in row 1. A1:E1: Product, Quantity, Unit Price, Revenue, Status. Make them bold.

  2. Sample data (rows 2–6). Use these numbers so you can check your results:

    Product Quantity Unit Price Revenue (expected) Status (expected)
    A 120 85 10,200 High
    B 45 250 11,250 High
    C 300 20 6,000 Normal
    D 18 450 8,100 Normal
    E 60 175 10,500 High
  3. Formulas in row 2, then copy them down to row 6 by dragging the fill handle:
    • Revenue D2: =B2*C2
    • Status E2: =IF(D2>=10000,"High","Normal")
  4. Totals in row 7. A7: Total. B7: =SUM(B2:B6) gives 543. C7: =AVERAGE(C2:C6) gives 196. D7: =SUM(D2:D6) gives 46,050.
  5. Currency format. Select C2:D7, then Home → Number Format → Currency (choose NPR/”Rs” or your local symbol).
  6. Chart. Select A1:A6, hold Ctrl, and select D1:D6. Then Insert → Column Chart → Clustered Column. Click the chart title and type “Revenue by Product”. Product B should have the tallest bar.
  7. Freeze the header. Click A2, then View → Freeze Panes → Freeze Top Row.

Check Your Work

Checkpoint How to verify
Formulas, not typed numbers Clicking any Revenue or Status cell shows a formula in the formula bar
Totals Quantity 543; Revenue 46,050; average Unit Price 196 (with the sample data)
Chart Column chart titled, with product names on the axis
Formatting Currency symbol shown; the header row stays visible when scrolling

Common Errors and Fixes

Problem Fix
#NAME? A function name is misspelt (e.g. =SUMM) or the text is missing quotes, as in "High".
Every Status shows “Normal” The numbers were typed as text (left-aligned). Retype them, or use Data → Text to Columns → Finish.
Tax is 0 in rows below the first The rate reference was not absolute. Use $B$1, not B1.
##### in a cell The column is too narrow for the number. Double-click the column border to auto-fit it.

Extension Task

For those who finish early: Add a column Rank using =RANK(D4,$D$4:$D$8) (adjust the rows to your layout), and apply Conditional Formatting → Data Bars to Revenue. Which product would you promote next month, and why?

Deliverable

The .xlsx file. Print the Sales sheet to PDF if required.

Viva

  1. What is a cell reference? Give a relative and an absolute example.
  2. Why is a chart useful for managers?
  3. What does SUM ignore that you still must check by eye?
  4. How is a spreadsheet different from a word-processor table?
  5. What is the difference between a formula and a function?
  6. Explain what the fill handle does when copying =B4*C4 down one row.
  7. What will =IF(D4>=10000,"High","Normal") return if D4 is exactly 10000?
  8. What is the difference between Freeze Top Row and Freeze Panes?