Quote of the Day
I'd rather fly in a Spitfire but fight in a Hurricane - because the Hurricane was made of non-essential parts. I had them all shot off at one time or another, and it still flew just as well without them.
— James 'Ginger' Lacey of 501 Squadron, who was the second-highest ace from the Battle of Britain.
Introduction
Figure 1: F6F Hellcat, the fighter that dominated US carrier sorties in the later part of the Pacific War.
I recently needed to generate a graph in Excel that combined a column chart with a timeline. The graph turned out well and I decided to share my work here. As my original work is proprietary, I will share the technique here using some US Navy (USN) air operations data from WW2.
I OCRed Table 5 of the WW2 Naval Aviation Combat Statistics (NASC) report, which contains both monthly squadron numbers and action sorties flown by month. I filtered the data to show the sum of the fighter (Wildcat, Hellcat, Corsair) and bomber (Dauntless, Helldiver, and Avenger) sorties.
For those who like to follow along, my workbook is here.
Background
I prepared a column chart of the monthly fighter and bomber sorties. I also added a timeline of major battles to provide some context for the number of sorties. The date ranges of the major battles were obtained from the Wikipedia.
The NASC separated the sortie data into carrier-based and land-based categories.
Analysis
Data
I used Tabula to grab Table 5 from the NASC and output a CSV file. I then used Power Query to clean the data and to prepare it for graphing.
Graph
Figure 2 shows my plot of carrier and land sorties versus time in the chart's upper half and the battle timeline on the chart's lower half. This graph nicely illustrates that:
- Until late-1943, the USN had relatively few sorties per month compared to the first few months of 1945.
- The Battles of Coral Sea, Midway, and Guadalcanal can be seen on the graph, but have relatively few sorties compared to the Battles of Manila, Iwo Jima, and Okinawa.
- There is a long carrier sortie drought during most of 1943.
Figure 2 shows very clearly that the USN needed ~18 months to build itself into a navy that could take on the IJN at sea.
Figure 2: Sorties vs Time with Timeline.
Conclusion
While I do not like to use Excel for graphics, it does allow you to easily combine different chart types. This can be useful for some types of presentations, such as this one.