This is your one stop shop on learning the new Excel formulas in Office 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

Want to Master the New Excel Formulas in Office 365?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch on YouTube and give it a thumbs up 👍

 

FILTER FORMULA

What does it do?

Filters a table array based on the filtering condition given

Formula breakdown:

=FILTER(array, include, [if_empty])

What it means:

=FILTER(data to be filtered, the filtering condition, [value to display if nothing gets matched])


Did you know that you can now filter your table data with an Excel Formula? Yes you can! It is definitely possible now with Excel’s FILTER Formula. It is a new formula introduced in Office 365 released in 2018!

We have a tax table that we want to dynamically filter with a given rate.

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the FILTER function in a blank cell:

=FILTER(

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

STEP 2: The FILTER arguments:

array

What is the data to be filtered?

Select the cells containing the tax data, do not include the headers:

=FILTER(C9:D14,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

include

What is your filtering condition?

We want to filter the tax rate that is greater than the specified rate. Type in the condition as the tax rate column > the specific tax rate.

=FILTER(C9:D14, D9:D14>G8

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

[if_empty]

What is the value to display in case nothing gets matched?

Just place an empty string to be displayed if nothing gets matched.

=FILTER(C9:D14, D9:D14>G8, “”)

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

Try it out now with different values and see it get filtered magically!

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

How to Use the FILTER Formula in Excel

 

RANDARRAY FORMULA

 

What does it do?

Creates an array of random numbers

Formula breakdown:

=RANDARRAY([rows], [columns])

What it means:

=RANDARRAY(number of rows, number of columns)


Did you know that you can now generate random numbers in an array? Yes you can! It is definitely possible now with Excel’s RANDARRAY Formula. It is a new formula introduced in Office 365 released in 2018!

It returns random values between 0 and 1 by default. I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the RANDARRAY function in a blank cell:

=RANDARRAY(

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

STEP 2: The RANDARRAY arguments:

[rows]

How many rows to fill random values with?

Let us go for 10 rows.

=RANDARRAY(10,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

[columns]

How many columns to fill random values with?

Let us go for 2 columns.

=RANDARRAY(10, 2)

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

Now we have our 10 x 2 area filled with random values between 0 and 1!

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

 

How to Use the RANDARRAY Formula in Excel

 

SEQUENCE FORMULA

 

What does it do?

Creates an array of sequential numbers

Formula breakdown:

=SEQUENCE(rows, [columns], [start], [step])

What it means:

=SEQUENCE(number of rows, [number of columns], [starting number], [increment per number])


Did you know that you can now generate a series of numbers with an Excel Formula? Yes you can! It is definitely possible now with Excel’s SEQUENCE Formula. It is a new formula introduced in Office 365 released in 2018!

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the SEQUENCE function in a blank cell:

=SEQUENCE(

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

STEP 2: The SEQUENCE arguments:

rows

How many rows to fill with values?

Let us go for 10 rows.

=SEQUENCE(10,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

[columns]

How many columns to fill with values?

Let us go for 3 columns.

=SEQUENCE(10, 3,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

[start]

Which number do you want the sequence of numbers to start?

Let us have it start at the number 10.

=SEQUENCE(10, 3, 10,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

[step]

Which increment for each number in the sequence?

Let us increment it by 100. So that numbers should look like: 10, 110, 210, 310, and so on…

=SEQUENCE(10, 3, 10, 100)

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

Try it out now and see that your sequence of numbers is generated magically!

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

How to Use the SEQUENCE Formula in Excel

 

SORT FORMULA

 

What does it do?

Sorts a table based on a column and order specified

Formula breakdown:

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

What it means:

=SORT(data to be sorted, [which column to be used for sorting], [ascending or descending order])


Did you know that you can now sort your table data with an Excel Formula? Yes you can! It is definitely possible now with Excel’s SORT Formula. It is a new formula introduced in Office 365 released in 2018!

We have a tax table that we want to sort by the tax rate in a descending order.

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the SORT function in a blank cell:

=SORT(

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

STEP 2: The SORT arguments:

array

What is the data to be sorted?

Select the cells containing the tax data, do not include the headers:

=SORT(C9:D14,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

[sort_index]

What is the column to be used for sorting?

We specify the column number here. Since the tax rate column is the second column, place in 2.

=SORT(C9:D14, 2,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

[sort_order]

What is the sort order? 1 for Ascending, -1 for Descending order.

Since we want descending order, place in -1.

=SORT(C9:D14, 2, -1)

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

Now it gets sorted magically!

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

How to Use the SORT Formula in Excel

 

SORTBY FORMULA

 

What does it do?

Sorts a table based on the column(s) specified

Formula breakdown:

=SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2], …)

What it means:

=SORTBY(data to be sorted, by which column to sort first, [by which column to sort afterwards], …)


Did you know that you can now sort your table data with an Excel Formula? Yes you can! It is definitely possible now with Excel’s SORTBY Formula. It also allows you to sort by multiple columns as well. It is a new formula introduced in Office 365 released in 2018!

We have a person list that we want to sort by Gender (ascending order) and then by Age (ascending order).

Do take note that in specifying the sorting order, 1 represents ascending order, -1 represents descending order.

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the SORTBY function in a blank cell:

=SORTBY(

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

STEP 2: The SORTBY arguments:

array

What is the data to be sorted?

Select the cells containing the person data, do not include the headers:

=SORTBY(B9:D14,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

by_array1, sort_order1

Which column will be used to sort first?

Select the cells containing the gender column, then type in 1 for it to be ascending order.

=SORTBY(B9:D14, B9:B14, 1,

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

by_array2, sort_order2

Which column will be used to sort next?

Select the cells containing the age column, then type in 1 for it to be ascending order.

=SORTBY(B9:D14, B9:B14, 1, D9:D14, 1)

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

Now it gets sorted magically!

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

How to Use the SORTBY Formula in Excel

 

UNIQUE FORMULA

 

What does it do?

Gets the unique values of a list

Formula breakdown:

=UNIQUE(array)

What it means:

=UNIQUE(data to have duplicates removed)


Want to remove duplicate values from your list? It is definitely possible now with Excel’s UNIQUE Formula. It is a new formula introduced in Office 365 released in 2018!

We have a list of names and we want to remove the duplicates from it. The UNIQUE Formula will make this very quick to do!

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the UNIQUE function in a blank cell:

=UNIQUE(

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

STEP 2: The UNIQUE arguments:

array

What is the data to be cleared of duplicate values?

Select the cells containing the names, do not include the headers:

=UNIQUE(C9:C14)

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

Now the duplicate names are all gone!

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

 

How to Use the UNIQUE Formula in Excel

 

HELPFUL RESOURCE:

 

New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

3

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin