09973382606 | Sayar Kaung (ICT)
Chapter 20 – Data Analysis
Spreadsheet
Spreadsheets are used for a number of different purposes, such as performing loan or tax
calculations and helping teachers calculate student grades from test percentages. They are used
to store numeric data values and perform mathematical calculations on the data values using
charts and graphs, which often make it easier for people to understand the data as information
and analyze it to find patterns or trends.
In this chapter, you will learn about the tools available in applications such as Microsoft
Excel, LibreOffice Calc and Apple Numbers.
Spreadsheet basis
• Software introduction
o Title bar
o Tab bar
o Work Sheet
o View (Normal, Page layout)
▪ Header/ footer
• Data/ time/ Page Number
• Work Sheet
o Row & Column
o Cell (Contents)
▪ Number
▪ Text
▪ Date
▪ Currency
o Replication
o Named cells and ranges
o Hide column, row
o Adjust width, height
o Alignment (left, center, right, top, middle, bottom)
o Border
o Page size
o Insert new (column, row)
o Merge
o Shading
o Protect cells/ sheets
09973382606 | Sayar Kaung (ICT)
Formulae
• Use of formulae → “=” sign
o Addition → +
o Subtraction → -
o Multiplication → *
o Division → /
o Exponential/ Power/ Indices (125) → ^ (shift + 6)
• Display formulae view adjust width → value view
Cell referencing
1. Relative cell referencing (default)
2. Absolute cell referencing (constant cell)
Functions
1. Sum
2. Average
3. Maximum
4. Minimum
5. Mode (most found)
6. Sqrt
7. Left
8. Right
9. Upper (Uppercase)
10. Lower (Lowercase)
11. Int
12. Round
13. Roundup
14. Rounddown
15. Len (Length) (Count numbers of character in a cell)
16. Count (counting → number cell)
17. Counta (cell count)
18. Countif (Check then count)
19. Sumif (Check then sum)
20. Averageif (Check then average)
21. If
22. Or (A or B)
23. And (A and B)
24. Vlookup (Vertical)
25. Hlookup (Horizontal)
26. Iferror
09973382606 | Sayar Kaung (ICT)
Sort & Filter
1. Sorting
a. Ascending
b. Descending
2. Search & Filter
a. Number filter ➔ greater, less, equal
b. Text Filter ➔ equal, start with, end with, contain
Conditional formatting
• Check cell value → change background & text colors
Data validation
• List
• Whole number
• Decimal number
09973382606 | Sayar Kaung (ICT)
Test the data model
Test range 1 to 7
1. Normal testing – 2 to 6
2. Extreme testing – 1 and 7 (minimum, maximum)
3. Abnormal testing – less than 1 or greater than 7 (0, -1, 8, 100, etc.)
4. Live data testing (real data)
Expected result in Actual result in
Data entry in A2 Test type Action
B2 B2
1 Extreme/ Normal -1 -1 -
-1 Abnormal Error Yes -
if
Expected result in Actual result in
Data entry in B3 Test type Action
B2 B2
Changed formula
addition to
1 Extreme/ Normal -1 0
multiplication sign
in cell C4
Need to make
-1 Abnormal Error No data validation in
cell B2
