Tag: How to

Excel & Business Math 02: Copying Formulas with Relative & Absolute Cell References

Posted on by Annie D

Excel & Business Math 02: Copying Formulas with Relative & Absolute Cell References
Excel & Business Math Class taught by Michael Girvin at Highline College.
Download Excel File: http://ift.tt/2BOo4xC
Download pdf Notes: http://ift.tt/2CffafT
Entire Class Web Site: http://ift.tt/2BjafuY

Topics in Video:

In this video learn about how to use Relative and Absolute Cell References in formulas that you want to copy. See four examples that teach you the difference between Relative and Absolute Cell References.

Next Video: Excel & Business Math 03

View on YouTube

Excel Magic Trick 1473: Sequential Number Incrementor for Just The Middle Number: AA-0009-17

Posted on by Annie D

Download Files: http://ift.tt/2BAGcz3
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to create a formula to create a product code where the middle part of the code must increment as it is copied down a column. See the Functions ROWS and TEXT. ROWS is good for incrementing numbers inside formulas – it is better than the ROW function because it allows structural changed to the spreadsheet whereas ROW Function does not. Also see the TEXT Function to format numbers with Custom Number Formatting and convert them to text.

View on YouTube

Excel Magic Trick 1474: Excel Twinkling Christmas Tree With Star & Formulas as Presnets!

Posted on by Annie D

Download Files: http://ift.tt/2iF8Tie
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to create a Twinkling Christmas Tree with a Star on Top in Excel using the RAND and CHAR functions, the Conditional Formatting Scales and Conditional Formatting Icons.
Happy Holidays from excelifun!

View on YouTube

Excel Magic Trick 1472: Count Between (or Above and Below) Upper & Lower Limits (7 Examples)

Posted on by Annie D

Download Files: http://ift.tt/2BAGcz3
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to
Topics:
1. (00:11) Introduction
2. (01:58) Ex 1: Between Upper & Lower Limits: =COUNTIFS Function
3. (04:45) Ex 2: Between Upper & Lower Limits: =DCOUNT Function
4. (07:00) Ex 3: Between Upper & Lower Limits: =COUNTIFS so we can copy down a column
5. (09:44) Ex 4: Above & Below an Upper and Lower Limit: Two COUNTIFS
6. (13:33) Ex 5: Above & Below an Upper and Lower Limit: DCOUNT Function
7. (14:22) Ex 6: Above & Below an Upper and Lower Limit: SUMPRODUCT & COUNTIFS in an array formula
8. (19:50) Ex 7: Above & Below an Upper and Lower Limit: Two COUNTIFS so we can copy down the column
9. (21:09) Summary
COUNTIFS Count Between Upper and Lower Limits
COUNTIFS Count Above and Below Upper and Lower Limits
Counting With Criteria
Counting With Conditions

View on YouTube

Excel & Business Math 01: Introduction to Excel. Why We Use Excel for Math. First Formula.

Posted on by Annie D

Excel & Business Math Class taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Download Excel File: http://ift.tt/2Bja2Ic
Download pdf Notes: http://ift.tt/2z0ULFQ
Entire Class Web Site: http://ift.tt/2BjafuY
Topics in Video:
1. (00:01) Introduction
2. (00:34) Class Web Site, Class Files & USB Drive to store files from class
3. (01:56) Download Excel & pdf Files. Look at different Browsers: Chrome, Internet Explorer, Mozilla
4. (03:05) Create Folder & Save Files using Save As & Windows Explorer
5. (05:49) Why we use Excel for Business Math
6. (09:46) Conventions for Class
7. (13:18) What does Excel do?
8. (14:35) Excel Workbook Layout
9. (17:38) Move Between Sheets
10. (19:00) Zoom in and out = Ctrl + Roll Wheel on Mouse
11. (20:00) Ribbon Tabs
12. (20:38) QAT – Quick Access Toolbar
13. (21:20) First Excel Formula: Total Sales
14. (31:33) Selecting a Range with the Selection Cursor
15. (31:40) Number Formatting as a Façade to display numbers in a different way that the Actual Number in the cell
16. (34:06) Second Excel Formula: Net Income. Also see how to copy a formula to the side (not down a column)
17. (35:32) Verify that Number Formatting is NOT misleading
18. (36:52) Review PDF Notes ()
19. (37:52) Summary

Next Video: Excel & Business Math 02

View on YouTube

Excel Magic Trick 1471: Array Formula: Add Daily Rainfall 5 AM to 5 AM Next Day: (Most Automatic)

Posted on by Annie D

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

View on YouTube

Excel Magic Trick 1470: Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day

Posted on by Annie D

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)

View on YouTube

Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable

Posted on by Annie D

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)

View on YouTube

Office 2016 Video #42: Access 2016: Export Tables, Queries & Reports to Excel, PDF or Text File

Posted on by Annie D

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.

View on YouTube

Office 2016 Video #40: Access 2016: Building Queries in Access (15 Examples)

Posted on by Annie D

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

View on YouTube

Office 2016 Video #41: Access 2016: Report Wizard to Create Reports Based on Queries

Posted on by Annie D

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

View on YouTube

Office 2016 Video #39: Access 2016: Create Database, Tables & Relationships, Import Excel Table too

Posted on by Annie D

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

View on YouTube