Excel Data Analysis 01 files:
https://excelisfun.net/files/EDA01-Start.xlsx
https://excelisfun.net/files/EDA01-FinishedAfterVideoFilmed.xlsx
https://excelisfun.net/files/fSales.txt
https://excelisfun.net/files/fSales-WithExtraYearData.txt
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:
Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations, https://youtu.be/mFusmAyagsk
Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, https://www.youtube.com/watch?v=LpvCj9vCtRA
Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, https://www.youtube.com/watch?v=eIaKC6zLmb0
Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling, https://youtu.be/vBLM3usYRF4
Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas, https://www.youtube.com/watch?v=2Jl1GEOHHWA
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.