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
Month: November 2017
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
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
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.
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.
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
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
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,
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.
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
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
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