All articles with 'FILTER()' Tag

Excel 365’s FILTER() function is great for getting a cut of data that meets your criteria. But what if you need to filter and then show non-adjacent columns? Something like below. In this article, let me show you a few options to get discrete columns after filtering with the FILTER function.

How about a fun Excel challenge? I have data in below format in the table named trips I want to know which employees visited both USA & UK? How would you solve this problem? Post your solutions in the comments. Need sample data & my solution? Click here to download the file. Want more challenges […]

Create a beautiful & fully interactive todo list with Excel using this tutorial (or download the free todo list template). Impress everyone.

9 box grid is a popular method for talent mapping. Download this free excel template for tracking and visualizing your employee performance & potential data.

In this article, I explain about powerful excel features like FILTER(), SORT() and CONCATENATEX() to create a similar 9 box talent map grid for your data.

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

Excel Dynamic Array Functions are a true game changer. These newly introduced DA functions can filter, sort, remove duplicates and do much more. The output of these functions can go to a range of cells. Hence the name – dynamic array functions.

When it comes to analyzing business data, managers are always asking, “so how many distinct x each y is doing?”

And that sends us, data analysts & reporting professionals running from pillar to post figuring out the best way to do it.

• We can use variations of SUMPRODUCT, COUNTIFS etc, but the methods are not flexible..
• We can use VBA, but it would become slow as you add more data.
• We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x.
• We might as well shave our head with a shovel before manually counting values.

And that brings us to 2 distinctly simple solutions:

• Using Power Pivot & Excel 2010
• Using regular pivot tables in Excel 2013

Today, lets talk about these 2 approaches & see why they are so better than anything else for distinct count situations.