Excel: Learn 47 Functions via a Business Services Case Study

Build a bridge to better Excel skills. Learn advanced date, text and logical functions, with pivot tables and charts
4.8 (12 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.
118 students enrolled
$19
$30
37% off
Take This Course
  • Lectures 26
  • Length 2.5 hours
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
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 1/2015 English

Course Description

One of the world’s leading consulting firms, the McKinsey Global Institute, has identified a huge gap between the demand for people with deep analytical skills, and the supply of qualified candidates. They call it a “critical shortage”, totaling more than 150,000 people by 2018. They’re not only talking about careers in computer programming. They’re saying that these skills will be required for almost all middle-class careers, from HR to sales, accounting to purchasing, and even factory production workers.

Where do you start, to improve your analytical skills and increase your opportunities for a good career? Start by learning Excel. Excel is the primary analytical tool used in business today. How do I know? I’m a senior business executive, with over 25 years of experience across several different industries.

Excel is the perfect foundation on which to build your analytical skills.

There are many Excel courses – why take one of mine? Because I’m a business executive, I know how Excel is used in business. I know the analytical skills needed. And don’t just take my word for it.

Here's more proof

A local university uses my Udemy courses to teach pivot tables. And my courses are recommended by the Oregon Business Education Association.

What do they like about my courses? They like the realistic case studies. One case study is followed throughout the entire course, showing how business uses Excel to solve real-world problems.

Students learn about Excel more deeply, and the knowledge “sticks”, because the course is more than just a list of commands and menu paths. The course is based on a real business problem that you solve during the course.

This course covers these topics in Excel:

  • Importing Text Files
  • Date Functions
  • Text Functions
  • Logical Functions (IF, OR, AND)
  • The VLOOKUP function, to make cross-reference tables
  • Table Functions, including pivot tables and pivot charts

Learn and master more than 47 Excel functions

The course is targeted at people who have some familiarity with Excel. You should know how to edit data, create formulas, and use the copy / paste functions, like Control-C and Control-V.

Content and Overview

There are 27 lessons, comprising about 2 hours of video. The course is based on a case study, to evaluate the performance of two service companies. Each company has sent a text file with their repair history, thousands of records each. The data is dirty: there are duplicate records, the dates are encoded in text fields,and the product codes are not harmonized. Just like real life. Students will learn how to use Excel functions to import the data and clean up the files.

Most lessons have a practice session. I provide two Excel files: the starting point and the finish point. I also provide the written script for each lesson. There's a quiz at the end, to ensure that you’ve mastered the topics. I make it easy for you to succeed.

Let's get started!

What are the requirements?

  • You should have a copy of Excel that you can use, preferably Excel 2013, but Excel 2010 will work.
  • You should be able to enter data into Excel, create formulas, copy/paste data, and use some basic short-cuts, like Control-C and Control-V.
  • This is not a beginner's course! But if you've been using Excel for several months, either at work or in school, you'll do fine.

What am I going to get from this course?

  • Clean / Scrub large data sets with many different types of Excel functions
  • Import text files into Excel, both delimited and fixed width files.
  • Work with text strings, to parse data, format text strings, clean/trim text data, join/concatenate text strings, and split text into columns.
  • Make Excel dates from text strings and values; calculate duration (like net work days), parse dates, and work with international dates
  • Build complex logical expressions with IF, AND, OR
  • Use VLOOKUP to harmonize data with cross-reference and mapping tables.
  • Summarize data with pivot tables and filters; remove duplicates from data sets
  • Visualize data with pivot charts

What is the target audience?

  • University students taking a course in Excel.
  • Recent graduates looking for job that requires some Excel skills.
  • People returning to the workforce after an absence of a few years, looking to improve their computer skills.
  • People with an informal background in Excel, looking to broaden their knowledge, especially with respect to data cleansing and Excel functions.
  • This is not an advanced class. Only a few complex expressions are introduced. There are no macros, for example.
  • This is not a beginner's class. You should have some familiarity with Excel.

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 Introduction
06:15

In which I provide the course outline, some examples of actual lessons so that you can see my teaching style, an explanation of the benefits of learning Excel via case studies, and some suggestions on how to maximize your learning potential.

02:15

In which I explain my qualifications to teach this course, and I explain why a senior business executive moonlights as a Udemy instructor!

Section 2: Business Case Study Introduction
05:13

In which the business case study is described in detail, including the sample company, Cowboy Printers; the business problem being faced - choosing a firm to repair printers; and the sample datasets are described. The sample datasets each have more than 20,000 rows of data, and are inspired by my job as VP of Global Services.

03:08

In which the service repair business process is described, in order to provide context for the business case study.

1 page

The practice sessions are an important part of this course. This document shows you how to download them. I recommend downloading the practice files for each lesson, rather than continuing to work with the same files from lesson to lesson. Good luck!

Section 3: Getting Started: Importing Data into Excel
04:28

In which you will learn what a text file is, how text files are structured, what a delimiter is, and the three main steps of the text import process in Excel. This step is critical, because in the business world, data is often sent as text files. It must be imported into Excel, before it can be analyzed.

04:54

In which the case study begins, by downloading the delimited text file with the repair history from National Repair Depot and importing it into Excel. The file has more than 20,000 data records!

03:36

In which the case study continues, by downloading the fixed width text file with the repair history from iSERC, and importing it into Excel. The file has more than 30,000 data records!

Section 4: Excel Functions: Working with Text Strings
02:05

In which I explain the concept of a text string, the position of the characters within a string, and the length of a string.

05:23

In which I explain the concept of parsing a text function, that is, creating a new field by breaking out a portion of a text string; and illustrate its use by creating a new field for site code from the RMA in the National file, and two new fields for dates in the iSERC file.

09:32

In which I provide several detailed examples for converting a text to a number, trimming unneeded spaces from text strings, calculating the length in characters of a text string, combining two text strings to make one string, parsing text strings into columns, and formatting numbers and dates as text strings.

6 pages

In which I list 19 text functions and show examples of their use in an Excel file.

Section 5: Excel Functions: Working with Dates
Introduction to Date Functions. Learn how Computers think about Dates.
02:19
Calculations with Dates - Building Dates and Calculating Workdays
05:43
Parsing Dates to Calculate Years, Months, Days, Weekdays, and Contract Lengths
09:53
17 Date Functions Explained in Detail! (Plus some bonus "Information" functions
4 pages
Section 6: Excel Functions: Logical Functions
05:35

In which I explain logical functions (like IF, OR, AND), and the meaning of a "Truth Table". In this lesson, I will also show you how to use the VLOOKUP function to harmonize the field "Product" across the two files, with a cross-reference (mapping) table, so that the field "Product" has the same meaning and the same values in both files.

06:58

In which I explain the OR function, and show its use to determine which products to consider relevant for the analysis of the turn-around time, by combining and comparing features from both the product code and the serial number fields.

07:05

In which I explain the IF function, the most commonly used logical function, and I show its use in a different case study, on sales reporting for Cowboy Business Machines

07:22

In which I explain the AND function, and show you how to build complex logical expressions, combining the IF function and the OR function to calculate a repair price depending on the product code and serial number.

5 pages

In which I provide a list of all of the logical functions, an example of their use, and some comments on when to use them.

Section 7: Putting it All Together: Analyzing the Data with Pivot Tables and Charts
Combining the Two Files into One - Preparation for Analysis
09:17
Table Functions - Removing Duplicates & Summarizing with Pivot Tables
07:39
A Picture is Worth a Thousand Words: Using Pivot Charts to Visualize the Results
07:58
The Written Report; Explaining the Results in Writing and With Clarity.
Preview
2 pages
Section 8: Course Overview and Additional Practice Exercises
Course Overview
03:34
6 questions

This quiz is based on the practice file. Complete the homework assignment attached to the Course Overview. You will then be able to answer the questions in the quiz.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Jeff Knowlton, Senior Executive. Data Scientist. Teacher.

I am a senior business executive, a data scientist, and a former university instructor. And I've been using Excel, and tools like it, for more than 30 years! 

In addition to my current role as VP Global Services, I've been a VP of Information Technology, a global project manager for the deployment of very large IT systems, a senior statistician/data scientist, a university instructor and a corporate trainer.

I bring a unique perspective to Excel and business analytics. I know how Excel is used in business, and I know what hiring managers are looking for. All of my courses are built on authentic case studies, using real data to show how Excel functions are used today in business.

I have two degrees from Stanford University, one in English with Honors in Humanities, and a graduate degree in Statistics (now called data science). I explain technical concepts in non-technical terms.

You can see more detail on my linked in page. Send me a connection request!

Ready to start learning?
Take This Course