Excel Histogram Charts from a Pivot Table

Today I am processing data from a patient level costing system (PLiCS) and trying to assess the cost distribution by the patients diagnosis.

[GARD align=”center”]

Frequency distribution charts can be a bit painful to produce in excel so I have fallen back on my favourite tool – the pivot table which enables me to produce dynamic excel histogram charts within minutes.

You start with your dataset showing as a minimum, the unique patient episode identifiers, diagnosis codes and the cost per episode. Pivot this table with cost in the row area (not data) and then the unique patient episode identifiers in the data area. As I’m interested in how many epsiodes fell into each cost bin I have used the COUNT function on cds_unique_identifier.

Creating Histogram or Frequency Distribution Charts with Pivot Tables

Now to get the cost buckets or bins, you right click in any of the rows showing Total_Cost and select group. The values in the dialog box are pre-populated but you can overwrite and I have chosen to set my intervals every £30.

Creating Histogram or Frequency Distribution Charts with Pivot Tables 2

This provides the summarised output table which can then be used to drive a pivot chart – just click in the pivot table then go to the INSERT tab and select the line chart of your choice.

Creating Histogram or Frequency Distribution Charts with Pivot Tables 3

Hey presto – a distribution chart that can be altered dynamically by using the filter to bring in more or less diagnoses.

Creating Histogram or Frequency Distribution Charts with Pivot Tables 4

Now I just need to fathom out what to do with the information.

  • Hugh

    Nice one Dr A.

    Quick and easy histograms which as you say are difficult in Excel – though maybe there is an add-on in the analysis tool pack.

    Surely the only use Commissioners have for PLICS data is to set Providers a locally tariff based on, oh let’s say, the the lower decile cost!

  • Hugh

    that’s meant to be locally agreed tariff and also it’s meant to be a joke!

  • Dr Ang

    The recent changes in the NHS led me to move in search of greener pastures. I’m now on the provider side and have complete sympathy with you. Those darn commissioners with their upper quartile obsessions.