Duplicate Drach's U-Boat Tonnage and Sunk Charts

Quote of the Day

He woke up every morning, and said "It's a great day to retreat, which way shall I go?"

— Historian Gary Gallagher on Confederate General Joseph E. Johnson. Gallagher has really stirred my interest in the US Civil War. He is a master lecturer and has figured out how to engage his audience. I look forward to each of his presentations.


Figure 1: Armchair Admirals' Program where Drach is Presenting His Charts.

Figure 1: Armchair Admirals' Program where Drach is Presenting His Charts. Drach is shown in the bottom square.

I am a huge fan of Drachinifel's naval history channel. The other day, Drach was participating in the Armchair Admiral's program, during which he presented two charts on the Battle of the Atlantic that I have never seen before: (1) A chart of tonnage sunk by U-boats versus time and (2) a chart of U-boats sunk versus time. The unique aspect of the charts was that the data points were colored based on whether the Enigma cipher was broken at the time and whether centimetric (microwave) radar was deployed. These charts really got me thinking about the impact of technology on the struggle against U-boats.

I decided to duplicate the two charts in Excel since they are simple. For those who like to follow along, my workbook can be downloaded here.


Here is the Battle of the Atlantic panel discussion where Drach presented his two charts.

Figure 2: Drach's Charts are being presented at time 1:12:52.


Data Source

Drach did not say where he got his data, so I just decided to use a table from Wikipedia that summarizes all ship losses by cause and month of loss (link). The data appears to be the same as Drach plotted.

While this table has the number of numerical data, it does not contain the dates for when Enigma was broken (there really was no one date) and centimetric radar was deployed. I decided to choose the dates based on

  • The capture of German Weather Ship Lauenburg, which provided Enigma codebook data at the end of June 1941.
  • The deployment of the ASV Mk III centimetric radar during May 1943. This radar made U-boats detectable by aircraft.

Given these two dates, I can now generate the graphs by:

  • Using Power Query to extract the data from the Wikipedia
  • Using Excel, reformat the data as three different series based on the month.
  • Plot the data using standard Excel charts.


Tonnage Sunk Per Month

Figure M: Ship Tonnage Per Month By Uboats.

Figure 3: Ship Tonnage Per Month By U-boats.

Uboats Sunk Per Month

Figure M: Number of Uboats Sunk Per Month.

Figure 4: Number of Uboats Sunk Per Month.


There are two things I glean from these graphs:

  • The breaking of the Enigma code seems to have no effect on tonnage sunk but did start an increase in the rate of Uboat sinkings.
  • It is unclear whether centimetric radar had any impact on tonnage sunk, but it did seem to increase in the rate of U-boats being sunk.

The increase in Uboat losses inevitably led to a reduction in tonnage sunk. The toll on U-boat crews was terrible; 70% of the crewman died during the war (link).

This entry was posted in Excel, History Through Spreadsheets, Military History, Naval History. Bookmark the permalink.