Excel for Data Analysis: Basic to Expert Level
4.6 (13 ratings)
109 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel for Data Analysis: Basic to Expert Level to your Wishlist.

# Excel for Data Analysis: Basic to Expert Level

Learn how to use Excel for data analysis to an expert level and be able to apply this practical skill in any field.
4.6 (13 ratings)
109 students enrolled
Created by Diego Fernandez
Last updated 11/2016
English
Current price: \$10 Original price: \$50 Discount: 80% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
• 6.5 hours on-demand video
• 1 Article
• 5 Supplemental Resources
• Access on mobile and TV
What Will I Learn?
• Use main file types depending on data’s original format and protect your spreadsheet when sharing it.
• Store data in workbooks consisting of one of more worksheets which can be created according to your requirements.
• Navigate worksheets by identifying cells as intersections of columns and rows while grouping them in ranges.
• Move quickly through working space by using keyboard short-cuts and specific key-tips.
• Perform data operations with basic arithmetic formulas, main categories’ built-in functions and arrays.
• Correct formula errors and perform input validation when interacting with users.
• Visualize data through conditional formatting, general and specific chart types, and single-cell spark-lines.
• Organize data interactively using tables and pivot tables while you sort and filter it according to your needs.
• Implement future scenarios through what-if analysis and find value for achieving certain formula result through goal seek.
• Analyze data with add-in packages to identify trends by spotting a signal and minimizing the noise.
• Summarize descriptive statistics of your data and provide a frequency histogram of these observations.
• Evaluate correlation and causation between explained and explanatory variables.
• Simulate random numbers based on pre-defined or user-defined probability distribution functions.
• Optimize formula result with solver add-in by determining changing variables and their constraints.
View Curriculum
Requirements
• Course is done with Microsoft Excel 2010. Easy compatibility with older and newer software versions. Some features are not available in older ones.
• Course Excel data file provided by instructor.
• NO previous Excel knowledge is required.
Description

Learn Excel for data analysis from basic to expert level and be able to apply this practical skill in any field. It explores spreadsheet software’s most relevant features which will help you become an expert as beginner, achieve proficiency as basic user or polish skills and finish learning curve as an intermediate one. All of this while using real-world data together with practical exercises done step-by-step.

Become an Expert in Excel for data analysis by learning its most relevant features.

• Use main file types and their options.
• Store data in worksheets within workbooks.
• Navigate worksheets’ cells quickly by using keyboard short-cuts.
• Perform data operations with formulas and built-in functions.
• Visualize data through conditional formatting and charts.
• Organize data interactively with tables and pivot tables.
• Implement scenarios with what-if analysis.
• Analyze data with statistical and probabilistic add-in tools.
• Optimize formula results with solver add-in.

Achieve Excel for data analysis proficiency and be able to apply it in any field.

Learning Excel is essential for any professional or academic career based on data analysis. It is the most commonly used data analysis software both professionally and academically and it’s a solid foundation before learning any other.

But as learning process can become difficult as complexity grows, this course helps by leading you through step-by-step real-world practical exercises for greater effectiveness.

Content and Overview

This course contains 39 lectures and 6.5 hours of content. It’s designed for all Excel knowledge levels and no previous familiarity is required.

At first, you’ll learn how to use main file types depending on data format and how to protect it when sharing. Then you’ll study how to store data in workbooks containing one or more worksheets and their navigation by identifying cells as intersection points of columns and rows. This can be done quicker when using keyboard short-cuts.

After that, you’ll perform data calculations with formulas and built-in functions, correct any errors in them, and perform input validation when interacting with users. Next, you’ll visualize data with conditional formatting and charts. You’ll organize it interactively with tables and pivot tables which can be sorted and filtered.

Later, you’ll implement scenarios with what-if analysis. You’ll also do data analysis through statistical and probabilistic add-in tools such as identifying trends, deliver descriptive statistics and frequency histograms, evaluate correlation and causation between explained and explanatory variables, and simulate random numbers with pre-defined or user-defined probability distribution functions. Finally, you’ll optimize a formula’s result using solver add-in by defining changing variables and their constraints.

Who is the target audience?
• Users with no previous knowledge who want to achieve an expert level in Excel for data analysis.
• Basic users who want to take the next steps in Excel for data analysis proficiency.
• Intermediate users who want to polish their Excel for data analysis skills and complete the learning curve.
Compare to Other Excel Analytics Courses
Curriculum For This Course
39 Lectures
06:26:39
+
Course Overview
4 Lectures 21:46

Course File
00:03

In this lecture you can download slides with section lectures’ details and main themes to be covered related to course description (objectives, requirements and instructor profile) and course overview main sections (excel file, formulas and functions, and data analysis).

Course Overview Slides
4 pages

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

Preview 04:19

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).

Preview 17:24
+
Excel File
8 Lectures 01:33:34

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

Excel File Slides
2 pages

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).
Preview 03:54

In this lecture you will learn main file options (open, save and print) and key protection options (workbook, worksheet and cells).

Preview 11:10

In this lecture you will learn that a workbook is a spreadsheet file containing several worksheets. You will also study main file types (standard .xlsx, macro-enabled .xlsm and comma-separated values .csv) and ribbons (menu tabs, customize and minimize options).

Workbook
12:44

In this lecture you will learn that a worksheet is composed of a large number of cells where data can be stored, manipulated or displayed. You will also study main worksheet options (insert, rename, move, delete, copy, zoom, split and freeze panes).

Worksheet
11:38

In this lecture you will learn that a cell is an intersection point of a vertical column and a horizontal row. You will also learn main column and row options (insert, delete), key cell formats (general, date, number, currency, percentage, accounting, scientific, time and fraction) and general cell references (relative, absolute, mixed, 3-D and external).

Cell
19:07

In this lecture you will learn that a range is a group of cells being selected. You will also study main range options (copy/paste, move and transpose), key range calculations (union and intersect) and useful columns and rows options (hide/un-hide and group/un-group).
Range
17:18

In this lecture you will learn that keyboard shortcuts are useful for performing actions more efficiently to increase productivity. You will also study main CTRL, SHIFT and ALT keys’ options (CTRL + a, CTRL + c, CTRL + v, CTRL + x, CTRL + z, CTRL + arrow, SHIFT + arrow, CTRL + SHIFT + arrow, ALT + =, CTRL + d, CTRL + 1), F keys’ alternatives (F1 to F12) and key tips with ALT or F10 (ALT/F10 + n).

Keyboard Shortcuts
17:43
+
Formulas and Functions
15 Lectures 02:23:06
In this lecture you can download slides with section lectures’ details and main themes to be covered related to formulas and functions (formulas and functions), key categories (date & time, financial, logical, lookup & reference, mathematic & trigonometric, statistical and text), formula errors, define range name, array formula and data validation.
Formulas and Functions Slides
3 pages

In this lecture you will learn section lectures’ details and main themes to be covered related to formulas and functions (formulas and functions), key categories (date & time, financial, logical, lookup & reference, mathematic & trigonometric, statistical and text), formula errors, define range name, array formula and data validation.
Formulas and Functions Overview
09:21

In this lecture you will learn that a formula is used to make calculations on values entered and stored in the worksheet. You will also study basic mathematical formulas (+, -, *, /, ^), arithmetic returns, fill range, status bar and quick operations.
Formulas
10:10

In this lecture you will learn that functions are built-in formulas consisting of one or more arguments. You will also study main function categories (date & time, financial, logical, lookup & reference, mathematic & trigonometric, statistical and text) and how to insert functions (from menu and manually).
Functions
04:58

In this lecture you will learn that date & time functions are useful when comparing two or more data points at different dates. You will also study main date extraction functions (YEAR, MONTH, DAY), date building function (DATE), date comparison functions (WEEKDAY, NETWORKDAYS) and current date function (NOW).
Date & Time Functions
12:46

In this lecture you will learn that financial functions are useful when performing these types of calculations with data. You will also study compounded interest, main payment functions through a loan amortization example (PMT, RATE, NPER, PV, FV), key amortization and depreciation functions through fixed assets example (SLN, SYD and DB).
Financial Functions
19:46

In this lecture you will learn that logical functions are useful when doing “if”, “and”, “or” operations with data. You will also study main logical functions (IF, IFERROR, Nested IF, AND, OR).
Logical Functions
12:00

In this lecture you will learn that lookup & reference functions are useful when searching for data in a specific range or array. You will also study main lookup & reference functions (VLOOKUP, HLOOKUP, MATCH and INDEX).
Lookup & Reference Functions
12:31

In this lecture you will learn that mathematical & trigonometric functions are useful when performing these types of calculations with data. You will also study main arithmetic functions (SUM and SUMIF), rounding functions (ROUND, ROUNDUP and ROUNDDOWN) and random number functions (RAND and RANDBETWEEN).
Mathematic & Trigonometric Functions
09:09

In this lecture you will learn that statistical functions are useful when performing these types of calculations with data. You will also study main descriptive statistics functions (AVERAGE, AVERAGEIF, MEDIAN, MODE, STDEV, COUNT, COUNTIF), maximum and minimum values (MAX, MIN, LARGE, SMALL), ranking function (RANK), probability functions (PERCENTILE and QUARTILE) and forecasting formula (TREND).
Statistical Functions
16:51

In this lecture you will learn that text functions are useful when manipulating text or number strings. You will also study main text functions (LEFT, RIGHT, MID, CONCATENATE, LEN and FIND) and text editing function (REPLACE).
Text Functions
09:07

In this lecture you will learn that array formulas are useful when performing several calculations in one single cell. You will also study how to insert an array and implement calculations in it (CTRL + SHIFT + ENTER).
Array Formula
06:03

In this lecture you will learn main formula errors (#### Error, #NAME? Error, #VALUE! Error, #DIV/0! Error, #REF! Error, Circular Reference) and formula auditing options (trace precedents, trace dependents, remove arrows, show formulas and error checking).
Formula Errors
08:19

In this lecture you will learn how to define a range name, use it in a formula and manage the range names.
Define Range Name
06:29

In this lecture you will learn that data validation functions are useful when interacting with users and corroborating their input.
Data Validation
05:36
+
Data Analysis
12 Lectures 01:56:12
In this lecture you can download slides with section lectures’ details and main themes to be covered related to data organization (sorting, filtering, conditional formatting), data visualization (chart types), interactive data analysis (tables and pivot tables), scenario generation (what-if analysis), data analysis tools package (trends, statistics, probability, correlation and causation) and equation optimization (solver add-in).
Data Analysis Slides
3 pages

In this lecture you will learn section lectures’ details and main themes to be covered related to data organization (sorting, filtering, conditional formatting), data visualization (chart types), interactive data analysis (tables and pivot tables), scenario generation (what-if analysis), data analysis tools package (trends, statistics, probability, correlation and causation) and equation optimization (solver add-in).
Data Analysis Overview
08:36

In this lecture you will learn that sort and filter options are useful when ordering data. You will also study how to sort one or multiple columns, insert and clear a table filter.
Sorting and Filtering
05:12

In this lecture you will learn that conditional formatting options are useful when configuring data based on certain conditions. You will also study highlight cell rules (top/bottom, data bars, color scales, icon sets) and how to manage these rules.
Conditional Formatting
09:25

In this lecture you will learn that charts are useful for visualizing data. You will also study main chart types (column, bar, line, area, pie) key specific chart types (stock, scatter), how to add trend-line to scatter chart and handy single cell spark-lines to spot trends (line, column, win/loss).
Chart Types
18:51

In this lecture you will learn that tables and pivot tables are useful when organizing data and interacting while analyzing it. You will also study main table options (insert, sort, filter, header and total) and key pivot table options (insert, drag fields, sort, filter, value field settings, chart).
Tables and Pivot Tables
14:54

In this lecture you will learn that what-if analysis is useful when generating and comparing different scenarios and doing backward calculations for goal seeking. You will also study scenario manager to add, modify, delete, summarize scenarios and use goal seek to find the value of a variable to achieve a desired result.
What-If Analysis
11:57

In this lecture you will learn that data analysis tools are statistical and probabilistic packages useful when performing data analysis. You will also study main trend analysis packages to identify a signal a remove noise within data (moving average and exponential smoothing).
Data Analysis Tools Package (Trends)
06:33

In this lecture you will learn that data analysis tools are statistical and probabilistic packages useful when performing data analysis. You will also study main statistics and probability analysis packages for key statistics summary and probability distributions (descriptive statistics and histogram).
Data Analysis Tools Package (Statistics and Probability)
08:30

In this lecture you will learn that data analysis tools are statistical and probabilistic packages useful when performing data analysis. You will also study correlation between variables, causation between variables, statistical relevance of coefficients and statistical relevance of causation (correlation and causation).
Data Analysis Tools Package (Correlation and Causation)
10:34

In this lecture you will learn that data analysis tools are statistical and probabilistic packages useful when performing data analysis. You will also study random number generation with several variables, standard normal probability distribution and discrete probability distribution.
Data Analysis Tools Package (Random Number Generation)
04:33

In this lecture you will learn that solver add-in is useful when optimizing an equation with two or more changing variables. You will also how to set an optimization objective, determine changing variables, and define constraints through an investment portfolio optimization example.
17:07