Welcome to this easy-to-follow tutorial! In this guide, we will explore 15 powerful Excel functions that can make your work faster, smarter, and much easier. Each function comes with a clear definition, a beginner-friendly Details, and a practical example.
Sample Dataset
Let’s explore each function with practical examples you can try right away!
1- XLOOKUP:
Searching for Specific Data
Syntax: =XLOOKUP(lookup_value,
lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Definition: XLOOKUP is a modern, versatile
Excel function that searches for a specified value in a column (or row) and
returns a corresponding value from another column (or row). It’s a powerful
replacement for older functions like VLOOKUP and HLOOKUP.
Details: Imagine you need to find an
employee’s salary quickly without scrolling through the dataset. XLOOKUP allows
you to search for the employee’s name in the Name column and retrieve their
salary from the Salary column in one step. It’s flexible, allowing exact or
approximate matches, and can handle errors gracefully with the optional
[if_not_found] parameter. The [match_mode] parameter lets you control whether
you want an exact match or the closest value, and [search_mode] allows you to
specify the search direction (e.g., first-to-last or last-to-first). This
function is ideal for quick lookups in large datasets.
Example: Suppose you want to find Fatima’s salary. Assuming her name is in the Name column (B2:B14) and her salary is in the Salary column (D2:D14), use: =XLOOKUP("Fatima", B2:B14, D2:D14) This formula searches for “Fatima” in cells B2:B14 and returns the corresponding value from D2:D14. If Fatima’s salary is 48000, the result will be 48000. Try experimenting with different names or adding the [if_not_found] parameter, like "Not Found", to handle cases where the name doesn’t exist.
2- FILTER: Pulling Out Matching Rows
Syntax: =FILTER(array, include,
[if_empty])
Definition: FILTER extracts rows from a
dataset that meet specific criteria, returning a dynamic subset of the data.
Details: FILTER is perfect for isolating
specific data based on conditions. For example, if you want to see only
employees from the Sales department, FILTER will return all rows where the
Department column equals “Sales.” The array is the range you want to filter,
include is the condition (e.g., C2:C14="Sales"), and [if_empty]
specifies what to display if no rows match (e.g., “No Results”). This function
is dynamic, meaning it updates automatically if the source data changes, making
it ideal for dashboards or reports.
Example: To display all employees in the Sales department, including their Name, Department, and Salary: =FILTER(B2:D14, C2:C14="Sales") This formula filters the range B2:D14 to show only rows where the Department (C2:C14) is “Sales.” The result will display names like Ahmed, Bilal, and Zainab (assuming they’re in Sales), along with their corresponding department and salary. Try changing the condition to “HR” or “Finance” to see different results.
3- SORTBY: Rearranging Data by Size
Syntax: =SORTBY(array, by_array1,
sort_order1, [by_array2, sort_order2], …)
Definition: SORTBY organizes a dataset based
on the values in one or more columns, with customizable sort orders.
Details: SORTBY is a dynamic sorting tool
that arranges your data based on a specified column. For example, you can sort
employees by their bonuses in descending order to see who received the highest
bonuses. The array is the range to sort, by_array1 is the column to sort by,
and sort_order1 is either 1 (ascending) or -1 (descending). You can add
additional sorting criteria with [by_array2, sort_order2] for multi-level
sorting. This function is great for ranking or prioritizing data.
Example: To sort the employee dataset (Name, Department, Salary, Bonus) by Bonus in descending order: =SORTBY(B2:E14, E2:E14, -1) This formula sorts the range B2:E14 based on the Bonus column (E2:E14) from highest to lowest. The result will show employees with the highest bonuses at the top. Try sorting by Salary (D2:D14) or in ascending order (1) to explore different arrangements.
4- UNIQUE: Spotting One-of-a-Kind Items
Syntax: =UNIQUE(array, [by_col],
[exactly_once])
Definition: UNIQUE extracts distinct values
from a list, removing duplicates automatically.
Details: UNIQUE is a time saver when you
need to identify distinct items in a column, such as unique regions or
departments in your dataset. The array is the range to analyze, [by_col]
determines if you’re extracting unique values by column (TRUE) or row (FALSE),
and [exactly_once] returns values that appear exactly once if set to TRUE. This
function is perfect for summarizing categories or cleaning up lists with
duplicates.
Example: To find unique regions for employees in the HR department: =UNIQUE(FILTER(G2:G14, C2:C14="HR")) This formula first filters the Region column (G2:G14) for employees in HR (C2:C14="HR") and then extracts unique regions.
5- SEQUENCE: Creating Number Lists Automatically
Syntax: =SEQUENCE(rows, [columns],
[start], [step])
Definition: SEQUENCE generates a list of
sequential numbers based on specified parameters.
Details: SEQUENCE is a handy function for
creating lists of numbers automatically, such as employee IDs or dates. The
rows parameter sets the number of rows, [columns] sets the number of columns
(default is 1), [start] is the first number (default is 1), and [step] is the
increment between numbers (default is 1). This function is ideal for generating
serial numbers or filling tables dynamically.
Example: To create a list of 13 sequential numbers for employee IDs: =SEQUENCE(13, 1, 1, 1) This formula generates a single column with numbers 1 through 13, starting at 1 with an increment of 1. Try adjusting the rows to 10 or changing the start to 100 to see how the output changes.
6- TEXTJOIN: Combining Text into One Line
Syntax: =TEXTJOIN(delimiter,
ignore_empty, text1, [text2], …)
Definition: TEXTJOIN combines multiple text
values into a single cell, separated by a specified delimiter.
Details: TEXTJOIN is perfect for merging
text from multiple cells into one, such as creating a comma-separated list of
products. The delimiter is the character (e.g., ", ") used to
separate values, ignore_empty determines whether to skip empty cells (TRUE or
FALSE), and text1, [text2], … are the text items to combine. This function is
great for summarizing lists or creating readable outputs.
Example: To combine all products sold by the Marketing department into a single cell: =TEXTJOIN(", ", TRUE, FILTER(L2:L14, C2:C14="Marketing")) This formula filters the Product column (L2:L14) for Marketing employees, then joins the results with a comma and space. The result might be “Tablet, Smartwatch.” Try using a different delimiter, like “ | ”, or filtering for another department.
7- LET: Simplifying Formulas with Named Parts
Syntax: =LET(name1, value1, [name2,
value2], …, calculation)
Definition: LET assigns names to parts of a
formula to make it more readable and efficient.
Details: Complex formulas can be hard to
read and maintain. LET allows you to define variables (names) for intermediate
calculations, reducing repetition and improving clarity. The name1, value1
pairs define variables, and calculation is the final formula using those names.
This is especially useful for formulas with repeated calculations, like
averaging filtered data.
Example: To calculate the average salary of Finance department employees: =LET(finSalary, FILTER(D2:D14, C2:C14="Finance"), AVERAGE(finSalary)) This formula names the filtered salaries (D2:D14 where C2:C14="Finance") as finSalary and then calculates their average. If the salaries are 62000, 65000, and 63000, the result is 63333.33. Try using LET with a different calculation, like summing bonuses.
8- INDEX: Grabbing Items by Position
Syntax: =INDEX(array, row_num,
[column_num])
Definition: INDEX retrieves a value from a
specific position in a range.
Details: INDEX is like a GPS for your
data, letting you pinpoint a value by specifying its row and (optionally)
column position within a range. The array is the range to search, row_num is
the row position, and [column_num] is the column position (if the range is
multi-column). This function is often paired with other functions like XMATCH
for dynamic lookups.
Example: To retrieve the product in the 4th row of the Product column: =INDEX(L2:L14, 4) This formula returns the value in the 4th position of L2:L14, which might be “Printer.” Try changing the row number or using a multi-column range like B2:E14 with a column number.
9- XMATCH: Finding Where Something Sits
Syntax: =XMATCH(lookup_value,
lookup_array, [match_mode], [search_mode])
Definition: XMATCH finds the relative
position of a value in a range.
Details: XMATCH is a modern replacement
for MATCH, designed to locate where a specific value appears in a column or
row. The lookup_value is what you’re searching for, lookup_array is the range
to search, [match_mode] controls exact or approximate matching, and [search_mode]
specifies the search direction. It’s often used with INDEX to dynamically
retrieve values.
Example: To find the position of Imran’s name in the Name column: =XMATCH("Imran", B2:B14) This formula returns the position of “Imran” in B2:B14, such as 9 if Imran is in cell B10. Try searching for a different name or experimenting with [match_mode] set to 1 for approximate matching.
10- TEXTAFTER: Cutting Text After a Spot
Syntax: =TEXTAFTER(text, delimiter,
[instance_num], [match_mode], [match_end], [if_not_found])
Definition: TEXTAFTER extracts the portion of
text that appears after a specified delimiter.
Details: TEXTAFTER is useful for parsing
text strings. For example, if a product name contains a specific character, you
can extract everything after it. The text is the string to parse, delimiter is
the character or string to split on, [instance_num] specifies which occurrence
of the delimiter to use, and [if_not_found] handles cases where the delimiter
isn’t found. This function is great for cleaning or reformatting text.
Example: To extract the text after “t” in “Smartwatch”: =TEXTAFTER("Smartwatch", "t") This formula returns “watch” (the text after the first “t”). Try using a different product name or setting [instance_num] to 2 to extract after the second occurrence of the delimiter.
11- TEXTBEFORE: Snipping Text Before a Spot
Syntax: =TEXTBEFORE(text, delimiter,
[instance_num], [match_mode], [if_not_found])
Definition: TEXTBEFORE extracts the portion
of text that appears before a specified delimiter.
Details: TEXTBEFORE is the counterpart to
TEXTAFTER, allowing you to grab text before a specific character or string. The
parameters are similar: text is the input string, delimiter is the splitting
point, and [instance_num] lets you choose which occurrence to use. This is
useful for extracting prefixes or cleaning up data.
Example: To extract the text before the space in “Power Bank”: =TEXTBEFORE("Power Bank", " ") This formula returns “Power.” Try using a different product name or delimiter, like “-” in a hyphenated string.
12- WRAPROWS: Folding Long Lists into Neat Rows
Syntax: =WRAPROWS(vector, wrap_count,
[pad_with])
Definition: WRAPROWS reshapes a list of
values into rows of a specified length.
Details: WRAPROWS is perfect for
reformatting a long list into a table-like structure. The vector is the range
to reshape, wrap_count specifies how many items per row, and [pad_with] fills
any empty spots in the last row. This is useful for organizing data visually,
such as displaying products in a grid.
Example: To arrange the Product column into rows of 5 items: =WRAPROWS(L2:L14, 5) This formula takes the products in L2:L14 and organizes them into rows, with 5 products per row. If there are 13 products, the last row will have 2 items. Try changing wrap_count to 3 or 4 to see different layouts.
13- TOCOL: Stacking Everything into One Column
Syntax: =TOCOL(array, [ignore],
[scan_by_column])
Definition: TOCOL stacks a range of values
into a single column.
Details: TOCOL is great for flattening
multi-column or multi-row data into one column. The array is the range to
stack, [ignore] lets you skip blanks or errors (e.g., 1 for blanks), and
[scan_by_column] determines whether to stack by column (TRUE) or row (FALSE).
This is useful for consolidating data for further analysis.
Example: To stack all products into a single column: =TOCOL(L2:L14) This formula takes the Product column (L2:L14) and lists all values vertically in one column. Try applying TOCOL to a multi-column range like B2:E14 to see how it stacks the data.
14- REDUCE: Building Totals Step-by-Step
Syntax: =REDUCE([initial_value], array,
lambda(accumulator, value))
Definition: REDUCE applies a LAMBDA function
repeatedly to reduce a range of values into a single result.
Details: REDUCE is a powerful function for
performing iterative calculations, like summing or multiplying values
step-by-step. The [initial_value] is the starting point, array is the range to
process, and lambda(accumulator, value) defines the operation to apply. This is
ideal for custom aggregations, such as summing filtered data.
Example: To sum the salaries of Finance department employees: =REDUCE(0, FILTER(D2:D14, C2:C14="Finance"), LAMBDA(a, b, a + b)) This formula filters salaries for Finance employees, then uses LAMBDA to add each salary to the accumulator, starting from 0. If the salaries are 62000, 65000, and 63000, the result is 190000. Try using REDUCE to multiply bonuses or apply a different operation.
15- MAP: Applying Changes to Every Item
Syntax: =MAP(array1, [array2], …,
lambda(parameter1, [parameter2], …))
Definition: MAP applies a custom calculation
to each item in one or more ranges, returning an array of results.
Details: MAP is like a transformer for
your data, allowing you to apply a formula to every value in a range. The
array1, [array2], … are the input ranges, and lambda(parameter1, [parameter2],
…) defines the calculation. This is great for tasks like increasing bonuses by
a percentage or reformatting text across a range.
Example: To increase all bonuses for Marketing employees by 10%: =MAP(FILTER(E2:E14, C2:C14="Marketing"), LAMBDA(x, x * 1.1)) This formula filters bonuses for Marketing employees, then multiplies each by 1.1. If the bonuses are 4200, and 4100, the result is 4620, and 4510. Try applying a different calculation, like adding a fixed amount.
Conclusion
Mastering these 15 Excel
functions—XLOOKUP, FILTER, SORTBY, UNIQUE, SEQUENCE, TEXTJOIN, LET, INDEX,
XMATCH, TEXTAFTER, TEXTBEFORE, WRAPROWS, TOCOL, REDUCE, and MAP—will transform
the way you handle data. Each function is designed to simplify specific tasks,
from searching and filtering to sorting and text manipulation. By practicing
with the provided employee dataset, you can build hands-on skills and apply
these functions to real-world scenarios. Experiment with different parameters,
combine functions, and explore how they interact to unlock Excel’s full
potential.
0 Comments