Excel is, without exaggeration, the most practical and most-used module of the ICDL exam in a real workplace. This article focuses on exactly the concepts that usually cause the most confusion: relative vs. absolute references, conditional functions, and Pivot Tables.
Relative vs. absolute references
When you write a formula like =A1*B1 in a cell and copy it to cells below, Excel by default shifts the row numbers relative to the new position (a relative reference) — exactly the behavior we usually want. But sometimes one specific cell (like a tax rate in C1) needs to stay fixed across every formula. For this, we mark an absolute reference with a dollar sign: $C$1. When you copy this formula, the $C$1 part never changes, while the rest of the reference stays relative.
IFERROR: avoiding an ugly error display
Formulas like division show a #DIV/0! error when the denominator is zero — which looks bad in a professional report. By wrapping the original formula inside IFERROR, you can define a replacement message:
=IFERROR(A1/B1, "Invalid")
If the inner formula errors out, your specified text is shown instead of the error code.
VLOOKUP vs. INDEX/MATCH
Both are used for the same job: finding a value in a table based on another value (e.g., finding a product's price by its code). VLOOKUP is simpler but has one important limitation: it can only find results in columns to the right of the column it searches in. Combining INDEX and MATCH does the same job without that limitation, and holds up better in large, complex tables.
Pivot Tables: summarizing thousands of rows of data in a few clicks
Suppose you have a thousand rows of sales data (date, product, region, amount) and want to know the total sales of each product in each region. Instead of writing complex formulas, go to the Insert tab and choose PivotTable. In the window that opens, drag the "Product" field into Rows, "Region" into Columns, and "Amount" into Values. Excel automatically builds a summary table showing total sales for every product-region combination — and by dragging fields between sections, you can change your view of the data instantly.
Summary
The difference between someone who only knows simple formulas and someone who genuinely "works" with Excel comes down to exactly these four concepts — absolute references, error handling, smart lookups, and summarizing data with Pivot Tables.
Blog