Search icon CANCEL
Subscription
0
Cart icon
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
$9.99 | ALL EBOOKS & VIDEOS
Save more on purchases! Buy 2 and save 10%, Buy 3 and save 15%, Buy 5 and save 20%
Data Analysis and Business Modeling with Excel 2013
Data Analysis and Business Modeling with Excel 2013

Data Analysis and Business Modeling with Excel 2013: Manage, analyze, and visualize data with Microsoft Excel 2013 to transform raw data into ready to use information

$35.99 $9.99
Book Oct 2015 226 pages 1st Edition
eBook
$35.99 $9.99
Print
$43.99
Subscription
$15.99 Monthly
eBook
$35.99 $9.99
Print
$43.99
Subscription
$15.99 Monthly

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now
Table of content icon View table of contents Preview book icon Preview Book

Data Analysis and Business Modeling with Excel 2013

Chapter 1. Getting Data into Excel

Thank you for taking the first step to becoming a knowledgeable and experienced Excel developer. You will learn how to manually create data in Excel and how to import data from various flat files, such as text, CSV, and Excel. By the end of this chapter, we will learn how to consume data from the Web.

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.

Manually creating data


Let's create our first dataset by using the following steps:

  1. First, fire up Excel 2013 and create a new spreadsheet. We will begin with creating some data manually as an introduction to gathering data. We will begin with typing in cell A1, as shown in the following screenshot:

  2. Type Revenue in cell A1 and Name in cell B1, as shown in the following screenshot. These are going to be our column headings of our dataset.

  3. We are now going to apply styles to the column headings so that they stand out. Highlight columns A1 and B1 and press Ctrl + B. This action will make the two strings that we selected bold. Another option is to highlight the cells and click on the Bold button in the toolbar, as shown in the following screenshot:

  4. Now, type 321, 45, 7, and 23 in the Revenue column. Then, type David, Bob, Bill, and Mike in the Name column. Your spreadsheet should look like the example in the following screenshot:

  5. For our finishing touches, we can apply styles to our data by adding borders around the cells. We can accomplish this by highlighting the cells A1 through B5 and clicking on the Borders button. This will bring up a new menu. Select All Borders. Remember that you need to first highlight the cells you want to add the borders to. Refer to the following screenshot:

Congratulations! You have just learned how to enter numbers, strings, and column headers in an Excel spreadsheet. You have also learned how to apply styles to the text and cells using various built-in Excel functions. You can think of this as your first Hello World program in Excel 2013. Your final output should look like this:

Importing data from various sources


In this section, we are going to cover the sources the data is imported from. They are as follows:

  • Importing data from a text file

  • Importing a CSV file

  • Importing other Excel files

  • Importing data from the Web

Importing data from a text file

There are times when data comes in a text file, and to be honest, this can be a scary thing. I am saying this because data that comes in a text file can be formatted in many ways, and you never know what you are going to get. Luckily, most of the time you will get comma-separated data and this is something Excel can consume pretty easily. Let's start with creating our own text file and then importing it into Excel:

  1. Open Notepad or a similar text editor. Copy and paste the following chunk of data into the text file, as shown in the following screenshot:

    Revenue,Name

    321,David

    45,Bob

    7,Bill

    23,Mike

    Then, save the file as data.txt on your desktop.

    Tip

    Downloading the example code

    You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

  2. Now, open Excel and create a new workbook. Go to the DATA tab and click on the From Text button, as shown in the following screenshot:

    Navigate to your data.txt file and click on the Import button, as shown in the following screenshot:

  3. You will now see a dialog box, as shown in the following screenshot. This dialog box will ask you how your data has been formatted. By default, you will have the Delimited option selected. This means that your data is separated by some characters such as spaces, commas, and semicolons. In our example, the values in the data.txt file are separated by commas. There are other options, but 99 percent of the time, you can just click on the Next button.

  4. Step two of the import wizard will now ask you to select the delimiter or the character that separates each of the values you are trying to import. Make sure that you click on the Comma delimiter and remove any other options that may have been checked automatically.

    Now, let's take a look at the Data preview area in the following screenshot. This area will show you a few records of how Excel plans to parse the data. As shown in the following screenshot, we can see that by choosing the Comma delimiter, Excel correctly splits the data into two columns. We can now click on the Finish button.

  5. The last dialog box will ask you to select where you want to paste the data. The default value is A1 and this is usually the cell you would like to insert the data into. At this point, you also have the option to paste your data into a new worksheet by choosing this option in the dialog box.

    After you click on the OK button, you will see your data in columns A and B.

Tip

You can also drag the actual data.txt file into Excel and this will activate the Text Import Wizard.

Importing a CSV file

The acronym CSV means comma-separated values. What this means to us is that when we use the Text Import Wizard, we need to select Comma as the delimiter. To import a CSV file, the steps are exactly the same as those in the Manually creating data section; however, the data.txt file is not a CSV file. A CSV file can be identified by its filename ending in .csv.

Importing other Excel files

If you perform the same steps that we just learned to import a text or a CSV file into an Excel workbook, then all that Excel does is open the file you are trying to import. It will not ask you for a delimiter, it will not open the Text Import Wizard, and it will simply open the Excel file. Let's go through a simple example to show you how to correctly merge multiple Excel files:

  1. Make two separate Excel files using the same techniques, as shown in the Manually creating data section. Split the dataset that we have been using, create one spreadsheet with the Revenue column, and create a second spreadsheet with the Name column.

    File one should be called one.xlsx and the file will look like this:

    File two should be called two.xlsx and will look similar to the following screenshot:

    Now, let's pause and see what we are trying to do here with the two files. The goal is to combine them into one file. There are two methods that we can use, so let's start with the easiest one.

    In the first method, open the one.xlsx and two.xlsx files. Using the two.xlsx file, highlight columns A1 through A5. Press Ctrl + C to copy the selected cells. Now, switch to the one.xlsx file and select column B1. Press Ctrl + V to paste the data. Your spreadsheet should now look like the following screenshot:

    Congratulations! At this point, you can save the file as final.xlsx and you are all done. You have combined two different Excel workbooks into one.

    The second method involves using an Excel feature that you will often use in different situations. Let's go through the following steps, and then, I will explain the benefits of using this technique:

    1. Open the one.xlsx and two.xlsx files. Using the one.xlsx file, right-click on the tab named Sheet 1, and select the Move or Copy... option, as shown here:

    The Move or Copy dialog box will appear. Select the workbook that you plan to move the data to. In this case, it is going to be 01 – Chapter – two.xls. Make sure that you have the second workbook open, or you will not be able to see this option in the drop-down menu. In the next section named Before sheet, select the option called (move to end), check the Create a copy checkbox, and click on the OK button, as shown here:

    You will now have your second spreadsheet with two tabs: one named Sheet 1 that holds your original data and another one named Sheet 1 (2) that holds the data we just imported from the first spreadsheet. From here on, we can just employ the first technique and combine both the datasets. Good job!

    What was so different about the second method? This method gives us options and that is the key. We currently have a spreadsheet that contains the raw data from each of the two workbooks. We can then create a third spreadsheet or a third tab that holds the data from the two datasets. If we make any mistakes, we can simply remake the third tab/spreadsheet, as our original data is still intact. We can also filter the data of our two original datasets before we combine any data. In practice, you will notice that you will be performing a unique combination of these two methods, depending on your dataset and the problem you are trying to solve.

Importing data from the Web

Here, we will learn how to grab data from the Web. This is the least common task that you will be asked to perform from the previous lessons but taking a look at how it is done is worth your time. We will go through a typical scenario in the following steps:

  1. Open Excel 2013 and create a new spreadsheet. The next steps will involve opening a web page in Excel and extracting data from a table found on the website. We will start with selecting the DATA tab and clicking on the From Web button, as shown in the following screenshot:

  2. A dialog box will appear. In the Address textbox, type http://pandasbootcamp.herokuapp.com and click on the Go button. After the page has been loaded, you will see a similar new web query dialog box, as shown in the following screenshot:

    You will notice that in several places, on the website, you can see a yellow square with a single back arrow, as shown in the following screenshot. This button tells you that Excel has found a table on the website. This button also tells you that you can grab the contents of the table and import them into your spreadsheet. What do we mean by saying that the website has a table on it? This is outside the scope of this book, as the answer requires you to know HTML. But for your reference, Excel looks for HTML <table> tags to identify tables on a website.

  3. Scroll down to the end of the web page and then click on the last button and Import button, as shown in the following screenshot. Why did we choose to select the last button and not the first one? In this example, there were two buttons to choose from. Sometimes, the button will be right next to the table that you are interested in, and at other times, you will have to complete the task by trial and error.

    Notice that the yellow button will change to a green checkbox. After you click on the Import button, you will get a new dialog box that will ask you where you want to paste the data, as shown in the following screenshot. The cell A1 is usually the default location selected, but you may change the location if you wish.

    At this point, Excel will grab the data from the website, and you will have a worksheet that looks similar to the following screenshot. You should have 50 records, but they do not have to match the ones in the following screenshot:

    Good job! You have just imported data from a website effortlessly, thanks to Excel's robust tools that helped you get the job done as easily and quickly as possible. The advantage of grabbing the data using the previous steps is that if there are any changes in the data on the website, we can easily update our spreadsheet to match any new changes. If we simply copy and paste the data from the website into Excel, we would have to perform these same steps every time the data changes. The Pandas Bootcamp website actually changes data every time you refresh the web browser. Try it!

    This means that if we ask Excel to refresh or to check whether the website has any new data, it will update our spreadsheet with the new data. Let's give it a try.

  4. Right-click on cell A1 or any other cell with data. Go to the menu bar, and click on the Refresh button, as shown in the following screenshot. Your data should have changed! This feature will allow your data to always be in sync with just a few clicks.

    Tip

    Alternatively, we can perform this operation by hitting the Refresh All drop-down button under the DATA tab in Excel.

Summary


The lessons in this chapter were designed to teach you how to gather data from various data sources. You should now be able to pull data from text files, CSV files, other Excel files, and web pages. Getting data in your hands is the first step in the data analysis life cycle, and you now have the skills needed for this process. In the next chapter, we will take a look at the last set of data gathering skills that all data analysts should be equipped with. Chapter 2, Connecting to Databases, will guide you through detailed step-by-step instructions on how to connect to Microsoft SQL Server databases using Excel's data connection tools.

Left arrow icon Right arrow icon

Key benefits

What you will learn

Product Details

Country selected

Publication date : Oct 27, 2015
Length 226 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781785289545
Vendor :
Microsoft
Category :
Concepts :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now

Product Details


Publication date : Oct 27, 2015
Length 226 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781785289545
Vendor :
Microsoft
Category :
Concepts :

Table of Contents

18 Chapters
Data Analysis and Business Modeling with Excel 2013 Chevron down icon Chevron up icon
Credits Chevron down icon Chevron up icon
About the Author Chevron down icon Chevron up icon
About the Reviewers Chevron down icon Chevron up icon
www.PacktPub.com Chevron down icon Chevron up icon
Preface Chevron down icon Chevron up icon
1. Getting Data into Excel Chevron down icon Chevron up icon
2. Connecting to Databases Chevron down icon Chevron up icon
3. How to Clean Texts, Numbers, and Dates Chevron down icon Chevron up icon
4. Using Formulas to Prepare Your Data for Analysis Chevron down icon Chevron up icon
5. Analyzing Your Data Using Descriptive Statistics and Charts Chevron down icon Chevron up icon
6. Link Your Data Using Data Models Chevron down icon Chevron up icon
7. A Primer on Using the Excel Solver Chevron down icon Chevron up icon
8. Learning VBA – Excel's Scripting Language Chevron down icon Chevron up icon
9. How to Build and Style Your Charts Chevron down icon Chevron up icon
10. Creating Interactive Spreadsheets Using Tables and Slicers Chevron down icon Chevron up icon
Tips, Tricks, and Shortcuts Chevron down icon Chevron up icon
Index Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Empty star icon Empty star icon Empty star icon Empty star icon Empty star icon 0
(0 Ratings)
5 star 0%
4 star 0%
3 star 0%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by


No reviews found
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.