Unit 2: Visualizing and communicating data
Basics of Data Analytics using Spreadsheet Laboratory notes · PTU syllabus (UGDSE102)
On this page
Unit summary
The second half of the lab turns cleaned data into insight: logical, lookup, conditional aggregation and text functions; charts; PivotTables and Pivot Charts; and finally interactive dashboards with conditional formatting, slicers and timelines.
After this unit you can
- Use IF, AND, OR, IFERROR, VLOOKUP, HLOOKUP, INDEX and MATCH
- Summarise with SUMIFS, COUNTIFS and AVERAGEIFS
- Build charts, PivotTables and Pivot Charts
- Design an interactive dashboard with slicers and timelines
PTU syllabus topics
- Logical functions (IF, AND, OR, IFERROR)
- lookup/reference functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
- aggregation functions (SUMIFS, COUNTIFS, AVERAGEIFS)
- text functions
- chart types and advanced charting
- PivotTables and Pivot Charts
- dashboard concepts
- conditional formatting and interactive dashboards with slicers and timelines
Topic 1
Logical and lookup functions
=IF(AND(B2>=40, C2>=75), "Eligible", "Not eligible")
=IFERROR(B2/C2, 0)
=VLOOKUP(E2, A2:C100, 3, FALSE)
=INDEX(C2:C100, MATCH(E2, A2:A100, 0))Lookup direction
Left to right only
Any direction
Column insertion
Can break the column number
Not affected
Ease
Simpler
More flexible
HLOOKUP searches across the top row instead of down the first column.
Example
Looking up a price: with product codes in A2:A50 and prices in C2:C50, =VLOOKUP("P103",A2:C50,3,FALSE) returns the price of P103. =INDEX(C2:C50,MATCH("P103",A2:A50,0)) does the same and also works when the lookup column is not the first.
Exam tip
Use FALSE (exact match) in VLOOKUP. Wrap it in IFERROR to show a message instead of #N/A.
Topic 2
Conditional aggregation
=SUMIFS(D:D, B:B, "North", C:C, "2026")— total sales for North in 2026.=COUNTIFS(B:B, "BCA", E:E, ">=60")— BCA students with 60 or more.=AVERAGEIFS(E:E, B:B, "BBA")— average marks of BBA students.
Example
Counting and summing with conditions: =COUNTIF(C2:C31,">=40") counts students who passed. =SUMIF(B2:B100,"North",D2:D100) totals sales for the North region. =AVERAGEIFS(D2:D100,B2:B100,"North",C2:C100,"2026") averages North sales for 2026.
Topic 3
Charts, PivotTables and Pivot Charts
Pick the chart for the message: column/bar for comparison, line for trends, pie for share, scatter for relationships, combo for two measures.
- 1Select the data
With headers, no blank rows
- 2Insert → PivotTable
- 3Drag fields
Rows, Columns, Values, Filters
- 4Change summary
Sum, Count, Average
- 5Insert a Pivot Chart
Example
Rows = Region, Columns = Month, Values = Sum of Sales instantly shows monthly sales for each region.
Topic 4
Dashboards
A dashboard shows key numbers and charts on one screen for quick decisions.
- Use conditional formatting (data bars, colour scales, icon sets) to highlight performance.
- Add slicers (buttons that filter PivotTables) and timelines (date filters) to make it interactive.
- Keep it simple: 3–5 key KPIs, consistent colours and clear titles.
Example
A one-screen sales dashboard: four cards at the top show total sales, orders, average order value and growth. Below are a line chart of monthly sales, a bar chart of sales by region and a slicer for year, all fed by PivotTables so that one click filters everything.
Key terms
- VLOOKUP
- Finds a value in the first column and returns a value from another column
- SUMIFS
- Adds values meeting several conditions
- PivotTable
- An interactive summary of a data table
- Slicer
- A visual button filter for PivotTables
- Dashboard
- A one-screen display of key metrics and charts
Quick revision
- IFERROR hides errors; INDEX-MATCH beats VLOOKUP for flexibility.
- SUMIFS/COUNTIFS/AVERAGEIFS take multiple conditions.
- PivotTable: rows, columns, values, filters.
- Slicers and timelines make dashboards interactive.
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 VLOOKUP and HLOOKUP.
- Q2.Why is INDEX-MATCH preferred over VLOOKUP?
- Q3.What does COUNTIFS do?
- Q4.What is a slicer?
- Q5.Which chart would you use to show a trend over time?
Long-answer questions
- Q1.Create a sales report using SUMIFS and a PivotTable by region and month.
- Q2.Design an interactive sales dashboard with charts, slicers and a timeline, and explain each element.
Stuck on this unit?
Message SBS on WhatsApp for help with Basics of Data Analytics using Spreadsheet Laboratory, or to ask about studying BCA at Synetic.
