Excel Formulas & Functions, PivotTables, Slicers & Charts - 365 MECS 02

Comprehensive introduction to what Excel can do with Worksheet Formulas & Functions, PivotTables, Slicers, Charts and Excel Tables. This is M365 Excel Video 2.

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

#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

[othmanelebbache]

after more than two decades in teaching excel, mike still shining, and his teatching style is just such fun ..

[jinsonjoseph]

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

[bagnon]

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.

[neogeo]

Just spent 3 hours straight on this playlist without moving from my chair, so much energy and dedication.
Forever grateful and indebted to you
excel really isfun

[jannikretlow]

I'm a former HCC student (back in 2008-20010) now living in Canada. I return to your videos anytime I need a brush-up on my Excel skills, or can't remember how to accomplish a specific task in Excel. I'm watching your 365 MECS series for the first time, now that I have Microsoft 365. Your videos continue to inspire and help me -- and I've shared your YouTube channel with colleagues and friends who want to learn Excel. You're the best!

[excelisfun]

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!

[mohammedgalal]

I REALLY DO NOT KNOW HOW TO THANK YOU.
WORDS ARE NOT ENOUGH FOR THIS GREAT JOB.

[Excelambda]

With this eloquence nobody can escape from becoming passionate about Excel.✌😉

[basamreddy]

Hi sir, iam from India . Every day watching your videos I lot of learn excel. Thank you so much sir.

[canirmalchoudhary]

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. 🙌

[joshuamanampiu]

Amazing how many new things I have learnt despite being a seasoned Excel user. Thanks Mike.

[AsadAli-fhsm]

"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."

[nsanch]

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.

[Vandalfoe]

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

[yousafhassan]

Sir Mike according to data and my statistical analysis more than 818K people love you!

[gaps]

Thanks so much for these amazing videos! Very clear and straight to the point. Thank you

[GeertDelmulle]

Holy smokes! I can’t read your book as fast as you can make videos about all of its contents!
You’re a machine! :-)

[qyuqyu]

A lot better than excel course in coursera. Thank you Mister.

[ihabmusslih]

since long time I follow your channel. thanks for every think

[suecampanelli]

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!