Month: July 2017

Excel Magic Trick 1450: Replace VLOOKUP When Making PivotTables with Excel 2016 Relationships

Posted on by Annie D

Download File:
Start File: http://ift.tt/2hhJRrv
Finished File: http://ift.tt/2f2Lt7L
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

Learn how to use Relationships in Excel 2016 (or later versions) rather than the VLOOKUP Function when you are making PivotTable Reports from multiple tables. See how to create a Product / Region Yearly Report with a Slicer for Months. Relationship feature allows you to see Multiple Tables in PivotTable Field List and drag and drop fields to make your report. Also lean about the automatic Grouping Feature for PivotTables to group transactional dates into months and years.
Excel PivotTable Reports made easy with Relationships rather than VLOOKUP.

View on YouTube

Excel Magic Trick 1449: DAX Measure % Change MOM & YOY Formulas For Incomplete Years

Posted on by Annie D

Excel Magic Trick 1449: Why Standard PivotTable Show Values As % Change Does Not Work for Incomplete Years and How To Fix It with DAX Formulas
Download File:
Start File: http://ift.tt/2uqGsLB
Finished File: http://ift.tt/2vCmsEe
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Video shows how to create DAX Formulas for Change & % Change formulas for both Month Over Month and Year Over Year.
Extensive discussion about the hows and whys of how the formula calculates. In particular we discuss how Filter Context and Row Context Work together in SUMX to get the correct amount for each time period.
See how to
1. (00:10) Introduction
2. (00:40) Look at Change and % Change “Show Values As” Standard PivotTable Calculations and why they are not correct when you have incomplete years, like is often the case with Dashboards & Reports.
3. (01:53) First insights about what the DAX formula will be required to doing order to calculate the correct % Change.
4. (03:07) Look at Excel Tables and Data Model and first Measure that have already been created
5. (03:45) Start Data Model PivotTable
6. (04:28) Theory about calculating Sales Last Year
7. (04:43) Discussion about Filter Context and how DAX Measures evaluate / calculate. Discussion and Visuals about how Criteria in Row Area of PivotTable (also known as Filter Context) flows across relationships to create filter Sales Tables that have fewer rows than the original data set so that DAX Formulas can calculate more quickly on Big Data.
8. (05:23) Introduce CALCULATE DAX Function (functions that can change the Filter Context) and SAMEPERIODLASTYEAR DAX Function (generates a valid list of dates from last year’s period).
9. (05:42) First attempt at DAX Measure to calculate “Sales Last Year” using CALCULATE and SAMEPERIODLASTYEAR.
10. (07:11) Diagram of what the SAMPERIODLASTYEAR Function does to the Filter Context Criteria.
11. (07:43) Problem with first attempt at “Sales Last Year” DAX Measure: It shows values for periods that have no current sales, and Total for the last year in data set adds all 12 months from previous period.
12. (08:04) Introduction to SUMX (iterates a formula over a table and then adds the results).
13. (08:54) Second attempt at “Sales Last Year” using SUMX, VALUES and IF DAX Functions
14. (09:15) Introduction to VALUES DAX Function (yields a unique list of items for a column in the Current Filter Context).
15. (10:51) Error from SAMPERIODLASTYEAR and how to use IF and HASONEVALUE DAX Functions to fix it.
16. (12:01) Look at “Sales Last Year” Measure in PivotTable and see how it is yielding the correct answer
17. (12:35) “Sales Lat Year” Formula: Extended discussion with Visuals and Diagrams to understand how VALUES generates a different size table in each cell in the PivotTable and how SUMX iterates over the VALUES generated table to calculate the correct answer in each cell in the PivotTable.
18. (15:42) Problem with “Total Sales” formula when using it to calculate the change between this year and last year.
19. (16:34) “Comparison Total Sales” DAX Formula to calculate correct Total Sales for the Numerator in the “% Change” formula. See the functions, SUMX, VALUES and IF
20. (18:07) DAX Formula to calculate the amount of change in Sales from Last Year and This Year. “Change” Formula.
21. (19:04) “% Change” DAX Formula to calculate the % change between last year’s period and this year’s period. See DIVIDE DAX Function.
22. (20:14) Add New Data to Sales Table to see that formulas will adjust.
23. (21:41) Summary

View on YouTube

Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

Posted on by Annie D

Complete Introduction to Power BI Desktop. This video will teach you about how to use Power BI Desktop to clean, transform data and make a Dashboard to Monitor Sales. Take multiple Text files and import them into a single table which can be used for Reports and Dashboards and can be updated easily when new data arrives.
Download Files:
Zipped Folder file Text Files: http://ift.tt/2uczRVa
Excel File: http://ift.tt/2vEnQFN
Finished File: http://ift.tt/2uc5gXA
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the third video in a series of three videos.
Topics in Video:
1. (00:10) Download Files
2. (00:24) Video Introduction and review of Topics covered in video
3. (03:13) Introduction to Power BI Desktop
4. (04:15) Overview of process of using Power BI Desktop to build our specific Reports and Dashboards
5. (05:09) Open and Save Power BI File
6. (06:28) Import Excel dProduct Lookup Tables
7. (08:05) Import and Append Multiple Text Files with Sales Data
8. (15:11) Clean Data
9. (16:42) Discussion of Relationships and why they are important
10. (17:58) Create Calendar Table using DAX CALENDAR Table Function
11. (21:27) Create Calculated Columns with DAX Functions: MONTH, FORMAT and YEAR
12. (21:54) Create Relationships and discuss more about Relationships
13. (26:25) Save As to save Power BI File
14. (26:54) Create Cross Tabulated Power BI Report
15. (28:11) Show Values As % of Column Total
16. (30:35) Create Gross Profit Dash Board
17. (31:29) DAX Measure for Gross Profit %
18. (34:24) Column and Bar Chart as Slicer for Gross Profit Dashboard
19. (36:13) Dashboard Interactivity
20. (38:00) Add New Data And Watch Dashboard Update
21. (39:53) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM

View on YouTube

Excel for Accountants: Power Query & PivotTables to Import & Clean Data and Build Reports

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tB6Zp7
Zipped Folder file Text Files; http://ift.tt/2tI91nF
Finished File: http://ift.tt/2t6W9Uh
Finished Data Model File For Last Example: http://ift.tt/2tI0d0J
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the second video in a series of two videos.
This video will teach you about how to use Power Query and PivotTables to clean, transform data and make summary PivotTable Reports. Take multiple Text files and import them into a single table which can be used for PivotTable reports and can be updated easily when new data arrives.
Topics in Video:
(00:10) Introduction to video topics and Excel files and how to navigate large workbook and video
(01:19) Overview of all Power Query & PivotTable steps in the video, including looking at Text Files that we need to import.
38. (07:05) Power Query To Import and Append Multiple Text Files with Sales Data. See how to use the From Folder feature to import many Text Files into a Single Proper Data Set thatr you can then use for PivotTable Reports.
39. (14:26) Power Query To Clean Data. Clean imported data before it is loaded to an Excel Sheet or the Data Model.
40. (17:30) Power Query To Merge Lookup Tables With Sales Table. See how to use the Combine, Merge, Left Outer Merge Feature to replace the VLOOKUP Function.
41. (22:50) PivotTable Basics and Monthly Profit Change and % Change Report using Show Values As feature and the PivotTables Group by Month feature.
42. (33:43) Build Cross Tabulated Report With Slicer and learn how to use the “Show Report Filter Page” option to create many PivotTables, each on a new sheet with a single click.
43. (39:56) Gross Profit Report, including a Formula in a PivotTable (Calculated Field)
44. (44:00) Add New Data and Update Reports
45. (46:18) Excel 2013 or 2016 Data Model to Reduce File Size Dramatically
(50:24) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Vice President
Darlene Sondergaard
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM
Tags: Excel, Microsoft Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Excel For Accountants, Excel for Accounting, WAATP Excel, WAATP Excel seminar, Power Query, Get & Transform, PivotTables, Pivot Tables, Power Query in Accounting, PivotTables in Accounting, Clean Data in Excel, Import Multiple Text Files into Excel, Power Query to Import Multiple Text Files, Transform Data in Power Query, Merge Tables, Combine Tables, Left Outer Merge, Replace VLOOKUP with Merge Table, Gross Profit Pivot Table Report, Cross Tabulated Report, PivotTable, Monthly Revenue Report, Basics of PivotTables, Pivot Table Basics, Power Query Basics, Introduction to Power Query

View on YouTube

Excel for Accountants: Formulas, Functions & Formatting to Build Account Templates (37 Examples)

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tB6Zp7
Finished File:  http://ift.tt/2t6W9Uh
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

This is the first video in a series of two videos.
This video will teach you everything you need to know about Excel for your Accounting Job : )

Topics in Video:
(00:08) Introduction to video topics and Excel files and how to navigate large workbook and video
1. (02:13) Keyboards Rule Because They Are Fast
2. (04:33) Number Formatting is a Façade
3. (07:01) How to Add Quickly
4. (10:15) Date Calculation Templates
5. (10:50) TODAY and (19:50) EDATE functions
6. (12:24) Angry Rabbit Mouse Copy Trick
7. (14:46) Date Number Formatting
8. (17:05) Formulas for Date Calculations
9. (20:21) Absolute & Relative Cell References
10. (22:15) Excel’s Golden Rule
11. (24:46) Payroll Quickly
12. (24:46) ROUND Function Importance
13. (24:46) Formulas Can NOT See Number Formatting
14. (31:40) Create Cosumer Loan Amortization Table
15. (33:13) PMT Function to calculate PMT on Annuity with Regular PMT and Constant Rate
16. (36:44) Fill Series to generate list of numbners from 0 to 360
17. (38:51) Smart Tags to Fill End of Month Only (And it knows Leap Year!!!)
18. (40:01) Borders to enhance Report
19. (42:48) Calculations for Amortization Table with Relative and Absolute Cell References
20. (47:10) Invoice Template
21. (47:24) VLOOKUP Function
22. (52:40) Data Validation, List to add Drop-Down List to Cell
23. (54:32) IF Function
24. (54:32) ISBLANK Function
25. (24:46) ROUND Function (link back to earlier part of video)
26. (01:02:35) FV of Investment Template
27. (01:02:35) Mixed Cell References
28. (01:05:50) F4 Key to cycle through 4 different types of Cell References (Merry Go Round)
29. (01:08:05) FV Function
30. (01:11:00) SUMIFS and COUNTIFS to create Summary Reports. Also: Advantage of Formulas Over PivotTable.
31. (01:18:38) Check Book Register Example
32. (01:19:02) Border Formatting
33. (01:20) Formula with Relative Cell References to calculate Balance
34. (01:21:29) IF Function
35. (01:21:29) ISNUMBER Function
36. (01:23:44) 1) Data Validation For Reconciling Column. 2) Conditional Formatting for whole row using Mixed Cell References
37. (01:29:50) Flash Fill to extract and clean Data Quickly

Learn Excel Basics, Learn how to build Accounting Templates, Learn Excel Tricks for your Accounting Job.

View on YouTube

Excel Magic Trick 1447: DAX DISTINCTCOUNT & CONCATENATEX to Count & List Stores Visited

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tSGcWC
Finished File: http://ift.tt/2tSGcWC
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
See how to use the : DAX functions DISTINCTCOUNT & CONCATENATEX to Count & List Stores that Customers Have Visited.

View on YouTube

Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tSGcWC
Finished File: http://ift.tt/2tSGcWC
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

See how to use Power Query to analyze a group of customers who have been given coupons to three stores. Our goal is to count how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons from the Coupon Redemption Table) and then extract the customer names in each group. Learn about many Power Query features and functions.

This trick comes from Bill Szysz, Power Query Master, at YouTube.

The data and goals are:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe’s.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
5) Goal 2: List Customers who visited a given set of stores.
Topics:
1. (00:13) Introduction & Problem Introduction
2. (02:35) Import From Excel Table
3. (04:01) Left Outer Merge or Left Outer Join
4. (06:47) Reference another Query in Power Query
5. (07:24) Filter to show null
6. (07:49) Group By Feature to aggregate and to list all matching records for Customers who went to Zero Stores. Use Count Rows and All Rows.
7. (09:28) Edit Table.Group Function to show just the column and convert to a list rather than a table (Change table type to list type in Group By action).
8. (10:07) Underscore character in Power Query = All items in Row
9. (11:15) Start Code for final report
10. (11:40) Filter out nulls to avoid errors from inconsistent data type in column.
11. (12:06) Remove Duplicates
12. (12:25) Steps to get three columns, one for each Store and then list the stores that each customer visited: 1) Duplicate Column then Pivot.
13. Pivot Features to get a record for each Customer containing the store names that they visited
14. (14:00) Group By to Count Customers in each Coupon Group and List of Customers in Each group using Count Rows and All Rows.
15. (15:40) Second Time we Edit Table.Group Function to show just the column and convert to a list rather than a table (Change table type to list type in Group By action).
16. (16:02) Append Earlier Query to current query so that the count and list of customers that visited no stores is listed in the final report.
17. (16:34) Edit Previous Query to update current query.
18. (17:26) Add Custom Column to Convert the Record to a List. This lists created for each row will be the columns in the final report. In this step see the Power Query Functions: Record.ToList, List.RemoveLastN. Also see how join the record to a columns and add a null row.
19. (20:40) Create a column that represents the Field Names (Column Names / Headers) in the final Table/Report. See how to add a column as an Index and then add a Prefix.
20. (21:31) Edit M Code using Advanced Editor and add a line of code that uses the Table.FromColumns Function to combine the Columns and Headers into a table.
21. (24:19) Check Query Report by adding store to transaction table
22. ( Edit Code to remove hard coded values by using the List.Distinct Power Query Function
23. (26:35) Look at Previous Steps in the Power Query Editor to check if the edited code will be correctly reflected and to verify if all stores are listed.
24. (26:08) Summary

Related Videos:
Excel Magic Trick 1444: Logical Formulas: Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1444 Part 2: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1447: DAX Formulas to Count Customers in 8 Sales Coupon Groups & List Store Names

View on YouTube

Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups

Posted on by Annie D

See how to use Power Query to analyze a group of customers who have been given coupons to three stores. Our goal is to count how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons from the Coupon Redemption Table) and then extract the customer names in each group. Learn about many Power Query features and functions. This trick comes from Bill Szysz, Power Query Master, at YouTube. The data and goals are:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe’s.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
5) Goal 2: List Customers who visited a given set of stores.
Download File:
Start File: http://ift.tt/2tSGcWC
Finished File: http://ift.tt/2tSGcWC
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Topics:
1. (00:13) Introduction & Problem Introduction
2. (02:35) Import From Excel Table
3. (04:01) Left Outer Merge or Left Outer Join
4. (06:47) Reference another Query in Power Query
5. (07:24) Filter to show null
6. (07:49) Group By Feature to aggregate and to list all matching records for Customers who went to Zero Stores. Use Count Rows and All Rows.
7. (09:28) Edit Table.Group Function to show just the column and convert to a list rather than a table (Change table type to list type in Group By action).
8. (10:07) Underscore character in Power Query = All items in Row
9. (11:15) Start Code for final report
10. (11:40) Filter out nulls to avoid errors from inconsistent data type in column.
11. (12:06) Remove Duplicates
12. (12:25) Steps to get three columns, one for each Store and then list the stores that each customer visited: 1) Duplicate Column then Pivot.
13. Pivot Features to get a record for each Customer containing the store names that they visited
14. (14:00) Group By to Count Customers in each Coupon Group and List of Customers in Each group using Count Rows and All Rows.
15. (15:40) Second Time we Edit Table.Group Function to show just the column and convert to a list rather than a table (Change table type to list type in Group By action).
16. (16:02) Append Earlier Query to current query so that the count and list of customers that visited no stores is listed in the final report.
17. (16:34) Edit Previous Query to update current query.
18. (17:26) Add Custom Column to Convert the Record to a List. This lists created for each row will be the columns in the final report. In this step see the Power Query Functions: Record.ToList, List.RemoveLastN. Also see how join the record to a columns and add a null row.
19. (20:40) Create a column that represents the Field Names (Column Names / Headers) in the final Table/Report. See how to add a column as an Index and then add a Prefix.
20. (21:31) Edit M Code using Advanced Editor and add a line of code that uses the Table.FromColumns Function to combine the Columns and Headers into a table.
21. (23:40) Thanks to Bill Szysz
22. (24:08) Summary

Related Videos:
Excel Magic Trick 1444: Logical Formulas: Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1444 Part 2: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1447: DAX Formulas to Count Customers in 8 Sales Coupon Groups & List Store Names

View on YouTube

Excel Magic Trick 1448: Conditional Formatting Formulas: Cell References To “Jump” To Next Template

Posted on by Annie D

Download File: http://ift.tt/2smLVzP
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
See how to create Conditional Formatting for Truck Arrival Dates that shows Blue when truck in unloaded, green when it less than 2 days since arrival, yellow when it is 2 days since arrival and red if it has been more than 2 days since arrival. BUT: the main point on this video is to learn how to use the OFFSET Function, MOD and ROWS functions to create Conditional Formatting Logical Formulas that can be copied to a new template and always see the correct date at the top of the template. This means the conditional formatting can be copied to the side of below and it will always know to read the date at the top of the current Template.

View on YouTube

Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups

Posted on by Annie D

See how to use Excel Formulas to analyze a group of customers who have been given coupons to three stores. Our goal is to create a single cell Array Formula that counts how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons). See the Excel Spreadsheet Functions: SUM, MMULT, ROW, COUNTIFS and ISTEXT. Learn about how Array Formulas Work. Outline:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe’s.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
Download File: http://ift.tt/2tvSeEE
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Topics in Video:

Related Videos:
Excel Magic Trick 1444: Logical Formulas: Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1444 Part 2: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1447: DAX Formulas to Count Customers in 8 Sales Coupon Groups & List Store Names

View on YouTube

Excel Magic Trick 1444 Part 2: Count Customer Names for 8 Sales Coupon Groups

Posted on by Annie D

See how to use Excel Formulas to analyze a group of customers who have been given coupons to three stores. Our goal is to count how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons) and then extract the customer names in each group. The data and goals are:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe’s.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
This Part 02 Video is an alternative method for counting from Tobias de Greeuw from the Netherlands. This alternative version we use the functions COUNTIFS, IF and TRANSPOSE. This alternative eliminates the need for eight separate Logical Formulas and simplifies the process by using an Array Formula.
Download File: http://ift.tt/2t6hC34
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

Related Videos:
Excel Magic Trick 1444: Logical Formulas: Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1444 Part 2: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1447: DAX Formulas to Count Customers in 8 Sales Coupon Groups & List Store Names

View on YouTube