Mastering the Magic of =REPT and =FILTER for Dynamic Data Solutions!

Hey,

Through our biweekly articles, our mission is to bring you the latest and greatest in Excel functions, features, tips, and tricks.

Whether you have just started your Excel journey or are an expert, we aim to provide you with valuable insights that will enhance your Excel skills and productivity.

Want to receive regular Newsletters over e-mail? - Subscribe here.?

Never Manually Input Cells Again With This Function

Picture yourself taking care of the inventory at your neighborhood ice cream parlor.

The guiding principle is straightforward: whenever a flavor's stock dips below two gallons, it's time to order more.

Now, the interesting part...how do you go about managing the inventory? Should you opt for manually entering data into cells?

Not around here, you shouldn't! Let me show you a way more efficient way to handle inventory.

Step 1: In your desired cell, use the formula =REPT("Message", Logical Test)

For us, our formula would be =REPT("Reorder", B2<$G$1)

Step 2: Copy for remaining cells. That's it!

And if the condition ever changes—say you want to start reordering when you hit three gallons instead of two—you can easily adjust your formula.

Want to receive regular Newsletters over e-mail? - Subscribe here.

How to Create a List of Duplicate Values in Excel

Ever felt like your spreadsheet is playing tricks on you?

Imagine this: You’re at your desk, sipping your third cup of coffee, and suddenly you realize you’ve been staring at the same names in two different lists. Déjà vu, right?

If you scroll down, you’ll notice how Group B has values already listed in Group A.

Sure, you could manually comb through your spreadsheet to find duplicates. But that method is prone to error (and would sting your eyeballs).

So? Let me show you a shortcut that creates a list of your duplicate values in just 30 seconds.

Step 1: Type =FILTER(LIST 1, COUNTIF(LIST 2, LIST 1))

Our formula here would be =FILTER(A2:A18, COUNTIF(B2:B18, A2:A18))

Step 2: Press Enter and you’re done!

Now you’ll find duplicates in just 30 seconds.

Want to receive regular Newsletters over e-mail? - Subscribe here.

?Thanks for being here!

?A quote that I love from entrepreneur Steph Smith: "How to be great? Just be good, repeatably."

Being "great" is the result of doing small things—finishing one Excel module, posting one Instagram, doing one gym session, etc.—over a long period of time

What's one thing you can do today to be great?

Stay Exceling,

Isha