So, a colleague has just sent you an email, asking you to produce a chart on regional flu vaccine uptake for their presentation by lunch time. It’s urgent, and they’re presenting to the clinical director. You know the data and where it is, but there’s no dashboard to point them to or take charts from quickly. What do you do? You’ve only got a few hours, and you have to fit it around your other tasks which are equally important. You need something quick and dirty, you need to use Excel.
Step 1: Go to NHS England’s Statistical Work Areas for Flu Vaccination data. Download it all.
Step 2: Place all the data in a folder.
Step 3: Open Excel, Go to the Data Tab on the Ribbon, and use Get Data From Folder.
Step 4: Combine and Transform the Data.
Step 5: Transform the data as required to get a final data table with the information you need (and if you’re not sure how, follow this other tutorial to find out how to collate weekly/monthly data).
Step 6: Pivot the data, then create a pivot chart.
Step 7: Beautify the chart, make it presentable for your audience.
This is what you start with.
This is an example of what you might be aiming for.
- Reduce the colour in each of the lines, making them slightly more pastel. Why? This reduces competition on your viewer’s eyeballs, so the chart doesn’t attack them visually when they first see it.
- Reduce the colour in the gridlines. Why? Reducing visual competition again.
- Remove the legend, and replace with data labels on the final point of the each line. Why? This allows the audience to quickly attribute the region to each line, without playing match the colour. It also gives a quick method for the viewer to rank the data.
- Remove all Field Buttons from the chart (right click a field button to see the below context menu). Why? To reduce visual clutter.
- Consider reducing the size of the y-axis labels by changing the display units if the units are in the millions or hundreds of thousands.
- Add a y-axis title, and reduce the text’s colour. Why? We want our audience to be able to see what the chart is representing, but we don’t want it to draw attention away from the actual data.
- Reduce colour in the x-axis labels. Why? Again, our audiences need to be able to check what dates the data pertains to, but we don’t want too much visual clutter.
Step 8: Add any pertinent annotations and a title to highlight the important aspects shown within the chart. Reach out to a colleague for feedback before finalising the design.
This final step is where we do our first past the post analysis, and where having a keen eye counts.
The example data shows each region trending upwards since November, but a slowing over the Christmas period. The other discrepancies in magnitude cannot be usefully discerned from this data. We might want to augment this data in future by looking at Vaccinations per 100,000 population using Census data split into NHS regions, to find out where regions are lagging behind others.
Here is where our questioning of the data produces additional tasks for us, so that we can present our stakeholders a thorough and complete picture of the data. Dependent on our task deadline, we might be able to spend the time doing this additional work and looking for other data to supplement our figures. If not, we have to know when enough is enough, and when what we have is good enough.
Make sure you fulfil your client’s brief. Always keep in mind what it is that they are looking for and create the visualisations to that end. Don’t go overboard if you don’t have to, but if time allows, don’t stop short when there are important revelations hidden within the data that your client will be interested in.
Know what you’re doing with the data, where it’s going, who will see it, and in what context. Its final presentation will tell you a lot about what else might need to be done and how long you should spend on it. Don’t be afraid to push back if you think the task requires more time.
Finally, read Data Visualisation by Andy Kirk, and Storytelling with Data by Cole Knaflic, amongst others. These are invaluable sources of knowledge for all data analysts and will improve your ability to produce visualisations dramatically. The above links are affiliate links, if you do decide to purchase those books I will receive a small amount as a referral reward.
Download the example file:
Static Interest 