Udemy
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Development
Web Development Data Science Mobile Development Programming Languages Game Development Database Design & Development Software Testing Software Engineering Development Tools No-Code Development
Business
Entrepreneurship Communications Management Sales Business Strategy Operations Project Management Business Law Business Analytics & Intelligence Human Resources Industry E-Commerce Media Real Estate Other Business
Finance & Accounting
Accounting & Bookkeeping Compliance Cryptocurrency & Blockchain Economics Finance Finance Cert & Exam Prep Financial Modeling & Analysis Investing & Trading Money Management Tools Taxes Other Finance & Accounting
IT & Software
IT Certification Network & Security Hardware Operating Systems Other IT & Software
Office Productivity
Microsoft Apple Google SAP Oracle Other Office Productivity
Personal Development
Personal Transformation Personal Productivity Leadership Career Development Parenting & Relationships Happiness Esoteric Practices Religion & Spirituality Personal Brand Building Creativity Influence Self Esteem & Confidence Stress Management Memory & Study Skills Motivation Other Personal Development
Design
Web Design Graphic Design & Illustration Design Tools User Experience Design Game Design Design Thinking 3D & Animation Fashion Design Architectural Design Interior Design Other Design
Marketing
Digital Marketing Search Engine Optimization Social Media Marketing Branding Marketing Fundamentals Marketing Analytics & Automation Public Relations Advertising Video & Mobile Marketing Content Marketing Growth Hacking Affiliate Marketing Product Marketing Other Marketing
Lifestyle
Arts & Crafts Beauty & Makeup Esoteric Practices Food & Beverage Gaming Home Improvement Pet Care & Training Travel Other Lifestyle
Photography & Video
Digital Photography Photography Portrait Photography Photography Tools Commercial Photography Video Design Other Photography & Video
Health & Fitness
Fitness General Health Sports Nutrition Yoga Mental Health Dieting Self Defense Safety & First Aid Dance Meditation Other Health & Fitness
Music
Instruments Music Production Music Fundamentals Vocal Music Techniques Music Software Other Music
Teaching & Academics
Engineering Humanities Math Science Online Education Social Science Language Teacher Training Test Prep Other Teaching & Academics
AWS Certification Microsoft Certification AWS Certified Solutions Architect - Associate AWS Certified Cloud Practitioner CompTIA A+ Cisco CCNA Amazon AWS CompTIA Security+ AWS Certified Developer - Associate
Photoshop Graphic Design Adobe Illustrator Drawing Digital Painting InDesign Character Design Canva Figure Drawing
Life Coach Training Neuro-Linguistic Programming Personal Development Mindfulness Personal Transformation Life Purpose Meditation Emotional Intelligence Neuroscience
Web Development JavaScript React CSS Angular PHP WordPress Node.Js Python
Google Flutter Android Development iOS Development Swift React Native Dart Programming Language Mobile Development Kotlin SwiftUI
Digital Marketing Google Ads (Adwords) Social Media Marketing Google Ads (AdWords) Certification Marketing Strategy Internet Marketing YouTube Marketing Email Marketing Retargeting
SQL Microsoft Power BI Tableau Business Analysis Business Intelligence MySQL Data Analysis Data Modeling Big Data
Business Fundamentals Entrepreneurship Fundamentals Business Strategy Online Business Business Plan Startup Freelancing Blogging Home Business
Unity Game Development Fundamentals Unreal Engine C# 3D Game Development C++ 2D Game Development Unreal Engine Blueprints Blender
30-Day Money-Back Guarantee
Office Productivity Microsoft Excel

Excel Case Studies: Sales Analysis with Pivot Tables, Charts

Learn by doing! Save time! Real case studies in sales analysis to learn functions IF, VLOOKUP, pivot tables and charts.
Rating: 3.3 out of 53.3 (204 ratings)
1,272 students
Created by Jeff Knowlton
Last updated 5/2017
English
English [Auto]
30-Day Money-Back Guarantee

What you'll learn

  • Starting with a large set of unformatted data, summarize and analyze the data with Excel to find the important "nuggets" of information, and explain the results with clarity.
  • Specifically, prepare a sales and margin report for business, starting with basic invoice information, summarizing results by product line and by region.
  • Make complex pivot tables and display the results visually, with charts and conditional formatting to make a customer scorecard.
  • Use the Excel functions =if, =vlookup, =countif, =sumif, and make nested if statements.
  • Make an Excel data model to connect several Excel tables.

Requirements

  • Basic familiarity with Excel is assumed. Students should be able to enter data and formulas. The examples are very easy to follow.
  • You'll need a copy of Excel that you can use, preferably Excel 2013 or Excel 2016 for Windows. Excel 2010 will work as well.

Description

Another Excel Course?!? How is this one different? This course is not just a list of Excel functions. Yes, it does teach the critical Excel functions like IF, VLOOKUP, and pivot tables and charts, but it does so through an extended case study example of sales reporting and analysis. So you'll learn WHY these functions are important, and how to use them to answer real business questions. You'll learn them by seeing them used in action to answer questions, like "Who is the top customer?" and "What are the top-selling products?" How do I know? I'm a senior executive, and a hiring manager, at a high-tech manufacturing company.

In this course, you are a business analyst, given the task of analyzing the sales results for a small company, to answer questions about sales and profitability. The lessons show the Excel functions in detail, highlighting each step. I provide the scripts for each lesson, along with the Excel file used, both the starting point and finishing point. I provide everything I can to make this course a success for you!

The course starts with a basic set of raw data. First I show you how to format the raw data, adding product lines, customer regions, and product cost, using IF and VLOOKUP. Then I show you how to summarize the data with advanced pivot table functions, filters, and slicers, to calculate sales and margin by product line by region. Next, I show you how to visually display the results with several different types of charts, and to make a color-coded customer scorecard. Finally, I show you how to write the up the results in a business report.

There is no more practical course on Excel and Business Analytics: this is exactly what business analysts do every day. I learned these Excel techniques over a period of years: you can learn them in a few days. Let's get started!

Who this course is for:

  • The course is designed for people starting their careers, looking for a jump-start. The examples in the case studies are taken directly from a business environment. This is how Excel is used today!
  • The course is designed for college students learning Excel in a business environment. In addition to Excel, you'll learn some business basics, like sales reporting.
  • The course is designed for people who may have been out of the office work environment, and who are looking to return. Excel is critical to success in many careers today.

Course content

7 sections • 36 lectures • 3h 8m total length

  • Preview03:01
  • Preview01:41
  • Preview03:29

  • Preview03:28
  • Preview03:36
  • Preview02:22
  • Margin Calculation
    6 questions
  • Preview05:29
  • Preview1 page

  • Introduction to Excel Functions
    02:11
  • Review of Formatting
    05:38
  • Preview07:54
  • Nested if Functions
    05:49
  • Tables and Names
    03:59
  • The vlookup Function
    08:45
  • Troubleshooting vlookup Functions
    09:40
  • Review of Excel Functions
    02:49

  • Introduction to Pivot Tables
    04:37
  • Pivot Table Layouts and Totals
    06:29
  • Preview07:03
  • Pivot Table Calculated Formulas
    09:14
  • Pivot Table Relationships and Data Models
    04:42
  • Review of Pivot Tables
    02:22
  • Preview07:52
  • Pivot Tables
    3 questions

  • Preview06:50
  • Pie Charts
    07:13
  • 3D Column Charts
    05:34
  • Advanced Charting Features
    07:45
  • Alternative Methods for Charts, and Scatter Plots
    05:38
  • Conditional Formatting
    09:44

  • Introduction to the Written Report
    02:01
  • Page Setup
    06:43
  • Print Functions
    03:19
  • Some Print Examples
    03:47
  • Copying Charts and Tables to Microsoft Word
    04:27
  • The Written Report
    06:54

  • Course Overview and Additional Practice Session
    05:07
  • Additional Practice Material - Final Quiz
    10 questions

Instructor

Jeff Knowlton
Senior Executive. Data Scientist. Teacher.
Jeff Knowlton
  • 4.6 Instructor Rating
  • 17,621 Reviews
  • 112,902 Students
  • 5 Courses

                   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 Information Business Services, I've been 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 can help you prepare for interviews, and excel in your first job as a business analyst.

      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! 

  • Udemy for Business
  • Teach on Udemy
  • Get the app
  • About us
  • Contact us
  • Careers
  • Blog
  • Help and Support
  • Affiliate
  • Terms
  • Privacy policy
  • Cookie settings
  • Sitemap
  • Featured courses
Udemy
© 2021 Udemy, Inc.