Book Image

Data Analysis and Business Modeling with Excel 2013

Book Image

Data Analysis and Business Modeling with Excel 2013

Overview of this book

Table of Contents (18 chapters)
Data Analysis and Business Modeling with Excel 2013
About the Author
About the Reviewers
Creating Interactive Spreadsheets Using Tables and Slicers

Getting started with data

Before we get started with Excel, let's go through the data analysis life cycle. These are the steps that should be taken every time you perform some sort of data analysis. The steps include gathering, preparing, analyzing, and presenting data. While you may not always be responsible for every step in the data analysis life cycle, every step is dependent on each other. Enough talking, let's take a moment to define each step.

Gathering data

Gathering data is exactly what it sounds like; in this step, you will be gathering all of the data you need for analysis. This might include data that you get from your client, boss, coworker, the Internet, or a database. There are other data sources, such as CSV files, but remember that it is your job to find the data. I once had a client asking me "Can you take a look at my code as it is not working?" He was trying to map some data into Google Maps and he was having trouble doing this. He sent me code snippets and asked me if I could figure out what the problem was. I took a look at his work, but I just did not have enough information to debug the issue. Guess what my next question to my client was? "Send me your code and the data you are trying to plot." Knowing what kind of data my client was working with and what the code was doing with the data were the two key questions that I needed to know.

I eventually figured out the issue for my client, but the point here is to show you that getting the data in your hands is the first step. Chapter 1, Getting Data into Excel, and Chapter 2, Connecting to Databases, will focus on providing you with all the skills needed to bring data from various sources into Excel.

Preparing data

You will soon realize that after you gather your data, it does not always come in a neat package for you. For example, you may be given a PDF document with 1,000 entries and asked to transfer that data into an Excel spreadsheet. You might get lucky and be able to copy/paste the records into Excel, or you might be forced to manually enter each record by hand. I used to work for a wholesaler of college text books and faced a similar situation. I needed to copy a very large PDF document and transfer its content to Excel. I remember refusing to do so and asking a coworker to put this data in a different format. I was trying everything under my control to avoid that PDF file. Unfortunately, in the end I had no choice but to roll up my sleeves and get the job done. As a data analyst, you would probably spend most of your time in the data analysis life cycle cleaning the data. In other words, you will gather data and organize it in a format you can work with. Munging and data wrangling are other terms you may hear that refer to this step of the process. Other common issues are numbers formatted as strings, missing values, extra spaces, and so on. We will go through various examples of the ones mentioned and their solutions in Chapter 3, How to Clean Texts, Numbers, and Dates, and Chapter 4, Using Formulas to Prepare Your Data for Analysis.

Analyzing data

After you gather and prepare your data, you are now ready to analyze it. Your main goal up until now was to get your data into Excel; this is our comfort zone where we know we can work with data. What do I mean when I say analyze your data? Well, this means that it is time to get your inquisitive and curious hats on. If you don't have any of these, then it is time to act like a detective, Inspector Gadget style (if you're old enough to remember who he is). In this step, we begin with inspecting every column one by one. For example, let's say that the first column was called Revenue and the second column was called Product Name. We would expect the Revenue column to have numbers in each of the values and the Product Name column to have strings as the values associated with this column. We will then look for any missing values, the largest number, and the smallest value. We might also take a look at the distinct values in the Product Name column and look for any misspelled words.

Are you trying to solve a problem? Are you trying to predict the next year's revenue? Did you ask for some background of the task you were assigned to do? Remember to ask all these questions to whoever is going to receive your analysis for feedback along the way. The last thing that you might avoid is that when you complete the analysis, you are told that you were analyzing or solving the wrong problem. You may also spend a lot of time figuring out what certain columns mean if you actually have the data to complete the task. Chapter 5, Analyzing Your Data Using Descriptive Statistics and Charts, Chapter 6, Link Your Data Using Data Models, and Chapter 7, A Primer on Using the Excel Solver, will give you enough exposure to analyzing and squeezing out insights from your data.

Presenting data

This is where the fun begins; you are now at a point where you can tell your story. At this point, you should know everything about your data, such as where it came from and how it was prepared or organized, and you should have completed the task you were assigned, at least in theory. For example, if you were asked to simply create a line chart with the monthly sales for the year, then this is where you should be at this stage. The data should be in Excel, the sales data should be aggregated on a monthly basis, and you should already have an idea of how to create and place your line chart. Before you spend an hour or so making your final spreadsheet look good, create a simple mockup and get feedback from your end user. I know that this is not always applicable to every situation, but getting feedback along the way will save you a lot of time from redoing the work at a later stage. Another little known fact is that people just change their minds or sometimes change their requirements, so always build your spreadsheets as flexible as possible. In our example, you may be asked to switch the data from quarterly to monthly for an analysis at the last minute. They may want the data over the past 5 years and a bar chart instead of a line chart. My advice to you is very simple; expect changes every single time. Luckily, Excel has many wonderful tools to help you spin up interactive and visually impressive workbooks. In Chapter 9, How to Build and Style Your Charts, and Chapter 10, Creating Interactive Spreadsheets Using Tables and Slicers, we will go through all these neat features that will equip you with the necessary knowledge to further enhance your skills.