Indique um curso   |   Siga nas redes sociais
Menu
Entrar / Criar conta

Entre para salvar cursos, acompanhar seu progresso e retomar seus estudos.

Entrar

Novo usuário? Criar Conta

Entrar / Criar conta
  • Desenvolvimento
    • Aplicativos móveis
      • Google Flutter
      • Ionic
      • Java Android
      • Kotlin
      • React Native
      • Swift
    • Banco de Dados
      • AWS Glue
      • AWS Lambda
      • Certificação em Oracle
      • Gestão de banco de dados
      • Modelagem de dados
      • MySQL
      • Oracle Database
      • Oracle SQL
      • SQL
      • SQL Server
    • Data Science
      • Análise de dados
    • Desenvolvimento de Games
      • Blender
      • Programação em Scratch
      • Unity
      • Unreal Engine
    • Desenvolvimento Web
      • Angular
      • AWS Cloud Development Kit (CDK)
      • CSS
      • Django
      • DotNet
      • HTML
      • JavaScript
      • PHP
      • React
      • VueJs
      • WordPress
    • E-Commerce
      • Magento
      • Majento
      • Negócios Online
      • Shopify
      • Site de Afiliação
      • WooCommerce
    • Ferramentas de Desenvolvimento
      • AWS CodePipeline
      • AWS Kiro
      • Confluence
      • DevOps
      • Docker
      • Entity Framework
      • Git
      • Jenkins
      • JIRA
      • Kubernetes
      • Lovable
    • Inteligência Artificial
      • Agentes de IA
      • Aplicações de IA
      • Engenharia de prompt
      • Fundamentos de inteligência artificial
      • IA generativa
      • Modelos de linguagem (LLMs)
    • Linguagens de programação
      • Algoritmos em Java
      • c plus plus
      • C#
      • Framework Spring
      • Go (linguagem de programação)
      • Java
      • Laravel
      • Node.Js
      • Programação em C#
      • Programação orientada a objetos
      • Python
      • Ruby on Rails
    • Outros
      • Logica de Programação
    • Teste de Software
      • Appium
      • Java
      • Python
      • Rest API
      • Selenium Testing Framework
      • Selenium WebDriver
      • Teste de API
      • Testes de automação
  • Desenvolvimento pessoal
    • AutoEstima
      • Autoconfiança
      • Autoestima
      • Inteligência emocional
    • Criação de Marketing pessoal
      • LinkedIn
      • Marca pessoal
      • Networking
    • Criatividade
      • Escrita criativa
      • Inovação
      • Pensamento criativo
    • Desenvolvimento de Carreira
      • Currículo profissional
      • Entrevista de emprego
      • Planejamento de carreira
    • Felicidade
      • Bem-estar
      • Gratidão
      • Psicologia positiva
    • Gestão de estresse
      • Ansiedade
      • Controle do estresse
      • Resiliência
    • Habilidades de memória e estudo
      • Leitura rápida
      • Memorização
      • Técnicas de estudo
    • Influência
      • Comunicação assertiva
      • Negociação
      • Persuasão
    • Liderança
      • Gestão de equipes
      • Habilidades de gestão
      • Liderança
    • Maternidade/Paternidadee relacionamentos
      • Educação dos filhos
      • Parentalidade
      • Relacionamentos saudáveis
    • Motivação
      • Disciplina
      • Hábitos
      • Motivação pessoal
    • Práticas esotéricas
      • Astrologia
      • Numerologia
      • Tarô
    • Produtividade
      • Definição de metas
      • Foco e concentração
      • Gestão do tempo
    • Religião e espiritualidade
      • Bíblia
      • Espiritualidade
      • Teologia
  • Design
    • 3D e animação
      • Autodesk Fusion
    • Design gráfico
      • Illustrator
      • InDesign
    • Web Design
      • Angular
      • Bootstrap
      • FlexBox
      • Photoshop
      • UI-UX
  • Ensino e estudo acadêmico
    • Educação online
      • Google Classroom
      • Google Sala de Aula
    • Idiomas
      • Alemão
      • Inglês
      • Latim
      • Libras
    • Treinamento de professores
      • Camtasia
  • Estilo de vida
    • Alimentos e bebidas
      • Culinária
      • Produção de Cerveja
    • Artes e artesanato
      • Desenho
    • Beleza e maquiagem
      • Beleza
      • Maquiagem
    • Cuidados com animais e adestramento
      • Adestramento de cães
    • Jogos
      • Xadrez
  • Finanças e Contabilidade
    • Contabilidade
      • Contabilidade
      • Demonstrações financeiras
      • Escrituração contábil
      • Fundamentos de contabilidade
    • Criptomoedas
      • Bitcoin
      • Blockchain
      • Trading de criptomoedas
    • Economia
      • Economia comportamental
      • Macroeconomia
      • Microeconomia
    • Ferramentas de gerenciamento de dinheiro
      • Gestão de dívidas
      • Orçamento pessoal
      • Planejamento financeiro pessoal
    • Finanças
      • Finanças corporativas
      • Finanças pessoais
      • Gestão financeira
    • Impostos
      • Contabilidade tributária
      • Imposto de renda
      • Planejamento tributário
    • Investimentos e negociação
      • Análise técnica
      • Day trade
      • Forex
      • Investimentos
      • Mercado de ações
    • Preparação para exame e certificação financeira
      • Certificações financeiras
      • CFA
      • CPA e CEA
  • Marketing
    • Marketing de redes sociais
      • Canva
      • Instagram
    • Marketing Digital
      • Google Ads
      • Google Analytcs
    • Otimização de mecanismos de busca (SEO)
      • Auditoria de SEO
  • Negócios
    • Administração
      • Agile
      • Gestão de processos de negócios
      • Gestão de produtos
      • Habilidades de gestão
      • ISO 9001
      • Liderança
      • PMI-ACP
      • PMP
    • Dados e Análises
      • Análise de dados
      • Análise de negócios
      • Análises no Excel
      • Aprendizado profundo
      • Data Science
      • Inteligência artificial
      • Machine Learning
      • Python
      • R
      • SQL
    • Empreendedorismo
      • Amazon FBA
      • Blogging
      • Estratédia de negócios
      • Fundamento dos negócios
      • Fundamentos do empreendedorismo
      • Impressão para camisetas
      • Plano de negócios
      • Startup
      • Trabalhar como freelancer
    • Vendas
      • Anúncios do Facebook
      • Desenvolvimento de negócios
      • Dropshipping
      • Estratégia de marketing
      • Funil de vendas
      • Geração de leads
      • Habilidades em vendas
      • Linkedin
      • Prospecção telefônica
      • Vendas B2B
  • Produtividade no escritório
    • Apple
      • macOS
    • Google
      • Aplicativos de produtividade
      • Google Drive
    • Microsoft
      • Excel
      • Pacote Office
      • Power Bi
      • Word
    • Oracle
      • Oracle Business Intelligence
    • SAP
      • SAP
  • TI e software
    • Certificação em TI
      • AWS Certified AI Business Strategist (AIB-C01)
      • AZ-104: Microsoft Azure Administrator Associate
      • AZ-305: Microsoft Azure Solutions Architect Expert
      • Certificação em AWS
      • Cisco CCNA
      • Microsoft Azure
    • Hardware
      • Arduino
      • Raspberry
      • Raspberry Pi
    • Outros
      • Amazon AWS
    • Rede e segurança
      • Hacking Ético
      • Monitoramento de rede
    • Sistemas Operacionais
      • AZ-140: Microsoft Azure Virtual Desktop Specialty
      • Linux
  • Desenvolvimento
    • Aplicativos móveis
      • Google Flutter
      • Ionic
      • Java Android
      • Kotlin
      • React Native
      • Swift
    • Banco de Dados
      • AWS Glue
      • AWS Lambda
      • Certificação em Oracle
      • Gestão de banco de dados
      • Modelagem de dados
      • MySQL
      • Oracle Database
      • Oracle SQL
      • SQL
      • SQL Server
    • Data Science
      • Análise de dados
    • Desenvolvimento de Games
      • Blender
      • Programação em Scratch
      • Unity
      • Unreal Engine
    • Desenvolvimento Web
      • Angular
      • AWS Cloud Development Kit (CDK)
      • CSS
      • Django
      • DotNet
      • HTML
      • JavaScript
      • PHP
      • React
      • VueJs
      • WordPress
    • E-Commerce
      • Magento
      • Majento
      • Negócios Online
      • Shopify
      • Site de Afiliação
      • WooCommerce
    • Ferramentas de Desenvolvimento
      • AWS CodePipeline
      • AWS Kiro
      • Confluence
      • DevOps
      • Docker
      • Entity Framework
      • Git
      • Jenkins
      • JIRA
      • Kubernetes
      • Lovable
    • Inteligência Artificial
      • Agentes de IA
      • Aplicações de IA
      • Engenharia de prompt
      • Fundamentos de inteligência artificial
      • IA generativa
      • Modelos de linguagem (LLMs)
    • Linguagens de programação
      • Algoritmos em Java
      • c plus plus
      • C#
      • Framework Spring
      • Go (linguagem de programação)
      • Java
      • Laravel
      • Node.Js
      • Programação em C#
      • Programação orientada a objetos
      • Python
      • Ruby on Rails
    • Outros
      • Logica de Programação
    • Teste de Software
      • Appium
      • Java
      • Python
      • Rest API
      • Selenium Testing Framework
      • Selenium WebDriver
      • Teste de API
      • Testes de automação
  • Desenvolvimento pessoal
    • AutoEstima
      • Autoconfiança
      • Autoestima
      • Inteligência emocional
    • Criação de Marketing pessoal
      • LinkedIn
      • Marca pessoal
      • Networking
    • Criatividade
      • Escrita criativa
      • Inovação
      • Pensamento criativo
    • Desenvolvimento de Carreira
      • Currículo profissional
      • Entrevista de emprego
      • Planejamento de carreira
    • Felicidade
      • Bem-estar
      • Gratidão
      • Psicologia positiva
    • Gestão de estresse
      • Ansiedade
      • Controle do estresse
      • Resiliência
    • Habilidades de memória e estudo
      • Leitura rápida
      • Memorização
      • Técnicas de estudo
    • Influência
      • Comunicação assertiva
      • Negociação
      • Persuasão
    • Liderança
      • Gestão de equipes
      • Habilidades de gestão
      • Liderança
    • Maternidade/Paternidadee relacionamentos
      • Educação dos filhos
      • Parentalidade
      • Relacionamentos saudáveis
    • Motivação
      • Disciplina
      • Hábitos
      • Motivação pessoal
    • Práticas esotéricas
      • Astrologia
      • Numerologia
      • Tarô
    • Produtividade
      • Definição de metas
      • Foco e concentração
      • Gestão do tempo
    • Religião e espiritualidade
      • Bíblia
      • Espiritualidade
      • Teologia
  • Design
    • 3D e animação
      • Autodesk Fusion
    • Design gráfico
      • Illustrator
      • InDesign
    • Web Design
      • Angular
      • Bootstrap
      • FlexBox
      • Photoshop
      • UI-UX
  • Ensino e estudo acadêmico
    • Educação online
      • Google Classroom
      • Google Sala de Aula
    • Idiomas
      • Alemão
      • Inglês
      • Latim
      • Libras
    • Treinamento de professores
      • Camtasia
  • Estilo de vida
    • Alimentos e bebidas
      • Culinária
      • Produção de Cerveja
    • Artes e artesanato
      • Desenho
    • Beleza e maquiagem
      • Beleza
      • Maquiagem
    • Cuidados com animais e adestramento
      • Adestramento de cães
    • Jogos
      • Xadrez
  • Finanças e Contabilidade
    • Contabilidade
      • Contabilidade
      • Demonstrações financeiras
      • Escrituração contábil
      • Fundamentos de contabilidade
    • Criptomoedas
      • Bitcoin
      • Blockchain
      • Trading de criptomoedas
    • Economia
      • Economia comportamental
      • Macroeconomia
      • Microeconomia
    • Ferramentas de gerenciamento de dinheiro
      • Gestão de dívidas
      • Orçamento pessoal
      • Planejamento financeiro pessoal
    • Finanças
      • Finanças corporativas
      • Finanças pessoais
      • Gestão financeira
    • Impostos
      • Contabilidade tributária
      • Imposto de renda
      • Planejamento tributário
    • Investimentos e negociação
      • Análise técnica
      • Day trade
      • Forex
      • Investimentos
      • Mercado de ações
    • Preparação para exame e certificação financeira
      • Certificações financeiras
      • CFA
      • CPA e CEA
  • Marketing
    • Marketing de redes sociais
      • Canva
      • Instagram
    • Marketing Digital
      • Google Ads
      • Google Analytcs
    • Otimização de mecanismos de busca (SEO)
      • Auditoria de SEO
  • Negócios
    • Administração
      • Agile
      • Gestão de processos de negócios
      • Gestão de produtos
      • Habilidades de gestão
      • ISO 9001
      • Liderança
      • PMI-ACP
      • PMP
    • Dados e Análises
      • Análise de dados
      • Análise de negócios
      • Análises no Excel
      • Aprendizado profundo
      • Data Science
      • Inteligência artificial
      • Machine Learning
      • Python
      • R
      • SQL
    • Empreendedorismo
      • Amazon FBA
      • Blogging
      • Estratédia de negócios
      • Fundamento dos negócios
      • Fundamentos do empreendedorismo
      • Impressão para camisetas
      • Plano de negócios
      • Startup
      • Trabalhar como freelancer
    • Vendas
      • Anúncios do Facebook
      • Desenvolvimento de negócios
      • Dropshipping
      • Estratégia de marketing
      • Funil de vendas
      • Geração de leads
      • Habilidades em vendas
      • Linkedin
      • Prospecção telefônica
      • Vendas B2B
  • Produtividade no escritório
    • Apple
      • macOS
    • Google
      • Aplicativos de produtividade
      • Google Drive
    • Microsoft
      • Excel
      • Pacote Office
      • Power Bi
      • Word
    • Oracle
      • Oracle Business Intelligence
    • SAP
      • SAP
  • TI e software
    • Certificação em TI
      • AWS Certified AI Business Strategist (AIB-C01)
      • AZ-104: Microsoft Azure Administrator Associate
      • AZ-305: Microsoft Azure Solutions Architect Expert
      • Certificação em AWS
      • Cisco CCNA
      • Microsoft Azure
    • Hardware
      • Arduino
      • Raspberry
      • Raspberry Pi
    • Outros
      • Amazon AWS
    • Rede e segurança
      • Hacking Ético
      • Monitoramento de rede
    • Sistemas Operacionais
      • AZ-140: Microsoft Azure Virtual Desktop Specialty
      • Linux
  • Home
  • Negócios
  • Dados e Análises
  • Microsoft Excel
  • Excel 2016 Intermediate to Advanced Free Course. Comprehensive Calculations & Data Analysis.

Excel 2016 Intermediate to Advanced Free Course. Comprehensive Calculations & Data Analysis.

Canal: excelisfun

GRATUITO

Inglês

Reportar problema neste curso (abre em nova aba)

Sobre o curso

1) Develop Effective and Efficient and solutions in Excel. 2) Use Microsoft Excel to solve typical business related problems with an emphasis on: 1) Making effective and efficient numeric, logical and text calculations and 2) effectively and efficiently converting raw data into useful information. 3) Understand Data Analysis and Business Intelligence terms and use data analysis concepts to create useful information from raw data: 4) Understanding the structure of an excel file as: Cells, Worksheets, Sheet Tabs and Workbooks 5) Using keyboards to accomplish tasks quickly 6) Understand and Utilize Number Formatting as Façade 7) Use Style Formatting, Number Formatting and Page Setup to present information in an effect way. 8) Use Proper Data Sets and correct Data Types to leverage Excel’s full capabilities 9) Create Effective and Efficient Formulas that can make numeric, logical and text calculations and perform data analysis, including extensive coverage of types of formulas, formula elements, lookup formulas, array formulas and much more. 10) Preform Data Analysis and Business Intelligence with Excel’s Built in Data Analysis feature such as Sort, Filter, Power Query, PivotTables and Power Pivot. Learn the concepts and techniques for how to importing, clean and transform data from multiple sources in order to build refreshable reports, dashboards and other data analysis outputs. 11) Automate tasks using Recorded Macros and VBA from the Internet: 12) Use Finance Functions, Statistical Functions and Rounding functions to make specific calculations If you are watching videos to learn all about Excel, watch Excel Basics Playlist, first, then watch the Highline Excel 2016 Class, second.

Aulas do Curso (30 vídeos)

0 de 30 aulas concluídas 0%
Progresso salvo somente neste navegador.
Descrição da aula 0: Free Advanced Excel Course at YouTube

Join Free class from excelisfun at YouTube: https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw Learn Advanced Excel from the world-renowned Excel Educator, Mike Girvin, Microsoft Excel MVP and Highline College Professor. 30 videos, 18 hours, free notes, Excel files and homework problems make this the #1 Free Online Advanced Excel Class in the world. This class uses the amazing new YouTube Learning Playlist structure. Topics in Class: 1. Data, Formulas & Data Analysis Fundamentals 2. Intro To Power Query & Power Pivot 3. References in Excel 4. Array Formulas 5. Conditional Calculations with SUMIFS, COUNTIFS & More 6. Date & Text Formulas 7. Data Validation 8. Lookup Formulas, VLOOKUP, INDEX & More 9. Visualizations: Charts & Conditional Formatting 10. Data Analysis in the Excel Worksheet 11. Big Data Analysis with Power Pivot & Power BI 12. Finance Calculations 13. Excel Macros

Descrição da aula 1: Highline Excel 2016 Class 01: Excel Fundamentals: Efficiency, Data, Data Sets, Formatting

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about these Excel Fundamentals for Excel Highline Excel 2016 Class: 1. (04:24) Download Files 2. (00:33) Prerequisites for class 3. (01:04) Excel Professional 2016 4. (01:50) Install Com Add-in Power Pivot 5. (02:54) Examples of Effective and Efficient Solutions in Excel 6. (09:20) What Excel Does: 1) Make Calculations & 2) Data Analysis 7. (11:25) Structure of Excel: Cells, Worksheets, Sheet Tabs and Workbooks 8. (13:00) Keyboard Shortcuts are efficient because they help you to accomplish tasks quickly 9. (15:06) Data Analysis and Business Intelligence terms 10. (16:31) What is Raw Data? 11. (17:16) Excel Proper Data Sets 12. (20:18) Terminology: Cleaning, Transforming, Importing Data Data 13. (22:40) Data Types and Default Alignment in Excel 14. (29:05) Excel Table feature 15. (34:45) Number Formatting is a Façade 16. (39:13) Date Number Formatting and Date Math (Date Formulas) 17. (43:51) Time Number Formatting and Time Math (Time Formulas) 18. (46:35) Percentage Number Formatting 19. (53:10) Custom Number Formatting 20. (01:03:00) Style Formatting allows you to present information in an effect way 21. (01:10:00) Cell Styles 22. (01:13:45) Page Setup allows you to print information in an effect way 23. (01:19:24) Summary 24. (01:20:25) Homework Problems Highline Excel 2016 Professional Class taught by Michael excelisfun Girvin Prerequisite class: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kbIJuNnApcYG3pQI3sG8j4

Descrição da aula 2: Highline Excel 2016 Class 02: Excel Fundamentals: Comprehensive Formula Lesson (15 Examples)

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:13) Introduction 2. (10:09) Excel’s Golden Rule 3. (04:31) Look at list of Formula Types and Formula Elements 4. (06:29) Number Formula Examples 5. (08:24) Text and Number Formulas for counting numbers above a hurdle 6. (11:15) When to combine Comparative Operator and Number in a cell or when to combine Comparative Operator and Number in the formula. 7. (12:36) DOLLAR function. 8. (14:09) How to combine Comparative Operator and Number in the formula. 9. (16:53) VLOOKUP function and Array Constant 10. (22:31) IF Function 11. (25:09) Logical Formula 12. (27:06) Aggregate Calculation / Aggregate Formula 13. (28:12) Array Calculation / Array Formula 14. (32:40) Excel Formula Order of Precedence (Excel’s Order of Operations) 15. (37:05) Tracking Down Errors in Excel: 4 Step Method. 16. (40:37) Types of Errors 17. (46:19) When to use ROUND function 18. (49:43) 3 ways to enter cell references into formulas 19. (53:22) Summary 20. (54:25) Homework

Descrição da aula 3: Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm Buy excelisfun products: https://teespring.com/stores/excelisfun-store In this video learn about the fundamentals of Data Analysis and Business Intelligence in Excel 2016: Sort, Filter, PivotTables, Power Query, Power Pivot Data Model: 1. (00:05) Introduction 2. (01:39) Sort 3. (03:02) Sorting one column 4. (03:23) Sorting multiple columns 5. (09:06) Sorting Mixed Data 6. (10:50) Filter feature 7. (13:22) Filter Drop-down Arrows to see Unique List 8. (15:34) Filter Different Data Types 9. (18:30) Filter to Extract Records 10. (20:54) OR Logical Test (OR Criteria) Discussion 11. (22:52) AND Logical Test (AND Criteria) Discussion 12. (28:23) BETWEEN and NOT Criteria 13. (30:57) PivotTable. Discussion of Crosstabulated tables and PivotTables as “Calculations with Criteria”, both AND Criteria and OR Criteria. 14. (33:05) PivotTable Basics: 1) Drag and Drop Field Names to add criteria to PivotTable, 2) Cross Tabulated Table, 3) Layout Formatting, 4) Number Formatting 15. (37:41) Adding Slicers 16. (40:21) Creating a Custom Style for a PivotTable 17. (44:48) Name PivotTable 18. (46:12) Create PivotTable using “Summarize Values By”, which allows us to change the Aggregate Functions like: SUM, COUNT, AVERAGE. 19. (51:49) Group Dates by Month and Year 20. (54:50) Create PivotTable using “Show Values As” to calculate “% of Column Total”. 21. (56:09) Hide items in Slicer 22. (57:24) Connect Multiple Slicers to Multiple PivotTables. 23. (58:57) Sort in PivotTable. 24. (01:00:11) Create multiple PivotTables with a single click using “Show Report Filter Pages” 25. (01:03:10) Why we need Power Query and Power Pivot Data Model 26. (01:05:21) Introduction to Power Query (Get & Transform) 27. (01:07:04) Power Query Example 1: Clean and Transform Data Table, Create PivotTable Based on Power Query Update, 3) Add new data to table and Refresh to update Query and PivotTable 28. (01:18:25) Power Query to Unpivot a Crosstabulated Table into a Proper Data Set. 29. (01:24:54) Introduction to Power Pivot and the Data Model 30. (01:25:32) Power Query to import multiple Text File tables with over one million records combine them into a single Table. We will use the “From File, From Folder” option. 31. (01:33:20) Load the million records in Power Query into the Power Pivot Data Model. 32. (01:34:42) Add an Excel Table into the Power Pivot Data Model 33. (01:35:30) Update Power Query 34. (01:36:36) Build a relationship between tables in the Power Pivot Data Model. 35. (01:38:08) Build PivotTable from Millions of Records from Two Tables 36. (01:40:34) Add new Text File to Folder and Update PivotTable. 37. (01:41:22) Summary

Descrição da aula 4: Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic Trick 1468)

Download Excel File #1: https://excelisfun.net/files/EMT1468.xlsm Download Excel File #2: https://excelisfun.net/files/EMT1468Finished.xlsm Download Zipped Folder: https://excelisfun.net/files/EMT1468TextFiles.zip Entire page with all Excel Files for All Videos: https://excelisfun.net/ Buy excelisfun products: https://teespring.com/stores/excelisfun-store Learn about how Topics: 1. (00:16) Introduction 2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016 3. (03:51) Example #1: Clean Data and create Excel PivotTable Report, then add new data and refresh report! 4. (17:06) Example #2: Transform Cross Tabulated Table into Proper Data Set 5. (21:50) Example #3: Import 1.2 Million Rows of Data from Text Files into Power Pivot and create a Data Model PivotTable, then add new data and refresh our report. 6. (22:34) Diagram to understand Data Model and Power Pivot 7. (46:46) Unhanded Exception Error. 8. (48:02) Summary Reference Video: Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model https://www.youtube.com/watch?v=Jzt-I4mt8sQ

Descrição da aula 5: Highline Excel 2016 Class 04: References: Relative, Absolute, Mixed, Sheet, Workbook, 3-D, Table…

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: (00:04) Download Files (00:30) Introduction to types of References 1. (01:38) Example 1: Not Copying Formula. Goal: Calculate Net Income. 2. (02:09) Example 2: Not Copying Formula. Goal: Estimate the Future Value of an Investment. 3. (07:52) (Example 3: Relative Cell Reference. Goal: Calculate Net Income for each Month. 4. (09:03) Example 4: Relative & Absolute Cell Reference. Goal: Estimate the Future Value of an Investment at different rates. 5. (11:33) Example 5: Relative & Absolute Cell Reference. Goal: Calculate each Expense as a % of Revenue (Expense Ratios) to use in next year's Budget. Also discussion of how to interpret Ratios. 6. (15:41) Example 6: Mixed Cell References & Assumption Tables. Goal: Calculate Expenses for Budgeting Process. 7. (29:07) Example 7: Relative & Mixed Cell Reference. Goal: Create "Common Sized Income Statement" for Accounting Report. 8. (33:31) Example 8: Mixed Cell References & Absolute Cell References. Goal: Estimate the Future Value of an Investment at different rates & length of investment. 9. (38:00) Example 9: Mixed Cell References & Relative Cell References. Goal: Calculate Profit for each Markup Price Schedule 10. (41:05) Example 10: Mixed Cell References & Relative Cell References in Expandable Range. Goal: Calculate Selling Price and Profit Margin based on different 11. (46:57) Example 11: Saving Formula Inputs with Scenario feature. Goal: Create different sets of assumptions for budget. 12. (51:52) Example 12: Sheet Reference. Goal: Check if Schedule Of Accounts Receivable matches the AR Controlling Account. 13. (55:51) Example 13: Sheet Reference Entered with New Window Open with Second View of Workbook. Goal: Calculate Jan & Feb Commissions, when Commission Rates are on an Assumption Sheet. 14. (01:00:22) Example 14: Workbook Reference. Goal: Access Net Income Number from External Workbook. 15. (01:06:22) Example 15: 3-D References. Goal: Add numbers from across sheets that each contain an identical template for units. 16. (01:10:55) Example 16: Defined Names. Goal: Estimate the Future Value of an Investment. Name Formula Inputs so that reading formulas is easier. 17. (01:18:23) Example 17: Defined Names. Goal: Name Fields so that it is fast, easy and accurate to enter ranges into formulas. 18. (01:13:33) Example 18: Defined Names. Goal: Use Defined Names to jump to other places in Excel Workbook File. 19. (01:21:22) Example 19: Table Formula Nomenclature (Structured References). Goal: Add Sales. Add Sales for each Product. 20. (01:32:42) Example 20: Table Formula Nomenclature from Different Sheet. Goal: Add Sales for each Product. Count Fields in Table. 21. (01:24:25) Example 21: Add column to Excel Table. Relative Reference in Table Formula Nomenclature. Goal: Add column to Excel Table for Commission Calculation. 22. (01: 01:26:34) Example 22: Mixed Reference in Table Formula Nomenclature. Goal: Add Defects for each month. 23. (01:35:48) Example 23: Table Formula Nomenclature for VLOOKUP and Data Validation. Goal: Create Dynamic Lookup Table. 24. (01:28:35) Example 24: Mixed & Absolute Reference in Table Formula Nomenclature. Goal: Add Defects for each month. (01:42:14) Summary Types of References in Excel: 1. Relative Cell Reference 2. Absolute Cell Reference 3. Mixed Cell Reference with row locked cell reference (also known as row absolute, column relative) 4. Mixed Cell Reference with column locked cell reference (also known as column absolute, row relative) 5. Sheet Cell Reference 6. Workbook Cell Reference 7. 3-D cell reference 8. Defined Name 9. Table Formula Nomenclature (Structured References)

Descrição da aula 6: Highline Excel 2016 Class 05: Excel Array Formulas: Comprehensive Lessons: 12 Examples

Download File: https://people.highline.edu/mgirvin/AllClasses/218_2016/Content/Week04/Busn218-Video05.xlsm In this video learn about: 1. (00:04) Download Files 2. (00:24) Aggregate vs. Array Calculations & Define Array 3. (02:55) Define Array Formulas 4. (05:49) Example of non-array formula. Standard Formula: Formulas that calculate a single answer. Goal: Calculate Total Expense and Total Net Income. 5. (06:43) Array Formula 1: Math Array Operation. Goal: Calculate Total Expense for each month without making calculations in many cells (we don’t need all the individual numbers). Main reason we use Array Formulas: To create compact solution to do all the intermediate steps in a single cell. 6. (10:00) How to decide whether or not you must use Ctrl + Shift + Enter to put the array formula into the cell. 7. (12:06) Implicit Intersection. 8. (14:49) SUMPRODUCT function to house an array calculation to avoid using Ctrl + Shift + Enter. 9. (16:46) Array Formula 2: Math Array Operations. Goal: Calculate Overall Total Expense in a single cell. 10. (19:34) Array Formula 3: Math Array Operations. Goal: Calculate Overall Net Income in a single cell. 11. (20:48) Formula 4: Use more than one argument in SUMPRODUCT. Goal: Calculate total Bank Deposit. Learn about SUMPRODUCT and discussion about size and dimensions of arrays in array calculations. 12. (25:06) Array Formula 5: Function Argument Array Operation in k argument. Goal: Add 3 biggest and 3 smallest. Use Array Constant to avoid Ctrl + Shift + Enter. 13. (28:32) Array Formula 6: Function Argument Array Operation in criteria1 argument. Goal: Add Sales for each Sales Team. Example of using an Array Formula to create a MUCH smaller formula than non-Array Formula alternatives. 14. No Example 7 15. (32:16) Array Formula 8: Function Argument Array Operation in criteria1 argument. Goal: Score Myers-Briggs Test in Human Resource Department. 16. (35:21) Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation. Goal: Score Myers-Briggs Test in Human Resource Department. 17. (37:15) Array Formula 10: IF function array formula to filter out values for an aggregate function. Goal: Calculate Average Gross Profit for Each Sales Rep. Learn about how to use IF Function to filter out values that we don’t want for our aggregate calculation. 18. (38:12) List of Functions that can NEVER do Array Calculations. 19. (43:11) Array Formula 11: Built-in Array Function: TRANSPOSE. Goal: Transpose rows and columns so we have Assumption Table that allows Mixed Cell References. Example of Array Formula where we enter multiple values as an array into multiple cells simultaneously. 20. (45:42) Array Formula 12: Built-in Array Function: FREQUENCY. Goal: Count how many sales are in each category. 21. (50:31) Summary Learn Array Formulas from Beginning to End.

Descrição da aula 7: Highline Excel 2016 Class 06: Conditional Calculations with Excel Formulas: Comprehensive Lessons

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about All the ways to make conditional Calculations with Formulas in Excel: Built-in Functions like SUMIFS, AND function, OR Function, IS Functions, Array Formulas, D Functions and more: 1. (00:23) Introduction 2. (01:28) AND Logical Test (AND Criteria) 3. (02:35) AND Function 4. (05:14) SUMIFS, COUNTIFS, AVERAGEIFS functions with AND Criteria 5. (05:43) Create Names From Selection 6. (10:55) Cross Tabulated Table for adding with 3 criteria: SUMIFS and Mixed Cell References. 7. (13:51) Between Logical Tests, Create Monthly Sales Report with SUMIFS. 8. (18:39) Create Monthly Sales Report with SUMIFS and EOMONTH functions. 9. (21:44) Count Sales Between Upper and Lower Limit (Frequency Table) with COUNTIFS (Alos consider how this method is different from FREQUENCY Array Function). 10. (25:40) D Functions (Database functions) with AND Criteria 11. (32:31) Array Formulas with AND Criteria. Use Two IF Functions. See how to calculate Standard Deviation with two conditions or criteria. 12. (41:15) OR Logical Test (OR Criteria) 13. (42:26) Defined Names and Scope in an Excel Workbook 14. (44:03) OR Function 15. (46:34) OR Criteria looking at one column using SUMIFS and SUMPRODUCT for adding and COUNTIFS and SUMPRODUCT for counting. Function Argument Array Operations. 16. (49:08) Average calculation with OR Criteria: AVERAGE & Array Formula (for 1 or more columns) 17. (01:01:10) D Functions (Database functions) with OR Criteria 18. (01:05:00) AND and OR and IF Functions for Credit Analysis for Accounts Receivable Department 19. (01:12:42) IF Function & IS functions. Bank Checkbook Example. 20. (01:20:17) Conditional Calculations: PivotTable or Formulas? 21. (01:22:18) Summary

Descrição da aula 8: Highline Excel 2016 Class 07 Excel 2016 MAXIFS, MINIFS & IFS Functions for Conditional Calculations

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:11) Introduction to these new Excel 2016 Functions 2. (01:13) MINIFS function to find minimum value with conditions or criteria. 3. (03:23) MAXIFS function to find max value with conditions or criteria. 4. (04:19) IFS function to select from more than two items and put a particular item into a cell based on logical tests.

Descrição da aula 9: Highline Excel 2016 Class 08: Text Formulas and Text Functions to Join and Extract Data

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:12) Introduction 2. (00:46) Text Formulas: Ampersand to Join Items together 3. (01:38) Text Functions to extract partial text items from a larger text item. 4. (02:57) Extract State with LEFT function 5. (04:07) Extract Zip Code with RIGHT function 6. (04:59) Extract First Name with LEFT and SEARCH functions 7. (06:46) Extract Last Name with RIGHT, LEN and SEARCH functions 8. (08:50) Extract Product with LEFT and SEARCH functions 9. (10:07) Extract Region with MID and 3 SEARCH functions 10. (13:28) Extract Money Amount from end of description Field with RIGHT, LEN and SEARCH functions, and a MATH operation to Convert Text Number Back to a Number 11. (15:34) TRIM function to remove extra spaces. 12. (16:22) Create Serial Number Date from ISO Date using DATE, LEFT, MID and RIGHT functions. 13. (18:45) Create Serial Number Date from ISO Date using TEXT function, Custom Number Format and Math Operation to Convert Text Number back to a Number. 14. (22:00) TEXT function and Custom Number Formatting to create labels 15. (26:02) Text Formulas: TEXTJOIN Excel 2016 Function 16. (29:05) Summary

Descrição da aula 10: Highline Excel 2016 Class 09: Date Formulas and Date Functions, including Fiscal Quarter & Year

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:12) Introduction 2. (00:22) Reminder of Date Math and Serial Number Dates and Number Formatting 3. (02:14) TODAY and NOW functions to show the current date and time 4. (03:13) EOMONTH function to get the end of the month date 5. (04:03) EDATE function to get Invoice Due Date (one month ahead) or Pension Vest Date 6. (05:58) DATE function that takes the inputs: year, month and day and then delivers a serial number date. 7. (06:17) YEAR, MONTH and DAY functions to get year, month and day as numbers from a serial number date. 8. (07:43) Year Sales Report with SUMIFS function 9. (08:58) Calculate Month Name with TEXT function and Custom Number formatting 10. (09:51) Calculate Day Name with TEXT function and Custom Number formatting 11. (10:18) Weekday Sales Report with SUMIFS function 12. (11:06) Standard Quarter with ROUNDUP function 13. (13:11) Label for quarter. 14. (13:47) Quarter Sales Report with SUMIFS function 15. (14:17) What is a Fiscal Quarter. 16. (14:46) VLOOKUP Formula for Fiscal Quarter when Jan, Fed, Mar = 4th quarter. 17. (16:45) IF Formula for Fiscal Quarter when Jan, Fed, Mar = 4th quarter. This type of formula will work in Power Pivot Data Model also. 18. (18:51) Calculate Fiscal Year with IF function. 19. (19:42) Label for Fiscal Quarter. This sort of label will sort correct, like when we use this sort of formula in our Power Pivot Data Model and we use our DAX formula in our PivotTable Row area. 20. (20:43) Fiscal Quarter Sales Report with SUMIFS function 21. (21:19) Summary

Descrição da aula 11: Highline Excel 2016 Class 10: Excel Data Validation (List, Custom & More), Comprehensive Lesson

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:16) Introduction 2. (00:46) Data Validation to allow only whole numbers between an upper and lower limit 3. (02:12) Create an Input Message for your Data Validation 4. (02:31) Error Message with “Stop” 5. (02:53) Circle Invalid Data 6. (04:12) Copy and Paste To Wreck Data Validation 7. (04:42) Data Validation to allow only decimal numbers below or equal to an upper limit 8. (05:10) Error Message with “Warning” 9. (06:26) Error Message with “Information” 10. (07:30) Data Validation drop down list using a range of cells 11. (08:14) Data Validation drop down list using a Defined Name 12. (08:14) Ctrl + Shift + F3 = Create Names From Selection for a single column 13. (08:52) Data Validation drop down list using a Dynamic Table Name 14. (09:50) INDIRECT Function trick for Table Name. INDIRECT function takes text that represents a reference and converts it back to a 15. (12:43) Data Validation List when you type text in source dialog box 16. (13:21) Create a Dependent Data Validation List based on a second Data Validation Dropdown List 17. (14:14) Ctrl + Shift + F3 = Create Names From Selection for a multiple columns column 18. (15:19) INDIRECT Function trick for Dependent Data Validation: to take text that represents a reference and convert it to a reference 19. (16:40) Data Validation to allow only dates between an upper and lower limit 20. (17:28) Data Validation to allow only times above or equal to a lower limit 21. (18:08) Data Validation to allow a certain text length 22. (18:40) Custom Data Validation Logical Formula to allow only prices that are less than or equal to double the cost 23. (20:21) Custom Data Validation Logical Formula to prevent duplicates 24. (23:11) Summary Comments

Descrição da aula 12: Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons, 20 Examples

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: (00:12) Introduction 1. (00:51) Data Validation List. VLOOKUP, Exact Match. IF & ISBLANK functions. Goal: Create Invoice. 2. (09:43) HLOOKUP with Exact Match Lookup. Goal: Lookup Tyrone's Phone Number. Horizontal Lookup is rare. 3. (10:48) VLOOKUP with Approximate Match Lookup. Goal: Lookup Pipe size. 4. (13:44) VLOOKUP with Approximate Match Lookup. Goal: Lookup Commission Paid. 5. (14:38) VLOOKUP with Approximate Match Lookup. Cell Reference for Column Index Number. Goal: Get Rating and Commission. 6. (16:20) VLOOKUP and MATCH functions with Exact Match Lookup. MATCH for Column Index Number. Goal: Retrieve employee data. 7. (22:55) VLOOKUP & ROWS or COLUMNS with Exact Match Lookup. ROWS or COLUMNS for Column Index Number. Goal: Retrieve record from Employee Table. 8. (26:53) Multiple VLOOKUP functions. Goal: Calculate taxes from complex tax table. 9. (33:16) How to use VLOOKUP, LEFT and SEARCH to do a "Partial Text Lookup. Goal: Lookup Product Price. (34:34)Also: Look at Data Mismatch in lookup formulas. 10. (36:55) INDEX and MATCH functions to do a "Lookup Left". INDEX array argument is a column. Goal: Given a flight range, lookup boomerang name in first column. 11. (39:51) INDEX and MATCH to find Vendor for Low Bid. INDEX array argument is a row. Goal: lookup vendor name for lowest bid. 12. (42:52) INDEX and MATCH functions to do a Two-Way Lookup. INDEX array argument is a table. Goal: Do Two-Way Lookup to find Discount. 13. (47:00) INDEX and MATCH to lookup a whole column or row. INDEX array argument is a table. Goal: Lookup: 1) whole column of values then 2) whole row of values. 14. (51:23) How to use VLOOKUP & CHOOSE to do a lookup to multiple tables. Goal: Lookup Commission rate from Multiple Lookup tables. 15. (57:25) LOOKUP function with lookup_value and array arguments. Approximate Match. Goal: Lookup Commission and Rating. LOOKUP to enter fewer arguments. 16. (01:00:55) LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Goal: Lookup Last Date and find site. LOOKUP to do Array Operation. 17. (01:05:08) LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Goal: "Lookup Adding" to total costs. LOOKUP to do Array Operation. 18. (01:09:28) INDEX and ROWS to Flip Grade Table. Goal: Flip a Grade Table. 19. (01:13:20) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 also in List 2? 20. (01:15:13) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 NOT in List 2? (01:16:40) Summary

Descrição da aula 13: Highline Excel 2016 Class 12: Two Lookup Values in VLOOKUP? Helper Column or Array Formula.

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:13) Introduction 2. (00:30) Helper Column Solution with Join Operator and VLOOKUP 3. (03:31) Array Formula with: INDEX and MATCH functions and a Join Array Operations. This formula requires Ctrl + Shift + Enter. 4. (05:31) Array Formula with: INDEX, INDEX and MATCH functions and a Join Array Operations. This formula does NOT requires Ctrl + Shift + Enter because we put the Array Operation into the array argument in INDEX function and use a zero ( 0 ) in row_num argument to retrieve the whole column (Resultant Array from the Join Array Operation).

Descrição da aula 14: Highline Excel 2016 Class 13: One Lookup Value to Return Multiple Items: INDEX & AGGREGATE

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about how to create a formula that can take One Lookup Value and Return Multiple Items. Learn about the INDEX and AGGREGATE Array Formula. See the functions: with INDEX, AGGREGATE, ROW, ROWS and IF functions. Learn how IFERROR is inefficient to use with Array Formulas that extract multiple items with a single lookup value.

Descrição da aula 15: Highline Excel 2016 Class 14: VLOOKUP as Relationship in Power Pivot Data Model & Vice Versa

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about how to create a Regional Sales Report with: 1. (00:13) Discussion about VLOOKUP or PowerPivot Data Model Relationships. 2. (01:57) Copy Sheets with Keyboard and Mouse. 3. (02:43) VLOOKUP Example to create Regional Sales Report from Two Tables. 4. (04:25) Power Pivot Data Model Relationships Example to create Regional Sales Report from Two Tables. With big data, one of the main advantages is that with relationships between lookup tables and transaction tables, the DAX formula process over the lookup tables (which contain unique lists) and in turn filter the transaction table down to a much smaller size, which in turn require less time when the formulas calculate. More later.

Descrição da aula 16: Highline Excel 2016 Class 15: Excel Charts to Visualize Data: Comprehensive Lesson 11 Chart Examples

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: (00:28) Define Charts. What do Charts do? (01:12) Effective Charts: No Chart Junk. Example of Chart Junk. (02:54) Types of Charts: 11 different chart types. (06:55) Terminology for Chart Elements: 1. (08:15) Column Charts 2. (12:15) Bar Charts 3. (13:41) Pie Charts 4. (14:25) Stacked Column Charts 5. (14:25) Clustered Column Charts 6. (17:44) Histogram. Two Examples: 1) Retail Sales Data Histogram with FREQUENCY Array Function. 2) Count Web Transactions by Hour with PivotTable. 7. (28:45) Line Charts. Time Series Chart. 8. (31:54) X-Y Scatter for Sample Data. 9. (34:45) Break Even Chart X-Y Scatter 10. (46:05) Combo Charts 11. (47:30) Bubble Chart (51:19) Summary

Descrição da aula 17: Highline Excel 2016 Class 16: Conditional Formatting to Visualize Data: Built-in & Logical Formulas

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about Conditional Formatting Topics : 1. (00:16) Introduction 2. (00:43) Built-in Feature: Cell Contains 3. (03:28) Logical Formula: Highlight Row (Record) where cell contains a value. 4. (10:00) Built-in Feature: Below Average 5. (10:55) Logical Formula: Highlight Row (Record) where sales are below average. 6. (13:02) Discussion about Volatile nature of Conditional Formatting and how it affects Spreadsheet Calculation Time. 7. (16:10) Built-in Feature: Top 3 values 8. (16:53) Logical Formula: Highlight Records that contain the top 3 values 9. (19:28) Built-in Feature: Data Bars 10. (21:16) Adding Borders to Report 11. (22:54) Built-in Feature: Color Scales (Heat Map) 12. (24:55) Built-in Feature: Icons 13. (29:25) Logical Formula: Format Whole Column Based on a condition. Learn about SIGN function. 14. (31:47) Logical Formula: Format with complex criteria (AND Logical Test) 15. (34:00) Logical Formula: Format with complex criteria (OR Logical Test). See how to use MATCH function. 16. (37:00) Logical Formula: Format Weekends and Holidays. Learn about NETWORKDAYS function. 17. (41:13) Logical Formula: Format items NOT in List 18. (43:27) Summary

Descrição da aula 18: Excel Magic Trick 1379: Power Query Combine Binary "Expand Button" Update January 2017

Download Files: Start File: https://excelisfun.net/files/EMT1379Start.xlsx Finish File: https://excelisfun.net/files/EMT1379Finished.xlsx Learn about how the Power Query (Get & Transform) Combine Binary "Expand Button" has changed in the Update January 2017. See a Text File Import From Folder. See the Functions that are automatically created and utilized in the updated version. Step through some of the M Code that is used by Power Query (Get and Transform) Reference Video: Highline Excel 2016 Class 17: How to Build Excel Dashboard PivotTable, Chart, Conditional Formatting https://www.youtube.com/watch?v=hYPwX_CfYv4&t=364s

Descrição da aula 19: Highline Excel 2016 Class 17: How to Build Excel Dashboard PivotTable, Chart, Conditional Formatting

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about how to build Excel Dashboard based on Data Model and see first DAX Measure (Formula): 1. (00:22) Introduction 2. (02:43) Power Query to Clean, Transform & Import Text Files (From Folder feature) 3. (09:35) Import Data into Power Pivot Data Model 4. (11:23) PivotTable #1: for Month & Year Revenue 5. (11:38) Group Dates in PivotTable based on Data Model: Automatically adds Columns to Data Model Table. 6. (13:38) First DAX Measure: "Formula for PivotTable" 7. (19:04) Add Slicer to Multiple PivotTables 8. (19:52) Chart #1: Line Chart for Month and Year Data 9. (20:27) Name PivotTables 10. (21:20) PivotTable #2: Revenue for each SalesRep. Name Pivot. Connect to Slicer. 11. (22:39) Chart #2: Bar Chart for SalesRep Revenue 12. (23:52) PivotTable #3: Create Year PivotTable. Name Pivot. Connect to Slicer. 13. (26:00) Create Text Label for Chart #2 based on PivotTable #3 and Slicer. 14. (27:10) PivotTable #4: Cross Tabulated Table for Product and Region. 15. (28:45) Conditional Formatting for PivotTable #4. 16. (29:58) Paste Linked Picture of PivotTable in Dashboard 17. (30:48) Add formatting and Alignment to Dashboard 18. (33:25) Add new Text Files to Folder and Refresh Dashboard 19. (34:59) Edit Query to remove data from Dashboard. 20. (35:38) Summary Reference Video: Excel Magic Trick 1379: Power Query Combine Binary "Expand Button" January 2017 Update https://www.youtube.com/watch?v=KqIMWTnkUiU

Descrição da aula 20: Highline Excel 2016 Class 18: Clean & Transform Data: Replace, Flash Fill, Text To Columns, Formulas

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:13) Introduction 2. (01:08) Find & Replace feature (Clean Data) 3. (03:49) Flash Fill: Clean Data (10 Examples) 4. (11:14) Text To Columns (TTC) 5. (12:16) Text To Columns (TTC) to split data apart into multiple columns based on a delimiter (Transform Data Set) 6. (15:31) Text To Columns (TTC) to convert ISO Text Dates to Serial Number Dates (Clean Data) 7. (16:11) Text To Columns (TTC) to convert Text Numbers back to Numbers (Clean Data) 8. (17:13) Review of Formulas to Clean and Transform Data. 9. (18:47) When to use Formulas for Data Cleaning and Transforming. 10. (19:36) Extract First Name automatically when source data changes with Formula (Clean Data). See LEFT & SEARCH functions. 11. (20:28) Formula for Last Name when some names have middle name and some do not (Clean). See functions: SUBSTITUTE, REPT, LEN, RIGHT functions. 12. (23:31) Summary

Descrição da aula 21: Highline Excel 2016 Class 19: Transform Data Sets using Advanced Filter (8 Examples)

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: (00:14) Introduction to Advanced Filter 1. (02:50) Extract Records based on AND Logical Test (AND Criteria). Comprehensive Example including Defined Names that are created and how to run the Advanced Filter a second or third time. 2. (09:48) Extract Records based on OR Logical Test (OR Criteria) 3. (10:39) Extract Records based on "Contains" Criteria ("Partial Text" Criteria) 4. (12:00) Advanced Filter with Complex Criteria 5. (13:00) Advanced Filter Extract to Different Sheet. Extract Data for a given year. See how to create formulas to create criteria base on year. See the functions, DATE and TEXT. 6. (18:00) Advanced Filter to get a Unique List from Column 7. (19:53) Advanced Filter with Helper Column Formula to Create Table Report with Professional and Neat Formatting and Page Setup. See the functions: AND and SEARCH. 8. (29:32) Advanced Filter with Formula Criteria on Large Data Set to Create Report

Descrição da aula 22: Highline Excel 2016 Class 20: Power Query Import Multiple Excel Files & PivotTable Show Values As

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:15) Overview: Clean & Transform & Import Multiple Excel Files with 1 Sheet Each & Create PivotTable Report 2. (03:00) Step 1: Import Multiple Excel Files, 1 Sheet Each, including Power Query (M Language) Excel.Workbook function. 3. (11:45) Step 2: Load to Data Model and create DAX Measure for Total Sales, Edit Power Query 4. (15:19) Step 3: Create PivotTable based on Data Model and Group Dates by Month and Year. 5. (16:50) Step 5: Use Show Values As feature in a PivotTable: % of Grand Total, Difference From, % Difference From, Running Total, and % Running Total. 6. (19:48) Step 4: Add Slicers for City and Year 7. (20:40) Step 6: Finalize Report 8. (21:40) Step 7: We Get New Files in Folder, After we Refresh: Everything Updates!

Descrição da aula 23: Highline Excel 2016 Class 21: Power Query Import Multiple Excel Workbooks with Multiple Sheets

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about how to import multiple Excel files using Power Query (Get & Transform): 1. (00:15) Introduction 2. (01:32) Files used for Import 3. (02:46) Multiple Excel Workbooks with Multiple Sheets 4. (03:50) Get "City" names from Excel file names 5. (04:40) Use Power Query Function: Excel.Workbook([Content],true) to get Excel Data and Promote Headers in each excel File. 6. (06:06) Get "Sales Rep" names from Excel Worksheet names

Descrição da aula 24: Highline Excel 2016 Class 22: How To Build Data Model & DAX Formulas in Power Pivot

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:16) Introduction to Entire Project, including what type of start files we have and what the final report looks like. 2. (03:36) What is Data Modeling? Steps to creating a Data Model. 3. (05:07) Data Modeling Step 1: Power Query to Clean, Transform & Import Fact Tables 4. (11:22) Data Modeling Step 1: Import Dimension Tables from an Excel Sheet 5. (12:23) What is a Calendar Table (Dimension Table)? Why a Calendar Table and not Group by Date? 6. (15:14) Data Modeling Step 1: Create Calendar Table in Excel & Import to Data Model 7. (17:45) Data Modeling Step 2: Create Relationships between Related Tables 8. (19:30) Data Modeling Step 3: Create DAX Calculated Columns in Calendar Table. Dee the DAX functions: MONTH, FORMAT, YEAR, ROUNDUP, and IF. See how to calculate Calculated Columns for Month Number, Month Name, Year, Quarter, Fiscal Quarter, Fiscal Year and Fiscal Period. 9. (21:40) What is Row Context? 10. (30:45) Data Modeling Step 3: Create DAX Calculated Columns in Fact Table for Revenue. See the functions ROUND, RELATED 11. (34:02) Data Modeling Step 3: Create DAX Measures using SUM function n to add values from Calculated Column. 12. (36:33) Data Modeling Step 3: Alternative Total Revenue Calculation: DAX Measure with SUMX. Learn how to perform Row Context in a DAX Measure. This formula calculates total revenue without a Helper Column. 13. (39:18) DAX Calculated Column or DAX Measure to calculate Total Revenue? 14. (40:45) Data Modeling Step 3: More DAX Measures. SUMX to calculate Total COGS. DAX Measure for Gross Profit. 15. (44:25) Data Modeling Step 4: Hide Tables & Fields not used in PivotTables 16. (46:15) Data Modeling Step 5: Create PivotTables and Pivot Charts and Final Dashboard 17. (47:41) What is Filter Context? 18. (47:41) Advantage of Power Pivot Data Model Columnar Database & Relationships & DAX Measures when you have Big Data. 19. (53:55) Data Modeling Step 6: Refresh Data Model when Source Data Changes 20. (54:49) Data Modeling Step 7: Fix Calendar Table 21. (55:50) Data Modeling Step 7: Dashboard After Refreshing 22. (56:11) Data Modeling Step 7: Create new DAX Formulas and create New Report.

Descrição da aula 25: Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 2016

Download Excel Files: Start File: https://excelisfun.net/files/EMT1298Start.xlsx Finish File: https://excelisfun.net/files/EMT1299Finished.xlsx Home page: https://excelisfun.net/ Learn how to: create Automatic Calendar Table in Data Model, New in Excel 2016. This ability was not in earlier versions of Excel.

Descrição da aula 26: Highline Excel 2016 Class 23: Intro to Power BI Desktop to Create Data Model & Visualization

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about a free download called Power BI Desktop that is a single program that does all that Excel’s Power Query & Power Pivot can do, and more. Comprehensive example of Importing multiple files into a single data set, importing lookup tables from Excel, building relationships, building DAX Calculated Columns & DAX Measures and building a visualization dashboard. Learn Power BI Desktop Basics. Introduction to Power BI Desktop. Getting Started with Power BI Desktop. Create Impactful Reports With Power BI Desktop. Microsoft Power BI

Descrição da aula 27: Highline Excel 2016 Class 24: Financial Functions: PMT, RATE, NPER and FV 12 Examples

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: (00:01) Introduction 1. (02:22) PMT function from Borrower point of view, End Payment (07:10) PMT with Begin Payment 2. (08:19) PMT function from Lender point of view 3. (09:25) PMT function with Balloon payment at end 4. (11:17) PMT function when payments don't start for 1 year 5. (17:20) RATE function: Adjusted APR when there are Points and Fees 6. (23:23) PMT and other formulas: Create an Amortization Table 7. (29:49) CUMIPMT function: to calculate total interest over the life of a loan 8. (30:41) NPER function: How long to pay off your Credit Card Bill 9. (32:20) FV to estimate what you will have at retirement 10. (35:16) PMT to estimate how much you can withdraw each month after you retire 11. (36:55) Calculate total you can withdraw after retirement and how much you actually deposited while saving. 12. (38:25) PMT to calculate how much you have to save each month to have a million dollars when you retire. Full 111 video Finance class at YouTube: http://www.youtube.com/playlist?list=PL90E1F26C7B85E78F

Descrição da aula 28: Highline Excel 2016 Class 25: Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more…

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video learn about: 1. (00:09) ROUND function so Invoices and Payroll spreadsheets are accurate 2. (03:49) ROUND to the penny, dollar or thousands position 3. (04:28) ROUND, ROUNDUP, ROUNDDOWN functions round to a digit or position 4. (05:48) MROUND, CEILING and FLOOR round to an amount or multiple

Descrição da aula 29: Highline Excel 2016 Class 26: Macro Recorder Basics & Copying VBA Code From Internet

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm In this video use Macro Recorder to record six Macros: 1. (00:12) Basics for Recorded Macros (VBA) 2. (05:55) Absolute Reference Macro: Format report that always has the same number of columns and rows. This Macro is stored in Personal Workbook. 3. (18:32) Absolute & Relative Reference Macro: Format report that that has an unknown number of rows 4. (26:38) Relative Reference Macro to convert vertical records into a proper data set with horizontal records 5. (31:09) Search for VBA Code online: Function to spell out numbers as words 6. (33:04) Search for VBA Code online: Add or count cells with red fill 7. (35:52) Form Control Button to Add Record to Existing Proper Data Set on different sheet. 8. (39:37) Summary YouTube Channels to learn VBA: ExcelVbaIsFun: https://www.youtube.com/channel/UC9OIUFZfYqELCFwWxT7OpKQ Dinesh Kumar Takyar: https://www.youtube.com/channel/UC-vzNYU9x8IYPk_r89mGvXA Book by Bill “Mr Excel” Jelen: Excel 2016 VBA and Macros: https://www.amazon.com/Macros-Content-Program-MrExcel-Library/dp/0789755858/ref=sr_1_2?ie=UTF8&qid=1467918868&sr=8-2&keywords=excel+2016+vba

Sobre o Canal

Logo do canal excelisfun
excelisfun

Mike Girvin: 18 Year Excel & Power BI YouTuber, 23 years as professor at Highline College, 13 years as an Excel and Power BI author (5 books), and 10 years running an award-winning boomerang manufacturing company (Gel Boomerangs) with sports boomerang sales all over the world. Here at my excelisfun YouTube Channel, I provide free education for the world, with over 3,800 videos, over 100 playlists free classes, over 10,000 downloadable Excel example files, and over 2,000 free pdf notes files. Data analysis, accounting, finance and statistics are my specialty. Hopefully these free Excel & Power BI resources will help you to become better with Excel, Power BI and data analysis so you can save time at work, help coworkers become better, and save so much time that you can take an extra vacation! This is all free, so pass the word along to all who you know: excelisfun is here to help! Go Team! P.S. if you want to Donate, use PayPal link below, or join my YouTube channel : )

GRATUITO

Cursos relacionados

Capa do curso Excel avançado: análise de dados
Português

All Levels

Excel avançado: análise de dados

Mais IFMG

Gratuito

Capa do curso Mini Curso Análise de Dados no Excel
Português

All Levels

Mini Curso Análise de Dados no Excel

Prof. Fernanda Maciel

Gratuito

Capa do curso Curso Gratis de Excel para Análisis de Datos y Gráficos 2026
Espanhol

All Levels

Curso Gratis de Excel para Análisis de Datos y Gráficos 2026

Class Lover | IA, Power BI, Python y Datos

Gratuito

Capa do curso Análise de dados no Excel
Português

All Levels

Análise de dados no Excel

Evandro Saidel

Gratuito

Compartilhar este curso
Link copiado!

Prévia do curso

Excel 2016 Intermediate to Advanced Free Course. Comprehensive Calculations & Data Analysis.

Comece a aprender

Encontre cursos gratuitos
e aprenda no seu ritmo

Criar Conta
  • youcourse@youcourse.com.br

Siga nas redes sociais

YouCourse

  • Termos de Serviço
  • Politíca de Privacidade

Categorias

  • Desenvolvimento
  • Desenvolvimento pessoal
  • Design
  • Ensino e estudo acadêmico
  • Estilo de vida
  • Finanças e Contabilidade
  • Marketing
  • Negócios
  • Produtividade no escritório
  • TI e software

Parceiros

Conheça o E-goi, plataforma de email marketing

© 2026 YouCourse. Todos os direitos reservados. Desenvolvido por Corujas.Dev