How to quickly visualize the distribution of your data using Ms Excel charts

Updated: Jan 22, 2020

The data for this demonstration comes from the PEPFAR Impact website, it was simplified to show you how you can, easily, visualize the distribution of any data using the box and whiskers plot chart, from Ms Excel

In this specific case, I would like to know how the distribution of the performance of the reported indicator “ANC Clients who know HIV Status”, across time (2005-2016).

1. Make sure that your data is on a flat file format.


2. Insert chart

a. Select the table content

b. Go to menu Insert, in the Charts group, choose the chart you want to display: Expand the charts option, to see “all char options"

c. On the Insert Chart Dialog box select “All Charts” tab.

d. Box & Whiskers as the chart type.

e. Clique “OK” button to close the dialogue box.


3. Design the Chart

a. On the chart group select Design-> Select Data

b. On the Select Data Source dialogue box:

i. Remove the FY from the Legend entries (Series)

ii. Edit the axis labels

iii. At the "Axis Labels" insert the location of what will appear at the axis. In my case will be the years (Column A2 to A317)

iv. Clique OK button to close the Axis Labels dialogue box

v. Clique OK button to close Select Data Source dialogue box


4. Visualize the data in the chart

The Chart is ready for a simple visualization of the distribution of performance of the indicator from 2005 to 2016.

Next time we can talk about how to format this chart.

If you wish to download the data used for this exercise, contact us and we will send the data to you.