One of the enduring arguments among naval historians is the correct categorization of the German capital ships Scharnhorst and Gneisenau. Were these “small” battleships, as the Germans maintained, or were they actually the last battlecruisers, as their British adversaries claimed? I have made a separate posting covering my data analysis of this question.
In order to practice some Excel chart-building skills, and to explore some of the more detailed Excel controls, I have prepared a workbook that includes a number of scatter charts. I created these charts from a blank workbook in an empty file, so all the work is my own. My humble work is available for your inspection on both GitHub and Google Drive.
Naturally, I begin with an Excel table with the key characteristics of 20th century heavy gun capital ships. The data in the table comes from the book The Complete Encyclopedia of Battleships by Tony Gibbons. The table contains three pieces of fictional data, all shown in red. These three red data points represent two key points in “alternate history” for two significant planned ships that never put to sea.
- The German battlecruiser Mackensen was built and launched, but was never crewed and commissioned due to resource constraints and changing priorities with the onset of World War I.
- The entry named Schlactshiff represents an alternate history where the Scharnhorst and Gneisenau would have been completed a year later after waiting for the availability of the planned 15-inch guns. This would have allowed them to be built as originally planned.
These two fictions are highlighted in the charts to allow comparison but prevent mis-representation.
The class of ships in question is included in the table twice with the same data. One entry is included with the name Gneisenau classified as battleships, as the Germans claimed. The second entry is named with the generic word Schlactkreuzer as battlecruisers, as the British claimed.
Battleship Gun Timeline Chart
This timeline highlights the main issue with the Gneisenau class. Their 11-inch guns make them the most visible data outlier for ships designated as battleships. Excel scatter plots are chosen almost exclusively in this analysis as bar charts give an erroneous impression of “sales volume” and line charts are not suitable for this data series.
I intentionally chose a different color scheme for each chart in the workbook. My objective is more about practice with the tools and less about optimal readability. I chose a “battleship gray” color scheme for this first chart. I chose a gray background with black axes, black labels, and with “sea blue” data points. I included black grid lines for major scale and white grid marks for minor scale. I like the “doughnut” option for data points as it resembles looking down gun barrels. The darker “doughnuts” signify multiple table entries for that data point. I used the “glow” chart element to highlight our class as well as the fictional data point. I added text labels with the ship class names for some of the outliers and some of the most interesting data points. I chose a linear trend line automatically calculated by Excel, in the same color as the data points. Years of school have drilled into me that one should include axes titles and units, preferably starting at zero for the vertical axis.
German Battlecruiser Speed History Chart
This timeline highlights one dimension of the most interesting British claim about the Gneisenau class. Key characteristics of the Gneisenau class closely align with an extrapolation over time of those same characteristics for the previous generations of German battlecruisers. In other words: whether intentional or not, in 1939 the German naval architects had built ships with characteristics that their fathers would likely have contemplated for the next generation of battle cruisers. In this chart, that key characteristic is the most important capability of a battle cruiser: speed.
I chose an “ocean” color scheme here. I selected white circular data points. My high-school chemistry teacher taught that we should use large data points in charts for visibility and because the precision implied in a tiny data point is usually illusory. (Appropriate in this case as maximum ship speed varies over time, depending on such things as engine maintenance and how many barnacles have attached to the hull.) I included white grid lines for major scale and no grid marks for minor scale. Again, I used the “glow” chart element to highlight our class as well as the fictional data point. I added text labels with the names for the key classes. I chose a linear trend line automatically calculated by Excel, in the same color as the data points. The outlying position of the ship class in question forces the position of the trend line. This could be creating a kind of bias in the calculated trend. Therefore, I created a line object with black to white color gradient and hand placed it as a linear extrapolation of the local maxima of the relevant data in order to show a more independent trend line. (I believe my added line reinforces the main point that extrapolating past speeds would lead to a speed close to the actual maximum speed of the Gneisenau class.)
German Battlecruiser Armor History Chart
This timeline highlights another dimension of the interesting British claim that the Gneisenau class had key characteristics that align with an extrapolation over time of those same characteristics for the previous generations of German battlecruisers. In this chart, that key characteristic is the one that historically most distinguished German battlecruisers from British ones: armor.
Merely to practice making chart object color choices in Excel, I chose a “German flag” color scheme here. (The flag of German republics has been gold, black, and red.) I selected square black data points, and made a deliberate exception of black diamond data points for the reference class Mackensen. I included gray grid lines for major scale and no grid marks for minor scale. Again, I used the “glow” chart element to highlight our subject class as well as the fictional data point. I added text labels with the names for the key classes. I chose a linear trend line automatically calculated by Excel, in the same color as the data points. This time I made the trend line dotted.
German Battlecruiser Gun Size History Chart
This timeline highlights another characteristic for German battlecruisers. In this chart, that key characteristic under review is main gun size.
Merely to practice making chart object color choices in Excel, I chose a “Prussian flag” color scheme here. (All the changing flags of the German navy during this historical period were red, black, and white.) I selected white “doughnut” data points, and made a deliberate exception of white diamond data points for the reference class Mackensen. I included very light gray grid lines for major scale and no grid marks for minor scale. Again, I used the “glow” chart element to highlight our subject class as well as the fictional data point. I added text labels in white with the names for the key classes. The data pattern discouraged the use of a linear trend line. The 11-inch gun size of the Gneisenau class clearly fell well below any trend of the previous generations.
Contemporary Battleship Table
This table and associated charts shows and compares the characteristics of battleships built within about a decade of the Gneisenau class. I used the default Excel chart settings, but used the “glow” chart element to highlight our subject class, the fictional data point, and the characteristics of two specific contemporary battleship classes (the Dunkerque class and the Yamato class.)
As the main question concerns proper categorization, I used the k-means statistical technique to mathematically group all the ships into categories. This table provides the key characteristics input into the clustering algorithm. The “Country” and “Type” columns are included in the table, but were not used as inputs to the clustering algorithm.
This worksheet also provides a pre-visualization of the relationship between the key characteristics in order to help the eye see how clustering might unfold. I used one of the Excel alternative charting styles to provide a simple clean appearance.
This table shows the results of the clustering analysis performed to define five clusters. I have provided nicknames to help distinguish the clusters as defined. There are additional separate columns showing the results of clustering analysis for up to twelve clusters.
The results are analyzed in more detail in the accompanying separate post. However, the results are interesting and pretty clearly defined. The analysis clearly and successfully segregates all the historical “true” battlecruisers (Cluster 3.) The analysis does not include the Gneisenau class in this cluster.
Though the clustering analysis did not incorporate dates, the analysis pretty clearly segregates a last “generation” of battleships. (Cluster 2.) To my surprise, the analysis does include the Gneisenau class in this cluster.
I welcome any thoughts or suggestions you might have on the data, the use of the Excel tool, the analysis, and the results. Feel free to contact me directly.
(Image courtesy of United States Navy.)