The DROP function is one of 14 functions added to Excel in the spring of 2022. It displays the contents of specified range but excludes the first or last X number of rows or columns.
In columns A:H in the screenshot below are the current standings in the English Premier League table. Using Power Query to connect to the BBC Sports webpage and setting the Query Refresh to “Refresh Data When Opening the File” means that it will always be up-to-date.
The DROP function in J12 (which being a dynamic array function spills to Q13) is used to display the playing records of the teams in 5th and 6th place in the league. The function in J12 is =DROP(A2:H7,4)
In other words…display the contents of A2:H7 but exclude the first 4 rows
When the source data (A2:H7) is updated, J12:Q13 is automatically updated so for example, if Brighton move up to 5th position, their “playing record” will move to A6:H6 and will also be displayed in J12:Q12.
In this video I provide several examples to show you how it works, including how to use it with unsorted data. Links to the video and individual sections below…
NOTE: If you don’t see the DROP function in your Excel, it’s only available to users on Microsoft 365 Beta Channel