Excel Formulas & Functions, PivotTables, Slicers & Charts - 365 MECS 02
Vložit
- čas přidán 30. 06. 2024
- Download Excel File: excelisfun.net/files/02-M365E... Download PDF File: excelisfun.net/files/02-M365E...
Comprehensive introduction to what Excel can do with Worksheet Formulas & Functions, PivotTables, Slicers, Charts and Excel Tables. This is M365 Excel Video 2.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: • Microsoft 365 Excel & ...
Topics in video:
1. (00:00) Introduction
2. (00:56) Calculations in Excel
3. (02:25) Worksheet formulas definition and elements
4. (03:00) Example of Number Formula
5. (05:07) ROUND function
6. (09:18) How Number Formatting Interacts with Numbers and Formulas
7. (13:22) Example of Text Formula
8. (14:01) TEXTBEFORE function
9. (18:02) FORMULATEXT function
10. (19:02) IFNA function
11. (19:37) Show Nothing with a Formula using Zero Length Text String “”
12. (20:44) Example of Logical Formula using Comparative Operators
13. (22:46) Don’t Get Tricked By Number Formatting
14. (24:54) Alt Keyboard Shortcuts
15. (25:56) Excel’s Golden Rule = Most Important Formula Tip
16. (27:53) Excel Models
17. (29:26) Percentage Number Format
18. (32:09) Style Formatting
19. (33:14) “Old School” Formula that Requires a Relative Cell Reference and a Manual Copy action.
20. (34:23) Angry Rabbit Copy Cursor
21. (35:37) “New School” Formula that Spills Results
22. (36:53) Dynamic Spilled Array Formula
23. (38:56) Beauty of Excel. Bricklin and Frankston and VisiCalc.
24. (39:39) Summary of Worksheet Formulas and Excel Models
25. (40:02) Define Data Analysis
26. (42:31) Define Data, Table, Field, Record
27. (45:07) Tools for Data Analysis
28. (45:40) Data Analysis Goals for Video
29. (45:52) Excel Table feature
30. (48:00) Define PivotTable
31. (49:24) PivotTable feature
32. (51:40) Grouping Dates to create Month Report
33. (52:02) PivotTable Cache
34. (53:12) Change Report Layout, Manually and Default Settings
35. (54:36) Calculations in Values Area of PivotTable
36. (56:36) Number Formatting for PivotTable
37. (59:13) Conditional Formatting in PivotTable
38. (01:01:13) Add New Data to Excel Table and Refresh PivotTable
39. (01:02:56) Default Settings For Expanding Excel Tables
40. (01:03:27) Excel Line Chart to Show Sales Trends from a PivotTable
41. (01:04:55) Formatting Chart
42. (01:05:25) Rules for Visualizing with Charts
43. (01:09:56) Add Slicer To PivotTable and Chart to visualize Sales Rep Sales Trends
44. (01:12:07) Excel Data Analysis Tools Summary
45. (01:12:44) Summary of Video
46. (01:13:13) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/... . Artist: audionautix.com/
#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#freeclass
#freecourse
#formulas
#pivottables
#slicers
#charts
#M365ExcelVideo2
#MECS2
#EasyPivotTable
#PivotTableReports
#ChartinginExcel
#WorksheetFormulas
#Formulasmadeasy
#LearnExcelFormulas
#FormulasandFunctions
#BuildingExcelModels
#Excel’sGoldenRule
#conditionalformatting
#TypesofFormulas
#NumberFormattingandFormulas
#BeautyofExcel
#freeclass
#FreePowerBIClass
#excelisfun
#excelformulas
#excelfunctions
#visualization
#excelbasics
#excelformulaforjobinterview
#excelforbeginners
#excelforbeginner
#freeexcelcourse
#excelisfun
A lot better than excel course in coursera. Thank you Mister.
You are welcome for the better!! And this is only the first of many free videos in this class : )
I consider myself an Excel expert and know most of what was covered in the video, but I still learned some new things and appreciated getting some groundwork laid for concepts and terminology that Mike uses in other videos (e.g. crosshair = angry rabbit). I think even "experts" will learn by watching these videos and I look forward to what's ahead. I'm also impressed that Mike knows what's coming up in future videos.
That is so funny about knowing what's coming up, because although I do plan the whole class out before I begin, with each new video, the class takes a new twist. I am never sure what exactly will be in the next video. As I make each new video, the class evolves and helps me figure out the next video. Although I do know that the next two videos will be about: Calculations and Data Analysis! See you next video!
Yeah, that’s crazy, right? As if he wrote the book on the whole thing, or something…
Oh, wait: he DID write the book on all of this! :-) LOL!
@@GeertDelmulle Right!?!?! But this class is in a different order, like the Excel Basics and Advance classes LOL
Mike sir in India today (13th July 2022) it is called “Guru Purnima “ where we thank all our teachers for imparting knowledge and providing us guidance. On this occasion Mike sir I would like to wish you on the occasion of Guru Purnima for teaching me whatever excel I know today and for all appreciation I receive from my colleagues for my excel knowledge!!
Thank you Mike sir
You are welcome on on the occasion of Guru Purnima !!!! : ) : )
With this eloquence nobody can escape from becoming passionate about Excel.✌😉
Thank you for your kindness, Teammate Excel Lambda : ) : )
Exceλambda this is why Mike aka @@excelisfun is simply AMAZING! His eloquence = infectious
I REALLY DO NOT KNOW HOW TO THANK YOU.
WORDS ARE NOT ENOUGH FOR THIS GREAT JOB.
"I want to thank you for explaining the topics to me in the best possible way and assisting me in managing my time effectively. I am from Pakistan, and I appreciate your guidance and support."
You are welcome!
Amazing how many new things I have learnt despite being a seasoned Excel user. Thanks Mike.
Next video will be even better : ) It was supposed to already be out, but I have been sick...
@@excelisfun Get well soon Mike. Praying for you.
Hi sir, iam from India . Every day watching your videos I lot of learn excel. Thank you so much sir.
You are welcome!!!
Thank you Mike for the great video. I enjoyed watching it this morning while drinking my coffee. I appreciated reviewing the Excel's Golden Rule and creating worksheet models. Problems, calculations are soo easy to understand when they are formatted that way. I share these tips with people at work, even my boys when they are doing their story math problems; it really helps.
I love this: "doing their story math"!!!!! Story math with Excel is very fun, indeed. I am so happy to hear that you get to do so well at work and coach others to do the same, N Sanch01!!! Go Team!!!!
Thanks so much for these amazing videos! Very clear and straight to the point. Thank you
You are welcome!!!!
Hi Mike: Great stuff as usual! I'm enjoying "re-learning" some stuff I've taken for granted. I must admit I've already learned (or been reminded) of AT LEAST one awesome tip in each of the last two videos.
Also, at 9:04, I thought you were perhaps exaggerating about the time saved, but I did a bit of math:
If you edit just 30 cells per working day (not unreasonable if you're in Excel all day) and save one second by not having to click to get back to the formula you just entered, that ACTUALLY adds up to more than 2 hours per year, or a whole day over the course of 4 years!! (Assuming just 250 working days per year).
That alone warrants using Ctrl-Enter, but if you combine that with a mindset of always looking for additional time savings, they really can add up to days' worth of extra time!! :D
I love that you did the math : ) : ) : ) Yes, Ctrl + Enter is just one of many things we can do to speed things up. Just think of Ctrl + Shift + Arrow - that saves much more time. The problem with me is that any time I save for vaction just gets used up making more Excel videos lol
Mike 様。御貴重な情報やknow-howをcommunityへ貢献することには、本当に感謝します。
: )
Mindblowing how detailed and useful this video and the series is. Thanks for the great work.
You are welcome, Safa!!!!
So excited for this series, Mike - Thanks so much for everything! :)
You are welcome for everything, Gaya A!!!! This class will be fun : )
I just posted two practice problems in the download workbook. This is the REAL fun: you get to try and apply the skills you just studied in the video. The fun problems are at the end of the workbook. I posted answers too so you can check your work : ) Download the Excel file to have this fun!
Thanks Mike. It's nice to go back to the roots every once in a while!! :) :)
Yes, even for me, it is fun to re-tell the story of Excel each time, each time I try to do it better : )
since long time I follow your channel. thanks for every think
You are welcome, for everything ihab!!!
Thank you very much
You are welcome very much, Adel!!!!
Though I started journey with this platform two years ago but I always had desire to start from fresh and I am so lucky to now pave along from zero point. Thanks you so much for introducing MECS series. 🙌
You are welcome. Over the last 20 years, I have created over 50 different video class series or classes that start at zero. Each time I do, the story gets better. The story in this one should be the best yet. You are welcome for MECS : ) : )
Holy smokes! I can’t read your book as fast as you can make videos about all of its contents!
You’re a machine! :-)
BTW, I see what you did there with the title: you’re building up to the punchline “Excel to the MECS”! ;-)
Sorry about that! The book was written during the pandemic. Now I am filming a class at Highline. There is overlap, though. It should be fun, like a machine ; )
Thanks a lot for the very hands-on learning experience. I could not thank you enough. I really enjoyed all of your videos. It helped me so much in my daily work.
Boom!Wow Picked Up Some Golden Nuggets"Tips" Awesome Fun Class...Thank You Mike :)
What were the nuggets? Inquiring minds want to know : ) Especially since you are advanced and talented with Excel, Bike Brother!
AWESOME, cant wait for the comming episodes, especially Data analysis 🙂👍
Glad you like these!!! More to come.
Amazing content as always! Thank you for teaching M 365 new formulas, I am sure they will come out very handy. Thanks a lot :)
You are welcome a lot!!!
The Return of the Angry Rabbit 🐰: in its sack: the very best of ExcelsFun and a lot more. Special thanks for the Excel Models: problems like these help a lot with learning: I always take a new empty worksheet when I practice or do the HWs. If I can think them over, and solve the problems there, than I know I understand how things work, and I was not just memorising things.
BAM: This is THE #1 to learning: " take a new empty worksheet when I practice or do the HWs" (solve problems yourself and apply concepts is the best glue to stick new concepts in your own head). Amazing work, Z T! People who want a template, or search the internet and find an answer and use it blindly, and who just want solutions and no whys, they can never know how little they know. And the world is filled with mostly people like that. CZcams is mostly filled with Excel videos that promise that the impossible is easy. This is one reason my videos get so many fewer views than many others. This is why a smart person like you who is striving to become the best, watches videos at excelisfun and says smart things like this: " take a new empty worksheet when I practice or do the HWs" : ) : ) : ) LOVE that you are becoming the best, Z T!!!!!
@@excelisfun Thank you for your feedback. I call it "the blank sheet test": nothing but a blank sheet and a problem. Most people avoid this, as you say, beacuse it turns out that they know nothing, it comes with a considerable amount of work, and one makes several mistakes during the learning process. We should learn from our mistakes, go back and check it again, read the pdf, apply what we know to new situations etc. ExcelsFun is pure Excel for the real professionals who take things seriously. With everything in life: there are no secrets, just hard work.
@@zt.5677 BAM: Hard work and do what you love is the key to life. You are smart, Z T!!!
Thank you very much for very informative video where even i knew everything here still I learned so many small nuances. I will keep watching.
Yes: there is nothing like getting the full story to fill in all the gaps : ) : ) Glad you are enjoying.
@@excelisfun very much . Thank you so much..
Amazing teaching and refresher too on these fun formulas! Always fun when working with Excel :):):) Great job, Sir!!!!!!!
Happy to be on the Team and helping our Team to make the world a better place!!!!!
Thanks Mike for this EXCELlent video.
You are welcome! Thanks for your amazing support, Fellow Teacher : )
Instead of using the alligator mouth analogy for greater and less than, for me it makes more sense to say its an arrow head pointing down or up the number line. So 2154 > 2000 means 2154 is further to the right of the number line compared to 2000. This also makes it easier to understand when using negative numbers, at least for me.
I LOVE it! Pointing down is a good way to say it. Next time, I get to say both analogies and it will be thanks to you : ) Whereas the Alligator simile is a funny way to remember, the pointing down is a more concrete way to remember the meaning : ) : ) : ) Go Team!
I always think of a squished L for 'Less than' (
Hey Mike, I’ve been a fan for many years and always learn something from your videos. Thank you for continuing to post great content. There are so many tools available in the market these days and it seems more are created each day. It’s a challenge to figure out which is the best to use. I seem to alway come back to Excel:)….most of the time I can resolve a question using Excel however the challenge is making the process repeatable, reliable and sustainable. I’m not an IT professional, nor a trained data analyst ….I’ve managed well with Excel but I’m facing a particularly complex situation. It’s a simple business question - filtering sales data for specific customers for specific time periods per customer for specific products which also have specific time periods to filter. I’ve dabbled in power query and power pivot before and I’m using power pivot right now to filter as much of the data as possible…i.e. download data to a .csv, opening it in Excel, make it a table, create additional Excel Tables for reference data (customers and products) and using the data model/power pivot run pivot tables to get as close to the answer that I need….I finish up the work using regular Excel. IMHO, this is not sustainable. I have to do this every quarter for over 50 scenarios. I would like to add effective start and end dates to my customers and to my products but I am not sure how to set it up…how to creat the correct relationships and then filter …can you point me in the right direction….which of your videos should I watch to learn how to do this? Thanks again for all that you do for the Excel community!
There are too many parameters and situational details that would need to be ascertained before I could point you in the right direction. I am not in the position to do consulting at this time. In addition I get questions like this every day and I am unable to help given that I already work 10 or more hours every day. Your best bet is to initiate a dialog at the best Excel help site I know: mrexcel.com/board. There are 100s of Excel experts that can help if you explain the data, details and goal.
Thanks! Will do!!
You are the GOAT (greatest of all time)
The GOAT of telling Excel stories ; ) Thanks for your kind words : )
Always perfect 👍 Mike, God Bless You..
Thank you, Usamn : ) I am happy that you enjoy : ) : ) : )
You are not changing at all. Always great.
Glad to always help!!! : )
Great video and explanation. Thank you!
You are welcome!
amazing, thank you so much
You are welcome, DC Channel : ) : )
You sir, are a legend.
Alwasy glad to help our Team : )
This is great! Thanks Mike!
You are welcome! Keep it up : )
What a wonderful explanation
Please release video weekly or twice a week. Just a humble request
I have been sick, so i am behind schedule. The plan was to do one or two a week, but sick...
Thanks Mike!
You are welcome, Teammate Chris M!!!!
Thank you Mike for this wonderful handy video and I think you built excel again from beginning to end.
I have some questions below:
1- When you created PT, you used a normal chart shape instead of Pivot Chart. Why?
2- When you created normal table, you immediately changed table names for referencing but you didn't change pivot tables names. Is it necessary to build names for Pivot Tables or can we use Pivot Table names in different data analyses?
3- Could you pls mention sorting options and grouping of Pivot Tables in other tutorials for unique fields because sometimes data is not coming so well sorted or has more mouths of years.
So many thanks again.
Amazing video. Got to learn alot
Glad this helps!!!
For many years I have faithfully used Ctl+Enter when appropriate and even Shift+Enter (although I seldom want to swim upstream!!) along with Tab, Ctl+Tab and especially Shift+Tab when I want to scamper to the left. In short, I have tenaciously persued improvements in efficiency and productivity with Excel. Admittedly I have reaped many benefits from these efforts BUT I have yet to see any sign of those extra TWO DAYS of VACATION!!?! LOL
You are just not paying attention then lol*100
Those days are there for sure, but perhaps you used them for something else besides vacation lol*10
@@excelisfun Shucks!! I've been waiting for an all expense paid voucher to see the Northern Lights or at least a free resort in Cancun!! I guess I will settle for a Staycation in my Phoenix backyard!! At least it will be (114 F today) WARM!! : ) ; )
Buckling up for this 🚀
Loving the "Save A Click" tip Mike 🙌 minute 8:57
Minute 27:30 = Spreadsheet Mantra 🔥!
@@spilledgraphics 100%. 4 decades it has been #1 most important thing in Excel. Also the leading cause of error when it is violated... : (
@@spilledgraphics Saving clicks helps during vacation time : )
Yes!!! Buckles are good ; )
Great video!
Glad you like it!!! Keep going with the MECSs : )
Absolute legend ExcelIsFun topG
This is amazing and always there is something new to learn :-) Thanks teacher.
One thing, when we make the formula in edit mode to correct it or to add something we cannot move to the cell that we want by using arrow key we can only proceed by clicking in the cell :-)
We could only using arrow key when we creat the formula for the first time.
If the lower left status bar says "Enter" or "Point", the the Arrow Keys can be used to get cell references. If the lower left status bar says "Edit", the the Arrow Keys can NOT be used to get cell references. When the Staus bar says "Edit", you can force it back to "Enter" by hitting the F2 key. I hope that helps. That might be my next video... ; )
@@excelisfun Great Mike :-)
I can say only one word "Awesome"
Thanks for another informative video. I'm really looking forward to learning more. Could you kindly shed some light on how you made the text in cell E28 of the Calcs worksheet automatically appear when data was entered?
Thank you 🤓
You are welcome, Jackie O!!!
Awesome video Sir ❤
Glad you heart it : ) : ) : )
Thanks for the amazing video. Do we have video which had covered only new formulas from 2019 till date ?
Hello Mike,
I am really blessed having a mentor like you for excel.
I have a question, I am also using excel 365 but couldn't find the function "textbefore". I am actually using crack version of office 365. Is that a problem?
TEXTBEFORE is in beta now, but it should be released to all of M 365 soon : )
@@excelisfun thanks Mike for your reply.
Sir Mike according to data and my statistical analysis more than 818K people love you!
To funny. That is a good one, Yousaf!!!!
Hi Mike, can you please do a video on how to split multiple lines of data in a cell into multiple rows instead of the displaying in the same line/row down below?
Power Query can split to rows.
@@excelisfun yes I have done that and text to columns already but wondering if this can be done through formulas. I would love to see that so that way it's dynamic and quick instead of power query.
@@7411045 Here are two formulas if your text is in cell C5:
=TEXTSPLIT(C5,,",") (function only in M 365 Beta)
=FILTERXML(""&SUBSTITUTE(C5,",","")&"","//a")
Video about FILTERXML formula is here: czcams.com/video/kDoaWCZ4VBM/video.html
If I can get to it, I will try to make a video sometime.
Hello Mike, Just I noticed the conditional formatting didn't update in the pivot table when you added data to the table even after you refreshed it.🤔
In this video? At what minute mark. I though it did update.
My Bad , The condition was the Top 3... Thank you Mike for the response, I just want you to know that everything I have learned in excel you have the greatest share of that in way or another. You are a legend
Good
Glad it is good for you, Simu!!!!
Thank you.
You are welcome!
mindblowing
Yes!!! I am glad it is blown and you are having fun : ) : )
Hello! sir I am huge fan of you. Thank you so much for giving us a lot of knowledge. I need a favor from you can you show us garnishments excel sheet. I am working in accounting & we do need to pay for child support, some keystones tax for state. And i want to track everything in my excel sheet. If you can make it and share with us will be appreicate. Once again thank you so much.
This video gives you all the tools you need to do such a thing. However, I do not have a template video on that specific topic.
Thank You
You are welcome!!!
Hello Mike Sir, thank you for the great video. I am learning a lot from you.
I was practicing the pivot table and when I put the date into the rows it does not change to months and rather remains in the date. Can you please help on that?
Right-click dates in Row area, click Group, then click Months and Years : )
Dear Mike, when I apply number formatting in a pivot table by right clicking in any cell of the pivot table and selecting number format, It works perfectly in the practice files i download from your link, however when i the do the same on my own files, the formatting only applies to the values of the selected column only and not to all value fields; i have to repeat the same on the rest. Is there some setting etc that i should know of ? What am i missing ?
Hi Mike, I hope you are well. I have Office 365, yet there is no textbefore option. Do I need to activate a seperate program, or are there versions in which this is not yet compatible in Office 365?
It should be there now, you can also try: File, Account, Update. Today is 8/28, can you see those functions now?
The text at @50:14 says "Alt - V N T" to create a pivot table, instead of "Alt - N V T"
I will pin your comment to the top. CZcams used to allow me to add an annotation to video, but not anymore... : (
Thanks for pointing this out and helping the Team.
@@excelisfun and in case it wasn’t clear, your videos are wonderful! Thanks for the great work you do!
@@jamesepace You are welcome, James! Glad it helps. And thanks for the heads up on the keyboard - your comment will help the Team.
Hello my friend, and thanks for the amazing video(as always). On HM(an1) sheet, it says that the tax rate is 9.95%, but in the input cells its said 11.8%. can you help me ?
May i reuse this video? I will share ur link on top of the description as a source link
Yes, you can use my posted stuff for non-nonprofit endeavors by simply giving me credit : ) Where are you sharing?
@@excelisfun same platform. I am not even memorized, so getting watch hours, so i am all ok with that if i get monetize option, you can get revenue. I am totally fine
@@iamajblove I think if you use the exact video and post it on your CZcams Channel, then CZcams marks it as a Copywrite Violation. If your channel is not monetized, then I think it is OK. You can try it and see what happens. Many times, other teachers in the world use the videos I post in there non-profit classes.
@@excelisfun i will try to fix those issues.
And thank u for allowing me sir
@@iamajblove Go Team!
Thank you so much for your videos. I have some problems with chapter 5 and the formula ROUND. I need to use ; insted of ,? When i use this is only bring the last two digits to zero insted of rounding the number to two?
I am not sure I understand? Can you give me a few examples of what the starting point is and what the end result should be. Then maybe I can back into the logic that you are trying to explain.
@@excelisfun Ok Ill try. In topics number 5 about ROUND function. The last four digits in that number 164,838375 loks like this 164,840000 insted of 164,84. for me to get this even to work i need to use Semicolon insted as shown i the video comma.
@@pierreharrysson Maybe try to increase decimals showing
I a having a hard time opening his file for Excel. I only have the online version. Any suggestions?
1. Is there a way to expire formula / functions, if we put some kind of validation or wihtout vba in terms of between dates, if that date expires, all such functions and formulas shouldn't work.
2. NAMED Ranges must have user ID and password, so no other person can able to see the formulas.
1. I have no idea how to do that. I am sorry. You always ask questions and I never seem to have the answer for you... : (
2. I am not sure how to assign a password to a define name... : (
@@excelisfun Boss you are the master, whatever I learned in Excel, it is because of you. Thank you
@@Al-Ahdal You are welcome!!!
good day sir, would like just to as question about the TEXTBEFORE function. In my version it is not considered a function. is there any way to download it or do u have other ways? thankyou so much!
Are 365 and 2021 similar? My subscription is almost up to 365 and I'm considering getting the standalone version instead.
Microsoft 365 Excel is the ONLY app to have. All others have less. 2021 and 365 are similar, but some of the coolest new things are just not in 2021. 2021 is only invented for entities that want the very old school "install" program. No individual should buy any version other than M 365. It's just too good.
@@excelisfun Good to know, thanks! Looking forward to the rest of the videos in this series.
Do you have a Discord Channel for live chat and questions about specific excel topics?
Did anyone else have issues with the PivotTable generating unique date values automatically when you added it to the rows column like in the video?
I am not sure what problem you are refering to. When you drag a filed to the Rows it always yields a sorted unique list.
What is the origin of calling the crosshair cursor and "angry rabbit?" I noticed it was also in your Excel 2003 webpages.
Work computer does not recognize =TEXTBEFORE( as a formula. I'm sure we have 365 at our company. Is there a setting, option, update, add-in needed to enable this function?
Are you sure that you don't have Excel 2019? or 2021? Those are static versions and do not have many things that M 365 has.
Hello, I once saw a company of 2000 employees without a good accounting system, actually they used a pretty bad informaiton system. I saw massive untractable dead values. These numbers came from a huge amount of vlookups from using SMART PIVOT. The numbers must be dead values because when other people run samrt pivots, the previous returned numbers will be different or gone. Control wise, these dead values makes reviews very hard, and makes the procedures difficult to understand. Is there any better way to improve?
From your description, I have absolutely no idea how to answer this question. The only way to find a solution to a complex problem like this is to do a full audit and find what is going on. One would would have to have all parameters and requirements presented to then make a decision.
I have no idea what a term like this means: "massive untractable dead values".
Hi! Is there a 5th Video already? It says in the Playlist that it is unavailable coz it's private...?
No, I got covid. I have only posted MECS 1 and 2, with two other partial videos. I will post #3 today or tomorrow : ) Here is playlist: czcams.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
@@excelisfun oh no! Hope you feel better. And really thanks for sharing your knowledge!
@@ren-eq5qv You are welcome!
At 50:11, you say Alt+NVT, but your slide shows Alt+VNT.
hi sir a small doubt while navigating sheets through that bottom small arows ,the arows have been greyed out can you please tell how to solve it sir
hey mike i have excel 2019. is it the same as Microsoft 365 excel?
100% no. Excel 2019 is missing 100s of functions and features and more importantly is missing huge important parts of M 365 Excel like the new formula dynamic spilled array engine and significant Power Pivot and Power Query features. As I have said over that last few years here at my channel, the only app to have in M 365 Excel. All others are significantly lacking. That said, I understand that many entities around the world refuse to obtain M 365 Excel and instead go with Excel 2019 and Excel 2022. Even the college I work for has refused. It is a shame because any potential extra costs are nothing compared to the new benefits for analysis, accounting, finance, economics, budgeting, data analysis and many other endeavors that all of us must engage in. The college I work for is so stupid, and limited in make smart decisions that would increase employee efficiency, that the college is strongly encouraging employees to move to Google Sheets. Pathetic. In the long run, however, there will only be M 365 Excel. All other versions will cease to be supported by Microsoft. I hope you can get M 365 Excel soon, Ruheen.
Hi. Love your videos. I really feel like you could help me on something that seems so simple but I cannot find an answer on no matter where I look (using various google searches, Reddit Excel forum, etc.) So simple. All I am trying to do is compare the difference in rank between two lists. Say for example you wanted to compare the AP poll to the coaches poll in college football. There would be a number by a team name, you would simply want to return a value showing the difference in rank between the two polls. That's it. I cannot believe how difficult this has been. Any help greatly appreciated!
What if the team is not in both lists, what do you want? Which list do you use first in the difference calculation? It is a simple look situation but there are a number of complicating factors.
This might work:
=IFNA(MATCH(TeamName,APTeamList,0)-MATCH(TeamName,CoachesTeamList,0),"Not in Both Lists")
or in M365:
=IFNA(XMATCH(TeamName,APTeamList)-XMATCH(TeamName,CoachesTeamList),"Not in Both Lists")
@@excelisfun Thank you for the reply!! So that kind of works but not really. I compared the two lists and it returns "not in both lists'" even though the two values both are on each list. So at least it returns something instead of some error value. Also, that does not compare the values, which is what I really need. If one is ranked #1 on one list, and same person is ranked #8 on another, I need it to return a value of 7 showing how far apart they are in ranking. This is for fantasy football if that helps so it involves a first and last name. College poll was just an example.
@@vodking If it returns "not in both lists" IT IS NOT IN BOTH LISTS. You may think it is, but it is not. It may be misspelling, or extra space, or a hidden characters, or partial name or other complications. Also, MATCH returns the relative position of an item in list, so if names are sorted by rank, you do not need to look at rank column. The formula does work. I can not help you beyond this without initiating a consulting job. As I said initially, problems like this may seem easy, but there are multiple complications that get in the way and usually take much effort to resolve.
might just be me, but =textbefore never shows up on my excel, tried looking online but could not find anything about the issue. Not sure what the issue could be that I'm not able to use it?
Do you have Microsoft 365 Excel?
@@excelisfun I'm assuming not, using the excel program that my company gave me. Figured I have another version, sadly haha.
@@paulhernandez2601 How about Spilled Array Formulas? Do you have Spilled Array Formulas? Like the FORMULATEXT Spilled Array example 18:02? What version of Excel do you have?
@@excelisfun Yeah, I can use FORMULATEXT, I just checked my account and it shows as microsoft365. One thing I noticed is that I have an account error message and it's recommending I reach out to an admin. Will be reaching out tomorrow to see if I'm missing something. Hopefully that will help with this.
@@paulhernandez2601 TEXTBEFORE might not be in all versions of M 365 yet. As long as you have the Dynamic Spilled Array Formulas, you will be OK : )
What are the shortcuts on a MAC?
textbefore function is not available in my laptop, why so?
@51:48 anyone know how to make the months and days appear as choices on online office 365? mine won't populate
Can excel do machine learning model?
I am not sure.
Sir how can I connect you through any social media platform, I would like to ask you some doubts about excel.
I am not able to use =textbefore for extract name though i have Microsoft 365. How to fix this.
you got to update your 365, happend to me too it happens when you switch from old excell to 365. You download it and you think you have the 365 but in reality its stuck on the old excel find the update for it and you should be able to use all the text formulas
When I try and follow along on 365 online it does not auto sort date into Months and Years when you drag it into the Rows section of PivotTable Field. Don't know if that is a feature they are missing or if I have done something wrong
Online Excel is not the same as Microsoft 365 Excel. Online Excel has only a small portion of all the real features in Excel. This is video 2 in this class, but most of the rest of the class is not even possible in Online Excel. This class is not for Online Excel, sorry.
You might try: right-clicking dates in row area of PivotTable and see if the grouping option si there to try and manually group dates into months and years.
@@excelisfun Thanks for the reply. I had a good poke around and couldn't see it. It is a shame as it is such a useful feature. Anyway I have switched to Excel 365 Desktop now and all is well.
Just want to take a moment as well to thank you for such a great course. Really could not ask for more
@@iiiaiiin You are welcome for it all! I am glad you got to the only app that matters: M 365 Excel : )
Hiya, once you have your Pivot table, select Design in the ribbon, and change it to Tabular Mode.
For some reason when I drag dates into rows it doesnt give me the months or days so frustrating
When I am doing the pivot table "Days(date) and Months (Date)" are not automatically showing up? IDK what to do :(
Right-click dates in row area and click Group, then group by year and month.
Bjr. How to accumulate days insun without a rainy day? ex: Sun, Sun, Rain, Rain, Sun, Sun, Sun, Sun, SoLeil is in boleen 1,1,0,0,1,1,1
♥️🇮🇳🙏
Glad you like it!!!
1:01:40 Ctrl + A will do the same thing
I always teach Ctrl + * because the VBA code recorded when you do a Macro is "Select Current Range", which is what we want in this case (everything outward to full empty cells), whereas when you use Ctrl + A, the code is "Select All", which can select everything and ruin the Macro. As a teacher I always try to set up things in the right order so that later in the class things go smoothly : )
I have a prblm 😢..My office 365 get close every time shows Licence problem.. Can u help me from solving this prblm.. I love to learn excel from ur classess. But now i can't for this prblm😔😔😔😔
I am not able to help you with that. I am sorry. You will have to contact MS