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
Tag: Productivity
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
Excel Basics 15: Excel Table Feature & Dynamic Ranges for VLOOKUP, PivotTables, & more!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2yLURpp
Download Full Written Notes: http://ift.tt/2maLU35
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:15) Example 1: Excel Table feature for VLOOKUP & Data Validation List
3. (07:10) Example 2: Excel Table feature for Chart
4. (09:45) Example 3: Excel Table feature for PivotTable
5. (13:10) Example 4: Excel Table feature for Other Formulas
6. (16:50) Example 5: Add New Column to an Excel Table AND Create Approximate Match Lookup Table from a written statement.
7. (17:47) Create Approximate Match Lookup Table from Written Instructions
8. (24:35) Change Settings to Allow Columns and Rows to be added to Excel Table as you work: Excel File, Options, Proofing, AutoCorrect, Auto Format As You Type, “Apply As You Work” – “Include New Rows and Columns in Table”
9. (26:10) Add columns to Excel Tables with Table Formula Nomenclature with Relative and Absolute References.
10. (29:30) Formula for Discounts when we need to calculate Net Revenue based on a Discount.
11. (34:14) Add new records to bottom of Excel Table and Formulas will automatically populate down
12. (35:05) Summary – Conclusion
Excel Basics 14: Excel VLOOKUP Function Made Easy! 9 Examples to Make Your Job Easier!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2zHeXRV
Download Full Written Notes: http://ift.tt/2zuiTol
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:10) Should we use IF or VLOOKUP?
3. (02:12) What does VLOOKUP do?
4. (03:22) Examples of Lookup Tables
5. (06:14) What does “V” in VLOOKUP mean?
6. (06:41) Formula 1: Exact Match VLOOKUP to get Product Price & Data Validation Dropdown List: =VLOOKUP(E23,A23:C27,3,0)
7. (09:50) VLOOKUP can do 2 different tyoes of Lookup
8. (11:29) Formula 2: Exact Match VLOOKUP to get Product Price for an Invoice: =VLOOKUP(A41,$A$23:$C$27,3,0)
9. (13:04) Add Data Validation List to a cell to create a drop-down list of valid lookup values for VLOOKUP Function
10. (18:03) Formula 3: Approximate Match VLOOKUP to get a Bonus Commission %: =VLOOKUP(B21,$F$21:$G$27,2)
11. (24:11) Formula 4: Approximate Match VLOOKUP to get a Bonus Commission % & Calculate Bonus Amount: =VLOOKUP(B33,$F$21:$G$27,2)*B33
12. (28:15) What VLOOKUP Does: Puts 1 of multiple things into a cell or formula.
13. (29:01) Formula 5: Exact Match VLOOKUP to get Employee E-mail and Phone Number: =VLOOKUP(A16,A2:I11,6,0)
14. (31:56) Formula 6: Exact Match VLOOKUP to List of Employee Salaries: =VLOOKUP(A23,$A$2:$I$11,9,0)
15. (32:48) Formula 7: Approximate Match VLOOKUP to get Tax Amount: =VLOOKUP(D4,A4:B8,2)
16. (34:23) Formula 8: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E13,$A$13:$B$17,2)
17. (36:31) Formula 9: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E4,$H$4:$I$9,2)
18. (38:08) Summary – Conclusion
Excel Basics 13: IF Function Made Easy! IS Functions Too! 14 Epic Examples!!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2zkj4lL
Download Full Written Notes: http://ift.tt/2zx9ZqF
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Detailed Introduction to the IF Function & Comparative Operators and Logical Tests
2. (08:53) IF Example #01: Your Bonus: =IF(C6 Greater Than =C11,C10,0)
3. (13:39) IF Example #02: In Balance?: =IF(B22=C22,”In Balance”,”NOT In Balance”)
4. (17:16) IF Example #03: Bonus? TRUE or FALSE?: =C4 Greater Than =$F$4
5. (17:54) IF Example #04: Bonus? “Bonus” or “No Bonus”: =IF(C11 Greater Than =$F$11,”Bonus”,”No Bonus”)
6. (18:54) IF Example #05: Bonus? 0 or $750: =IF(C18 Greater Than =$F$18,$G$18,0)
7. (20:20) IF Example #06: Bonus Commission? 5% or 1%: =IF(C25 Greater Than $F$25,$G$25,$H$25)
8. (23:34) IF Example #07: Calculate Commission Paid: =IF(C32 Greater Than $F$32,$G$32,$H$32)*C32
9. (27:46) IF Example #08: “Under” or “Over”: =IF(C39 Less Than $F$39,$H$39,$G$39)
10. (31:25) IF Example #09: “No Balance” or Show Nothing: =IF(C46=0,$H$46,””)
11. (35:03) IF Example #10: Is it a Number?: =ISNUMBER(C7)
12. (38:26) IF Example #11: Is cell empty?: =ISBLANK(C7)
13. (39:09) IF Example #12: Is it Text?: =ISTEXT(C7)
14. (40:23) IF Example #13: Checkbook Balance: =IF(ISBLANK(B5),””,G4-F5+E5)
15. (48:22) IF Example #14: Sales Category: =IF(L7 Greater Than $O$7,”Large”,”Small”) and then make a PivotTable
16. (52:50) Summary – Conclusion
Mr Excel & excelisfun Trick 188: Sales Team Region Report: 5 Different Amazing Methods
Download workbook: : http://ift.tt/2zdkYFt
and
http://ift.tt/2hcNeB0
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use
Topics:
1. (00:11) Introduction
2. (00:31) Method 1: Power Pivot Data Model & Create PivotTable dialog box with Data Model PivotTable
3. (02:34) Method 2: Array Formula with SUMIFS and SUMPRODUCT
4. (06:42) Method 3: VLOOKUP and Standard PivotTable
5. (08:10) Method 4: Power Pivot Data Model & Relationships with Data Model PivotTable
6. (11:05) Method 5: Power Query with Pivot, Merge and Group By features
7. (14:37) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 188
Excel Basics 12: Complete Formula Lesson of Formula Types & Formula Elements 12 Examples
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2iZNLH6
Download Full Written Notes: http://ift.tt/2z9Fzrs
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (00:30) Different Types of Formulas
3. (02:07) Formula Elements – what you can out into a formula.
4. (04:55) Formula 01: Goal: Add calls made a service center last week.: =SUM(C23:C29) . Number Formula.
5. (05:53) Formula 02: Goal: Calculate Monthly Insurance Expense.: =C37/12. Number Formula.
6. (06:25) Formula 03: Goal: Calculate Deduction for Each Employee.: =ROUND(C46*$F$46,2) . Number Formula.
7. (09:31) Formula 04: Goal: Calculate Cost of Goods Sold (COGS) in Accounting: =(C58-D58)*E58. Number Formula.
8. (13:17) Formula 05: Goal: Join “Text ” and an Item Number into One Cell.: =”Item # “&C70. Text Formula.
9. (17:02) Formula 06: Goal: Join First & Last Names into One Cell.: =C82&”, “&B82. Text Formula.
10. (20:43) Formula 07: Goal: Extract State Abbreviation using the RIGHT Function: =RIGHT(B96,2) . Text Formula.
11. (24:01) Formula 08: Goal: Extract Product Number using the LEFT Function: =LEFT(B108,6) . Text Formula.
12. (26:00) Formula 09: Goal: Determine If Debits = Credits: =B127=C127. Logical Formula.
13. (30:00) Formula 10: Goal: Determine If Employee Gets a Bonus: =C135 Greater Than =$F$135. Logical Formula.
14. (33:35) Formula 11: Goal: Count how many of each product we sold: =COUNTIFS($B$148:$B$153,B156). Number Formula.
15. (35:15) Formula 12: Goal: Formula to determine whether we need to reorder?: =$D$173 Greater Than B166-C166. Logical Formula.
16. (41:00) Formula 13: Goal: Calculate Net Income: =C181-SUM(C182:C185) . Number Formula.
17. (42:30) Summary – Conclusion