Excel: Learn 47 Functions via a Business Services Case Study
3.9 (23 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.
161 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel: Learn 47 Functions via a Business Services Case Study to your Wishlist.

Add to Wishlist

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
3.9 (23 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.
161 students enrolled
Created by Jeff Knowlton
Last updated 1/2015
Current price: $10 Original price: $30 Discount: 67% off
5 hours left at this price!
30-Day Money-Back Guarantee
  • 2 hours on-demand video
  • 5 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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
View Curriculum
  • 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.

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!

Who 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.
Compare to Other Excel Courses
Curriculum For This Course
26 Lectures
Course Introduction
2 Lectures 08:30

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.

Preview 06:15

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

Preview 02:15
Business Case Study Introduction
3 Lectures 08:21

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.

Preview 05:13

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

Preview 03:08

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!

Preview 1 page
Getting Started: Importing Data into Excel
3 Lectures 12:58

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.

Getting Started: Introduction to Importing Text Files

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!

Importing Delimited Text Files - the file from National Repair Depot

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!

Importing Fixed Width Text Files - Independent Service Electronic Repair Center
Excel Functions: Working with Text Strings
4 Lectures 17:00

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

Preview 02:05

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.

Preview 05:23

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.

Join, Format and Trim Texts, Convert them to Values, & Split them into Columns

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

19 Text Functions Explained in Detail!
6 pages
Excel Functions: Working with Dates
4 Lectures 17:55
Introduction to Date Functions. Learn how Computers think about Dates.

Calculations with Dates - Building Dates and Calculating Workdays

Parsing Dates to Calculate Years, Months, Days, Weekdays, and Contract Lengths

17 Date Functions Explained in Detail! (Plus some bonus "Information" functions
4 pages
Excel Functions: Logical Functions
5 Lectures 27:00

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.

Introduction to Logical Functions

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.

The OR Function

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

The IF Function

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.

The AND Function & Complex Logical Expressions

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.

All 9 Logical Functions Explained in Detail
5 pages
Putting it All Together: Analyzing the Data with Pivot Tables and Charts
4 Lectures 24:54
Combining the Two Files into One - Preparation for Analysis

Table Functions - Removing Duplicates & Summarizing with Pivot Tables

A Picture is Worth a Thousand Words: Using Pivot Charts to Visualize the Results

Course Overview and Additional Practice Exercises
1 Lecture 03:34
Course Overview

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.

Final Quiz - Based on the Practice File
6 questions
About the Instructor
Jeff Knowlton
4.5 Average rating
633 Reviews
5,933 Students
5 Courses
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!