Chapter 3: Analyzing Data with Microsoft Excel

Overview

Microsoft Excel provides several features and tools that you can use to analyze data in various formats. Understanding how these features work can significantly expand the types of data analysis tasks that you can complete.

Many data analysis tasks in Excel consist of sorting, filtering, formatting, and summarizing data. More sophisticated data analysis tasks can be performed with the built-in and add-in functions that Excel supplies. In this chapter, you’ll learn basic sorting, filtering, and querying skills as well as learn about the tools you use for more sophisticated statistical analysis and “what-if” scenarios. You will not only learn how to work with data stored in Excel worksheets, but also how to bring external data into Excel—data from a server-based database or data from the Web, for example—and use Excel’s data analysis features and tools on the imported data.

As the basis for the examples in this chapter, you’ll analyze data in a spreadsheet that records occupancy rates and purchasing habits for a fictional hotel chain’s preferred guests. You should imagine yourself to be the hotel’s marketing specialist, trying to figure out how to increase business from these preferred guests by using the data analysis features of Excel. The spreadsheet you’ll use, named Hotel.xls, is included in the Chap03 folder at the location where you copied the sample files for this book.

Objectives

In this chapter, you will learn how to analyze data in Excel by

• Using basic features for sorting, filtering, formatting, and importing data.

• Using worksheet functions.

• Creating and interpreting charts.

• Importing and analyzing data from external data sources and the Web.

• Using the basic features of the Analysis ToolPak and the Solver Add-In.