Microsoft Excel Data Analyst- Basic to Intermediate (3 days with agenda)

INTRODUCTION

This course is tailored for individuals with a general understanding of Excel who aspire to advance their skills and explore more functions and features within Microsoft Excel.

Module 1: Classic data analysis with excel

• 1. Types of Data Analysis
• 2. Data Analysis with Excel
• 3. Copying Name using Formula Autocomplete
• 4. Creating Range Names

Module 2: Spreadsheet fundamental

• 1. Editing Names
• 2. Using Names in a formula
• 3. Difference between Tables and Ranges

Module 3: Predefined functions

• 1. Quick analysis with TOTALS
• 2. Aggregates values using Sum
• 3. Average of data
• 4. Count of cells
• 5. Running Total of table data

Module 4: Application of Data Validation

• 1. Prepare the Structure for the Worksheet
• 2. Format Serial Number Values

Module 5: Data Normalization

• 1. Date Formats
• 2. Converting Dates in Serial Format to Month-Day-Year Format
• 3. Obtaining Today's Date
• 4. Customizing the Definition of a Weekend
• 5. Extracting Year, Month, Day from Date
• 6. Obtaining Date from Year, Month and Day

Course Outline :: Microsoft Excel Data Analyst- Basic to Intermediate::

• 7. Combining lexis

Module 6: Pivot Tables

• 1. Creating PivotTable
• 2. Recommended PivotTables
• 3. PivotTable Fields
• 4. PivotTable Areas
• 5. Nesting in the PivotTable
• 6. Filters
• 7. Slicers
• 8. Summarizing Values by other Calculations
• 9. PivotTable Tools
• 10. Expanding and Collapsing Field
• 11. Report Presentation Styles

Module 7: Data Visualization in Excel

• 1. Creating Combination Charts
• 2. Creating a Combo Chart with Secondary Axis.
• 3. Discriminating Series and Category Axis
• 4. Chart Elements and Chart Styles

Module 8: Module Build Interactive Excel Dashboards

• 1. Report Layout Finalization
• 2. Selecting the Background
• 3. Changing the Font
• 4. Slicers
• 5. Drill-down analysis

Module 9: Examining Distribution

• 1. Descriptive Statistics
• 2. Constructing advanced graphs and charts
• 3. Introduction to Statistical Data Analysis
• 4. One categorical variable
• 5. One numerical variable

Module 8: Descriptive Statistics

• 1. Arithmetic mean of data
• 2. Most common value in data
• 3. Standard deviation of data

Course Outline :: Microsoft Excel Data Analyst- Basic to Intermediate::

• 4. Variance of data
• 5. Range of data
• 6. Maximum value of data
• 7. Minimum value of data
• 8. Summation of values
• 9. Counts of number values

Module 9: Constructing advanced graphs and charts

• 1. Boxplot
• 2. Interactive Histogram with detail
• 3. Scatterplot
• 4. Stacked bar chart

Module 10: Introduction to Statistical Data Analysis

• 1. Correlation coefficient between two sets of numbers
• 2. Coefficient of determination between two sets of numbers
• 3. Slope of a regression line through two sets of numbers

Module 11: Regression Analysis

• 1. Simple Linear Regression
• 2. Regression Function
• 3. Exploring Regression
• 4. Performing a Regression Analysis
• 5. Checking the Regression model

Module 12: Forecast Analysis

• 1. Forecast using historical data
• 2. Create Forecast Chart
• 3. Create Forecast Data

Module 13 - Power BI Desktop

• 1. Using Power Query Editor
• 2. Data types
• 3. Renaming columns and queries
• 4. Navigating the main Power BI
• 5. Desktop window
• 6. The data model
• 7. Create and delete relationships

Course Outline :: Microsoft Excel Data Analyst- Basic to Intermediate::

• 8. Modify data formats

Module 14 - Data Normalization

• 1. Normalizing a dataset
• 2. Investigate the dataset

Module 15 - Visualizations

• 1. Overview of visualizations
• 2. Create a column or bar chart
• 3. Basic formatting of visualizations
• 4. Focus mode
• 5. Create a line chart

6, Create a card visualization

• 7. Create a gauge visualization
• 8. Create a table visualization
• 9. Create a matrix visualization

Module 16 - Working with Filters

• 1. Slicers
• 2. Slicers and the data model
• 3. The Filters pane
• 4. Visual filters
• 5. Creating a Top N filler
• 6. Filter an entire page

Module 17 - Spatial Visuals

• 1. Create a map visualization
• 2. Create a filled map

Module 18 - Create Dashboards

• 1. Reports to dashboards
• 2. Create a new dashboard
• 3. Pin visualizations from other pages and reports
• 4. Pin an entire report page
• 5. Quick Insights
• 6. Related Insights
• 7. Other tiles
• 8. Add an image to a dashboard

Course Outline :: Microsoft Excel Data Analyst- Basic to Intermediate::

• 9. Add web content to dashboard
• 10. Add video to a dashboard
• 11. Add a text box to a dashboard

Module 19 - Working with Dashboards

• 1. Renaming dashboards
• 2. Dashboard actions
• 3. Duplicate a dashboard
• 4. Refresh dashboard tiles
• 5. Performance inspector
• 6. Settings
• 7. Dashboard themes
• 8. Arrange a dashboard for mobile use

Time Day 1 Day 2 Day 3

9.00am –

9.15am

Sign in and Start class Sign in and Start class Sign in and Start class

9.15am –

10.30am

Module 1: Classic data

analysis with excel

Module 2: Spreadsheet

fundamental

Module 8: Module Build

Interactive Excel

Dashboards

Module 9: Examining

Distribution

Module 13 - Power BI

Desktop

Module 14 - Data

Normalization

10.30am –

10.40am

Morning break Morning break Morning break

10.40am –

1.00pm

Module 3: Predefined

functions

Module 4: Application of

Data Validation

Module 8: Descriptive

Statistics

Module 9: Constructing

advanced graphs and charts

Module 16 - Working with

Filters

1.00pm –

2.00pm

Lunch break Lunch break Lunch break

2.00pm –

4.00pm

Module 5: Data

Normalization

Module 10: Introduction to

Statistical Data Analysis

Module 11: Regression

Analysis

Module 18 – Create

Dashboards

Course Outline :: Microsoft Excel Data Analyst- Basic to Intermediate::

1.

4.00pm –

4.10pm

Afternoon break Afternoon break Afternoon break

4.10pm –

5.00pm

Module 7: Data

Visualization in Excel

Q& A

Module 12: Forecast

Analysis

Q&A

Module 19 - Working with

Dashboards

Q&A

Programme scope, delivery arrangements and suitability are confirmed during enquiry.