What this book covers

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.