Love you Mike! Can’t believe the enthusiasm you have for Excel and teaching, it’s something very special ❤
@excelisfun7 ай бұрын
I am happy to make special fun Excel videos for you and the rest of the Team!!!!
@Excelambda7 ай бұрын
Great video!! ✌ If I will find time this week I will publish a study about my FRQ function, concept plus a tone of examples. - can deal with lower or upper limits (pivot or countifs functionality + frequency functionality in one) - can calculate frequencies of non numeric values (ignored by FREQUENCY) - results can have same framework of bins array (if bins array is 2D, FREQUENCY results are always a clm vector) - calculates for any bins array and/or sorted unique bins intervals that can be listed next to the frequencies.....and more ✌😊
@excelisfun7 ай бұрын
You are working for Microsoft now, right lol You, da man!!!!!! Excel is Lambda Fun!!!!
@Excelambda7 ай бұрын
@@excelisfun Not yet ! 😂
@vishal.pandey20017 ай бұрын
Mr ExcelisMagic Sir , You are the greatest excel lover ever known to me. Though the problem has been solved earlier , you always brings up alternative with definite explanation as to what, why and how Please never stop posting as it brings in me passion after always watching your videos
@excelisfun7 ай бұрын
I am very happy that you enjoy what I post. It has been 15 years 362 days so far that I have posted, and I plan to keep doing it forever, or as long as I can (which ever comes first) : ) : )
@williamarthur48017 ай бұрын
it may not gave been the main purpose of the video but I'd never thought of using find and replace on a pivot, what joy 😊😊
@excelisfun7 ай бұрын
Yes!!! Ctrl + H Rules in Excel and Word : )
@HusseinKorish7 ай бұрын
Wow ... i spent alot of time to use grouping in pivot table reports ... and my numbers included decimals ... and of course it didn't work correctly ...now i konw ... thanks Mike
@excelisfun7 ай бұрын
Yes, it is important to know this fact about Pivots : ) : )
@chrism90377 ай бұрын
Thanks Mike, another great video!!!!! I didn't know about the upper limit issue in pivot tables.
@excelisfun7 ай бұрын
The Microsoft programmers had to pick one side to include, and they chose the lower limit. For most projects it doesn't matter because the patter will still be revealed, but if you need to include upper limit... : )
@markpodesta46057 ай бұрын
Thank you Mike. Can you also create similar dynamic pivot tables by using GROUPBY and PIVOTBY?
@excelisfun7 ай бұрын
There is not an automatic groupby feature in those functions, but it would be cool if Microsoft added one : )
@viktorasgolubevas23867 ай бұрын
@@excelisfun a raw and not very tested version for thought and improvement could be =GROUPBY(CEILING.MATH(C6:C25 , 500) , C6:C25 , COUNT) and "opposite" option for include/exclude =GROUPBY(FLOOR.MATH(C6:C25 , 500) , C6:C25 , COUNT)
@roywilson95807 ай бұрын
Thanks Mike, another great video. I have noticed that Excel can do some very odd things to the unwarey so it is always a pretty good idea to double check upper bounds, lower bounds and any calculation that includes conditionals or many nested functions.
@excelisfun7 ай бұрын
I 100% agree. Very smart, Roy! I always do projects at least two ways to try assure that things are correct.
@roywilson95807 ай бұрын
@@excelisfun I do the same and have even been known to resort to pen, paper and brain-power when calcution results look off but is probably just me showing my age :D
@excelisfun7 ай бұрын
@@roywilson9580I am that way too: sometimes paper and pencil to check : )
@dhaval14897 ай бұрын
I think using =Counta(filter()) would also do the trick in every situation, but it is bit slow in performance
@excelisfun7 ай бұрын
You are right. COUNTIFS, SUMIFS and the like will almost always calculate faster than direct array operations.
@syrophenikan7 ай бұрын
Fantastic! I learn something new EVERY time!!!
@excelisfun7 ай бұрын
Awesome!!! You and I both: I learn something new each time too : )
@FutureCommentary17 ай бұрын
My takeaway is that I didn't know one could group categories in pivot table the way you did. And I didn't know the frequency function. I'm more likely to use it than countifs in similar cases.
@excelisfun7 ай бұрын
You inclination is good because COUNTIFS always gives the most flexibility with creating just the categories that you want.
@roncoderre7 ай бұрын
Hey, Mike..... Maybe something like this to enhance the FREQUENCY approach to skip the extra category?: =CHOOSEROWS(FREQUENCY(C6:C25,F15:F18),SEQUENCE(1,4))
@nguyentuan8267 ай бұрын
you can use =drop(FREQUENCY(C6:C25,F15:F18), -1) to remove extra category,
@excelisfun7 ай бұрын
Thanks, Ron!! Thank extra category always gives people trouble.
@excelisfun7 ай бұрын
@@nguyentuan826 Thanks Tuan Nguyen!! Microsoft is so good to us with DROD and TAKE : )
@peterjarabek55416 ай бұрын
THANKS. EXCELLENT VIDEO & EXPLANATION:)
@SyedMuzammilMahasanShahi7 ай бұрын
Thanks amazing Mike for this EXCELlent video.
@excelisfun7 ай бұрын
You are most welcome, Fellow EXCELlent Teacher : ) : ) : )
@kamal3r7 ай бұрын
Informative and new way for me
@excelisfun7 ай бұрын
Glad this helps : )
@gvitullib7 ай бұрын
Wao! that trick over the pivot table doing the labels thing, I didn't know it. Mike., kudos men!
@excelisfun7 ай бұрын
Ctrl + H is fun : )
@rajeshmajumdar49997 ай бұрын
Thank you so much!
@excelisfun7 ай бұрын
You are welcome so much!!!
@Chaingangsoldiernit7 ай бұрын
Super helpful. Thank you!
@excelisfun7 ай бұрын
You are welcome!!!
@prabhusundar30067 ай бұрын
Great video Mike, We can explore the same using sumproduct as well..
@excelisfun7 ай бұрын
You can use SUMPRODUCT, but as I researched and wrote about in my 2012 book, Ctrl + Shift + Enter: Mastering Excel Array Formulas, COUNTIFS will almost always calculate more quickly than SUMPRODUCT because COUNTIFS use underlying code whereas SUMPRODUCT has to make direct array calculations, row-by-row.
@excelisfun7 ай бұрын
Here is how you would do it with SUMPRODUCT: =SUMPRODUCT(--($C$6:$C$25>=E25),--($C$6:$C$25
@excelisfun7 ай бұрын
It took me about 20 minutes to get the new file uploaded, but it is there now.
@prabhusundar30067 ай бұрын
@@excelisfun thank you Mike..... I've read your cntr+ shift+enter book back in 2015 and have been following you since 2014....you have been my greatest inspiration and have learnt so much following you.... Thank you for everything... 😊
@excelisfun7 ай бұрын
@@prabhusundar3006 You are welcome for everything : )
@johnborg54197 ай бұрын
Thanks Mike.
@excelisfun7 ай бұрын
You are welcome, Formula Guy John!!!
@nadermounir82287 ай бұрын
Great video Mike 📹 👍. One question : i didnt quite get what u mean that the decimal numbers in pivot tables are not included ?
@excelisfun7 ай бұрын
Decimals numbers induce the Pivot to group this way. If you have integers, the categories are different: there is no duplicate numbers in the upper limit of one category and the lower limit of the next category. Categories go like this: 0-499, 500-999 and so on, rather than 0-500, 500-1000 and so on.
@excelisfun7 ай бұрын
I added an integer example to the download workbook file : )
@excelisfun7 ай бұрын
It took me about 20 minutes to get the new file uploaded, but it is there now.
@nadermounir82287 ай бұрын
@@excelisfun ah I see now that is very interesting I didn't know that before.
@nadermounir82287 ай бұрын
@@excelisfun thank u so much Mike for adding the interger example. This makes everything very clear 👌
@timestoryx7 ай бұрын
wow great :D
@excelisfun7 ай бұрын
Glad it is great for you !!!!
@tofikqureshi65077 ай бұрын
Hello Sir Pls help me I'm facing the one condition use Google sheet make pivot table need click pivot table showing data as same sheet not new sheet data visible same sheet pls❤help
@excelisfun7 ай бұрын
I am sorry, I do not know how to use Google Sheets : (
@Rahul-29147 ай бұрын
Need some corrections in countif formula because pivot table has different values
@excelisfun7 ай бұрын
That does happen often. The specific details always lead to a solution ; )
@joshpeters8137 ай бұрын
First😊
@excelisfun7 ай бұрын
First Place Trophy for you : ) : )
@excelmaster13286 ай бұрын
Sir what is the work in company for Excel. What work does company give for Excel jobs
@txreal27 ай бұрын
Changed 500.00 to 500, PivotTable still not working. So it's not just the decimal format or something else? Thanks.
@excelisfun7 ай бұрын
I do not know what you mean. What is not working? I just added an example of integer and decimal numbers to the download file on the 1849 (an) worksheet tab. You can check that out.
@excelisfun7 ай бұрын
If you tried Number Formatting, you might be missing a crucial fundamental concept in Excel: Number Formatting is a façade - Number Formatting never changes the actual number, it just displays it on the surface in a different way. Number Formatting is not seen by formulas and PivotTables - Pivots and Formulas always see and act on the underlying number.
@MohdSaif-oq3ow7 ай бұрын
How to download free Excel laptop plz share me link