Excel for Data Analytics
Date :………………………………..Day 1. Time: Hour 1,2,3
Module-1.1:Formula
1.Aggregation Functions ( SUM, AVG, COUNT, MAX, MIN)
2.Conditional Functions ( IF, COUNTIF, SUMIF)
3.Text Functions (COUNTA, LEFT, LEN, REPLACE, CONCATENATE)
4.Date and Time Function (DATE, TIME, NOW, YEAR)
5.Reference Functions ( VLOOKUP, LOOKUP, MATCH, ROW)
Module-1.2:
Table Formatting
Conditional Formatting
Pivot Table
Data –sorting
Data–filtering
Data –Pivot
Data–unpivot
.csv to table(Raw Data)
Module-1.3:
Histogram-chart Design
Power pivot
Data Cleaning-Blank row deleting, Transformation data
Excel for Data Analytics:
Topics Covered: 1
Introduction of Excel interface
Cell formatting – Copy, Cut, and Paste in Excel
Formatting Shortcuts
Adding and Deleting Columns and Rows in Excel
Use of Excel Shortcuts (File attached)
Cell Referencing (Related vs absolute referencing- File Attached)
Self-finance handling in Excel. (File Attached)
Flash Fill Tutorial (File Attached)
Conditional Formatting. (File Attached)
Microsoft Free Account and Office 365 installation (File attached)
Topics Covered: 2
1. SUM SUMIF SUMIFS Functions (File attached)
2. AVERAGE AVERAGEIF AVERAGEIFS Functions (File attached)
3. COUNT Counta Countblank COUNTIF COUNTIFS Functions (File attached)
4. IFERROR Functions (File attached)
5. Text Functions (File attached)
6. MAX MIN MAXIFS MINIFS Functions (File attached)
7. ROUND Functions (File attached)
Topics Covered: 3
1. Lookup function (Vlookup, Hlookup & Xlookup)
2. Index Match
3. Chat GPT and Data Analytics
4. How can we use Chat GPT in excel function?
5. Excel function Explanation with ChatGPT.
Topics Covered:4
1. What is a Power Pivot and Power Query?
2. Data cleaning with Power query.
3. Working with multiple tables in power pivot.
Please also find the working files for your reference.
Topics Covered:5
1. What is a pivot table?
2. Creating a pivot table.
3. Design & formatting a pivot table.
4. Working with a pivot table.
5. Working with Slicer.
Please also find the working files for your reference.
Topics Covered:6
1. What is Visualisation? How to choose your best chart?
2. Excel Charts & Graphs formatting
3. Pie chart and Doughnut chart
4. Line chart
5. Area chart
6. Bar chart & column chart
7. Tree map
8. Histogram & pareto chart
9. Use of Pivot Table and Dynamic chart
Topics Covered:7
1. Heat Map and Scatter plot.
2. Waterfall chart
3. Excel Dashboard -1(First Real-life Project)
4. Excel Dashboard -2(Second Real-life Project)
Excel M-1.1 for Data science:
Topics Covered:
1.MAE=Mean Absolute Error
2.MSE= Mean Square Error
3.TSE= Total Square Error
4.RMSE=Root Mean Square Error
Solver:
Data Analysis:
