Excel: Learn 47 Functions via a Business Services Case Study
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.
Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
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:
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!
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Course Introduction|
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.
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|
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.
In which the service repair business process is described, in order to provide context for the business case study.
|Lecture 5||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|
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.
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!
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|
In which I explain the concept of a text string, the position of the characters within a string, and the length of a string.
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.
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.
|Lecture 12||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.
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
|Section 6: Excel Functions: 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.
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.
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
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.
|Lecture 21||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
Table Functions - Removing Duplicates & Summarizing with Pivot Tables
A Picture is Worth a Thousand Words: Using Pivot Charts to Visualize the Results
The Written Report; Explaining the Results in Writing and With Clarity.Preview
|Section 8: Course Overview and Additional Practice Exercises|
|Quiz 1||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.
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!