Pivot Tables 101 Analyzing prescription data with Microsoft Excel without going crazy or staying up

1
Pivot Tables 101Analyzing prescription data with
Microsoft Excel without going crazy or staying up
all night

• Prepared by the DoD Pharmacoeconomic Center

2
Objectives

• Explain what a pivot table is and what its good
  for
• Describe what data files should look like and
  explain how they are imported into Excel
• Build pivot tables and analyze sample pharmacy
  data using pivot tables and pivot charts

3
If we have time

• Demonstrate quickly transferring pivot tables
  onto Powerpoint slides for presentation
• Discuss what you can do if you have more data
  than the maximum row limit in Excel (65,536 rows)
  or the maximum number of unique items in a field
  (32,500)

4
Self-Assessment Questions

• When would you change data types when importing
  data into Excel?
• How do you select data to include in a pivot
  table?
• If you want to look at a graph of medication use
  over time, what fields do you need and where do
  you put them on the pivot table page?
• How do you limit a pivot table or chart to only
  those items you want to look at?
• How do you change the aggregate measure (e.g.,
  sum, count, max, min) of your data?

5
What are Pivot Tables?

• Expands spreadsheet functions of Excel so you can
  easily explore multiple views of data
• Multiple filters measures
• Query large amounts of data
• Drill-down to identify issues
• Define your own formulas
• Auto charting graphing
• Microsoft Office 2003
• 2007 goes even further

6
Data for Pivot Tables

• Import data as columns
• Items read down, not across
• Each column is a data field

• Generally need at least one summing measure
• Number of Rxs, tablets, users, costs
• Include multiple parameters for filtering

7
But what if you want to look at data at the
individual prescription level?

• Sure, theres always an exception

• Each row 1 Rx, so we will have the program
  count the rows to get total numbers of Rxs

8
Lets Get Started
9

• Interactive session

10
Self-Assessment Questions

• When would you change data types when importing
  data into Excel?
• How do you select data to include in a pivot
  table?
• If you want to look at a graph of medication use
  over time, what fields do you need and where do
  you put them on the pivot table page?
• How do you limit a pivot table or chart to only
  those items you want to look at?
• How do you change the aggregate measure (e.g.,
  sum, count, max, min) of your data?