Let us start the week with a simple formula (well, to be fair, let us start the week with a strong cup of coffee, then this formula).
Often when we have large data sets, we apply data filters to select and display only information we want to see.
Some of you know that whenever we apply filters on a dataset, we can look at status bar area to find out if any filter is applied on the current worksheet. It looks like this when filters are applied:
But, what if you need a way to show “filtering” status thru formulas?
Well, you can do that very easily by using two simple formulas called as COUNTA() and SUBTOTAL().
Here is how it works:
And the formula is, (assuming we want to test the range B4:B3000)
=IF(COUNTA(B4:B3000)>SUBTOTAL(3,B4:B3000), "list filtered ["&SUBTOTAL(3,B4:B3000)&" items]","Not filtered")
How does the formula work?
- We are asking excel to findout how many values are there in the range B4:B3000 using COUNTA() formula.
- Then we are asking excel to tell how many values are there after filter in the same range using SUBTOTAL() formula [introduction to excel SUBTOTAL formula].
- Now, if the first value is more than second, we knew that the list is filtered. So we are showing a message like,
- “List Filtered [x items]”.
What would you use this type of formula for?
I have used this type of formula in charts, dashboards where I need to tell whether source data is filtered or complete. What about you? Where would you use this type of formula?
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.