Unit 1: Understanding and describing data
Basics of Data Analytics using Spreadsheet Laboratory notes · PTU syllabus (UGDSE102)
On this page
Unit summary
The first half of this lab builds core spreadsheet skills: workbooks, data entry, formatting, basic functions and referencing; importing and cleaning data; and describing data with descriptive statistics, frequency distributions and the Data Analysis Toolpak.
After this unit you can
- Enter, format and calculate data using basic Excel functions and cell references
- Import CSV, text and web data and clean it with Find & Replace and text functions
- Compute descriptive statistics and frequency distributions
- Use the Analysis Toolpak for summary statistics
PTU syllabus topics
- Excel basics — workbook/worksheet/cells/ranges
- data entry and formatting
- SUM/AVERAGE/MIN/MAX/ROUND
- cell referencing
- importing data from CSV/text/web
- data cleaning and transformation
- Find & Replace and text functions
- descriptive statistics — mean/median/mode
- range/variance/standard deviation
- frequency distributions and the Data Analysis Toolpak
Average
=AVERAGE(B2:B50)
Rounding
=ROUND(B2, 2)
Standard deviation
=STDEV.S(B2:B50)
Frequency count
=COUNTIF(C2:C50, "Pass")
Absolute reference
=$B$2 * C2
$ fixes the cell when copying
Topic 1
Excel basics
A workbook contains worksheets; each worksheet is a grid of cells; a group of cells such as A1:A10 is a range.
| Function | Example |
|---|---|
| SUM | =SUM(B2:B20) |
| AVERAGE | =AVERAGE(B2:B20) |
| MIN / MAX | =MAX(B2:B20) |
| ROUND | =ROUND(C2, 2) |
Relative (A1), absolute ($A$1) and mixed ($A1, A$1) references control how formulas change when copied.
Topic 2
Importing and cleaning data
- Import: Data → Get Data → From Text/CSV or From Web.
- Find & Replace (Ctrl + H) fixes repeated errors.
- Text functions:
TRIM(removes extra spaces),PROPER/UPPER/LOWER(case),LEFT/RIGHT/MID(extract text),CONCATor&(join),TEXTSPLITor Text to Columns (split). - Remove Duplicates (Data tab) deletes repeated rows.
Topic 3
Descriptive statistics and the Analysis Toolpak
| Measure | Function |
|---|---|
| Mean / median / mode | AVERAGE, MEDIAN, MODE.SNGL |
| Range | =MAX(r) − MIN(r) |
| Variance / SD | VAR.S, STDEV.S |
| Frequency distribution | FREQUENCY(data, bins) or a PivotTable |
- 1
Enable it
File → Options → Add-ins → Analysis ToolPak
- 2
Data → Data Analysis
- 3
Choose Descriptive Statistics or Histogram
- 4
Select input range and output
- 5
Tick Summary statistics
- 6
Read mean, SD, skewness, range
Exam tip
The Toolpak's Descriptive Statistics output gives mean, median, mode, SD, variance, kurtosis, skewness, range, minimum, maximum, sum and count in one table — quote it in practical answers.
Key terms
- Range
- A group of cells such as A1:A10
- TRIM
- Removes extra spaces from text
- Analysis Toolpak
- An Excel add-in for statistical analysis
- FREQUENCY
- Counts values falling in each bin
Quick revision
- Clean text with TRIM, PROPER, Find & Replace, Remove Duplicates.
- AVERAGE, MEDIAN, MODE.SNGL, STDEV.S for statistics.
- Enable Analysis ToolPak for descriptive statistics and histograms.
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.What is the difference between a workbook and a worksheet?
- Q2.What does the TRIM function do?
- Q3.How do you import a CSV file into Excel?
- Q4.What is the Analysis Toolpak?
Long-answer questions
- Q1.Import a CSV data set, clean it and compute descriptive statistics.
- Q2.Create a frequency distribution and histogram of student marks in Excel.
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.