Cookies help us display personalized product recommendations and ensure you have great shopping experience.

By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    sales and data analytics
    How Data Analytics Improves Lead Management and Sales Results
    9 Min Read
    data analytics and truck accident claims
    How Data Analytics Reduces Truck Accidents and Speeds Up Claims
    7 Min Read
    predictive analytics for interior designers
    Interior Designers Boost Profits with Predictive Analytics
    8 Min Read
    image fx (67)
    Improving LinkedIn Ad Strategies with Data Analytics
    9 Min Read
    big data and remote work
    Data Helps Speech-Language Pathologists Deliver Better Results
    6 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: How to Use Pivot Tables to Mine Your Data
Share
Notification
Font ResizerAa
SmartData CollectiveSmartData Collective
Font ResizerAa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Big Data > Data Mining > How to Use Pivot Tables to Mine Your Data
Data MiningData QualityData VisualizationData WarehousingDecision Management

How to Use Pivot Tables to Mine Your Data

knowwareman
knowwareman
9 Min Read
SHARE

To succeed at Six Sigma or any process improvement effort, you’ll often have to analyze and summarize text data. Most companies have lots of transaction data from “flat files” like the one shown below, but because the data consists of text and raw numbers, they sometimes have a hard time figuring out what to do with it. These examples use Excel along with QI Macros for Excel:

pivot table data in Excel

To succeed at Six Sigma or any process improvement effort, you’ll often have to analyze and summarize text data. Most companies have lots of transaction data from “flat files” like the one shown below, but because the data consists of text and raw numbers, they sometimes have a hard time figuring out what to do with it. These examples use Excel along with QI Macros for Excel:

More Read

The Total Cost of Big Data Performance [VIDEO]
Web 2.0 – Ensuring Your Data is the Asset
Can You “Near Me Now”?
Landscape: Data Warehousing and Business Analytics
A Better Way to Model Data

pivot table data in Excel

To summarize and analyze this data, you will want to learn how to use Excel’s PivotTable tool. In past incarnations it was known as Crosstab (for cross tabulation). With Pivot Tables and the file above you could:

  • Count the number of deliveries all doctors performed.
  • Count the number of times each doctor had a “Complication” during delivery.
  • Sum or average the charges per delivery by doctor.
  • Count the number of deliveries for each diagnosis.

And do it easily.

Pivot Tables are a Great Tool, but the User Interface is Awkward

I have found that few people know how to use Excel’s PivotTable function to analyze this kind of data. I have to believe that it’s because the user interface isn’t intuitive. But here’s how you do it step by step:

Step 1: Your Data Must Have Column Headings!

As you can see in this data sheet, each column has a heading. The Pivot Table will not run if there is a blank cell in any heading. One of the first mistakes people make is inserting blank columns to make the file more readible, and then they wonder why the Pivot Table won’t work.

Avoid Mistakes: No Blanks In Column Headings!

Step 2: Select The Data

You can select the data using your mouse or you can click on any cell in the data and the PivotTable will automatically select all of the data. Below I clicked on E6.

select data for pivot table in Excel

Then when I click on Data-PivotTable to start the Pivot Table wizard, the wizard will automatically select all of the surrounding data. But I’m getting ahead of myself. To initiate the PivotTable, click on Data and select PivotTable and PivotChart Report:

pivot table on Excel's menu

This will launch the PivotTable Wizard:

Excel pivot table wizard

The most common defaults are selected: Microsoft Excel list or database and PivotTable. You could also pull in data from an external data source like Microsoft Access or you could summarize multiple ranges (i.e., more than one column contains words that you want to aggregate into one count).

I invariably just click Next > which leads us to Step 2 of the Wizard, selecting your data. If you’ve already clicked on a cell in the center of your data as I suggested, Excel will select everything around it. In this case A1:L240.

pivot table select data range in Excel

Again, I usually just click Next > to go to Step 3 of the Wizard where I can choose to put the summaries in a new worksheet or the current worksheet.

pivot table in new or existing worksheet

Again, I usually just click Finish! If you know what you’re doing, you can usually use the Layout and Options to specify how you want the summaries to appear, but it’s usually easier to work from the default table and field list:

pivot table outline to drag and drop data

Step 2: Picking the Best Layout for Your Data

The PivotTable works on a “drag-and-drop” interface. Just click on a field and drag it into one of the four boxes:

  • Page Fields for higher level summaries (e.g., facility or location names)
  • Row Fields should have the most frequent heading (often dates)
  • Column Fields should contain less frequently used headings (Excel only has 256 columns available). If you select a column with too many unique words in the cells, the PivotTable will overflow.
  • Data Items are where you drag and drop the words or numbers you want to count or summarize.

If I want to know how things add up by date, I usually drag the Date field into the Row Fields. Putting the dates down the left side prevents overflows and makes it easier to run a control chart with the QI Macros:

pivot table outline to drag and drop data

Then I might want to look at the results by Physician. So I drag Physician into the Column Fields:

pivot table in Excel

Then, maybe I want to summarize the Total Charges by physician by day. So I drag Total Charges into the Data Item Box which gives me totals by day and physician as well as some insight into how often the doctors work:

pivot table in Excel

Now let’s say that I wanted to analyze the charges in terms of adverse events (pregnancy didn’t go as planned). I’d just drag and drop Adverse Events into the Page Fields. The Pivot Table gives me a choice of viewing all charges or just the charges with the key word complication, Outlier, Readmission, etc.:

pivot table page fields in Excel

If I select Outlier, I get some insight into the cost of complications. The costs are almost 50% higher than a normal delivery:

pivot table in Excel

I could also use the “-” to evaluate all pregnancies with no complications:

pivot table in Excel

If you double click on “Sum of Total Charge” and change it to Average, you get the average:

I could also change it back and group all of the charges into monthly charges.
How to: Just Right click on any date and select Group and Show Detail/Group/Months:

The Pivot Table will group the months.


Warning! Bonehead Excel Behavior:
Grouping dates will not work if there is even a single blank or text cell where there should be a date.

Now I can select B4:I4, hold down the control key to select B8:I8 and run a pareto chart:

pareto chart in Excel

Changing The Focus

What if I wanted to change this table to count adverse events by Physician and by age of patient? Hint: It’s easy with drag and drop.

1. Just click on date and pull it out of the table.

2. Click on Sum of total Charge and pull it out of the table.

3. Drag Adverse events down into Data Fields.

4. Then click on the Age field and drag it into the Row Fields:

Are there more younger women with complications or are there just more younger women giving birth? Does one physician have more complications than the others? We could run a pareto chart to show complications by physican (MD6 is 40% of total complications, almost twice as high as his or her peers):

What Else?

Maybe I’d like to evaluate average length of stay (LOS) for all deliveries. Just pull the adverse events out of the table and drop in LOS (change it to an average). Not much going on here. The youngest and the oldest had a slightly longer length of stay:

Get the Idea?

There’s a wealth of information hiding in these dense flat files of words and numbers. Start using Excel’s Pivot Table function to slice and dice your files (no matter how large). Then use the QI Macros to graph the results. You’ll find it easy to find the 4-50 and start making breakthrough improvements.  Go to www.qimacros.com for more information.

TAGGED:data miningsix sigma
Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

sales and data analytics
How Data Analytics Improves Lead Management and Sales Results
Analytics Big Data Exclusive
ai in marketing
How AI and Smart Platforms Improve Email Marketing
Artificial Intelligence Exclusive Marketing
AI Document Verification for Legal Firms: Importance & Top Tools
AI Document Verification for Legal Firms: Importance & Top Tools
Artificial Intelligence Exclusive
AI supply chain
AI Tools Are Strengthening Global Supply Chains
Artificial Intelligence Exclusive

Stay Connected

1.2kFollowersLike
33.7kFollowersFollow
222FollowersPin

You Might also Like

PAW: Predictive Modeling for E-Mail Marketing

7 Min Read

PAW: The unrealized power of data

6 Min Read

Web Mining: Short/Long Term User Profile

4 Min Read

Tracking the data trackers

4 Min Read

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

AI and chatbots
Chatbots and SEO: How Can Chatbots Improve Your SEO Ranking?
Artificial Intelligence Chatbots Exclusive
ai is improving the safety of cars
From Bolts to Bots: How AI Is Fortifying the Automotive Industry
Artificial Intelligence

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-25 SmartData Collective. All Rights Reserved.
Go to mobile version
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?