Getting Answers from Data using Excel brings you the best techniques for slicing and dicing your mess of data until the insights fall right out of it. You will be surprised at the powerful analysis that can be done with simple spreadsheets, including data science techniques like data mining, segmentation, and clustering. You will be given sample spreadsheets for real-world personal and business case studies. Do the hands-on case study challenges and experience the power of these simple analytical techniques. You will learn PivotTables, sorting, filtering, VLOOKUP, formulas and more. You will learn to turn your mess of data into answers, decisions, and action. 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 youll 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. Thats why most of the time you spend in this class will be hands on, doing case studies of personal and business issues. All you need is a basic familiarity with Excel. If youve used Excel for anything-making a list or adding up a column of numbers-youre good to go. You dont need the latest version. These powerful techniques have been in Excel for a long time. At the end of this course you will be able to: Draw hidden insights from any data file Design a PivotTable to best answer any particular question Make data-informed personal and business decisions Merge disparate data files into a form for easy analysis Focus and organize your data to surface answers using sort, filter and PivotTables. Apply the VLOOKUP function in both forms, in particular to merge data or turn numerical values into pre-defined categories Calculate an entire column using a simple formula and replicating 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 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 columnSometimes 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? Thats 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. Excel is not a spectator sport. Youll have many opportunities to practice what youre learning. Youll often be asked to try it to solidify the techniques youre learning. Most of all, youll be given case studies to explorea 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. Case StudiesBelow 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 mining challenge: Find the most likely to. While the case study is about banking, the approach is applicable anywhere. As you do each case study, 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? Useful techniques: sorting to find largest expenses; PivotTables to aggregate by month and category; grouping to see how small expenditures add upEvent participant management Data file: Participants in a charity race Issues: Organize alphabetically or by group, highlight those earning a milestone year pin, and figure out how many T shirts to order in each size Useful techniques: sort alphabetically, filter into groups, filter to list those earning pins, and use PivotTables to count T shirts. Consultant billing Data file: Daily time and activity log Issue: Billing each clie
