Download Files: Start Excel File: http://ift.tt/2ACcTLV
Download Files: Finished Excel File: http://ift.tt/2yoi8ZB
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to
In this video learn how to use Array Formulas to automate the reporting process for Rain Totals where the staring time for a day period changes.
Topics:
1. (00:01) Introduction and description of reporting requirements
2. (02:08) Date and Time Number Formatting and Time Formula for Time Equivalent for Date Start Time.
3. (03:32) Count Unique Date Array Formula. See the functions FREQUENCY, SUM and INT. See two versions of this formula. One version converts positive numbers with the Logical Test Greater than Zero and a Double Negative. The other uses the SIGN Function.
4. (13:34) Array Formula to Extract a List of Unique Dates that assumes that we may not have a complete list of sequential dates, and there is a variable Start Time. See the functions AGGREGATE, ROW, ROWS, IF, SIGN, FREQUENCY, SUM and INT.
5. (26:02) Formula to add rain totals using SUMIFS & IF Functions and criteria based on the start date-time.
6. (28:39) Array Formula to Extract a List of Unique Dates that assumes we HAVE a complete list of sequential dates. See the functions, AGGREGATE, IF, ROWS and INT.
7. (34:09) Re-organize Order of Logical Tests in IF Function to create a more efficient formula.
8. (36:03) Add new data and test formulas and reporting system.
9. (36:50) Summary
Related Videos:
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Excel Magic Trick 1470: Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day
Tag: Productivity
Excel Magic Trick 1470: Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day
Download Files: Start Excel File: http://ift.tt/2BT9DI6
Download Files: Text Files for Import: http://ift.tt/2ALvzpk
Download Files: Finished Excel File: http://ift.tt/2BUj1eu
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to take a rainfall data set with Time-Date Stamps and add rain totals for each day and have the ability to change the definition of the 24 hour period by subtracting an Hour Increment. See how to use Power Query (Get & Transform) to import Multiple Text Files and create a Custom Column and use the Group By feature to create the final report. Then see that the report will update when we get new Text Files.
Topics:
1. (00:11) Introduction and description of process of Importing Text Files, Creating an Excel Parameter, Create Custom Column, Group By to create Report and Update after new Text Files arrive.
2. (02:08) Look at Text Files
3. (02:46) Create a Parameter in Excel and Import it into Power Query
4. (06:03) Importing Text Files using From Folder feature
5. (09:39) Look at Custom Function that Power Query automatically creates
6. (11:35) Convert Dates to Decimal Numbers so we can use Column in a Formula
7. (12:42) Create Custom Column to subtract Start Time
8. (13:44) Remove Other Columns and Rearrange Columns
9. (14:15) Use Group By feature in Power Query and compare and contrast PivotTable in Excel to Group By in Power Query
10. (15:55) Load Report to Excel Sheet
11. (16:35) Change Parameter in Excel and refresh Power Query Report
12. (17:06) Load new Files to Folder and Refresh Power Query Report
13. (18:43) Verify that Excel Files NOT imported
14. (19:13) If Folder Path Chages, Change it in Source Step
15. (19:54) Summary
Related Videos:
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Excel Magic Trick 1471: Add Daily Rainfall 5 AM to 5 AM Next Day: Array Formula (Most Automatic)
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Download Files: http://ift.tt/2iF8Tie
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to take a rainfall data set with Time-Date Stamps and add rain totals for each day and have the ability to change the definition of the 24 hour period.
Topics:
1. (00:11) Introduction to data set and the task at hand
2. (01:30) Look at Time Values and determine what Time Value should be subtracted from each Date-Time to get correct category.
3. (03:32) Add Data Validation List to Time Cell so we only subtract the correct whole hour.
4. (04:55) Helper Column Formula to subtract hour amount. See Relative Cell References and Absolute Cell Reference. See the INT Function.
5. (06:47) Create PivotTable
6. (08:05) What happens if we change Number of Hours to Subtract? PivotTable MUST be refreshed
7. (08:35) Summary
Related Videos:
Excel Magic Trick 1470: Add Daily Rainfall 5 AM to 5 AM Next Day: Power Query
Excel Magic Trick 1471: Add Daily Rainfall 5 AM to 5 AM Next Day: Array Formula (Most Automatic)
Office 2016 Video #42: Access 2016: Export Tables, Queries & Reports to Excel, PDF or Text File
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download files: http://ift.tt/2jxY9m1
Learn how to export Tables, Queries and Reports From Access 2016 to Excel, Text File or PDF File.
Office 2016 Video #40: Access 2016: Building Queries in Access (15 Examples)
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download files: http://ift.tt/2jxY9m1
Topics in this Video:
1. (00:01) Introduction
2. (01:20) What is a Query?
3. (02:10) Query 01 Description and Sell Price
4. (06:50) Query 02 Product Records From SC
5. (08:40) Query 03 On Hand = 10
6. (09:57) Query 04 Sell Price $10 AND On Hand 8 (AND Logical Test)
7. (12:40) Query 05 Price Between $5 And $10 (AND Logical Test)
8. (16:05) Query 06 Sell Price 5 AND 10 (AND Logical Test)
9. (18:03) Rename Queries
10. (19:15) Query 07 Supplier Code Toy Records SC Or AP (OR Logical Test)
11. (20:43) Query 08 Ave Sell Price (Aggregate Calculation)
12. (22:28) Query 09 Ave Sell Price Group By Supplier
13. (23:17) What is Group By?
14. (24:23) Query 10 Min & Max Sell Price Group By Supplier
15. (25:18) Query 11 Inventory Value (Formula or Expression)
16. (28:50) Query 12 Gross Profit for each Product (Formula or Expression)
17. (30:40) Query 13 Products that Contain *rail* (Contains Query)
18. (32:20) Query 14Product Re-Order Phone List (Query based on Two Tables)
19. (35:04) Query 15 Records by Supplier (Parameter Query)
20. (36:45) Summary
Office 2016 Video #41: Access 2016: Report Wizard to Create Reports Based on Queries
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download files: http://ift.tt/2jxY9m1
Topics in this Video:
1. (00:01) Introduction
2. (01:22) Report Wizard to Create Inventory Value Report
3. (06:44) Report Wizard to Create Product Re-order Phone List Report
4. (12:26) Summary
Office 2016 Video #39: Access 2016: Create Database, Tables & Relationships, Import Excel Table too
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download files: http://ift.tt/2jxY9m1
Topics in this Video:
1. (00:01) Introduction
2. (01:56) Create New Database
3. (03:52) Save and Default Table (we’ll edit later) and talk about data that goes into table.
4. (05:36) Import SupplierData Table from an Excel
5. (08:08) Data Types & Field Properties for SupplierData Table
6. (18:48) Create a Product Table and add Data Types and Field Properties
7. (26:00) Data Type Lookup Wizard to Create a One-To-Many Relationship
8. (28:55) Data Type Lookup Wizard to Two Item Lookup
9. (30:43) Relationship Window to add “Referential Integrity”
10. (32:56) Create a Form for the Products Table
11. (34:54) Test Data Validation of Data Types and Field Properties
12. (34:54) Add Raw Data using Products Form
13. (39:41) Summary
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
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.