Share Description
In this course, you will :
- Learn various data analysis techniques that can be easily implemented in Excel 2010, 2013, 2016, and 2019.
- Utilize powerful data analysis, techniques, and tools to transform data into insight.
- Bring your Excel skills from beginner to intermediate to expert level.
- Work with real-world data and solve real-world problems.
- Learn 60+ commonly used Excel functions (Logical, Text, Date, Math, Statistical, Financial).
- To summarise data sets, use aggregation commands (SUM, COUNT, SUBTOTAL, and so on) and their conditional variants (SUMIFS, COUNTIFS, AVERAGEIFS, and so on).
- VLOOKUP, INDEX, and MATCH functions are used to combine data from multiple worksheets.
- Using the Goal Seek, Data Table, and Scenario Manager tools, conduct scenario and sensitivity analysis.
- Use the PivotTable and PivotChart tools to learn best practises for data analysis and data presentation.
Syllabus :
1. Data Cleaning and Preparing Tools
- Sort and Filter
- Text to Columns
- Remove Duplicates
- Data Validation
- Find and Replace
- Data Entry Form
2. 50 Most Frequently Used Functions
- Logical Functions: IF, AND, OR
- Math Functions: SUM, COUNT, SUBTOTAL
- Text Fuctions: RIGHT, LEFT, CONCATENATE, LEN, UPPER, LOWER, PROPER
- Date Functions: YEAR, MONTH, DAY, EOMONTH, DATE
- Conditional Functions: SUMIF, SUMIFS, COUNTIF, COUNTIFS
- Statistical Functions: AVERAGE, AVERAGEIF, AVERAGEIFS
- Statistical Functions: MAX, MIN, MEDIAN, MODE, VAR, STDDEV, CORREL
- Financial Functions: PMT, PV, FV, RATE, NPER, NPV, IRR
3. Pivot Table for Data Analysis
- Introduction to Pivot Table
- Calculated Fields in Pivot Table
- Grouping Data with Pivot Table
- GetPivotData
5. Scenario and Sensitivity Analysis
- What-If Analysis: Goal Seek
- What-If Analysis: Data Table
- What-If Analysis: Scenario Manager
- Auditing Formulas
6. Lookup Functions and Merging Techniques
- VLOOKUP With Exact Match
- INDEX and MATCH vs VLOOKUP
- VLOOKUP With Approximate Match
7. Formatted and Interactive Tables
- Format as Table
- Data Model Tool
- Slicer in Pivot Table
8. Charts and Visualization Techniques
- Conditional Formatting
- Sparklines as Mini Charts
- Column Chart and Bar Chart
- Line Chart
- Pie Chart
- Area Chart
- Combo Charts
- Speedometer Chart
- PivotChart
- Creation of Interactive Dashboard
9. Data Analysis ToolPak for Statistics
- Scatter Plots
- Activation of Data Analysis ToolPak Add-in
- Descriptive Statistics
- Histogram
- Correlation
- Linear Regression
- Explanation of Regression Output
- Muliple Linear Regression
10. Bonus Tips and Tricks
- Insert Hyperlinks
- Protect Worksheet and Workbook
- Hot Shortcuts for Working in Excel
11. Case Study: A/B Testing with Excel
-
Example of A/B Testing for Company Website
