This post was last Updated on August 20, 2020 by Himanshu Tyagi to reflect the accuracy and up-to-date information on the page.
Hello guys, this the beginning of Module 1 “Data Analysis and Visualization with Excel” of Data Analysis and Machine Learning Course, in which we will first learn Microsoft Excel as a Data Analysis and Visualization tool.
Microsoft Excel is part of the Microsoft Office 365 suite and is most widely used by Data Analysts to work on data sets and gain knowledge from the data sets.
However, it is not a proper Business Intelligence (BI) tool as compared to Tableau, Microsoft PowerBI, etc. If you have a large dataset, then it is not recommended to work with Excel as it is not a Big Data tool.
In that case, R and Python are your best friends. But, there is no harm in adding MS Excel to your skillset. It is being used in the industry more as a Data Visualization tool to build dynamic and interactive dashboards.
Now, let’s see how MS Excel qualifies as a powerful Data Analysis tool.
Features available in MS Excel for data analysis
1. Sort – You can easily sort data in Excel in either ascending or descending order.
2. Filter – Filtering allows you to apply single or multiple conditions on top of your data to get the desired data. For example, let’s say you have a record of hundreds of thousands or even larger data of Population in the city with the First Name, Last Name, Age, and other relevant fields.
You want to analyze how many people are there in the city whose First Name starts with a specific character and is between an age group of 18 to 30. Excel makes it very easy for Data Analysts to perform these tasks.
3. Formatting and Conditional Formatting – Excel also provides several options for Formatting the data i.e., change the visual appearance of data without altering the values.
In simple formatting, let’s say there is a value of 2 in a cell which is a Numeric or Int data type and you want to make it a percentage, then you can use Formatting in Excel to easily do it. We will see Formatting and Conditional Formatting in more detail, later in the course.
5. Data visualization using Charts – Data Visualization is an important part of the Data Analysis process. It makes it easier for the end-user to comprehend data and make better business decisions.
Different types of charts that you can create inside Excel:
- Pie Chart
- Area Chart
- Column Chart
- Line Chart
- Scatter (X, Y) Chart or Bubble Chart, etc.
We will see different types of basic and advanced Excel charts later in this course.
6. Dynamic and Interactive dashboards using basic and advanced charts – Here we will see how we can create dynamic and interactive dashboards in Excel using Pivot Tables and Excel Charts.
7. What if analysis – It is another powerful feature of MS Excel with which you can try different values for Excel Formulas to analyze different scenarios.
8. Financial Planning – Excel is also widely used as a Financial Planning tool as it allows a multitude of tasks – Data Storage, Data Visualization, Financial Modelling, Regression/Statistical Analysis, etc
Course Outline for Data Analysis and Visualization with Excel
Now, let’s talk about what we will learn in this Data Science & Analytics Course as far as Excel as a tool for DA is concerned.
1. Working with spreadsheets: Inserting, Deleting Data and Formatting values inside cells or range of cells.
2. Functions: SUM, SUMIF, FIND, SUBSTITUTE, REPLACE, RANK, VLOOKUP, CONCAT, LEN, and other most widely used Excel functions by Data Analysts.
3. Data Validation and Error Handling: Applying validations on cells to allow insertion of specific data type or within range. Also, we will see how to handle the errors when an end-user enters the wrong value in a cell on which data validation rules are applied.
4. Getting inference from tables: Pivot Charts
5. Data Visualization in Excel using various types of charts: Line chart, Area chart, Pie chart, Scatter chart, Thermometer chart, and other advanced charts.
6. Building Dynamic and Interactive Dashboards In Excel – Using Pivot Tables and advanced Excel charts to create a dynamic and interactive dashboard for a sales company.
