## How important are pivot tables in data-driven decision support?

by Dan Power

Editor, DSSResources.com

Some analysis tools are used more and are used more effectively than others. For example, pivot tables have become key analysis tools since Microsoft included the capability with Excel. Companies have massive amounts of information and have difficulty managing, retrieving and analyzing it (cf., Lohr, New York Times on April 23, 2011). Lohr discusses this problem and asserts that “data-driven decision making” can increase productivity by 5 to 6 percent. Pivot tables are a key tool for analyzing and summarizing large amounts of data.

A pivot table is a data summarization tool found in many data visualization applications that use data from a spreadsheet/worksheet or database. Pivot tools sort, count, total or average data stored in a table or spreadsheet. The tool displays the results in a representation called a "pivot table" that shows the summarized data. Pivot tools quickly create cross tabulations. The user sets up and changes the table structure by dragging and dropping fields. This "rotation" or pivoting of the summary table gives the concept its name.

According to MacDonald, a pivot table "tallies key amounts, like the average amount spent for a customer in a specific city, education level, or gender. However, there are several potentially important relationships, and, therefore, several types of summary tables that you could create. Pivot tables are the perfect tool because they give you almost unlimited flexibility when you want to figure out different relationships."

Why organize data into a Pivot Table? Three key reasons for organizing data into a Pivot Table are:

1. Summarizes data into a compact, understandable format

2. Helps find relationships in data

3. Organizes the data in a format that’s easy to chart

Companies can certainly benefit from more sophisticated analytics than pivot tables. Pivot tables are however, an easy to use and understand analytical tool for managers and IT folks. Perhaps "data scientists" will use more sophisticated statistical and modeling tools, but keeping the tool simple and easy to use is also important. From that perspective the pivot table tool is important to data-driven decision support. Simple analytics are often all that we require to get insight from big data sets.

In an analysis, we want to avoid three types of errors. A type I error occurs when we reject the null hypothesis when it is in fact true. Our error is falsely rejecting. A type II error occurs when we accept the null hypothesis when the alternative hypothesis is in fact true. Our error is falsely accepting. A type III error occurs when we ask the wrong question. Our error is accepting an answer that is irrelevant to the problem.

The biggest challenges we encounter in data-driven decision support are: 1) gathering accurate, useful data, 2) having the data easily accessible for analysis, 3) asking the right questions of available data, and 4) finally, carrying out the analysis correctly with appropriate tools.

Pivot tables do not solve all of our problems, but they can increase the chances our analysis will be useful and correct.

Recently, I answered the question "What are your 3 tips for using Excel as a business intelligence tool?” (Posted on Nov. 29, 2010 at focus.com). My response was:

"Excel is a desktop analysis tool that can be used by an analyst or a manager to summarize data, visualize relationships among variables and forecast trends. The keys to using Excel are:

1) have a good dataset that you know how to manipulate. Are there missing data values? How many rows of data should be in the dataset?

2) be familiar with the Excel data analysis tools, especially pivot tables. What is a pivot table? What does it mean to pivot? What is drill down? What is a pivot chart?

3) know when to use different types of charts. What chart is most appropriate for the analysis? What is a regression line? How do you change titles, data series, chart attributes?"

References

Check 25 easy PivotTable reports at http://office.microsoft.com/en-us/excel-help/25-easy-pivottable-reports-HA001034633.aspx?CTT=3

Lohr, S. "Data-Driven Decisions Can Aid Companies' Productivity," New York Times, April 24, 2011 at URL http://www.nytimes.com/2011/04/24/business/24unboxed.html?_r=1&ref=stevelohr .

Lohr, S. "Mining of Raw Data May Bring New Productivity, a Study Says," New York Times, May 13, 2011 at URL http://www.nytimes.com/2011/05/13/technology/13data.html?ref=stevelohr .

MacDonald, M., What Is a Pivot Table, Excel: The Missing Manual, 08/12/2005 at http://oreilly.com/windows/archive/whatisapivottable.html

Last update: 2011-06-25 05:50

Author: Daniel Power

Print this record

Show this as PDF file

You cannot comment on this entry