4.25 out of 5
4.25
56 reviews on Udemy

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
Instructor:
Jeff Knowlton
308 students enrolled
English [Auto]
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

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!

Course Introduction

1
The Benefits of Learning Excel via Authentic Business Case Studies

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.

2
Meet the Instructor, a Senior Business Executive and Data Scientist

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

Business Case Study Introduction

1
Case Study - Cowboy Printers and the Sample Datasets: More than 50000 data rows!

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.

2
The Service Repair Business Process - Context for the Case Study

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

3
How to Download the Excel Practice Files for the Case Study

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!

Getting Started: Importing Data into Excel

1
Getting Started: Introduction to Importing Text Files

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.

2
Importing Delimited Text Files - the file from National Repair Depot

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!

3
Importing Fixed Width Text Files - Independent Service Electronic Repair Center

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!

Excel Functions: Working with Text Strings

1
Introduction to Text Strings

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

2
Parsing Text Strings - Splitting off Sections of Text to Make New Fields

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.

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

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.

4
19 Text Functions Explained in Detail!

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

Excel Functions: Working with Dates

1
Introduction to Date Functions. Learn how Computers think about Dates.
2
Calculations with Dates - Building Dates and Calculating Workdays
3
Parsing Dates to Calculate Years, Months, Days, Weekdays, and Contract Lengths
4
17 Date Functions Explained in Detail! (Plus some bonus "Information" functions

Excel Functions: Logical Functions

1
Introduction to Logical Functions

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.

2
The OR Function

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.

3
The IF 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

4
The AND Function & Complex Logical Expressions

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
All 9 Logical Functions Explained in Detail

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.

Putting it All Together: Analyzing the Data with Pivot Tables and Charts

1
Combining the Two Files into One - Preparation for Analysis
2
Table Functions - Removing Duplicates & Summarizing with Pivot Tables
3
A Picture is Worth a Thousand Words: Using Pivot Charts to Visualize the Results
4
The Written Report; Explaining the Results in Writing and With Clarity.

Course Overview and Additional Practice Exercises

1
Course Overview
2
Final Quiz - Based on the Practice File

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.

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.3
4.3 out of 5
56 Ratings

Detailed Rating

Stars 5
26
Stars 4
23
Stars 3
5
Stars 2
2
Stars 1
0
30-Day Money-Back Guarantee

Includes

2 hours on-demand video
Full lifetime access
Access on mobile and TV
Certificate of Completion
Excel: Learn 47 Functions via a Business Services Case Study
Price:
$40.98 $30

Community

For Professionals

For Businesses

We support Sales, Marketing, Account Management and CX professionals. Learn new skills. Share your expertise. Connect with experts. Get inspired.

Community

Partnership Opportunities

Layer 1
samcx.com
Logo
Register New Account
Compare items
  • Total (0)
Compare
0