(Analyze Your Data) : (Excel 2021/365 Functions)
Uploaded by
Shadi(Analyze Your Data) : (Excel 2021/365 Functions)
Uploaded by
Shadi[ANALYZE YOUR DATA]
[Excel 2021/365 Functions]
Author: Non-Tech to TECH
EXCEL 365/2021 >>> EXCEL IT
Let’s explore the cool stuffs available in Excel 365/2021.
i. Spilling: Excel formulas that return a set of values, also known as an array, return these values to
neighboring cells. This behavior is called spilling.
ii. #SPILL! Error: Most often, a #SPILL! error occurs when a spill range is blocked by something on
the worksheet, and the solution is to clear the spill range of any obstructing data. However, a
#SPILL! error can have other causes as well.
iii. TEXTJOIN Function: This function is similar to CONCAT but more powerful. It joins multiple
cells or ranges to one string with a specified delimiter. Yes, you can specify the delimiter or a
separator in the function. Remember if the delimiter is a number, it will be treated as a text.
Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Above example the delimiter is space, Ignore blank values in TRUE.
iv. IFS Function: This function checks the multiple conditions and returns the corresponding value.
Note: You can only include 127 combinations of logic and conditions.
Syntax: IFS(logical_test1,value_if_true1,[logical_test2, value_if_true2], ...)
v. Switch: This function helps you to evaluate your expression against the given set of values as IF
function does. It returns the value based on the first exact match.
Syntax: SWITCH (expression, value1, result1, [value2, result2], ..., [default])
vi. SORT Function: The SORT function allows you to dynamically sort your dataset results based on
a column within the data. To understand the SORTBY function, the syntax is as follows (parameters
in brackets are optional):
Syntax: SORT(array, [sort_index], [sort_order], [by_col])
vii. SORTBY Function: If you need to sort data by a column that will not appear in the result, you
can use the SORTBY function.
Syntax: SORTBY(array, by_array1, [sort_order1]…)
viii. UNIQUE Function: You’d like to get a unique list of items from your data set. The result will
include each item from the data set only once in the final list. In Legacy Excel, this required using
multiple functions and was quite complicated. You’d like to get a distinct list of items from your data
set. The result will show items that occur only once in the data set. You’d like your unique list to
update automatically without the need to refresh a query.
Syntax: UNIQUE(array, [by_col], [occurs_once])
ix. The XLOOKUP Function: The XLOOKUP function is the intended successor to the older,
tried-and-true VLOOKUP function. XLOOKUP is much more flexible and easier to use than
VLOOKUP.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array)
lookup_value – is the value we want to find
lookup_array – is the list to find the lookup_value within
return_array – is the list to return from upon discovery
[if_not_found] – is what to display if no match exists (e. text message or default value)
[match_mode] – specifies the Match Type. (0 = Exact match {default}, -1 = Exact match or
next smaller, 1 = Exact match or next larger, 2 = Wildcard match)
[search_mode] – specifies the Search Mode. (1 = Search first to last {default}, -1 = Search
last to first, 2 = Binary search {ascending}, -2 = Binary search {descending})
x. The FILTER Function: The FILTER function can be thought of as the new power lookup function.
Situations you will benefit from when using the FILTER function include: Cases when you need to
return multiple results for one or more lookup values. When using VLOOKUP or INDEX/MATCH,
the return value is always the first occurrence of qualifying data. Suppose you need to return all
instances of qualifying data? This was an extremely difficult operation to perform using older Excel
functions. Cases when you need to use a filter that automatically refreshes without the need to
execute a data refresh. Scenarios where you need to sum or count the filtered values. The FILTER
function can be used as an alternative to the SUMIF(S) and COUNTIF(S). To understand the FILTER
function, the syntax is as follows (parameters in brackets are optional):
Syntax : FILTER(array, include, [if_empty])
xi. SEQUENCE Function: The SEQUENCE function is useful in the following situations: You need
to generate a list of index numbers. You’d like to simulate dates based on specific intervals.
Modeling and simulations. Excel Calendar & loan amortization tables (examples in a later section).
Transforming legacy Excel formulas to power formulas (examples in later sections). The
SEQUENCE function by itself is not the most exciting function. In fact, it seems downright bland
and limited in its use. The power of the SEQUENCE function comes when it is combined with
other functions. We will see many creative and impressive uses of SEQUENCE when we delve into
the Advanced and Expert sections of this course. To understand the SEQUENCE function, the
syntax is as follows (parameters in brackets are optional):
Syntax: SEQUENCE(rows, [columns], [start], [stop])
JOIN TO EXPLORE MORE
Non-Tech to Tech basically designed for aspirants, employees who don’t belongs / have any
technical degree / work experience. Let’s join our hands & guide each other in best possible way to
achieve our profession goal / growth. Share your experience.
Do join me for more such informative data stories and resources.
THANKS & WARM REGARDS,
BIMAL SUBHASIS