Time for a quick but very handy Excel FORMULA trick.
You have two tables. ORDERS & PRODUCTS. You want to see all the orders if the product name is mentioned in the other table. Thanks to the new FILTER() function in Excel, we can do this easily now.
The data setup
For the purpose of this example, assume you have a table named orders and another table named products.
The formula to filter one table by another list
Here is the formula to fetch all orders for the products listed in the other table.
=FILTER(orders, COUNTIFS(products, orders[Product]))How does this formula work?
This is a two-part puzzle.
PART 1 – COUNTIFS with Arrays:
Let’s look at the COUNTIFS portion first:
COUNTIFS(products, orders[Product])This formula returns an array of 1s & 0s
- It will be 1 whenever the orders[Product] matches one of the products table items.
- Else it will be 0
- The size of this array will be same as orders table. So if orders table has 100 rows, then you will get 100 ones or zeros here.
PART 2 – FILTER
The NEW FILTER() function in Excel can take a list or table and filter it based on a criteria.
So, for example, =FILTER(orders, orders[Product]=”Milk Bars”) would filter all rows where the product names is Milk Bars.
The trick is to use COUNTIFS output (which will be a bunch of 1s & 0s) as the criteria for FILTER. When Excel sees these 1s&0s, it’s going to include the rows with 1 & exclude the rows with 0.
Thus, producing the relevant orders for us.
Here is a quick demo of the formula in action:
Checking the reverse – Filter list when values are not in the other list
We can use the same approach to check for reverse too. This is helpful in finding which customers did not purchase in a month or which products did not sell in a given country etc. Here is an example formula for that:
=FILTER(orders, COUNTIFS(products, orders[Product])=0)Problems with this approach:
While this is a great technique, if you have large amounts of data then the formula could be slow. In such cases, I recommend using Power Query to handle the job. We can use inner join in Power Query merge operation to filter one table by merging it with another table.
For more on Power Query, please read this article.
Example workbook
If you need a sample file to practice this formula, click here and download it.
How do you deal with this problem?
I have been using the FILTER(… COUNTIFS()) approach for quick scenarios and Power Query Merge option for more complicated situations. What about you? How are you solving this problem? Please share your thoughts or questions in the comments section.
21 Responses to “Make a Pivot Table in Excel [15 Second Tutorial]”
[...] First step is to create Pivot Table. Here is a video tutorial on making pivot tables. [...]
Dear Sir,
I would like to study pivot table from basics detailed.Pls send me the tips of that
merccccccccccccccccccccccccccccccccccccc
[...] Click here to see a video tutorial of making pivot tables in excel | One more Pivot Table Video Tutorial [...]
it is very useful
hello sir
please send me some information of pivotable. and how to use it
because I am beginer so how can i learn it the pivotable.
and sir will you send me pivotable data because i have no data and i don't
know how to applyed for the pivotable. give some videos so I learn quikely.
thank you
and my best
anticipation
Hi,
This tutorail was helpful. If possible can you please email me some data, so I can create another pivot table? I am trying to learn ASAP and need some info.
Thank you
@Keri
Have a look at FakeNameGenerator.com
Top of the day to you. can you pls send me a detailed tutorial on the pivot table tutorial? Thanks.
Hi,
please tell me how to use Pivot table.
@Karthick, Yemi
Have a read of: http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
Dear Sir,
I visited this website of urs for the very first time and trust me this is fabulous.i learnt pivot table through this.Thanks a lot.In corporate world nobody gonna make u learn these things.m very grateful to u.This is a great initiative from ur side.Commendable job done.Hats off to u.For beginners also this is very helpful.The way this website makes u learn excel is very good.
Pooja
Can you please email me information to put into a pivot table and possibly a complete set of instructions? I am a beginner to pivot tables and am possibly interested in obtaining a position that requires pivot tables. thank you.
[...] Click here to see a video tutorial of making pivot tables in excel | One more Pivot Table Video Tutorial [...]
It's simply great..!!
Cool!
Fast but not furious.
Pls email me details of pivot table. I want to learn it asap because I just got a job that requires this. Thanks
it is very useful..............thanks
Sir please can u send me the complete explanation of pivot tables from basics
hello,
I wanted to know how to put pivot with emp id, emp name, emp position in one row.
Ex:-
cel A4 should have Emp id
Cell B4 should have Emp Name
Call C4 should have Emp Position
many thanks in advance
Nice information, thanx my dear.