Microsoft Excel: Data Reporting and Dashboards From Scratch!

Microsoft Excel: Data Reporting and Dashboards From Scratch!

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 7.5 Hours | 1.17 GB

Get the “Right Knowledge” to work with your Data in Excel and learn to build your own Interactive Excel Dashboards

This course gives you a perfect start to the “Data Analytics world”.

“If you feel something is complicated or not your cup of tea, its majorly because you tried jumping on the advanced concepts rather than absorbing the basics first” –

This course is an effort to provide ample background of each concepts and stresses on the fundamental areas to ensure that you learn it correctly and can re-implement the concepts when required.

This course is not designed to teach you certain functionalities of Excel; But, to teach you the right skills which you can re-implement on other problems too…

The course is structured to cover all major Excel Concepts while Explaining the Data Reporting concepts.

  • Data Understanding – Using concepts that will help you understand the framework of your Data in Excel
  • Data Cleaning – Using various tools like – Got To special, Remove duplicates, etc
  • Data Preparation – Using various Functions like Vlookup, Text Functions, Date Functions, Logical Functions, etc
  • Data Summarizing – Explained using Functions and Pivot Tables
  • Data Presentation – Explained using Different chart types we should use depending on the requirement.
  • Reporting & Dashboards – Explained using Form/ActiveX controls and Pivot Table with the help of Slicers

All of this using Microsoft Excel. The course flow ordered exactly like the points above. This order should be followed while working on Excel as well.

After completing this course you will be able to:

  • Work confidently and Save your time and effort by using smart functionalities of Excel.
  • Create your own interactive Reports and Dashboards
  • Re implement the concepts to different problems
  • Be ready to answer Interview questions.
  • Start Loving Excel (in case you currently don’t)
Table of Contents

Understanding Your Goals
1 Course Outline
2 Defining Dashboards Reports and Switchboards
3 Properties of a Good Dashboard
4 Steps to create Dashboards Flow of this course

Understanding the Platform – Microsoft Excel
5 Intro to the section
6 Tangible and Intangible Features
7 All Tabs Overview
8 Excel Options Adding Developer Tab
9 Formula Bar
10 Name Box
11 Status Bar
12 Types of Shortcuts
13 Custom Lists and Fill handle Dragging.
14 Creating your own Custom Lists

Understanding Your Data
15 Section Intro
16 Case Study Intro
17 Solution Q1-Q3 Data Understanding
18 Data Makeup – Number Formats
19 Solution Q4-Q6 Conditional Formatting
20 Solution Q7-Q9 Conditional Formatting
21 Solution Q10-Q12 Conditional Formatting
22 Solution Q13 Find Replace
23 Solution Q14-Q16 Copy Paste Paste Special
24 Solution Q17-Q18 Go To Special

Sort Filter
25 Intro to Section
26 Explaining Sort Filter
27 Questions
28 Solution Q1-Q4 Sort Custom Sort
29 Solution Q5-Q7 Filters Custom Filters
30 Solution Q8-Q10 Custom Filters Advanced Filters

Data Cleaning
31 Introduction to section
32 Introduction to Case Study
33 Solution Q1 Removing Duplicates
34 Solution Q2 Missing Value Treatment
35 Solution Q3 Redundant Columns

Formulas Functions
36 Introduction to Excel Functions
37 Symbols used in Excel Functions Part 1
38 Symbols used in Excel Functions Part 2
39 Symbols used in Excel Functions Part 3

Data Preparation
40 What is Data Preparation

Data PreparationDate Functions
41 Explanation Case Study intro
42 Solution Q1-Q3 Year Month Day Functions
43 Why Connecting back to the Theory
44 Solution Q4-Q6 Date Calculations
45 Solution Q7-Q8 Advanced Date Calculations
46 Solution Q9 Advanced Date Formatting

Data PreparationLogical Functions
47 Explanation Case Study Intro
48 Solution Q1-Q2 IF Function
49 Solution Q3 Nested IF
50 Solution Q4 OR Function
51 Solution Q5 AND Function
52 Solution Q6 IFERROR Function

Data PreparationText Functions
53 Introduction
54 Solution Q1-Q2 Basic Text Functions
55 Solution Q3 LEFT FIND
56 Solution Q4 MID FIND
57 Solution Q5-Q6 REPLACE SUBSTITUTE

Data PreparationLookup Reference Functions
58 Introduction
59 Decision Matrix and Case Study Intro
60 Solution Q1 VLOOKUP
61 Solution Q2 Index Match
62 Solution Q3 VLOOKUP Wildcard

Data Summary
63 Introduction
64 Introduction to Case Study
65 Basic Aggregate Functions
66 Named Ranges
67 Conditional Aggregate Functions COUNTIF
68 Conditional Aggregate Functions Using Wildcards
69 Multi-Condition Aggregate Function COUNTIFS SUMIFS
70 Conclusion

Data Presentation Dashboard
71 Introduction
72 Creating Charts
73 Dashboard Recap
74 Controls
75 Check boxes
76 Formatting Controls

Data Summary Pivot Table
77 Introduction to Section and Case Study
78 Solution Q1-Q2 Basic Pivot Features
79 Solution Q3 Summarize By
80 Solution Q4 Calculated Field
81 Solution Q5 Grouping Dates
82 Solution Q6 Report Filter Pages

Dashboard Pivot Table
83 Introduction
84 Calculating Summary Reports
85 Incorporating Charts
86 Creating Controls using Slicers
87 Formatting Slicers

Time Savers
88 Intro To Section
89 Carbon Concept
90 Link Excel Charts to PPT Dynamically – Without getting them blurry
91 Indirect Function