Hi, this is Wayne again with a topic “The Excel TAKE and DROP Functions”.
In this Excel video, I will teach you how to use the Excel, take function and the Excel drop function. Let’S get started so here we have a spreadsheet, it’s a list of employees of a small business and I would like to use the take and drop functions to produce a list of employees that meet certain criteria. For example, let’s say I want to Spotlight the employees that have worked extra long hours, so I’m going to click anywhere in column e. How about here and then I’ll go to the Home tab here in the editing group, there’s sort and filter and I’m going to sort from largest to smallest, so it looks like Luise and Alvaro Phillip, Etc have worked the most recently. I could easily use a formula to pull out a list of these hardest working employees to do this, I’m just going to click somewhere else in the the spreadsheet, how about H1 and I’ll type equals take, and then I’ll put in my left parenthesis Excel is now Expecting an array, comma and then rows you can also put in columns but we’ll get to that. So first, what’s my array of data? Well, I’m just going to click and drag from A1 all the way through to f71. Now, if this data was huge, let’s say there were 20 different columns and a th000 different rows.
Obviously I wouldn’t click and drag on that array of data. Instead, I would just type the cell references. Okay, now that the array is specified, I can put in a comma and then decide how many rows to include so.
Let’S say I want the top 10 hardest working employees. I do have to keep in mind that row number one has my column, titles or headings in it. So if I want the top 10, maybe I should say 11 rows. I could put in my right parenthesis: tap enter and the take function has taken the top 10 hardest working employees and put them over here on this part of the spreadsheet. Now let’s say we decide that we don’t really need to know the total income. It’S more about the hours worked in that case, I only want five columns, not all six.
So let’s do the same formula again, but with one more element in the formula so equals take left parenthesis. I need the array this time I’ll just type it in A1. Through f71 and of course, the word through is denoted with a colon, so A1 colon f71 comma.
The number of rows is 11, remember, comma and then how many colums I just want five columns, not six right parenthesis tap enter and I get the same list of people, but with only five columns. Let’S do another example this time, instead of the top 10 hardest working employees, let’s go with the bottom 10. Maybe these individuals are sick or they’ve been on vacation or whatever it might be. But let’s see who the bottom 10 employees are for number of hours recently worked, so I’ll type equals take left parenthesis it’s that same array, A1 through f71, comma, and I want 10 rows, but I want them to be the bottom 10. So I’m going to put in minus 10 right parenthesis tap enter, and these are the bottom 10 based on ours worked okay, so we’ve been looking at the Excel. Take function.
Let’S now look at the Excel drop function, so I’m going to click here, I’ll type equals drop, left parenthesis Excel, shows me what it’s expecting and it should look pretty familiar array rows and maybe columns first, my array: A1 through f71, 1 comma. Now the number I put in for rows that number of rows will be dropped from the list of data and everything else will be displayed here. So this is a way I could show maybe the rest of the employees, the ones who aren’t in the top 10. So, let’s drop the first 11 rows, write parenthesis, tap enter and it worked. The first result is Courtney, who is the 11th employee in that list? Now I’m I’m going to click on cell h37 in this case and go up to my formula just to give you one last example: let’s say I want to drop also one of the columns I could put in comma One Tap enter and it dropped. My first column, the one with the employee ID number, so in this article we’ve looked at both the Excel take function and the Excel drop function.
I hope you can see some ways that this could be useful as a way to quickly grab data out of a larger data set, put it somewhere else to Spotlight it or to otherwise draw attention to it, and I hope you found this video to be helpful. If you have please like, follow And subscribe, and when you do click the bell and you’ll be notified when I post another video, if you’d like to support my channel the best way to do that is to join me as a channel member, you could also click The thanks button below the video you could support me through my patreon account, or by buying Channel merch and you’ll, see information about those options in the description below the video speaking of supporting the channel, I need to say thank you to my awesome. Super techie Channel supporters. Thank you so much for your support. I really appreciate you .