Draw answers from your data using analysis tools such as filter and PivotTables, with business and personal case studies
Your instructor: Carol Jacoby
Are you a decision maker? Possibly for a business, or maybe in your community, with your family, or just for yourself? If so, you want answers. Your time is valuable. Your goal is not learning Excel. You want to learn how to draw actionable insights from the data you have. The questions are always changing. Every answer raises more questions. You need to explore the data. You need flexibility. Microsoft Excel gives you that flexibility and transparency.
All you need is a basic familiarity with Excel. If you’ve used Excel for anything–making a list or adding up a column of numbers–you’re good to go. You don’t need the latest version. These powerful techniques have been in Excel for a long time. Also, you can use any platform. To prove it, I’m using a Mac and giving complete Windows instructions, but you’ll see that it’s the same on either one.
Sometimes you have too much data, overwhelming columns of numbers and categories with no clear order or meaning. The answers may be in there somewhere, but how do you draw them out? That’s the focus of this class. You will get the most out of the class if you have or can get data and if you have questions to answer or decisions to make. Keep it in mind as you work through the tools.
Getting Answers from Data using Excel for Mac or Windows brings you the best techniques for slicing and dicing that data until the insights fall right out of it. First you will structure the data to make finding answers quick and flexible. Your data may be in inconsistent pieces or have extraneous information, and you’ll learn how to fix that. Immediately start exploring your data with sort and filter. PivotTables aggregate the data. Summarize it by one factor, then quickly try another. Discover what is driving the outcome. Find the 20% that gives you 80% of the value. The Excel techniques are simple. The complexity comes from applying these seemingly simple techniques to real-life problems. That’s why most of the time you spend in this class will be hands on, doing case studies of personal and business issues.
At the end of this course you will be able to:
- Merge disparate data files
- Put your data in a form for easy analysis
- Sort your data based on numerical and categorical values
- Filter your data based on various criteria
- Use PivotTables to make an instant summary table of the data
- Design a PivotTable to best answer a particular question
- Use a PivotTable to “slice and dice” the data various ways for deeper insights
- Use a PivotTable to analyze an issue whose outcome is “yes” or “no”
- Effectively use the various PivotTable options for “summarized by”
- Find a category most likely to have some characteristic (data mining)
- Organize the data to see similarities (prepare for clustering)
- Build a unified data table using VLOOKUP to draw data from another
- Choose numerical or categorical forms of each column to best answer your questions
- Change categorical values into numerical using Replace
- Change numerical values into categorical using VLOOKUP or grouping
- Write a simple formula
- Replicate a single formula to an entire column
- Draw insights from any data file for which there is a question or issue
Excel is not a spectator sport. You’ll have many opportunities to practice what you’re learning. You’ll often be asked to “try it” to solidify the techniques you’re learning. Most of all, you’ll be given case studies to explore—a data file and an often-vague issue. Your challenge is to discover what you can in the data to help shed some light on the issue. There are multiple possible answers. What you learn may raise further questions. There may be different approaches to the problem. This is key to learning the essential skill of turning data into answers.
Below are the case studies you will be exploring. Even if you have no knowledge or interest in the specific industries, the techniques you will practice are applicable everywhere. For example, finding bank clients most likely to go into default is an instance of a general and common data science challenge: Find the ________ most likely to _________. No knowledge of any of these applications is needed. In each case, think about your own issues and how you might use the techniques.
Personal finance
Data file: Individual expenditures over 5 months
Issue: Where is all my money going?
Consultant billing
Data file: Daily time and activity log
Issue: Billing each client, tracking work hours and income
Retail sales
Data file: anonymized customer purchase data
Issue: Find customers with similar buying patterns or items with similar customers for cross-marketing
Banking
Data file: Demographic and financial data for customers
Issue: Find a reasonably large category of customers who are more likely to go into default
Marketing
Data file: Customer demographics and marketing campaign data
Issue: How to increase marketing effectiveness. Specifically, focusing on those customers who had not been contacted before, find the best months for a marketing campaign
Market segmentation
Data file: Customer demographics and marketing campaign data
Issue: Can the customers be divided into segments for customized marketing? Quantify the benefit of using two different approaches rather than the same approach for everyone
Repeat marketing
Data file: Customer data on those who had been contacted previously
Issue: What is the effect of repeat marketing to the same customers? If they’ve turned down previous offers, is it a waste of time to contact them again?
Your own case study challenge
Data file: Whatever data you have available to work with
Issue: You choose. Apply the techniques you’ve learned to your own data and questions. This is the most important and rewarding of all the case study challenges.
Why does a decision maker need to learn about Excel?
Excel has the reputation of being for “bean counters” and “number crunchers.” It seems to be about automating repetitive tasks and creating pretty graphs and static reports. But nothing you do is repetitive, and you want substance, not pretty. You need answers and Excel is just the tool to give you the flexibility and transparency to explore and discover.
The problem is that Excel is crammed with features. No one can be an expert in all of them. A typical Excel book is over 1000 pages long and, even then, just explains how to operate Excel. It doesn’t tell you how to use Excel to solve your problems.
The good news is that you don’t need to learn all these features. There are a few specific features that will help you answer the questions you need to answer. Dig into them, find their power, and forget the rest.
The key is in finding those few magic techniques that unlock the solutions to your specific problems. That’s why the courses in the series Excel for Decision Makers all start with a broad problem to be solved and present techniques from a wide variety of Excel features. The techniques in this class allow you to reorganize your data, focus in on key items, summarize in multiple ways and find the sweet spots and outliers. No cute graphics, no fluff.
