Business Data Analysis Excel

Learn to apply the important concepts and techniques of data analysis using Excel.

Last updated 2022-01-10 | 4.6

- Confidently use the most crucial Excel functions and techniques for analysis
- Correctly use the appropriate charts in Excel
- Apply analysis techniques to datasets in Excel

What you'll learn

Confidently use the most crucial Excel functions and techniques for analysis

Correctly use the appropriate charts in Excel

Apply analysis techniques to datasets in Excel

Manipulate and prep datasets in Excel

Build presentation ready dashboards in Excel

Create your own formulas in Excel

Turn real world data into business insights in Excel

* Requirements

* You should be able to use a PC or Mac at the beginner level
* Microsoft Excel 2013+

Description

Ibadullah Khalil - 5 Stars

“A great instructor and guide for the course. All topics were well driven and to the point with no gibberish language used to make it difficult for the learners just like other instructors. A highly recommended course for every beginner who aims to become a data analyst”

Jianyong Chen - 5 Stars

“This is one of the best course I took on Udemy so far. The instructors explained everything in detail and there are practices after each section. Make sure you download all the files, practice on your own, and you will learn a lot. Highly recommend!”

Every business generates data. But whether you're able to turn that into useful insights depends on your ability to process, manipulate, and ultimately translate that data.

Whether you're working for a company or running your own, being able to make better decisions requires you to be able to analyze and interpret data.

What data do you need? How do you prep that data? How do you analyze it to answer specific questions?

In this course, we're going to show you step-by-step, exactly how to do that, by starting with the very basics.

Why Microsoft Excel?

This course is as much about Excel, if not more, than data analysis concepts and techniques. Why?

Excel is still the most popular tool used by analysts in all industries, across all roles. It's used by financial analysts, marketing analysts, sales analysts, and of course, data analysts.

Yes, there are many tools out there but if you learn and master the core concepts and techniques of data analysis in Excel, you'll be able to apply your knowledge to a broad range of applications.

Don’t underestimate Excel--a single worksheet can technically hold more than 17 BILLION data points (granted, it will make your computer REALLY slow). Fortunately, most of us running analysis will need to process far fewer data points than that. Still, it speaks to the power of the program.

Maurizio Zippo - 5 Stars

“This is one of the best course I have ever taken and thoroughly enjoyed. Highly recommended if you want to brush up your existing Excel Skills and to learn how to build some complicated formulae. The Pivot Table and Charts were something I always struggled with but thanks to Symon and Travis' explanations they are now so easy to use every day at work, saving me tonnes of time! Many Thanks Symon and Travis for putting together this great course you are my Excel heroes! The case studies on Cryptocurrency and The Start Up Market are absolute gems! I can't wait to get started with your next course.”

Why learn from us?

By signing up with us, you will be learning from two instructors with a combined 35 years of relevant experience across a wide range of analyst roles.

Travis has been a digital marketer, investor, and entrepreneur for 20 years. He’s led growth in Asia for Groupon and even helped build Excel when he was as an engineer at Microsoft. He is a data analysis and data visualization expert who pulls from his industry experience to teach some of the advanced data analysis topics you’ll find in this course.

Symon has been interpreting and analyzing data for 15 years. He has served as a financial analyst, marketing analyst, and even head of marketing analytics across half a dozen industries. If you look at his other courses on Udemy, you'll see that he is an Excel and analysis ninja, having taught nearly 180,000 students with over 18,000 reviews.

Together, we’ve pulled directly from our personal experience and put everything we could into this course. In fact, all of the case studies you’ll find inside are inspired by real-world projects we’ve worked on.

What if I don't have any data analysis or Excel background?

No worries! This is why we start with the very basics in our foundational sections, which is designed to get you familiar with Excel before we move on to more advanced topics. You can be a complete beginner and take this course. It might sometimes feel like you're drinking out of a fire hose, but if you put in the time and effort we promise you’ll see results by following our course and doing the exercises.

We then progressively increase the difficulty as you move along in the course, culminating in advanced techniques taught through our case studies.

If you do have an Excel background, you can skip or skim through the foundational sections and move directly into the more advanced topics.

Stephen Dias - 5 Stars

“If you're trying to learn the foundations of excel this course is all you need.”

Brandon Parker - 5 Stars

“I have a lot of experience building excel models and purchased the course for the case studies. I didn't expect to learn much from the introductory parts but was surprised to find additional keyboard shortcuts I had never learned as well as powerful new functions that will make my life a lot easier. This course helped me brush up on functionality that I often neglected while using excel and was super comprehensive. I look forward to continuing using this course as additional case studies are added and highly recommend it for even excel experts.”

Learn through practice.

Like most things in life, becoming proficient at data analysis in Excel takes practice. A lot of practice.

Sure, you'll pick up plenty just from watching, but you'll be doing yourself a disservice if you don't download our more than 100 custom-built Excel practice files specifically designed to help you master the program.

We're not done adding all our content yet, either. This class was initially created an "intro" course, but it will soon become a complete coverage of all things data analysis in Excel.

In conclusion, does any of the following apply to you?

1. Do you want to really know your way around Excel?

2. You have data for your work or business but you're not sure what to make of it?

3. You might know your way around Excel but you're not confident about turning data into business insights

4. You frequently receive Excel spreadsheets from others but want to better understand how they arrived at their conclusions?

5. You want to learn the best practices when it comes to using Excel for data analysis.

6. You are intimidated by spreadsheets and terms like Pivot Tables and VLookups

7. You want to learn a skill set that is useful for any role in any industry you get into later.

If so, then take charge and take our course.

You have absolutely ZERO risk. Udemy gives you a solid-as-an-oak-tree 30-day money back guarantee. You have nothing to lose and everything to gain.

So if you're ready to go from data deficient to data proficient, enroll now!

Who this course is for:

Anyone who wants to learn the core concepts of data analysis in the Excel environment
Anyone who wants to learn to apply data analysis techniques using Excel
Anyone who wants to become proficient at using Excel for data/business analysis
Anyone who wants to be able to turn data into business insights in Excel
Anyone who wants to be able to run simple to sophisticated analysis on data using Excel
Anyone who wants to learn the most frequently used Excel functions and techniques used by analysts

Course content

15 sections • 132 lectures

Watch This First 3 lectures

Course Intro. Preview 01:14

Watch this first. Depending on your background with Excel and data analysis, we'd recommend different strategies for taking this course.

A few reminders before we begin Preview 01:05

COURSE FILES Preview 00:11

Dropbox link to course files

Jumpstart 7 lectures

Jumpstart Overview Preview 04:55

Video games anyone? We introduce the data set we'll be working with in this quick introductory example.

JUMPSTART NOTE Preview 00:40

Jumpstart Part 1 Preview 08:49

In this example, we see that simple built-in EXCEL formulas and functionality can make for quick analysis of numerical data.

Jumpstart Part 2 Preview 06:22

We take the sample data a bit farther--how do we calculate sub totals? We use the conditional functions in EXCEL.

Jumpstart Part 2 Filtering Unique Values Preview 03:11

Jumpstart Part 3 Preview 06:48

Having new calculations is helpful but sometimes the best way to show case your analysis is through visualization (aka using charts). We introduce you to two basic charts here.

NOTE: In the video, there is a slight error in the file ("Wii" as a platform category was showing up twice). The files in this lecture have been updated and corrected.

Quick Reminder Preview 00:26

Getting Acclimated with EXCEL - Foundations 15 lectures

Getting Acclimated Section Overview Preview 00:27

Section overview to go over the topics we'll cover in this section, which includes the Excel user interface, common keyboard shortcuts, basic functionality that all analysts working with Excel should know.

EXCEL User Interface Preview 05:21

In this lecture, we'll go over the Excel user interface and it's different components to get you familiar with it all before we really start to play with things.

Adding, Deleting, and Editing Data in a Cell Preview 03:01

In this lecture, we explore how to add data to, delete data from, and edit data inside a cell in Excel.

Introduction to Keyboard Shortcuts Preview 01:51

While we will be introducing many more Excel keyboard shortcuts in this course, we'll show you a few of the easier ones in that are frequently used in Excel

50 Time Saving Excel Shortcuts Preview 00:09

Navigation Keyboard Shortcuts Preview 02:34

In this lecture, we explore some Excel keyboard shortcuts designed to help you navigate through a worksheet more efficiently.

Data Selection Keyboard Shortcuts Preview 02:20

We will look at the use of keyboard shortcuts to selecting cells with EXCEL.

Inserting and Deleting Rows and Columns Preview 04:21

In this lecture, we look at how to insert and delete rows or columns in EXCEL, which you'll be doing quite often conducting data analysis in EXCEL.

Auto Fit Row and Column Size Preview 01:31

When the data inside the cells aren't showing up correctly, chances are the column or row sizes are not the right size. In this lecture, we'll look the useful autofit option in EXCEL.

Freeze Panes and Split Boxes Preview 04:32

Sometimes, you may want to look at multiple places within a dataset that is too far apart for for the entire data set to show on screen. When this happens, you may want to use the freeze pane or split view options in EXCEL.

Formatting Numbers Preview 07:24

There are many things a numbers can represent so when you're dealing with number in your dataset, having the right formatting can help avoid confusion.

Formatting Cells Preview 04:03

The simple cell in EXCEL may appear simple but in fact it has many formatting options that can add visual clarity to your analysis.

Grouping and Hiding Rows or Columns Preview 01:57

Sometimes, not all of the data needs to be shown. In those situations, grouping and hiding select cells could make an otherwise clunky spreadsheet more manageable.

Installing EXCEL Add-Ins Preview 00:49

In this lecture, we walk you through how to add a couple of very useful Excel Add-Ins that are useful for analysis.

Quiz #1

To quiz you on what you've learned in this section

Basic EXCEL Formulas & Functions - Foundations 17 lectures

Basic Excel Formulas & Functions Section Overview Preview 00:32

Section overview to go over the topics we'll cover in this section, which includes useful math, logical, and finance functions that comes with Excel.

Basic Math Functions in Excel Preview 04:48

Doing simple math operations in EXCEL is very easy and straight forward. In this lecture we look at the operations as well as two commonly used built-in functions.

Conditional Math Functions Preview 06:13

We looked at the SUM and COUNT functions previously, now we look at their conditional counterparts, SUMIF and COUNTIF functions in EXCEL.

MIN, MAX, and AVERAGE Functions Preview 05:51

In this lecture, we look at more of EXCEL's built-in math functions: MIN, MAX, and AVERAGE functions.

Logical Operators and Functions Preview 08:55

In this lecture, we introduce to you the logical operators and the logical IF, AND and OR functions in EXCEL.

Anchoring Cell References and Name Ranges Preview 09:14

One of the most basic building blocks to understanding how to write your own formulas for manipulating and analyzing data is cell referencing. In this lecture we look at how you can anchor cell references in EXCEL.

Array Functions Preview 05:03

Without a built in conditional MIN and MAX functions, we look at how using array functions in EXCEL can help us create our own DIY versions of MINIF and MAXIF functions.

Quick note about next three lectures Preview 00:16

The next four lectures are more finance related.

Internal Rate of Return (IRR) Functions Preview 09:17

In this lecture, we take start a short digression into the world of finance by exploring the set of internal rate of return (IRR) functions available in EXCEL. We look at what these functions calculate and each of their limitations when used.

Net Present Value (NPV) and Cash Multiple Preview 13:09

Continuing our exploration from the finance world, we look at the concept of the Net Present Value (NPV), what it means, how it's calculated, and how to use the NPV function in EXCEL.

Loan Amortization Table Preview 03:28

In the world of finance, we often deal with debt, like a mortgage on a loan. In this lecture, we'l show you how to use EXCEL's built in functions to construction an amortization table.

Compound Annual Growth Rate (CAGR) Preview 03:26

Whenever you're looking at data with a time component (e.g. sales), you'll likely be looking at growth rates to help understand your data. One common growth rate metric is known as the Compound Annual Growth Rate (CAGR). We look at how it's calculated in this lecture.

Formula Auditing Preview 04:47

As you get more sophisticated with your formula building, it's very easy to make mistakes. But EXCEL has a very useful tool for auditing your formulas.

Fill Commands Preview 04:10

In this lecture we look at a keyboard shortcut alternative to the copy and paste functions in EXCEL and not the minor differences in the results.

IS Functions Preview 04:17

Some EXCEL functions are designed to give you useful information, rather than to calculate something. In this lecture, we look at the very useful IS functions.

Rounding Numbers Preview 06:35

When working with numbers, you'll often find yourself wanting to round the results of the values. In this lecture, we explore how to use the ROUND functions in EXCEL.

Quiz #2

Text, Times, and Dates Data Formats - Foundations 16 lectures

Text, Times, and Date Data Formats Section Overview Preview 00:26

Section overview to go over the topics we'll cover in this section, which includes a deep dive in to how you'll be interacting with three very important data formats that you'll encounter when running analysis in EXCEL.

Note to EU Students about Date Format Preview 00:20

How EXCEL Treats Text, Times, and Dates Data Preview 04:52

In this lecture, we look at how EXCEL stores and interprets text, times, and date data in cells.

Changing the Case of Text Preview 02:32

Often, you'll be working with text that is inconsistently or improperly cased. However EXCEL provides ready functions that can help you change the case of text easily.

Combining Strings of Text Together Preview 02:23

In this lecture, we'll look at how you can combine strings of text together in EXCEL.

Cutting Substrings from a Text Preview 05:10

Instead of combining strings, sometimes we may want to cut a substring from an existing string of text. In this lecture, we look at the various options available in EXCEL for doing this task.

Using the FIND Function Preview 05:29

In this lecture, we look at how to use the FIND function for more advanced text search and manipulation in EXCEL.

Searching for Specific Text in Data Preview 05:37

Continuing our series on dealing with text in EXCEL, we look at how you can search for and extract a specific string inside a longer text.

Splitting a Text Field into Multiple Columns Preview 03:51

In this lecture, we look at EXCEL's very useful feature that allows you to easily split a text into multiple columns using various delimiters.

Converting Dates from Text into Values Preview 03:05

Often times, date values in datasets could be stored as text values rather than more useful numeric values. In this lecture, we look at how you can convert text formatted dates into numeric dates.

Adding and Subtracting Dates Preview 05:14

In this lecture, we look at how to add and subtract date values in EXCEL.

Compiling Monthly and Annual Totals Preview 04:43

In this lecture, we explore how to leverage date data to compile both monthly and annual totals in EXCEL.

Conducting Day of the Week Analysis Preview 04:06

In this lecture, we look at how to conduct a day of the week analysis.

Combining Date and Time Data Preview 03:40

Often, when you have both date and time data, it is helpful to combine them together.

Adding and Subtracting Times Preview 05:39

In this lecture, we look at why time data is a bit more tricky to deal with than date data and how to add and subtract time values.

Conducting Hours of the Day Analysis Preview 05:17

In this lecture, we how we can do an hour of the analysis on time in EXCEL.

Introduction to Statistics in EXCEL - Foundations 8 lectures

Introduction Statistics in EXCEL Section Overview Preview 00:22

Section overview to go over the topics we'll cover in this section, which includes an overview of core statistical concepts, statistical functions in EXCEL, and how to use them.

Brief Overview of Statistics Preview 03:35

In this lecture, we go through a brief overview of the most relevant concepts in statistics for conducting data analysis in EXCEL.

Mean, Median, & Mode Preview 08:39

In this lecture, we look at the three most common statistical averages--the mean, median, and mode.

Percentile and PercentileRank Preview 06:03

In this lecture, we look at percentiles and percentilerank in EXCEL.

Frequency Distributions Preview 06:40

In this lecture, we're going to explore frequency distributions and how to use them in EXCEL.

Standard Deviation and Variance Preview 06:11

You can't talk about statistics without understanding standard deviation and variance. In this lecture, we'll break down these concepts so you'll know exactly what their corresponding functions in EXCEL are doing.

Trends in Data Preview 07:18

In this lecture, we'll give you a first introductory taste of how to look at trends in datasets in EXCEL.

Introduction to Correlation Preview 06:23

In this lecture, we go over correlation and how to calculate and interpret it when running analysis of a dataset in EXCEL.

Handling Data in EXCEL 12 lectures

Handling Data Overview Preview 00:37

Quick overview of the various data functions and topics to be covered in this section.

Lookup with VLOOKUP Preview 04:16

Lookup with HLOOKUP Preview 02:26

Name Arrays for Reuse and Readability Preview 03:22

Group Records with Nested IF Statements Preview 04:20

Avoid Errors with IF and ISNUMBER Preview 05:39

Create Dropdown Lists for Filtering Preview 09:27

Create Unique Lists Preview 04:49

Fast Lookup with INDEX and MATCH Preview 10:00

Multi-Condition Lookup with DSUM Preview 06:23

Create Rankings with SMALL and LARGE Preview 05:26

Multi-Condition Lookup with INDEX and MATCH Preview 05:50

Pivot Tables 9 lectures

Insert a Pivot Table Preview 06:38

Prepare Data for Pivoting Preview 09:10

Learn to Pivot a Table Preview 09:35

Group Rows and Columns Preview 05:24

Format for Readability Preview 05:28

Modify Output Values for Insights Preview 08:25

Insert Calculated Fields Preview 08:16

Sort, Filter and Slicer Preview 08:54

Handle New Data in Pivot Tables Preview 08:21

Introduction to Charts in Excel 15 lectures

Intoduction to Charts in Excel Overview Preview 00:30

Quick intro for this section on charts in Excel

Why Use Charts Anyway? Preview 10:11

Why even use charts? Using charts can help you communicate the insights and trends in your data that you just can't otherwise. In this lecture, we'll look at examples of each of the chart types we'll cover in the section.

Line Chart Preview 04:52

In this lecture, we use sample data and build out a line chart from scratch in EXCEL.

Pie Charts Preview 05:35

In this lecture, we use sample data and build out pie charts from scratch in EXCEL. We'll also see why you should use other charts in place of pie chars whenever possible.

Bar Chart Preview 03:54

In this lecture, we use sample data and build out a bar chart from scratch in EXCEL.

Column Chart - Clustered Preview 06:26

In this lecture, we use sample data and build out a simple column (clustered) chart from scratch in EXCEL.

Column Chart - Stacked Preview 05:36

In this lecture, we use sample data and build out a stacked column chart from scratch in EXCEL.

Column Chart - 100% Stacked Preview 05:40

In this lecture, we use sample data and build out a 100% stacked column chart from scratch in EXCEL.

Combination Chart Preview 02:24

In this lecture, we use sample data and build out a combination chart in EXCEL using both a column chart and a line chart.

Scatter Plot Preview 03:12

In this lecture, we use sample data and build out a scatter plot chart from scratch in EXCEL.

Area Chart Preview 04:21

In this lecture, we use sample data and build out an area column chart from scratch in EXCEL.

Bubble Chart Preview 05:19

In this lecture, we use sample data and build out a bubble chart from scratch in EXCEL.

Gantt Chart Preview 04:15

In this lecture, we use sample data and build out a gantt chart from scratch in EXCEL.

Bullet Chart Preview 09:16

In this lecture, we use sample data and build out a bullet chart from scratch in EXCEL, which is really a combination of three different charts on two different axises.

Waterfall Chart Preview 15:19

In this lecture, we use sample data and build out an advanced waterfall chart from scratch in EXCEL.

Case Study: Crytocurrency Data 6 lectures

Cryptocurrency Case Study Overview Preview 03:06

In this intro lecture, we look at a quick introduction to cryptocurrencies and what questions we'll look to answer in the case study.

Getting and Prepping the Data Preview 12:53

In this lecture, we download and organize the data to prep it for analysis.

CORRECTION Preview 00:18

Bitcoin vs S&P500 Preview 13:32

In this lecture, we analyze the bitcoin and S&P500 data and see what the two datasets could tell us how the two compare in terms of their investment returns.

Ethereum, Litecoin, & Bitcoin Preview 08:38

In this lecture, we extend our analysis to include two additional cryptocurrencies--Ethereum and Litecoin.

Introduction to Moving Averages Preview 11:04

In this lecture, we take a quick look at whether moving averages could give us additional insight into the data.

Case Study: Survey Data 5 lectures

Survey Data Overview Preview 03:12

In this lecture, we look at the finished study results that came out of the survey data.

Preparing the Data Preview 06:14

We clean and get rid of irrelevant data before we start doing any analysis.

Demographics - Who Are the Survey Respondents? Preview 09:39

In this lecture, we take the demographic data to get an understanding of who our respondents are based on the survey results.

Pivoting the Data Part 1 Preview 11:05

We did things the hard way, now we do them the easier way with pivot tables, starting with some interested responses to hosting questions.

Pivoting the Data Part 2 Preview 06:09

We continue our case study by segmenting responses to select questions based on host demographics.

Case Study: The Startup Market 8 lectures

So, You Want to Do a Startup Preview 03:12

A Note On Data Preparation Preview 03:57

Scan the Data Preview 02:02

Dig into the Categories Preview 08:55

Exercise 1 Preview 01:10

Pivot and Chart Part 1 Preview 07:31

Pivot and Chart Part 2 Preview 08:29

Analysis Leads to Finding Preview 05:01

Case Study: Data Preparation (for Startup Market Data) 6 lectures

Startup Funding CSV File Import Preview 15:21

Import and Fix Startup Info Data - Part 1 Preview 12:06

Import and Fix Startup Info Data - Part 2 Preview 11:34

Import and Fix Startup Info Data - Part 3 Preview 07:36

Join the Two Tables Preview 12:28

Make Unique Lists Preview 05:40

Appendix: Bonus Topics 3 lectures

How to extract/copy unique values from a column of data Preview 02:44

How to filter list for unique data points only Preview 03:11

Using Excel's built in function to filter a list that has repeat data values to extract only its unique values.

How to model timing contingencies Preview 09:29

When you need to model something that has a variable time dimension, here's one way to do that.

New to Udemy? 2 lectures

OPTIONAL: How to use Udemy, download files, etc... Preview 19:13

Overview of some useful Udemy features like speeding up lectures, bookmarking, downloading files, and how to to ask questions to the Q&A forum.