Beyond basic sums, Excel has powerful tools to analyse and manage data. This chapter covers the advanced features that turn a table of numbers into useful information.
1Useful built-in functions
Functions are ready-made formulas (all start with =):
| Function | Does | Example → result |
|---|---|---|
UPPER(text) | Makes text UPPERCASE | =UPPER("vid") → VID |
LOWER(text) | Makes text lowercase | =LOWER("VID") → vid |
CONCATENATE(a,b) | Joins text together | =CONCATENATE("Vid","aara") → Vidaara |
COUNTIF(range,cond) | Counts cells meeting a condition | =COUNTIF(B2:B9,">=75") → how many ≥ 75 |
IF(test,a,b) | Returns a if test is true, else b | =IF(A2>=33,"Pass","Fail") |
- UPPER/LOWER change text case; CONCATENATE joins text together.
- COUNTIF(range, condition) counts cells that meet a condition.
- IF(test, value-if-true, value-if-false) makes a decision, e.g. Pass/Fail.
2Sorting & filtering
Sorting arranges rows in order:
- Ascending (A→Z, small→large) or Descending (Z→A, large→small).
- Multi-level sort — sort by one column, then break ties with another (e.g. by Class, then by Marks).
Filtering — showing only some rows
| Tool | Does |
|---|---|
| AutoFilter | Adds dropdown arrows to headings to quickly show rows matching a value |
| Advanced Filter | Filters using more complex, multiple conditions you set up |
- Sort ascending (A→Z) or descending (Z→A); multi-level sort breaks ties using a second column.
- AutoFilter adds dropdowns to show rows matching a value; Advanced Filter handles complex conditions.
- Filtering hides non-matching rows (doesn't delete them) — clear it to see all again.
3Validation, formatting & charts
Data Validation restricts what can be typed into a cell — e.g. only whole numbers 0–100 for marks, or only dates. It stops bad data being entered.
Conditional Formatting automatically changes a cell's look based on its value — e.g. colour fails in red, top scores in green. The formatting updates as the data changes.
Charts
A chart turns numbers into a picture. Pick the type to fit the data:
| Chart | Best for |
|---|---|
| Column / Bar | Comparing values |
| Line | Change over time |
| Pie | Parts of a whole |
Select data → Insert → Chart → choose & customise. The chart updates when the data changes.
- Data validation restricts cell entries (e.g. marks 0–100) to stop bad data.
- Conditional formatting auto-changes a cell's look based on its value (red fails, green tops).
- Charts (column/line/pie) turn numbers into pictures and update when the data changes.
★ Practical: a smart marks ledger
In Excel, build a marks sheet that:
- Uses IF to print Pass/Fail and COUNTIF to count how many passed.
- Sorts students by Marks (descending), then does a multi-level sort by Class.
- Uses AutoFilter to show only students from one class.
- Adds conditional formatting (fails in red) and a column chart of the marks.
Ready for the chapter test?
Answer 5 questions. Score 60% or more to mark this chapter complete.
Start the test →💡 Log in to save your progress and earn the certificate.