Analyzing Business Data with Excel

Chapter 2. Pivot Tables and Problem Solving

Business data analysis is a search for relationships. How does advertising impact sales? Does an increase in returns mean we have a quality problem? Whether we are looking for problems or identifying best practices, it all comes down to relationships. Business analysis requires a complex, multidimensional approach. Businesses capture and store large amounts of data. As companies try to become more efficient, the job of sifting through this data looking for valuable insight is becoming more common and important.

Excel is ideal for this kind of work. It can import data from most databases, it can handle almost any statistical or formatting problem, and it has a great pivot table feature. Pivot tables were designed for researching relationships in data. They allow us to try different combinations by dragging and dropping, making it easy to check a large number of relationships quickly. They create interactive tables and charts and can quickly filter the data or change point of view.

In this chapter we use pivot tables to analyze a business process. This is an ad hoc activity and the end product is information, not an application. So, formatting and appearance are not the main concerns. The real goal is to find specific problems or opportunities.

We work with two kinds of data. First, there are categorical items. These elements separate data into well-defined groups. If you look at a customer file, one of the fields might be the customer’s ZIP ...