If you ever wondered how other data professionals manage, analyze, and visualize data with Excel, then this book will be a wealth of knowledge for you. This book is filled with step-by-step instructions and progresses through the same natural stages a data analyst goes through in practice. The examples are deliberately small so that you can understand the problems being solved and solutions are shown in detail without skipping any steps along the way. In addition, my extensive experience in the industry will help you explore practical real-world examples that go beyond theories and provide you with a strong foundation that can be used in a wide range of data-intensive roles that you may encounter throughout your career. After reading the entire book, you will have the confidence to work with data and tell a compelling story about its findings using Excel.
Chapter 1, Getting Data into Excel, covers several examples of how you can create your own data or bring data into Excel from various sources. Data can come from many sources, and in practice, you will normally find data in flat files, such as CSV or Excel.
Chapter 2, Connecting to Databases, covers how to connect to a Microsoft SQL Server database, although there are various flavors of databases. Step-by-step examples are provided to give you plenty of practice. Nearly, all of the organizations that you will analyze data for will store all of the data in a relational database.
Chapter 3, How to Clean Texts, Numbers, and Dates, covers how to clean data or prepare data for analysis, which is one of the most time-consuming steps in the data analysis life cycle. Cleaning data is a must-have skill for anyone working with data. Bad data can come from various sources, such as manually entered data, bad web forms that allow erroneous data to enter a company's database, or bugs in software, which can all lead to very messy data that you have to deal with. In this chapter, we will also take a look at several examples of how to deal with strings, numbers, and dates in Excel.
Chapter 4, Using Formulas to Prepare Your Data for Analysis, covers the use of Excel's formulas to create custom columns, identify key metrics, and make decisions based on business rules. Formulas are one of the key features that showcase the power of the tool, and this chapter provides you with plenty of practical examples to help you gain valuable experience.
Chapter 5, Analyzing Your Data Using Descriptive Statistics and Charts, uses Excel to explore data to identify bad data, spot outliers, and trends. After data has been cleaned and prepared, it is now time to dig a little deeper. Are there any issues with your data? Do you have bad data? Do you understand what kind of data is in each column and how it relates to the rest of your dataset? Using Excel's built-in tools and charting capabilities, you will learn more about the data you are working with.
Chapter 6, Link Your Data Using Data Models, covers how to combine and link data using database concepts by taking advantage of the new features of Excel 2013. Excel's data model allowa us to combine tables in a similar way to how the LOOKUP functions accomplished this previously. This new functionality will allow the analyst to merge datasets faster and with ease. Organizing data is the key concept in this chapter that will propel you to answer questions about the data.
Chapter 7, A Primer on Using the Excel Solver, teaches you the basics of the Excel Solver, which is one of the most underrated tools that comes with Excel. You will learn how to activate the add-ins all the way through to solving business problems that are relevant to today's workplace. The information in these few pages will elevate you above other Excel developers.
Chapter 8, Learning VBA – Excel's Scripting Language, introduces you to Excel's very own scripting language. After performing the same data transformations over and over again, a smart data analyst will try to find ways to automate repetitive tasks. Excel's solution to this problem is VBA (Visual Basic for Applications), in which you will learn how to create macros to automate certain tasks. This chapter will empower you with knowledge that will differentiate you from a casual Excel user to a powerful, skilled, and advanced Excel developer.
Chapter 9, How to Build and Style Your Charts, discusses how to use Excel's built-in charting tools to quickly create visually appealing charts. Visualizing data is not only a great way to understand it but also a great way to tell a story to an audience. This chapter also covers how to customize properties, such as titles, legends, colors, and so on. This chapter focuses on the keys to generate creative, simple, and concise charts that will deliver insights from your findings.
Chapter 10, Creating Interactive Spreadsheets Using Tables and Slicers, helps you leverage Excel's interactive slicers, which is one of the most exciting chapters in this book that will simply impress you. Here, you will gain the ability to slice and dice data interactively, create custom filters that automatically update the data on the fly, and watch the audience engage with the data. You can filter by dates, strings, and numbers; the possibilities are endless!
Appendix, Tips, Tricks, and Shortcuts, provides you with useful shortcuts and tips that have been used throughout this book for reference purposes.
You will need a copy of the Microsoft Office Home and Student 2013 software. In addition, you will also need an up-to-date Windows laptop or desktop.
This book is for new and experienced Excel users who want to enhance their skills in the data analysis life cycle. This includes gathering, preparing, modeling, and finally, presenting data. This book also has everything a data analyst would ever want to know, including how to clean data retrieved from databases, use advanced Excel tools, and create interactive spreadsheets.
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "But for your reference, Excel looks for HTML <table> tags to identify tables on a website."
A block of code is set as follows:
SELECT * FROM pmthistory a JOIN users b on (a.userid = b.id)
New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "If we did not have the Clear All Filters button, the users would have to figure out how they would clear every slicer one at a time to start over."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail <[email protected]>, and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
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.
We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from https://www.packtpub.com/sites/default/files/downloads/9545EN_ColorImages.pdf.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
If you have a problem with any aspect of this book, you can contact us at <[email protected]>, and we will do our best to address the problem.