The Most Important Excel Functions in 2021

 

source : 

Time Stamps 00:00 The Most Important Excel Functions in 2021 00:32 SORT Function (SORTBY) 03:16 UNIQUE Function in Excel 05:41 XLOOKUP 07:43 FILTER Function 09:29 SEQUENCE Function in Excel

#1 – The SORT & SORTBY Functions

Using the 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):

SORT(array, [sort_index], [sort_order], [by_col])

• array – is the range or array you want to be sorted. This can be the entire data set or part of the original data set.
• [sort_index] – is the column index position (read left-to-right in the data) which you want to sort by. The default response is 1 (left-most column).
• [sort_order] – defines the sort order for the column selected in the [sort_index] For an ascending sort, enter the value 1.  For a descending sort, enter the value -1.  The default response is 1 (ascending).
• [by_col] – is used when your data is not in a traditional table format, where columns are the categories (fields) and the rows are transactions (records). This would be used when your data is set up in the opposite format; rows are categories and columns are transactions.  This is an option that you will likely never use or use infrequently at best.  To invoke this argument, enter the value TRUE.  The default response is FALSE.

If you wish to sort by more than one criterion (i.e., primary ascending sort by Name, secondary descending sort by Salary), array notation will be required to define the multiple “sort_index” and “sort_order” levels.  Example: =SORT(DataSet, {1,2}, {1,-1}).

Take for example this dataset with names, dates, and salaries.

This table has been turned into a proper Excel Table and given the name “TableSalary”.

Our objective is to sort the list in descending order by the Yearly Salary column.  This can be done using the SORT function.

Our formula for solving this issue would be as follows:

=SORT(TableSalary, 3, -1)

In English, the logic reads, “Sort the table named ‘TableSalary’ by the third column (3) in descending order (-1).”

Using the SORTBY Function

If you need to sort data by a column that will not appear in the result, you can use the SORTBY function.

To understand the SORTBY function, the syntax is as follows (parameters in brackets are optional):

SORTBY(array, by_array1, [sort_order1]…)

• array – is the range or array you want to be sorted. This can be the entire data set or part of the original data set.
• by_array1 – is the array of items you wish to sort. This column will not be part of your result.
• [sort_order1] – defines the sort order for the column selected in the [by_array1] For an ascending sort, enter the value 1.  For a descending sort, enter the value -1.  The default response is 1 (ascending).

The SORTBY function can accept multiple “by_array” and “sort_order” declarations to allow for multi‑level sort conditions (i.e. sort by country, then sort by region, then sort by state.)

Using the same dataset as before, if we wish to generate a list of names that are sorted in ascending order, but we don’t want any of the adjacent columns of data, we can write the following formula.

=SORTBY(TableSalary[Name], TableSalary[Yearly Salary], -1)

Complete Course: Excel Functions in 365

Confused about the NEW Excel Functions? Spill Errors and What Dynamic Arrays mean?

In my EXCEL DYNAMIC ARRAYS COURSE we cover it all!

You’ll master these functions through multiple exercises and challenges so you can use the FILTER Function instead of a complex combination of INDEX & MATCH.

Or use XLOOKUP instead of VLOOKUP.

Not only will these save you from headaches but also from wasting time doing things the long way.

Get access to the Course HERE.

#2 The UNIQUE Function

The UNIQUE function is useful in the following situations:

• 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.

Although features exist in Excel that will provide such lists, like Pivot Tables, these features require manual intervention to update if the source data changes.  Because the UNIQUE function is dynamic the update occurs automatically.

To understand the UNIQUE function, the syntax is as follows (parameters in brackets are optional):

UNIQUE(array, [by_col], [occurs_once])

• array – is the part of the original data set that includes your results; the part of the data you wish to derive the list from.
• [by_col] – is the direction you are reading the list for comparison. To read the list across columns, choose TRUE.  To read the list across rows, choose FALSE.  The default selection is FALSE.
• [occurs_once] – If this is set to TRUE, the results list includes only items that occur once in the original data. This can be referred to as a “distinct” list.  If this is set to FALSE, the results list includes all items from the original data but only one of each item.  The default selection is FALSE.

Let’s look at some examples of the UNIQUE function in action.

We have the dataset below and we wish to generate a unique list of Regions.

We can write the following UNIQUE formula:

=UNIQUE(TableDivRev[Region])

But what if you wanted a unique list of Division/Region combinations?  The formula would appear like so:

=UNIQUE(TableDivRev[ [Division] : [Region] ] )

(NOTE: Spaces added to the formula to provide clarity; they are not used in the formula.)

Generating DISTINCT Lists

Suppose you only want to generate a list of Apps where the results are apps that only occur once in the dataset.

The following formula will use a FALSE for the [by_col] argument (since we are reading by rows) and use a TRUE for the [exactly_once] argument.

=UNIQUE(TableDivRev[App], FALSE, TRUE)

#3 – 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.

The logic for the XLOOKUP function is as follows:

=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})

Our objective in the following example is to select an App from the dropdown list located in cell F3.  From this selection, we want to return the associated Division from column H and the associated Profit from column C.

To get the Division, we write the following formula (in cell F4):

=XLOOKUP(F3, I4:I13, H4:H13, "Missing")

Did you see the hidden awesomeness?

Notice that in the data, the column that we are returning data from is to the LEFT of the column we are searching.

This would be impossible with a traditional VLOOKUP function (without performing some crazy in-memory, virtual table construction which only 9 people on planet Earth find enjoyable.)

We have also included an argument that issues a text response if the item being searched for is not found in the list.

To get the Profit, we write the following formula (in cell F5):

=XLOOKUP(F3, A4:A13, C4:C13)

#4 – 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):

FILTER(array, include, [if_empty])

• array – is the range or array you want to be included in the result. This can be the entire data set or part of the original data set.
• include – is the test you are performing on each record in the data set. This is the engine that moves the FILTER function forward.  Here is where you define the criteria for included versus excluded records.
  The logic will resemble the test portion of an IF function; we select a range of cells and compare each item in the range to a defined value (ex: B4:B15>E4, where column B is the data and cell E4 is what the data is being compared against.)  Any record that results in a TRUE statement will be included in the results.  Any record that results in a FALSE statement will be excluded from the results.
• [if_empty] – defines what is to be displayed if no records match the include test (ex: “No Data”). Although this argument is optional, if it is not defined, and no data is returned from the include test, a #CALC! error will be displayed.

If we wished to generate a list of employees (located in cells A4:A15) who earn more than the value defined in cell E4 (90,000), we can write the following formula.

=FILTER(A4:B15, B4:B15 > E4)

As a bonus, if we combine the FILTER function with the SORT function, we can have the results of the FILTER sorted in descending order by Salary.

=SORT(FILTER(A4:B15, B4:B15 > E4), 2, -1)

#5 – The 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):

SEQUENCE(rows, [columns], [start], [stop])

• rows – is the number of rows to return.
• [columns] – is the number of columns to return. (the default is 1)
• [start] – is the first number in the sequence. (the default is 1)
• [stop] – is the amount to increment each subsequent value in the array. (the default is 1)

A Simple Example

If we need to generate a list of whole numbers from 1 to 10, we can write the following formula:

=SEQUENCE(10)

If we wanted to make a two-column list of whole numbers ranging from 1 to 20, the formula would be as follows:

=SEQUENCE(10, 2)

If we wanted the same 10 by 2 list of values but we want to start with 2 and advance in increments of 3, the formula below will produce the desired results.

=SEQUENCE(10, 2, 2, 3)

A Practical Example

Using the UNIQUE function from earlier, suppose we are generating a dynamic, unique list of Apps using the following function (cell G3).

=UNIQUE(C3:C21)

What we would like is a list of item numbers to the left of the unique list of Apps that grows and shrinks as the list of Apps grows and shrinks.

We can place the following SEQUENCE formula in cell F3 to produce the desired results.

NOTE:  The use of the COUNTA function is to count the number of items in the spilled array starting in cell G3.  This will provide the rows argument with the needed value to know how far to list numbers.

=SEQUENCE(COUNTA(G3#) )

If the list of unique Apps were to change, the numbered list to the left will update to match in length.

Practice Workbook

Feel free to Download the Workbook HERE.