Pinterest Pixel

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

Bryan
This is your one stop shop on learning the new Excel formulas in Office 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE! We have detailed examples for each formula below!

This is your one stop shop on learning the new Excel formulas in Office 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE! We have detailed examples for each formula below!

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 👍

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

 

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:

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

Download excel workbookFILTER-FORMULA.xlsx

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

=FILTER(

Excel FILTER Formula

 

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,

Excel FILTER Formula

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

Excel FILTER Formula

[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, “”)

Excel FILTER Formula

 

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

Excel FILTER Formula

Excel FILTER Formula

 

 

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:

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

Download excel workbookRANDARRAY.xlsx

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

=RANDARRAY(

RANDARRAY Formula in Excel

 

STEP 2: The RANDARRAY arguments:

[rows]

How many rows to fill random values with?

Let us go for 10 rows.

=RANDARRAY(10,

RANDARRAY Formula in Excel

[columns]

How many columns to fill random values with?

Let us go for 2 columns.

=RANDARRAY(10, 2)

RANDARRAY Formula in Excel

 

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

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 workbookSEQUENCE.xlsx

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

=SEQUENCE(

SEQUENCE Formula in Excel

STEP 2: The SEQUENCE arguments:

rows

How many rows to fill with values?

Let us go for 10 rows.

=SEQUENCE(10,

SEQUENCE Formula in Excel

[columns]

How many columns to fill with values?

Let us go for 3 columns.

=SEQUENCE(10, 3,

SEQUENCE Formula in Excel

[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,

SEQUENCE Formula in Excel

[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)

SEQUENCE Formula in Excel

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

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 workbookSORT-FORMULA.xlsx

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

=SORT(

SORT Formula in Excel

 

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,

SORT Formula in Excel

[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,

SORT Formula in Excel

[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)

SORT Formula in Excel

 

Now it gets sorted magically!

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 workbookSORTBY-FORMULA.xlsx

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

=SORTBY(

SORTBY Formula in Excel

 

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,

SORTBY Formula in Excel

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,

SORTBY Formula in Excel

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)

SORTBY Formula in Excel

Now it gets sorted magically!

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 workbookUNIQUE-FORMULA.xlsx

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

=UNIQUE(

UNIQUE Formula in Excel

 

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)

UNIQUE Formula in Excel

 

Now the duplicate names are all gone!

UNIQUE Formula in Excel

 

 

 

 

If you like this Excel tip, please share it



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


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Filter a Pivot Table by Dates

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...