ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data Hangman

The 71 key terms

1data question

A clear question that can be answered with numbers you can collect or already have, such as how many, how much or what changed over time.

2data science life cycle

A repeating cycle of five stages: define the objective, acquire data, clean it, analyse it, communicate insights. Findings can send you back to an earlier stage.

3raw data copy

An untouched copy of the original data, made by duplicating the worksheet or using Save As before cleaning, so you clean only the copy and never lose the original.

4Ctrl+T

Shortcut that converts a data range into a Table with named columns, filter buttons and automatic growth when new rows are added. Tick 'My table has headers' first.

5missing value

An empty cell where data should be. Find it with a filter for blanks, then delete the row, fill it from a reliable source or exclude it so AVERAGE and COUNT are not distorted.

6=COUNTIF($A$2:$A$100,A2)>1

Gives TRUE when the value in A2 appears more than once in column A. Put it in a helper column to flag duplicate rows, then filter for TRUE.

7=VALUE(A2)

Converts text that looks like a number, such as '45' stored as text, into a true number so it can be added, averaged and sorted correctly. Left-aligned numbers are a warning sign.

8Sort

Sorting puts the rows of a table in order using the values in one column, like A to Z or smallest to largest, and keeps each row together.

9filter

A filter shows only the rows that match a value you choose and hides the other rows. The hidden rows are not deleted. Clear the filter to see them all again.

10filter then sort

First filter the table to keep only one group, then sort a number column from largest to smallest. The top row is the highest value and the bottom row is the lowest in that group.

12=MIN(range) and =MAX(range)

MIN finds the smallest (lowest) value in a range of cells and MAX finds the largest (highest) value. For example, =MAX(B2:B10) shows the top score in cells B2 to B10.

13=AVERAGE(B2:B10)

A spreadsheet function that adds up the numbers in cells B2 to B10 and divides by how many there are, to find the mean (the typical value). It is not the total.

14=MEDIAN(B2:B30)

Returns the middle value of the numbers in B2:B30 once ordered. With an even count it averages the middle two. You do not need to sort the cells first.

15mean vs median

An outlier drags the mean towards it but barely moves the median. If the two differ a lot, the data is skewed, so the median is usually the fairer typical value to report.

16=MAX(B2:B30)-MIN(B2:B30)

The range is the largest value minus the smallest value. It measures spread, and one outlier can make it much bigger.

17=STDEV.S(B2:B30)

Returns the sample standard deviation of the numbers in a range, showing how spread out they are. Use STDEV.P if the range is the whole population.

18standard deviation

A measure of how spread out values are around the mean. A small value means the data is clustered close to the mean. A large value means it is widely spread. It uses the same units as the data.

19=COUNTA(A2:A100)

COUNTA counts every cell in a range that is not empty, including text. Use it to count survey responses. COUNT only counts numbers.

20=COUNTIF(C2:C50,"Yes")

COUNTIF counts the cells in a range that meet a condition. SUMIF works the same way but adds up the values that meet it. Example: count every 'Yes' in C2:C50.

21=AVERAGEIF(A2:A50,"Year 9",C2:C50)

Finds the mean of only the values in the last range whose matching cell in the first range meets the criterion, such as the average mark for just Year 9 students.

22COUNTIFS

Counts only rows that meet two or more criteria at once, using pairs of criteria range and criterion. SUMIFS works the same way but adds numbers, and its sum_range comes first.

23=IF(AND(B2>50,C2="Yes"),"Pass","Fail")

AND or OR inside IF joins several tests into one TRUE/FALSE. AND needs every test true. OR needs only one true. The IF then gives its first result if TRUE, otherwise its second.

24=IFERROR(value, value_if_error)

Runs a formula and shows your chosen value, like 0 or "Not found", if it gives an error. Use it carefully, because it can also hide real mistakes.

25XLOOKUP

A lookup function that finds a key in one column and returns the matching value from another column, such as a price for a product code. It can show a message if no match is found.

26=ROUND(number, digits)

Rounds a number to a set number of decimal places. Use 1 for one decimal place or 0 for a whole number, e.g. =ROUND(AVERAGE(B2:B9),1). It changes the value itself, not just how it looks.

28=(C2-B2)/B2

Percentage change: (new - old) / old, formatted as a percentage. A positive result is a rise, a negative result is a fall. Use it for year-on-year growth.

29bar chart

A chart that uses bars to compare amounts in different categories. Select the labels and values, then choose Insert chart. Column charts have upright bars; bar charts lie sideways.

30line chart

A chart that joins points with a line to show how a value changes over time. Time goes along the bottom axis, so you can see if the trend goes up or down.

31pie chart

A circle chart split into slices. Each slice shows how big one part is compared with the whole, often as a percentage. All the slices add up to 100%.

32scatter graph

A chart that plots each record as a point using two number columns, one on the x axis and one on the y axis, to show whether two things are related.

33Choosing the right chart

Pick the chart that matches your question: a bar chart to compare categories, a line chart for change over time, and a pie chart for parts of a whole.

34Title, axis labels and legend

The parts that help a viewer read a chart: the title says the topic, axis labels name the data and units, the legend explains colours.

35Add series

Adds a second set of values to a chart so two groups can be compared on the same axes, with a legend to tell them apart.

36Combo chart

A chart that mixes types, like columns plus a line, and puts one series on a secondary axis so two data sets with different units, such as sales and temperature, can be read together.

37misleading chart

A chart whose design, like a cut-off axis, 3D effects or a cherry-picked range, makes the data look different from what it truly shows.

38pattern, trend, outlier

Look for patterns and trends in data, like highest, lowest, going up or down. An outlier is a value that stands out from the rest. Compare groups to see differences.

39=QUARTILE.INC(B2:B100,1)

Finds Q1, the value 25% of the way up the data. Use 3 for Q3. IQR = Q3 - Q1. A value more than 1.5 times the IQR below Q1 or above Q3 is flagged as an outlier.

40Conditional formatting

A spreadsheet tool that changes a cell's colour or icon automatically when its value meets a rule, so highs, lows and outliers stand out.

41Investigate the outlier

Check if an outlier is a typing error or a real extreme value. Fix errors, keep real values, and compare results with and without it. Never delete data just to fit your story.

42trendline

A line fitted through a chart's points that shows the overall direction of the data. A chart tool can add one with its equation, and the R-squared value shows how well it fits.

43=AVERAGE(B2:B4)

A moving average. It averages the latest 3 values. Copy it down and the range slides by one row each time, smoothing the ups and downs to show the trend.

44=CORREL(B2:B9,C2:C9)

Finds Pearson's r for two equal-length number ranges. The result is from -1 to 1: near 1 is strong positive, near -1 is strong negative, near 0 is no linear link.

46correlation is not causation

Two things can rise or fall together without one causing the other. A trend shows a relationship, not the reason, so a hidden third factor may explain both.

47SLOPE and INTERCEPT

SLOPE gives m and INTERCEPT gives c of the best-fit line, so a prediction can be written as y = m*x + c. Both functions take known_y first, then known_x.

48=FORECAST(x, known_ys, known_xs)

Estimates a new y value for a given x by extending the trend of existing data, e.g. predicting next year's sales. It is less reliable the further x is beyond the known data.

49reliability

How much you can trust a prediction. It drops with small samples, outliers and predicting far beyond your data, so always state how uncertain the prediction is.

50static vs interactive vs live

A static visualisation is fixed. An interactive one lets users change the view. Live data updates automatically, while stored data is a fixed copy saved at one point in time.

51pivot table

A tool that summarises a big table by grouping rows under row and column fields, then working out a sum, count or average for each group. Refresh it when the data changes.

52Rows, Columns, Values, Filters

The four areas of the PivotTable field list. Drag a field into one to lay out the summary: Rows and Columns label it, Values calculates, Filters narrows the data.

53Value Field Settings

A pivot table option that changes what is calculated for each group, such as Sum, Count or Average. It can also show values as a % of column total and set the number format.

54Group

A pivot table feature that bundles dates into months or years, or numbers into equal-sized ranges, so the summary shows useful chunks. Right-click a value, then choose Group.

55PivotChart

A chart linked to a pivot table. It updates whenever you change the pivot table's fields or filters. Create it with Insert > PivotChart.

56slicer

A set of buttons linked to a table, pivot table or chart. Click a button to filter the data, so a viewer can explore it interactively. Clear the filter to show everything again.

57Insert > Slicer

Adds a panel of buttons for one field. Click a button to filter the pivot table and its pivot chart. Hold Ctrl to select several buttons at once.

58Insert > Timeline

A timeline slicer filters a pivot table by dragging along a date range. Pick months, quarters or years from the time level drop-down. It needs a date field.

59Report Connections

A slicer setting that links one slicer to many pivot tables. Tick each pivot table to connect; they must share the same source data. One click then updates every linked chart.

60=Data!B2

Pulls the value from cell B2 on the sheet named Data. Write the sheet name, then !, then the cell. Names with spaces need quotes: ='Sales Data'!B2.

61List validation

A rule that lets a cell accept only items from a set list, shown as a drop-down. Items can be typed in or taken from a range of cells, which keeps spelling consistent.

62Named range

A readable name given to a cell or range, like Selected_Year, which formulas and charts can use instead of cell addresses such as B2:B30.

63Data Validation List

A drop-down in one cell, made with Data Validation, that lets the user pick an item from a range; formulas read that cell so the chart shows only the chosen item.

64=INDEX(B2:M10,MATCH(A13,A2:A10,0),0)

MATCH finds the row of the item chosen in the selector cell A13. INDEX returns that whole row into a helper range, and the chart plots the helper range, so it updates when the choice changes.

65form control

A Developer tab control, such as an option button or combo box, that writes the user's choice into a linked cell so formulas and charts can react.

66Scroll bar

A form control that changes a linked cell's number between a minimum and maximum, in steps, so a chart can move through values like years. A spin button does the same.

67="Sales for "&B1

A chart title linked to a cell. Click the title, type = then a cell or text joined with &, and the title updates when the cell changes, such as a drop-down choice.

68Plot visible cells only

A chart built from a filtered table plots only the visible rows, so applying or clearing a filter updates the chart. Hidden rows stay out unless Show data in hidden rows is on.

69Data visualisation mock-up

A sketch of a chart or graph made before building it, showing the chart type, titles, labels, colours, fonts and layout so the design can be checked and improved.

70Dashboard layout

Arranging charts, slicers and key figures on one sheet, neatly aligned and sized, with gridlines hidden, so the main message is easy to see at a glance.

71Chart verification

Comparing a chart's plotted values, category labels, totals and axis scale with the source data to confirm the chart shows the data accurately.

72Sales rose from 40 to 65

A trend statement says what changed, which way it went and the numbers that show it, plus a limit or reason such as 'because' or 'might'.

73conclusion

A statement that answers a question using evidence from the data, such as numbers. It should only say what the data shows and stay within the data's limits.

74Communicate insights

The final life cycle stage: present findings so the audience sees the answer to the original question. State the key finding first, link to the objective and note data limits.