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
IF
=IF(B2>=40, "Pass", "Fail")
VLOOKUP
=VLOOKUP(A2, Table, 3, FALSE)
INDEX-MATCH
=INDEX(C:C, MATCH(A2, A:A, 0))
SUMIFS
=SUMIFS(D:D, B:B, "North", C:C, "Q1")
IFERROR
=IFERROR(formula, "Not found")
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.
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.
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.
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.