How to extract sorted rows from a table in Excel

Excel tables allow us to filter and sort data, hiding the surplus and putting it in ascending or descending order.

However, clearing the filter and sort state means the table reverts back to its original condition, so you may desire a more permanent solution. Formulas give us the flexibility to decide what, where, and how the data is outputted.

In this example, we have a customer table and plan on rewarding the top five longest-serving customers with an email gift.

To contact them, we need to obtain their details but only want to return the relevant data without any unnecessary extras.

Using CHOOSECOLS, we specify the column numbers corresponding to the columns we want returned: Name, Email, and Registration Date.

=𝙲𝙷𝙾𝙾𝚂𝙴𝙲𝙾𝙻𝚂(𝚝𝚋𝚕𝙲𝚞𝚜𝚝𝚘𝚖𝚎𝚛𝚜,𝟸,𝟹,𝟾)

We then wrap SORT around this statement and put 3 as the [𝚜𝚘𝚛𝚝_𝚒𝚗𝚍𝚎𝚡] to place the rows in ascending order according to their registration dates.

=𝚂𝙾𝚁𝚃(
𝙲𝙷𝙾𝙾𝚂𝙴𝙲𝙾𝙻𝚂(𝚝𝚋𝚕𝙲𝚞𝚜𝚝𝚘𝚖𝚎𝚛𝚜,𝟸,𝟹,𝟾),
𝟹)

The final stage is to use TAKE to extract only the first five rows of the sorted array.

=𝚃𝙰𝙺𝙴(
𝚂𝙾𝚁𝚃(
𝙲𝙷𝙾𝙾𝚂𝙴𝙲𝙾𝙻𝚂(𝚝𝚋𝚕𝙲𝚞𝚜𝚝𝚘𝚖𝚎𝚛𝚜,𝟸,𝟹,𝟾),
𝟹),
𝟻)

#exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit

---

The Global Excel Summit is the world's largest virtual gathering of Microsoft Excel users and experts.

Find us on: