Plot monthly diurnal cycles of ozone using pivot tables in excel

Summary

Hourly ozone concentrations collected at Weybourne in the UK 2010-2014. Make a pivot table to calculate average monthly diurnal (daily) cycles and then plot them in a chart. Format the chart and add text boxes to label each month.

Step by Step guide

1. Download data from the UK-AIR DEFRA website. Hourly atmospheric ozone concentrations at Weybourne Atmospheric Observatory in the UK 2010-2014.

2. Insert a Column and add the Date and Time together

3. Press Ctrl + 1 to open the Format Cells box and change the number format to a custom number format dd/mm/yyyy hh:mm

4. Use Ctrl + A to select the whole table and then Insert a Pivot Table

5. Add the Date and Time field to Rows and the Ozone field to Values

6. Right click inside the Date and Time column and select Group

7. Group by Hours and Months

8. Right click on the Ozone field and select Value Field Settings

9. Change it from Sum to Average

10. Go to the Design Tab and remove the Subtotals and Grand Totals

11. You can’t make a scatter chart with a Pivot Table so copy the table and Paste As Values

12. Select the table and Insert a Scatter chart with Smooth Lines

13. Change the Axis Maximum on the horizontal axis from 350 to 300, and change the Major Unit from 50 to 25. There are 24 hours in a day, plus one to get a gap inbetween each one. Then 300 is 25 x 12 as there are 12 months in a year.

14. While the chart is selected go to the Insert Tab and insert a TextBox

15. Type “J” in the text box, for January, then format and position it.

16. Select the text box, hold down Ctrl and click and drag to make copies of the text box for every month

17. Change the letters in the text boxes so they match the month

18. Select the Horizontal Axis and change the Label Position to None, to get rid of the numbers at the bottom as they don’t mean anything.

Final Result