Advanced Excel: exercises
Module 1: Advanced Formulas and Functions
Exercise 1: Logical Functions
Scenario: You need to evaluate sales performance and categorise it based on predefined
criteria.
Dataset:
Product Jan Feb Mar Apr
A 100 150 200 250
B 200 250 300 350
C 300 350 400 450
Instructions:
1. IF Function:
○ Use the IF function to categorize the total sales of each product as "Good" if
the total sales are above 1000, otherwise "Needs Improvement".
○ Formula: =IF(SUM(B2:E2)>1000, "Good", "Needs Improvement")
2. AND Function:
○ Use the AND function to check if the sales for each product are above 100 in
all months.
○ Formula: =IF(AND(B2>100, C2>100, D2>100, E2>100),
"Consistent", "Inconsistent")
3. OR Function:
○ Use the OR function to check if the sales for each product exceeded 300 in
any month.
○ Formula: =IF(OR(B2>300, C2>300, D2>300, E2>300), "High
Sales", "Average Sales")
IF Function
Purpose: Performs a logical test and returns one value if the condition is TRUE and another
if it’s FALSE.
Syntax:
=IF(logical_test, value_if_true, value_if_false)
AND Function
Purpose: Checks if all conditions are TRUE. Returns TRUE only if all conditions are met.
Syntax:
=AND(condition1, condition2, ...)
OR Function
Purpose: Checks if at least one condition is TRUE. Returns TRUE if any condition is met.
Syntax:
=OR(condition1, condition2, ...)
Exercise 2: Lookup and Reference Functions
Scenario: You need to extract specific data from a larger dataset.
Dataset:
Product ID Product Name Price
101 Widget A 10
102 Widget B 15
103 Widget C 20
Instructions:
1. VLOOKUP:
○ Use the VLOOKUP function to find the price of "Widget B".
○ Formula: =VLOOKUP("Widget B", B2:C4, 2, FALSE)
2. INDEX and MATCH:
○ Combine INDEX and MATCH to find the price of "Widget C".
○ Formula: =INDEX(C2:C4, MATCH("Widget C", B2:B4, 0))
The formulas VLOOKUP and the combination INDEX and MATCH both perform lookup
operations in Excel, but they differ in functionality, flexibility, and use cases.
Usage:
● Vertical Lookup: VLOOKUP stands for Vertical Lookup. It searches for the value in
the first column of the specified range and returns a value from a specified column in
the same row.
● Simple and Intuitive: It's straightforward to use for simple lookups where the lookup
column is to the left of the return column.
Limitations:
● Fixed Structure: The lookup value must be in the first column of the table array.
● Less Flexible: It cannot look to the left of the lookup column or handle more complex
scenarios.
● Performance: May be slower with large datasets.
INDEX and MATCH
Usage:
● Flexibility: INDEX and MATCH can look up values in any direction—left, right, up, or
down—making it more versatile than VLOOKUP.
● Dynamic Arrays: They allow for more dynamic ranges and arrays, which can
change based on other calculations.
● Two-Part Function: While more complex, this combination is powerful for more
advanced lookup requirements.
When to Use Each
● Use VLOOKUP:
○ When the data structure is simple and you are looking up values to the right
of the lookup column.
○ When ease of use and quick setup are priorities.
○ When working with small to moderately sized datasets.
● Use INDEX and MATCH:
○ When you need to look up values to the left of the lookup column or in any
direction.
○ When dealing with large datasets where performance is a concern.
○ When you need more flexibility and dynamic range references.
○ When working with complex datasets that require advanced lookup
capabilities.
VLOOKUP Function
Purpose: Searches for a value in the first column of a table and returns a value in the same
row from another column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
INDEX Function
Purpose: Returns the value of a cell at a specific row and column in a range.
Syntax:
=INDEX(array, row_num, [column_num])
● array: The range of cells.
● row_num: The row number in the array.
● [column_num]: (Optional) The column number in the array.
MATCH Function
Purpose: Searches for a value in a range and returns its position.
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
● lookup_value: The value you’re searching for.
● lookup_array: The range to search in.
● [match_type]: 0 for an exact match, 1 for the closest smaller match, -1 for the
closest larger match.
Exercise 3: Text Functions
Scenario: You have a list of product codes that need to be manipulated and formatted.
Dataset:
Product Code
A12345
B67890
C54321
D98765
E24680
Instructions:
1. TEXT Function:
○ Format the product codes as "Code: A12345".
○ Example Formula for Cell B2: ="Code: " & A2
2. LEFT Function:
○ Extract the first letter of each product code.
○ Example Formula for Cell C2: =LEFT(A2, 1)
3. RIGHT Function:
○ Extract the last five digits of each product code.
○ Example Formula for Cell D2: =RIGHT(A2, 5)
4. MID Function:
○ Extract the numeric part of each product code.
○ Example Formula for Cell E2: =MID(A2, 2, 5)
5. LEN Function:
○ Find the length of each product code.
○ Example Formula for Cell F2: =LEN(A2)
6. SUBSTITUTE Function:
○ Replace the letter "A" with "Z" in the product code "A12345".
○ Example Formula for Cell G2: =SUBSTITUTE(A2, "A", "Z")
TEXT Function
Purpose: Formats a number or date into text using a specific format.
Syntax:
=TEXT(value, format_text)
● value: The number or date you want to format.
● format_text: The format you want to apply (in quotes).
LEFT Function
Purpose: Extracts a specific number of characters from the start (left) of a text string.
Syntax:
=LEFT(text, num_chars)
● text: The text to extract from.
● num_chars: The number of characters to extract.
RIGHT Function
Purpose: Extracts a specific number of characters from the end (right) of a text string.
Syntax:
=RIGHT(text, num_chars)
● text: The text to extract from.
● num_chars: The number of characters to extract.
MID Function
Purpose: Extracts a specific number of characters from the middle of a text string.
Syntax:
=MID(text, start_num, num_chars)
● text: The text to extract from.
● start_num: The position to start extracting from.
● num_chars: The number of characters to extract.
LEN Function
Purpose: Counts the number of characters in a text string (including spaces).
Syntax:
=LEN(text)
● text: The text to count characters in.
SUBSTITUTE Function
Purpose: Replaces specific text in a string with new text.
Syntax:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
● text: The text to search in.
● old_text: The text you want to replace.
● new_text: The new text to replace it with.
● [instance_num]: (Optional) Which occurrence to replace (if omitted, all are
replaced).
