Excel For Data Analysis Basic To Expert Level

Learn data analysis main concepts from basic to expert level through a practical course with Excel.

Last updated 2022-01-10 | 3.6

- Use main Excel file or workbook types and protect them before sharing.
- Store data in workbooks consisting of one of more worksheets.
- Navigate worksheets through their cells which can be grouped in ranges.

What you'll learn

Use main Excel file or workbook types and protect them before sharing.

Store data in workbooks consisting of one of more worksheets.

Navigate worksheets through their cells which can be grouped in ranges.

Move quickly through worksheet using keyboard shortcuts.

Perform data calculations using basic arithmetic formulas

main categories built-in functions and arrays.

Correct formula errors and perform user input data validation.

Organize data interactively using tables and customizable pivot tables while sorting

filtering and performing calculations on their contents.

Visualize data using conditional formatting

main chart types and single cell sparklines.

Implement data scenarios summary forecast using what-if analysis and find value for achieving certain calculation result using goal seek changing cell iteration.

Analyze data and estimate optimal parameter using data analysis tools package and solver Microsoft Excel® Add-ins.

Identify data trends using moving average and exponential smoothing tools.

Summarize data descriptive statistics and print its frequency histogram.

Estimate correlation between variables and analyze regression summary output between explained and explanatory variables.

Generate random numbers based on specific probability distribution.

Estimate optimal parameter using constrained minimization.

* Requirements

* Microsoft Excel® spreadsheet software is required.
* Practical example spreadsheet provided with course.
* Prior basic spreadsheet software knowledge is useful but not required.

Description

Use main Excel file or workbook types and protect them before sharing.
Store data in workbooks consisting of one of more worksheets.
Navigate worksheets through their cells which can be grouped in ranges.
Move quickly through worksheet using keyboard shortcuts.
Perform data calculations using basic arithmetic formulas, main categories built-in functions and arrays.
Correct formula errors and perform user input data validation.
Organize data interactively using tables and customizable pivot tables while sorting, filtering and performing calculations on their contents.
Visualize data using conditional formatting, main chart types and single cell sparklines.
Implement data scenarios summary forecast using what-if analysis and find value for achieving certain calculation result using goal seek changing cell iteration.
Analyze data and estimate optimal parameter using data analysis tools package and solver Microsoft Excel® Add-ins.
Identify data trends using moving average and exponential smoothing tools.
Summarize data descriptive statistics and print its frequency histogram.
Estimate correlation between variables and analyze regression summary output between explained and explanatory variables.
Generate random numbers based on specific probability distribution.
Estimate optimal parameter using constrained minimization.

Course content

4 sections • 42 lectures

Course Overview 5 lectures

Course Description Preview 04:48

In this lecture you will view course disclaimer and learn which are its objectives, how you will benefit from it, its previous requirements and my profile as instructor.

Course Overview Preview 06:45

In this lecture you will learn that it is recommended to view course in an ascendant manner as each section builds on last one. You will also study course structure and main sections (course overview, excel file, formulas and functions, and data analysis).

Excel for Data Analysis Preview 12:17

In this lecture you will learn data analysis definition, course calculations with Microsoft Excel® spreadsheet software, course file downloading, course data definition and Microsoft Excel® Add-ins for data analysis calculations (Analysis ToolPak and Solver).

Course File Preview 00:03

Before starting course please download .XLSX Microsoft Excel file as additional resources.

Course Overview Slides Preview 11 pages

You can download .PDF section slides file as additional resources below.

Excel File 8 lectures

Excel File Slides Preview 13 pages

You can download .PDF section slides file as additional resources below.

Excel File Overview Preview 04:03

In this lecture you will learn section lectures’ details and main themes to be covered related to Excel file (file options, workbook, worksheet, cell, range and keyboard shortcuts).

File Options Preview 03:17

In this lecture you will learn main file options definitions.

Workbook Preview 08:45

In this lecture you will learn workbook definition and main calculations (CONCATENATE() function).

Worksheet Preview 10:19

In this lecture you will learn worksheet definition.

Cells Preview 10:36

In this lecture you will learn cells definition and main calculations (A1, $A$1, $A1, A$1, 3D references).

Ranges Preview 10:21

In this lecture you will learn ranges definition and main calculations (SUM() function).

Keyboard Shortcuts Preview 16:48

In this lecture you will learn keyboard shortcuts definition and main calculations (CTRL + LETTER, SHIFT + ARROW, CTRL + SHIFT + ARROW, F1 to F12 keyboard shortcuts).

Formulas and Functions 16 lectures

Formulas and Functions Slides Preview 32 pages

You can download .PDF section slides file as additional resources below.

Formulas and Functions Overview Preview 08:55

In this lecture you will learn section lectures’ details and main themes to be covered related to formulas and functions (formulas, date & time functions, financial functions, logical functions, lookup & reference functions, mathematic & trigonometric functions, statistical functions, text functions, array formulas, formula errors, defined name and data validation).

Formulas Preview 05:28

In this lecture you will learn formulas definition and main calculations (+, -, *, / operators).

Date & Time Functions Preview 12:59

In this lecture you will learn data & time functions definition and main calculations (YEAR(), MONTH(), DAY(), DATE(), WEEKDAY(), NETWORKDAYS(), NOW() functions).

Financial Functions 1 Preview 14:30

In this lecture you will learn financial functions definition and main calculations (RRI() function).

Financial Functions 2 Preview 15:17

In this lecture you will learn financial functions definition and main calculations (PMT(), RATE(), NPER(), PV(), PPMT(), IPMT(), SUM(), SLN(), SYD(), DB() functions).

Logical Functions Preview 13:59

In this lecture you will learn logical functions definition and main calculations (IF(), IFERROR(), AND(), OR() functions).

Lookup & Reference Functions Preview 11:35

In this lecture you will learn lookup & reference functions definition and main calculations (VLOOKUP(), HLOOKUP(), MATCH(), INDEX() functions).

Mathematic & Trigonometric Functions Preview 09:31

In this lecture you will learn mathematic & trigonometric functions definition and main calculations (SUM(), SUMIF(), RAND() RANDBETWEEN(), ROUND(), ROUNDUP(), ROUNDDOWN() functions).

Statistical Functions 1 Preview 14:49

In this lecture you will learn statistical functions definition and main calculations (AVERAGE(), AVERAGEIF(), MEDIAN(), MODE.SNGL() functions).

Statistical Functions 2 Preview 18:23

In this lecture you will learn statistical functions definition and main calculations (STDEV.S(), STDEV.P(), VAR.S(), VAR.P(), SKEW(), KURT(), CORREL(), COUNT(), COUNTIF(), MAX, MIN(), LARGE(), SMALL(), RANK.EQ(), RANK.AVG(), PERCENTILE.INC(), PRECENTILE.EXC(), QUARTILE.INC(), QUARTILE.EXC() functions).

Text Functions Preview 11:55

In this lecture you will learn text functions definition and main calculations (LEFT(), RIGHT(), MID(), CONCATENATE(), LEN(), FIND(), REPLACE() functions).

Array Formula Preview 06:32

In this lecture you will learn array formula definition and main calculations (CTRL + SHIFT + ENTER).

Formula Errors Preview 11:11

In this lecture you will learn formula errors definition.

Defined Name Preview 07:00

In this lecture you will learn defined name definition.

Data Validation Preview 06:11

In this lecture you will learn data validation definition.

Data Analysis 13 lectures

Data Analysis Slides Preview 17 pages

You can download .PDF section slides file as additional resources below.

Data Analysis Overview Preview 09:43

In this lecture you will learn section lectures’ details and main themes to be covered related to data analysis (sorting and filtering, conditional formatting, chart types, tables and pivot tables, what-if analysis, data analysis tools package Microsoft Excel ® Add-in, moving average analysis tool, exponential smoothing analysis tool, descriptive statistics analysis tool, histogram analysis tool, correlation analysis tool, regression analysis tool, random number generation analysis tool and solver Microsoft Excel® Add-in).

Sorting and Filtering Preview 09:59

In this lecture you will learn sorting and filtering definitions.

Conditional Formatting Preview 10:06

In this lecture you will learn conditional formatting definition.

Charts 1 Preview 13:51

In this lecture you will learn charts definition (line chart, column chart, pie chart).

Charts 2 Preview 14:00

In this lecture you will learn charts definition (scatter chart, line sparkline, column sparkline, win/loss sparkline).

Tables and Pivot Tables Preview 13:32

In this lecture you will learn tables and pivot tables definition.

What-If Analysis and Goal Seek Preview 13:01

In this lecture you will learn what-if analysis and goal seek definitions.

Data Analysis Tools (Time Series Smoothing) Preview 18:51

In this lecture you will learn data analysis tools for time series smoothing definition and main calculations (data analysis tools package Microsoft Excel® Add-in moving average and exponential smoothing analysis tools).

Data Analysis Tools (Summary Statistics and Histogram) Preview 09:14

In this lecture you will learn data analysis tools for summary statistics, histogram definitions and main calculations (data analysis tools package Microsoft Excel® Add-in descriptive statistics and histogram analysis tools).

Data Analysis Tools (Correlation and Regression) Preview 16:19

In this lecture you will learn data analysis tools for correlation, regression definitions and main calculations (data analysis tools package Microsoft Excel® Add-in correlation and regression analysis tools).

Data Analysis Tools (Random Number Generation) Preview 06:06

In this lecture you will learn data analysis tools for random number generation definition and main calculations (data analysis tools package Microsoft Excel® Add-in random number generation analysis tool).