This solution uses the TAKE, SORT and FILTER functions which are only available in Excel 365.
Imagine that you work for a company that sells property. Here’s a list of property sales in the London area for November:
The list, which is in a Table called Sales1, includes the date of the sale, the address of the property, the value of the sale and the Sales Rep (the person in the team responsible for managing the sale). The properties are listed in the Table in random order.
The sales manager wants a couple of reports creating. One that displays the top 10 most expensive properties sold that month and one that displays the top 5 most expensive properties sold broken down by sales rep.
In this video I demonstrate how to create these reports using 3 functions: TAKE, SORT and FILTER.
Report 1: Top 10 Most Expensive Properties
For the first report I used a combination of TAKE and SORT: =TAKE(SORT(Sales1,3,-1),10)
So what is the formula doing? It is sorting the Table in descending order by sales value and then retrieving the first 10 rows from said Table.
The SORT function sorts the Table based on the values in the 3rd column (Sale Value) in descending order (that is what the -1 means)
The TAKE function literally takes the top X number of rows, 10 in this case, from the sorted version of the Table.
Report 2: Top 5 Most Expensive Properties Per Sales Rep
For this report, where the data is stored in a Table called Sales3, I used this formula:=TAKE(SORT(FILTER(Sales3,Sales3[Sales Rep]=G1),3,-1),5)
This applies a filter to the Table, retrieving only the records where the Sales Rep matches the value in G1 (G1 contains the name of a Sales Rep). It then sorts those records into descending order based on the Sale Value of the property and displays the top 5 records for that Sales Rep
If you want to see it in action, check out the video below. The video also includes a couple of bonus tips…
- The difference between TAKE/SORT and SORT/TAKE (you get different results depending on which order you enter the functions)
- Using CHOOSECOLS to specify only certain columns in the report
Download a copy of the file(s) used in this video: https://share.zight.com/Z4uExlXn
The formulas that I used in the video:
=TAKE(Sales1,10)
=TAKE(SORT(Sales1,3,-1),10)
=SORT(TAKE(Sales1,10),3,-1)
=TAKE(CHOOSECOLS(SORT(Sales2,3,-1),2,3,4),10)
=TAKE(SORT(FILTER(Sales3,Sales3[Sales Rep]=G1),3,-1),5)