Advanced Excel

Course Overview

Week 1

Introduction to Data Analysis Using Spreadsheets

1. Fundamentals of spreadsheet applications
2. introduction to the Excel interface
3. navigate worksheet and workbook.

Getting Started with Using Excel Speadsheets

1. Perform basic spreadsheet tasks, such as viewing, entering and editing data, and moving, copying and filling data.
2. fundamentals of formulas
3. reference data in formula

Cleaning & Wrangling Data Using Spreadsheets

1. Importance of data quality
2.  How to import file data in to Excel
3.  Fundamentals of data privacy
4.  Remove duplicate and inaccurate data
5.  Remove empty rows in your data
6.  How to use the Flash Fill and Text to Columns features to manipulate and standardize the data.

Week 2

Analyzing Data Using Spreadsheets

1.  Fundamentals of analyzing data using a spreadsheet
2.  Filter and sort data
3. VLOOKUP ,XLOOKUP and HLOOKUP reference functions
4. Pivot tables in Excel, and use several pivot table features
5. Charts and Pivot Charts

Data validation and formatting

1. Conditional Formatting
2. Data validation using error alert
3. List method

Week 3

1. Power Query is a tool that allows users to connect to data outside of Excel and clean and manipulate the data.
2. COUNTIF function to determine how many times a certain word or phrase appears in a selected data set, such as a specific department name or other identifying feature. 
3. Sparklines display a visual representation of data points via lines or columns inside a single cell. 

Week 4

1. What If Analysis 
2. Goal seek
3. Scenario Manager
4.  Data Table
5. Forecast Sheet
6. PMT Function,ROI Function,IRR function