Excel Exam Guideline (based on your theory files)
Friday, August 8, 2025 3:12 PM
1. INTRO (Basics)
What is Excel?
Excel is a big grid made of cells (small boxes). Each cell is where you can type numbers, text, or formulas.
• Rows: go horizontally (1, 2, 3 …).
• Columns: go vertically (A, B, C …).
• Cell reference: column letter + row number (example: B3).
• Range: group of cells, like A1:A10.
Formulas always start with =. Example:
• =2+2 → 4
• =A1+B1 → adds two cells.
Operators
• + add, - subtract, * multiply, / divide, ^ power
• Comparison: >, <, =, >=, <= (returns TRUE/FALSE).
Order of calculation:
1. Brackets ( )
2. Powers ^
3. Multiplication & Division
4. Addition & Subtraction
Autofill (fill handle)
That small square in the bottom-right of a cell → drag it:
• Type 1, 2, drag down → Excel continues 3, 4, 5…
• Type Monday, drag down → Excel continues Tuesday, Wednesday…
• With RMB (right mouse button) drag: choose Linear Trend (add +1), Growth Trend (multiply), Date
Series, etc.
• Flash Fill: Excel guesses patterns. Example:
○ Column A: “John Smith”
○ In B1, you type: “John”
○ Drag fill → Excel fills only first names.
Paste Special
Instead of CTRL+V, use RMB > Paste Special. Options:
• Values (ignore formulas)
• Formatting only
• Formulas only
• Transpose (switch rows ↔ columns)
2. CELL REFERENCES
There are 3 types:
• Relative (changes when copied)
Example: =A1+B1 copied down becomes =A2+B2.
• Absolute (always fixed)
Use $. Example: =$A$1+B1. Copying down always points to cell A1.
• Mixed (lock either row or column)
○ =A$1 → row locked
○ =$A1 → column locked
Example: VAT in $C$1 → formula =B2*$C$1 works for all rows when copied.
3. BASIC FUNCTIONS
New Section 5 Page 1
3. BASIC FUNCTIONS
Functions are ready-made formulas. Syntax:
=FUNCTION(argument1, argument2, …)
• =SUM(A1:A5) → adds numbers.
• =MAX(A1:A5) → largest value.
• =MIN(A1:A5) → smallest value.
• =AVERAGE(A1:A5) → mean.
• =COUNT(A1:A5) → counts numbers only.
• =COUNTA(A1:A5) → counts all non-empty cells.
• =SQRT(9) → 3.
Example: Student grades in A1:A10
• Highest: =MAX(A1:A10)
• How many filled in: =COUNTA(A1:A10)
4. IF FUNCTION
Syntax:
=IF(logical_test, value_if_true, value_if_false)
Example:
• =IF(A1>=50,"Pass","Fail")
If A1 is 70 → Pass, if 40 → Fail.
Nested IFs: multiple conditions.
Example:
• =IF(A1>=90,"A",IF(A1>=80,"B","C"))
90+ = A, 80–89 = B, else C.
5. IF with AND / OR + SUMIF / COUNTIF
• AND → all conditions must be TRUE
Example: =IF(AND(A1>=50,B1>=50),"Pass","Fail")
• OR → at least one condition TRUE
Example: =IF(OR(A1="Yes",B1="Yes"),"Eligible","Not Eligible")
• SUMIF(range, condition, sum_range)
Example: =SUMIF(A1:A10,">=50",B1:B10) → add numbers in B1:B10 if A1:A10 is ≥50.
• COUNTIF(range, condition)
Example: =COUNTIF(A1:A10,"Pass") → count how many “Pass”.
6. DATE FUNCTIONS
• =TODAY() → today’s date.
• =DATE(2025,8,22) → 22 Aug 2025.
• =YEAR(A1), =MONTH(A1), =DAY(A1) → extract parts.
• Difference: =TODAY()-A1 → number of days since A1.
• Times: =NOW() → current date & time. Multiply time by 24 to convert to hours.
Example:
• Start date in A1 = 01/01/2025
• End date in B1 = 01/03/2025
• Formula: =B1-A1 → 59 days.
7. VALIDATION
Validation limits what can be entered.
Examples:
• Whole numbers 1–10 only → Data > Validation > Whole Number → between 1 and 10.
• Dates between 01/01/2025 and 31/12/2025.
New Section 5 Page 2
• Dates between 01/01/2025 and 31/12/2025.
• Drop-down list → choose “List” and type items (“Yes,No”).
• Custom: =MOD(A1,2)=0 → only even numbers.
8. VLOOKUP
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example table:
ID Name Grade
101 Alice 85
102 Bob 70
Formula:
• =VLOOKUP(102,A2:C10,3,FALSE) → returns 70.
Important: Lookup column (first column in table_array) must contain the search value.
9. ROUNDING
• =ROUND(3.14159,2) → 3.14
• =ROUNDUP(3.14159,2) → 3.15
• =ROUNDDOWN(3.14159,2) → 3.14
Warning from your les: forma ng decimals rounding. Forma ng changes appearance only.
R UND actually changes the number.
10. COUNTING (Recap)
• COUNT(A1:A10) → counts numbers.
• COUNTA(A1:A10) → counts all filled.
• COUNTIF(A1:A10,">50") → counts cells > 50.
11. CELL FORMATTING
Change how data looks, not what it is.
• Number: Currency, %, decimal places.
• Date: Long (Wednesday, August 22, 2025) vs Short (22/08/25).
• Alignment: left, right, center.
• Font, color, borders.
12. CONDITIONAL FORMATTING
Automatically color cells if condition is met.
Examples:
• Highlight values > 100 → Home > Conditional Formatting > Greater Than.
• Highlight today’s date → use =T DAY().
• Top 10 → highlight highest 10.
• Data bars → fill cells with color bars.
• Icon sets → arrows, symbols.
• Custom formula → e.g., =A1>B1 highlights cells where A > B.
13. GRAPHS (Charts)
Types of charts in your files:
• Column/Bar → compare categories.
• Line → trends over time.
• Pie → share of total.
New Section 5 Page 3
• Pie → share of total.
• Stacked column → parts of a whole.
• Combo chart (secondary axis) → compare values with very different scales.
• Map chart → by region.
• Sparklines → tiny charts inside a cell.
Steps: Select data → Insert → Chart type → Format elements (titles, axis, labels, gridlines).
14. PIVOT TABLES
Used for summarizing large tables.
Steps:
1. Select data → Insert > PivotTable.
2. Drag fields into:
○ Rows (categories)
○ Columns (subcategories)
○ Values (numbers, sums, counts, averages)
○ Filters (extra filtering).
Examples:
• Sales data: drag Product to Rows, Month to Columns, Sales to Values → get table of sales per
product per month.
• Right-click numbers → Show Values As → % of Total.
• Group dates → by Month, Quarter, Year.
• Pivot Chart → automatically linked to pivot.
New Section 5 Page 4