Skip to content

Email Newsletter Challenge 24

This challenge will test your ability to organize data quickly, and you’ll do it with formulas.

If you’re up to the task? Download the spreadsheet challenge using the links below.


This week, your task is to use formulas to place each employee name under the correct department.


We will use the FILTER function to solve this problem. (For any Excel users, please note that the FILTER function is only available in the Office 365 version of Excel.)

Start by selecting cell E4, and enter the following formula.

=FILTER($B$4:$B$19, $C$4:$C$19=E3)

The FILTER function considers all of the employee names (B4:B19), then looks at the corresponding departments (C4:C19), and returns only those names that share a row with a department equal to “IT” (E3).

Also, we make the B4:B19 and C4:C19 references absolute references by including dollar signs. We do this to prevent these references from changing in the next step where we will copy the formula over to the right.

Speaking of which, the final step is to use the fill handle to copy the formula in cell E4 to cells F4 and G4.

Once you’ve done that, you are finished with this week’s spreadsheet challenge.

If you still have questions, just leave me a comment below. I’m here to help! 😊

I hope you had fun, felt challenged and learned something new. And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top