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.
3.8 (7 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
73 students enrolled
$19
$40
52% off
Take This Course
  • Lectures 39
  • Length 6.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 10/2015 English

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

What are the 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.

What am I going to get from this course?

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

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

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Course Overview
Article

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

4 pages

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

04:19

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.

17:24

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

Section 2: Excel File
2 pages

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

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

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

12:44

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

11:38

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

19:07

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

17:18
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).
17:43

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

Section 3: Formulas and Functions
3 pages
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.
09:21
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.
10:10
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.
04:58
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).
12:46
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).
19: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).
12:00
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).
12:31
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).
09:09
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).
16:51
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).
09:07
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).
06:03
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).
08:19
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).
06:29
In this lecture you will learn how to define a range name, use it in a formula and manage the range names.
05:36
In this lecture you will learn that data validation functions are useful when interacting with users and corroborating their input.
Section 4: Data Analysis
3 pages
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).
08:36
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).
05:12
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.
09:25
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.
18:51
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).
14:54
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).
11:57
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.
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 trend analysis packages to identify a signal a remove noise within data (moving average and exponential smoothing).
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 main statistics and probability analysis packages for key statistics summary and probability distributions (descriptive statistics and histogram).
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 correlation between variables, causation between variables, statistical relevance of coefficients and statistical relevance of causation (correlation and causation).
04: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 random number generation with several variables, standard normal probability distribution and discrete probability distribution.
17:07
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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Diego Fernandez is author of high-quality online courses and ebooks at Exfinsis for anyone who wants to become an expert in financial data analysis.

His main areas of expertise are finance and data analysis. Within finance he has focused on stock fundamental, technical and investment portfolio analysis. Within data analysis he has concentrated on applied statistics, probability, optimization methods, forecasting models and machine learning. For all of this he has become proficient in Microsoft Excel®, R statistical software and Python programming language analysis tools. 

He has important online business development experience at fast-growing startups and blue-chip companies in several European countries. He has always exceeded expected professional objectives by starting with a comprehensive analysis of business environment and then efficiently executing formulated strategy.

He also achieved outstanding performance in his undergraduate and postgraduate degrees at world-class academic institutions. This outperformance allowed him to become teacher assistant for specialized subjects and constant student leader within study groups. 

His motivation is a lifelong passion for financial data analysis which he intends to transmit in all of the courses.

Ready to start learning?
Take This Course