**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

Resolve the captcha to access the links!