Excel Basics 25: Power Query (Get & Transform) to Clean & Transform Data into Proper Data Set

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start File: http://ift.tt/2zDvXc0
Download Kipped Folder with Text Files: http://ift.tt/2n3o0ar
Download Excel Finished File: http://ift.tt/2zF0Yw8
Download Full Written Notes: http://ift.tt/2n3o1v1
Class web page link: http://ift.tt/2kPyedk
Topics in this Video:
Topics in this Video:
1. (00:01) Introduction to Power Query and the examples in this video
2. (02:50) Difference between Data Ribbon Tabs in Office 365 Excel 2016 and Non-Office 365 Excel 2016
3. (04:32) Example #1: Convert “NOT a Proper Data Set” into a Proper Data Set & Make PivotTable Report
4. (05:00) Compare Flash Fill and Power Query
5. (06:16) What is a Delimiter?
6. (07:02) What is a Query? In Power Query, Access and other programs?
7. (09:22) Import data from an Excel sheet into the Query Editor using From Table Range button
8. (10:18) Split By Delimiter
9. (11:30) Rename columns
10. (12:00) What is a Data Type?
11. (14:53) Close Query and Load to Sheet
12. (16:22) Queries Pane
13. (17:00) Make PivotTable
14. (18:04) Add New Data and Refresh
15. (20:12) Example #2: Import Multiple Text Files & Transform into Proper Data Set
16. (20:28) Download and unzip Text Files
17. (22:11) What is a Text File and how are they used in Data Analysis?
18. (24:02) Use From File, From Folder option to import multiple Text Files into Power Query and combine into a single Proper Data Set
19. (25:50) Use Query Editor to finish combine query.
20. (27:05) How to edit or delete steps in a query.
21. (27:05) How to edit the folder path if location of folder changes
22. (27:51) Close and Load straight to s Worksheet.
23. (28:30) Add new Text Files to Folder and update in Excel
24. (29:38) Summary

View on YouTube

Excel Basics 24: Excel Charts & Graphs to Visualize Quantitative Data. No Chart Junk!!!

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2A7qA25
Download Full Written Notes: http://ift.tt/2jgXDZx
Class web page link: http://ift.tt/2kPyedk
Topics in this Video:
1. (00:01) Introduction to Excel Charts & Graphs
2. (02:46) Excel Chart Terminology
3. (04:00) What is Chart Junk?
4. (05:35) Column and Bar Charts for comparing numbers across categories
5. (11:35) Formatting Chart Elements with Buttons or Task Pane
6. (15:25) Cross Tabulated Reports and Clustered & Stacked Column Charts
7. (18:15) Pie Charts less effective than Column or Bar Charts
8. (19:19) Line Charts to show trends or patterns over time
9. (24:25) Select Data Source dialog box
10. (25:45) X Y Scatter Charts for X Y Data to show a Relationship between Two Numbers
11. (31:05) Recommended Charts
12. (33:11) Summary

View on YouTube

Excel Basics 23: Data Analysis Features: Sort, Filter, Flash Fill to Organize & Clean Data

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start File: http://ift.tt/2B0xJAj
Download Excel Finished File: http://ift.tt/2iE9vVS
Download Full Written Notes: http://ift.tt/2B1xrcx
Class web page link: http://ift.tt/2kPyedk
Topics in this Video:
1. (00:01) Introduction about Data Analysis features
2. (01:40) Sorting
3. (11:45) Filtering
4. (21:50) Flash Fill
5. (29:01) Summary

View on YouTube

Excel Basics 21: Relationships Rather than VLOOKUP for PivotTable Reports (Excel 2016 Data Model)

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start File: http://ift.tt/2AjgGx4
Download Excel Finished File: http://ift.tt/2z4t13R
Download Excel VLOOKUP File: http://ift.tt/2AgX1hs
Download Full Written Notes: http://ift.tt/2AUlNkh
Class web page link: http://ift.tt/2kPyedk

In this video learn about how to use Excel Tables, One-To-Many Relationships and the Data Model as a substitute for a VLOOKUP Function Helper Column for a Large Data Set. Discuss pros and cons. Introduction to One-To-Many Relationships in the Excel Data Model.

View on YouTube

Excel Basics 22: Set Default PivotTable Layout & Options in Excel 2016 Office 365 or Later

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2hOufwR
Download Full Written Notes: http://ift.tt/2jKQYux
Class web page link: http://ift.tt/2kPyedk

Learn about how to set Default Layout for a PivotTable in Excel 2016 Office 365 or later versions.

View on YouTube

Mr Excel & excelisfun Trick 190 Inventory w Subtotal, MINIFS, Power Pivot, AGGREGATE, or Power Query

Mr Excel & excelisfun Trick 190 Inventory with Subtotal, MINIFS, Power Pivot, AGGREGATE, or Power Query
Download workbook: : http://ift.tt/2jDcKQJ
Question sent in:
I work for a retailer & was asked to create a spreadsheet to show our sales teams what we own & what we can sell. Simple enough right? Well here is the catch. The items we sell contains multiple cartons & are inventoried on a per carton basis.
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use
Topics:
1. (00:01) Dueling Song
2. (00:40) Explanation of problem
3. (01:50) Subtotals Solution (Mr Excel)
4. (04:00) MINIFS and Helper Column Solution (excelisfun)
5. (05:45) Power Pivot and MINX DAX Formula Solution (Mr Excel)
6. (09:18) AGGREGATE Function with no Helper Column & Array Formula (excelisfun)
7. (12:19) Power Query (Mr Excel)
8. (16:15) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 190

View on YouTube

Excel Basics 20: PivotTable Power: 14 Amazing PivotTable Reporting Tricks: Slicers to Show Values As

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start File: http://ift.tt/2AVqSca
Download Excel Finished File: http://ift.tt/2yW1zFl
Download Full Written Notes: http://ift.tt/2AUlNkh
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:51) Review of PivotTables we have already completed in this class.
3. (03:15) What is Data Analysis in one sentence
4. (03:34) What’s is Proper Data Set in Excel?
5. (03:50) Example 1: Use VLOOKUP to Add New Fields/Column to the Data Set:
6. (06:50) Example 2: Create Country & Product Category Average Revenue Report
7. (08:14) How to interpret Criteria/conditions/filters in a Cross Tabulated Report
8. (10:00) Indicate Until with Label, Not Number Formatting
9. (10:32) Example 3: Change SUM Function to Average Function
10. (11:34) Example 4: Change the PivotTable Style (If you want)
11. (12:33) Example 5: Name PivotTable
12. (13:23) Example 6: Group Daily Dates into Months and Years
13. (16:54) Example 7: Add Year Slicer to our “Country Report” PivotTable and format the Slicer
14. (18:18) How to Interpret Criteria / Conditions / Filters from Rows area, Columns area and Filter or Slicer area in a PivotTable.
15. (20:00) Example 8: From “SalesData” Table, Create Product Revenue PivotTable Report with Multiple Calculations in One PivotTable
16. (22:08) Example of Text Field in Values area of PivotTable
17. (23:25) Example 9: Connect Slicer to 2 Pivots
18. (25:00) Formulas to Test Show Values As PivotTable Calculations
19. (26:30) Example 10: Add a “Show Values As” “Difference From”
20. (29:40) Example 11: Add a “Show Values As” “% Difference From”
21. (30:22) Example 12: Add a “Show Values As” Running Total”
22. (32:22) Example 13: Count of Phones Purchased Report & % Phones Purchased Report
23. (35:21) Example 14: Show Values As % of Row Total: Does CPA Prep Course Help? Report
24. (37:56) Summary – Conclusion

View on YouTube

Excel Basics 19: SUMIFS, COUNTIFS, AVERAGEIFS, functions calculations w 1 or more Criteria

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2hDa2Ko
Download Full Written Notes: http://ift.tt/2ju4m64
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:30) Review of SUMIFS and COUNTIFS examples we hjave already done in this class.
3. (03:38) What is an AND Logical Test
4. (06:40) Example 1. Goal: Count How Many BUSN Classes You Had With a Grade of 3 or Higher. COUNTIFS Example.
5. (10:52) Excel and Access are NOT Case Sensitive
6. (11:59) Example 2. Goal: Add Total Credits for BUSN Classes in the Year 2017. SUMIFS Example.
7. (14:14) Example 3. Goal: Average Grades for BUSN Classes in the Year 2017. AVERAGEIFS Example.
8. (16:11) Example 4. Goal: Add Total COGS for Quad Product in the Year 2017. SUMIFS Example.
9. (18:03) Example 5. Goal: Add Total Sales for each Product in the Year 2017. SUMIFS Example with Relative and Absolute Cell References.
10. (20:39) Example 6. Goal: Count number of Sales Great Than 2000 for each product in the year 2017. COUNTIFS function with 3 Criteria.
11. (24:22) Example 7. Goal: Add Sales for each Product automatically with the SUMIFS function and the Excel Table feature.
12. (27:21) Example 8. Goal: Count how many times an Assembly Line Post fell below the required Less Than 10 second assembly time. COUNTIFS Function.
13. (29:37) Example 9-10. Goal: Find Min Time for Each Assembly Line Post. See the New MAXIFS and MINIFS Functions in Office 365 Excel 2016.
14. (33:34) Summary – Conclusion

Adding with Conditions Criteria, Counting with Conditions Criteria, Average with Conditions Criteria,

View on YouTube

Excel Basics 18: Defined Names in Excel Formulas & Functions & For Jump Go To!

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2yW3XA9
Download Full Written Notes: http://ift.tt/2mpps6K
Class web page link: http://ift.tt/2kPyedk

In this video learn about how to Create Defined Names, and use them in formulas and to Jump to areas in the Excel Workbook.

View on YouTube

Excel Basics 17: Style Formatting: By Hand, Cell, Table & Conditional Formatting

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2mppN9w
Download Full Written Notes: http://ift.tt/2yXWjW8
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (00:32) Example 1: Accounting Style Formatting (4 Examples)
3. (08:47) Example 2: Accounting Borders
4. (09:25) Example 3: Column widths!!
5. (10:16) Example 4: Wrap Text in Cell: Multiple Lines in a Cell (Alt + Enter)
6. (11:38) Example 5: Table Format button (Converts to Excel table)
7. (12:55) Example 6: Cell Styles
8. (14:00) Example 7: Built-in Conditional Formatting to Visualize Data
9. (17:11) Summary – Conclusion

View on YouTube

Excel Basics 16: Mixed Cell References #1 Trick to Creating Formulas Quickly!!!

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2AzPPtr
Download Full Written Notes: http://ift.tt/2yu3pNs
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:24) Example 1: Mixed Cell References in Multiplication Table
3. (04:24) Song and Excel Example in High Speed
4. (05:20) How Cell References Move Throughout Copy Action
5. (07:06) Two Questions Method for Determining Which Type of Cell Reference you need.
6. (13:05) Sledge Hammer Method for Determining Which Type of Cell Reference you need.
7. (15:23) Use Ctrl + Enter to Populate a Highlighted Range with a Formula
8. (16:17) Example 3: Mixed Cell References for Budget with Income Statement Accounts
9. (24:28) Use Ctrl + Enter to Populate a Highlighted Range with an Edited Formula
10. (27:26) Example 2: Mixed Cell References & Assumption Tables.
11. (31:40) Example 4: Mixed Cell References for Retirement Savings Table
12. (37:07) Example 5: Mixed Cell References & Correct Assumption Table for Dynamic Payroll Table
13. (41:22) Two Stories about ROUND Function examples in working world
14. (43:46) Summary – Conclusion

View on YouTube

Mr Excel & excelisfun Trick 189: VLOOKUP for Missing Values in Column

Download workbook: http://ift.tt/2AyjzqN
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use
Topics:
1. (00:01) Dueling Song
2. (00:29) IFNA and VLOOKUP Mr Excel
3. (03:02) LOOKUP and VLOOKUP excelisfun
4. (06:39) IF and ISBLANK and VLOOKUP Mr Excel
5. (11:40) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 189

View on YouTube