Excel Data Analysis Class 01: PivotTables, Power Query, Formulas and Charts

Excel Data Analysis 01 files:
In this video learn the basics of Data Analysis using Microsoft PivotTables, Power Query, Worksheet Formulas and Charts. Learn how to use Power Query to import data, use worksheet formulas to create the sales table (the data model), use the Standard PivotTable tool to build four sales reports, use a Slicer to filter your report, use the Chart tool to build a line chart to show sales trends over time, and finally use Power Query to refresh the reports when new data arrives.
Video compares a Standard PivotTable to a Power Pivot Data Model PivotTable and to Power BI Visualizations and Data Model. This video details how the data analysis process works in all three tools: Standard PivotTable, Power Pivot Pivot Table and Power BI Visualizations.
Topics in the video:
1. (00:00) Intro
2. (00:10) Files to use in class
3. (00:34) Goals for video
4. (01:25) Data Analysis Step #1: Get Data. Use Power Query to Get Data. Learn about important data analysis terms.
5. (07:24) Data Analysis Step #2: Create Data Model. Use Worksheet formulas to create flat table for the Standard PivotTable tool. Learn about the new lookup function XLOOKUP.
6. (12:44) Data Analysis Step #3: Create Calculations & Reports. Create first report: Year Month Sales Report with Product Slicer.
7. (14:32) Grouping in a PivotTable.
8. (19:02) Data Analysis Step #4: Create Visualizations: Line Chart to show sales trends.
9. (21:41) Add Slicer to PivotTable.
10. (23:53) Create remaining reports.
11. (25:40) Show Values As Calculations in a PivotTable.
12. (29:35) Limitations of Standard PivotTable: Grain of the Table.
13. (31:10) Cross Tab Report.
14. (32:12) Data Analysis Step #5: Get New Data and Refresh Reports & Visualizations
15. (34:52) Conclusion

Four video release dates:

This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.

[HusseinKorish]

We are so lucky to have Mike Girvin on youtube ...That's why "Excel is fun" channel is very important ...because even if we were excel experts ...it's important to refresh our knowledge from to time to time ... thanks alot Mike.

[drsteele]

It's terrific how Mike Girvin provides ample enthusiasm to people wanting to learn Excel. Bravo, Mike.

[richardhay]

Clean, systematic presentation. Great pedagogy. Like riding on an intellectual superhighway from start to finish. No potholes; no detours--just smooth sailing.

[excelisfun]

Topics in Video:

1. (00:00​) Intro
2. (00:10​) Files to use in class
3. (00:34​) Goals for video
4. (01:25​) Data Analysis Step #1: Get Data. Use Power Query to Get Data. Learn about important data analysis terms.
5. (07:24​) Data Analysis Step #2: Create Data Model. Use Worksheet formulas to create flat table for the Standard PivotTable tool. Learn about the new lookup function XLOOKUP.
6. (12:44​) Data Analysis Step #3: Create Calculations & Reports. Create first report: Year Month Sales Report with Product Slicer.
7. (14:32​) Grouping in a PivotTable.
8. (19:02​) Data Analysis Step #4: Create Visualizations: Line Chart to show sales trends.
9. (21:41​) Add Slicer to PivotTable.
10. (23:53​) Create remaining reports.
11. (25:40​) Show Values As Calculations in a PivotTable.
12. (29:35​) Limitations of Standard PivotTable: Grain of the Table.
13. (31:10​) Cross Tab Report.
14. (32:12​) Data Analysis Step #5: Get New Data and Refresh Reports & Visualizations
15. (34:52​) Conclusion

[jean-marcherard]

17 years working on Excel and ... still learning!
Thank you Mike

[nikakalichava]

these data analysis videos scratch the part of my brain where pleasure exists. Thanks, Mike!

[srbrt]

You're BRILLIANT, Mike Girvin! I appreciate you and your teaching so much. As an aspiring data analyst, this type of education is so enriching. Thank you for sharing this to the global community - you're a genius.

[skrapsaker]

This channel and teacher is a TREASURE!! Wow!!! Ty, SIR!🙏

[makeuplily]

Your channel is the only one that breaks down info into digestible steps, is very organized, detailed, and with a great array of info that has really helped me out especially during a difficult period of being unemployed. I really REALLY appreciate it Mike!
THANK YOU!🙌

[iammuko]

I rarely sub to channels unless they are exceptional. You are exceptional, Sir!

[PunmasterSTP]

Now I normally leave a pun in the comments, but I feel that doing so here might take away from the expression of gratitude I'd like to give. This video was one of the most helpful things I have ever seen, on YouTube or anywhere else, and I am *amazed* at the width and breadth of your very high-quality material. Thank you for making all of this publicly available, and I can't wait to check out everything else that you have!

[stephenkibara]

Excellent video with great pacing and clear communication. The best of the best

[atiqkhan]

i havent seen a fanastic teacher like mike, mike you really made excel a fun.
thanking you

[anandraj]

Thank u for teaching all these properly without leaving any doubt.
Now finally I think I can get a job

[ogwalfrancis]

Pivot table, the heart of Excel data analysis, thanks so much Mike🙏🙏

[AllAboutRSCC]

Some videos beg me for a like. This one earned it without asking

[jojochan]

I have the book <Excel insights> from library but the youtube videos make so much more sense to me! Really appreciate it.

[nsanch]

Thank you Mike for the video. I enjoyed making the formulas. The pivot tables are really rewarding also because reports can be made so easy, including the chart and slicers. Updating the data, tables, formulas charts and pivots with a new source file is amazing, and a task I can practice more because it make me nervous as easy as it is. Thank you for showing all these things.

[vane]

Hi MIke,
May I suggest that instead of going back to the original Fact Table sheet location and pressing Alt+N+V each time, you could just Copy/Paste the already created pivot table.
That keeps all the formatting, and you just play around with PivotTable Fields pane to do whatever you want.
I've noticed that's quicker.
I am a big fan of your work, however, so please keep up with those great posts you've been creating

[markward]

I always look forward to these videos and this is something I'm now working on in work. This is an incredibly informative series. Thank you Mike for creating you are the best!