Embedded Files
Visit and subscribe theCPAtube. Complete all HBL assignments on SLS & GC.
Master Your Spreadsheet Skills
11 (UPPER SEC) MS-Excel Skills You Must Know
11 (UPPER SEC) MS-Excel Skills You Must Know
1. Sorting Ascending/Descending
2. Conditional Formatting
3. Data Validation
4. Combination Chart
5. TEXT series
6. COUNT series
7. RANDBETWEEN
8. IF
9. ROUND series
10. VLOOKUP
11. =TODAY()
Other Skills You Should Already Know
Insert Row / Column
Cell Merge
Cell Formatting (Colours)
Borders
Currency
Decimal Places
Calculating Percentages
Data Type (Number, Date, Currency)
Percentage Calculations
Percentage Calculations
Example 1
Percentage Boys = (Boys/Class Size) * 100
...
There are 10 boys in a class of 40 students. What % of the class is boys?
% boys = (10/40) x 100 = 25%
Example 2
Percentage Marks =
(Actual Marks/Total Marks) * 100
...
Alfred scored 40 marks. The test paper is 50 marks. Calculate his percentage score.
% Marks = 40/50 x 100 = 80%
(1) TODAY
(1) TODAY
Today's Date
How do we get today's date?
Ans : = TODAY()
Ans : = TODAY()
...
Other related Date functions (YEAR, MONTH, DAY)
= YEAR (1 Feb 2020) | Ans = 2020
= MONTH (1 Feb 2020) | Ans = 2
= DAY (1 Feb 2020) | Ans = 1
....
You can also combine Date functions
= YEAR (TODAY()) | Ans = 2020
Do not forget the equals sign & brackets.
(2) Sorting
(2) Sorting
ASCENDING /
ASCENDING /
DESCENDING
DESCENDING
MS-Excel_FineSkills_USVideo Tutorial
Video Tutorial
Check out the video tutorial on Sorting Ascending vs Descending order here
Data Validation Settings
(text length, number range, etc)
Input Message
A message to help user to input the correct data.Title = Input Data
Error Alert
A message to alert user if data is not accepted.Title = Error(4) Conditional Formatting
(4) Conditional Formatting
In MS-Excel,
go to Home/Conditional Formatting
Conditional formatting will automatically format (colour) cells that meet specific conditions
(5) Combination Chart
(5) Combination Chart
In MS-Excel,
Highlight the data to be plotted
Goto Insert/Chart
Choose Chart No 6.
Combination Charts will allow to create a combination of a line chart and a column chart.
Step 1: Selecting Data
Step 1: Selecting Data
Step 6: Chart 6 Option 2
Step 6: Chart 6 Option 2
(6) COUNT series
(6) COUNT series
COUNT (range)
COUNTA (range)
COUNTIF (range, condition)
...
There are only 3 of these.
Do not confuse IF with COUNTIF (which is a counting function).
IF is a logic function.
(7) IF
(7) IF
...
=IF (Condition, TRUE, FALSE)
=IF (Condition, TRUE, FALSE)
...
There is one condition and a TRUE & FALSE outcome
Example 1: GCE O/N-level Marks
Example 1: GCE O/N-level Marks
= IF (Marks >=75, "Distinction", "Pass")
= IF (Marks >=75, "Distinction", "Pass")
Example 2: NAPFA 2.4km Standards
Example 2: NAPFA 2.4km Standards
= IF (Time < 10.31, "Gold", "Pass")
= IF (Time < 10.31, "Gold", "Pass")
...
Do not confuse IF with COUNTIF (which is a counting function).
IF is a logic function.
(8) RANDBETWEEN
(8) RANDBETWEEN
...
Generate Random numbers
=RANDBETWEEN(smallest, largest)
(1) Generate a random 3-digit number
= RANDBETWEEN(100,999)
= RANDBETWEEN(100,999)
(2) Generate a random 4-digit number
= RANDBETWEEN(1000,9999)
= RANDBETWEEN(1000,9999)
(9) ROUND series
(9) ROUND series
(1) ROUND (number)
(1) ROUND (number)
Round follows maths rules
Example 1: ROUND (9.1, 0 ) = 9
Example 2: ROUND (9.8, 0 ) = 10
(2) ROUNDUP (number, no. of decimal place)
(2) ROUNDUP (number, no. of decimal place)
Rounds up number
Example 1 : =ROUNDUP(9.1,0) = 10
Example 2 : = ROUNDUP(9.8,0) = 10
(3) ROUNDDOWN (number, no. of decimal place)
(3) ROUNDDOWN (number, no. of decimal place)
Rounds down number
Example 1 : =ROUNDDOWN(9.1,0) = 9
Example 2 : = ROUNDDOWN(9.8,0) = 9
(10) VLOOKUP
(10) VLOOKUP
...
In MS-Excel,
Go to Formulas
LookUp & References
VLOOKUP
Tutorial video will be featured at gg.gg/theCPAtube
Practice Makes Perfect
Google Sites
Report abuse