Three Excel tricks today: Fill in the empty cells when you have multiple row fields in a pivot table. Fill in the empty cells because there were not any sales. And Change the Pivot Table Defaults for all future pivot tables.
Month: February 2019
Excel Document Location – Episode 2258
An awesome trick from Candace & Robert in Topeka of how to copy the path to your workbook using Document Location on the Quick Access Toolbar in Excel.
Excel Fill Text On Subtotal Rows – Episode 2257
Revisiting a topic from Episode 1995 – how do you bring the customer name field down into the subtotal rows? Kimberly from Oklahoma City and Sarah from Omaha collaborate for a faster way.
Excel Fill Monday Weds Fri Dates – 2256
There is a well-known trick in Excel to fill just weekdays. But what if your work week is not Monday through Friday? Today, a trick with WORKDAY.INTL in Excel to fill Monday, Wednesday, Friday.
Excel Filter to Over $20K – Podcast 2255
An awesome tip from Kris and Taylor in New Mexico – how to use the AutoFilter to filter to all rows above $20K in Excel.
E-DAB 04: Excel Data Analysis & BI Basics: PivotTables & Slicers Create Dashboards & Summary Reports
Download Start Files: https://ift.tt/2T3OFDi
Download Finished Files: https://ift.tt/2EdyTM2
Pdf notes: https://ift.tt/2T0eiFf
This video teaches all the tricks of PivotTables from Slicers to Show Values As to Dashboards. Learn how to create Cross Tab Repots, Dashboards, Frequency Distributions from Text Data and a CPA Pass Rate Report.
This class : Data Analysis & Business Intelligence Made Easy with Excel Power Tools – Excel Data Analysis Basics = E-DAB Class – Sponsored by YouTube and taught by Mike Girvin, Highline College Instructor, Microsoft Excel MVP and founder of the excelisfun channel at YouTube. This is a free educational resource for people how want to learn about the Basics of Data Analysis and Business Intelligence using Microsoft Power Tools such as, PivotTables, Power Query, Power Pivot, Power BI Desktop and more.
Topics:
1. (00:12) Introduction
2. (01:35) What is a PivotTable? What is a Cross Tab Report.
3. (03:20) Build a Cross Tab Report with a PivotTable
4. (11:15) PivotTable Cached Data
5. (12:10) Building a Standard PivotTable as Part of Dashboard
6. (12:54) Standard PivotTable vs. Data PivotTable
7. (14:18) What is a Dashboard?
8. (17:20) Use Group By Feature to group Monthly and Yearly Amounts
9. (18:55) Summarize Values By to Change Aggregate Function
10. (19:35) Use Slicers to Filter Entire PivotTable
11. (23:21) Cell Phone Data Examples from Video to Build Frequency Distribution:
12. (24:18) Show Values As to Change to Specific Calculations
13. (25:20) CPA Data Examples from Video to Build CPA Pass Rate Report
14. (27:47) Practice Problems, Homework
15. (27:57) Summary
E-DAB 03: Excel Data Analysis & BI Basics: Excel Spreadsheet Formulas Old School? Or Dynamic Arrays?
Download Start Files: https://ift.tt/2tA7n6s
Download Finished Files: https://ift.tt/2GZKst9
Pdf notes: https://ift.tt/2tzeI6b
This video #3 in this calss teaches about when and why to use Excel Spreadsheet Formulas for Data Analysis. We compare and Contract Old School Formulas to the new Office 365 Dynamic Array Formulas. We learn about SUMIFS, COUNTIFS and AVERAGEIFS for making Data Analysis Summary Reports.:
This class : Data Analysis & Business Intelligence Made Easy with Excel Power Tools – Excel Data Analysis Basics = E-DAB Class – Sponsored by YouTube and taught by Mike Girvin, Highline College Instructor, Microsoft Excel MVP and founder of the excelisfun channel at YouTube. This is a free educational resource for people how want to learn about the Basics of Data Analysis and Business Intelligence using Microsoft Power Tools such as, PivotTables, Power Query, Power Pivot, Power BI Desktop and more.
Topics:
1. (00:11) Introduction
2. (01:24) Excel Spreadsheet Formulas Uses in Data Analysis
3. (03:42) Change Default Settings for Table Formula Nomenclature (Structured References).
4. (04:39) Cell References in Formulas.
5. (04:39) Excel Functions to Make Calculations Based on one Condition, four examples:
6. (04:39) SUMIFS Function with One Condition shown on next page in cell H13
7. (08:54) SUMIFS Function with One Condition & Dynamic Array shown on next page in cell K13
8. (15:21) COUNTIFS Function with One Condition shown on next page in cell H24
9. (16:39) COUNTIFS Function with One Condition & Dynamic Array shown on next page in cell K24
10. (17:40) Excel Functions to Make Calculations Based on an AND Logical Text.
11. (17:54) SUMIFS for Summary Report Based on Adding, Old School Cell References and Dynamic Arrays.
12. (24:32)AVERAGEIFS for Summary Report Based on Averaging, Old School Cell References and Dynamic Arrays.
13. (27:52) COUNTIFS for Summary Report Based on Counting, Old School Cell References and Dynamic Arrays.
14. (28:40) Homework sheets.
15. (28:58) Summary
E-DAB 02: Excel Data Analysis & BI Basics: Data, Proper Data Sets, Excel Tables, Logical Tests, More
Download Start Files: https://ift.tt/2BNhIjZ
Download Finished Files: https://ift.tt/2U1H2K4
Pdf notes: https://ift.tt/2BJxrRa
This video #2 in our class teaches about Data, Data Types, Proper Data Sets, Excel Tables, Soring, Filtering and Logical Tests.
This class : Data Analysis & Business Intelligence Made Easy with Excel Power Tools – Excel Data Analysis Basics = E-DAB Class – Sponsored by YouTube and taught by Mike Girvin, Highline College Instructor, Microsoft Excel MVP and founder of the excelisfun channel at YouTube. This is a free educational resource for people how want to learn about the Basics of Data Analysis and Business Intelligence using Microsoft Power Tools such as, PivotTables, Power Query, Power Pivot, Power BI Desktop and more.
Topics:
1. (00:12) Introduction
2. (01:14) What is Excel? What about the Data in the Cells?
3. (08:30) What is Data, Raw Data?
4. (10:01) Understanding the Difference Between Data & Information.
5. (10:45) Define Proper Data Set.
6. (in pdf notes) Why Proper Data Sets are Mandatory.
7. (14:25) Tables are not Charts
8. (14:50) Use Excel Tables For Dynamic Data. Learn about Excel Table Feature.
9. (20:00) Data Types in Proper Data Sets in Various Tools
10. (24:00) How to Sort Data.
11. (26:07) Filtering & Extracting Data.
12. (28:47) Understand & Use AND Logical Tests and OR Logical Tests
13. (34:35) Summary
E-DAB 01: Excel Data Analysis & BI Basics Class 01: What is Data Analysis & Business Intelligence?
Download Notes from Video: http://bit.ly/2V156wO
Full class web site: http://bit.ly/2EhGcnl
This video is an introduction to the : Data Analysis & Business Intelligence Made Easy with Excel Power Tools – Excel Data Analysis Basics = E-DAB Class – Sponsored by YouTube and taught by Mike Girvin, Highline College Instructor, Microsoft Excel MVP and founder of the excelisfun channel at YouTube. This is a free educational resource for people how want to learn about the Basics of Data Analysis and Business Intelligence using Microsoft Power Tools such as, PivotTables, Power Query, Power Pivot, Power BI Des
Topics:
1. (00:00) Intro Song
2. (00:12) Intro to First video in class
3. (01:36) Instructor
4. (01:59) Scope of Class
5. (02:40) Version of Excel
6. (03:30) Define Data Analysis & Business Intelligence
7. (06:00) Goals of Class
8. (09:31) Videos Topics Presented In Class
9. (10:33) Files for you to Download
10. (10:59) What You Will Gain After Taking This Class
11. (12:17) Summary
Excel Magic Trick 1543: MAXIFS & MINIFS to Lookup Numbers on Either Side of Hurdle
Download Excel Start Files: http://bit.ly/2EfpEfI
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs
In this video learn how to lookup numbers on either side of a hurdle using MAXIFS and MINIFS Functions which are available in Office 365 or Excel 2019. This trick comes from brianxyz at YouTube!
Original question:
I have 3, 6, 7, 13, 15, 18, 19 in a column and I want the first number greater and less than 11. (I want 7 in a cell and 13 in a different cell).
Excel Magic Trick 1542: Lookup First Numbers Greater Than Hurdle & Less Than Hurdle!?!
Download Excel Start Files: http://bit.ly/2N3rHGj
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs
In this video learn how to lookup numbers on either side of a hurdle. Lookup First Numbers Greater Than Hurdle & Less Than Hurdle!?!
Original question:
I have 3, 6, 7, 13, 15, 18, 19 in a column and I want the first number greater and less than 11. (I want 7 in a cell and 13 in a different cell).
Excel Magic Trick 1541: Add Sales Between Upper & Lower Date-Time with SUMIFS
Download Excel Start Files: http://bit.ly/2N3rHGj
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs
In this video learn how to Add Sales Between Upper & Lower Date-Time with SUMIFS. Learn about Date and Time Values in Excel.