Using Excel to Plot Year of Dedication and Locations of Confederate Monuments

Quote of the Day

Half of what you learn in medical school is going to be shown to be either dead wrong or out-of-date within five years. Trouble is, nobody can tell you which half.

— Dr. David Sackett, pioneer of evidence-based medicine. Everyone who works in the technology field understands this problem. I often compare our engineering knowledge to milk – it has a shelf life.

Figure 1: Monument to the First Minnesota at Gettysburg. (Source)

I have been listening to the controversy surrounding the Confederate monuments around the United States. I live in Minnesota, a Union State, and we have no Confederate monuments in the state. Minnesota did send troops to the Civil War and they performed well (Figure 1).

I heard some discussions on television about all the Confederate monuments around the country and when they were erected. I decided to look for the data and plot it for myself. I very quickly found a document from the Southern Poverty Law Center that looked interesting and provided me some interesting data tidying and charting challenges. My focus here is on duplicating their chart of monuments dedications dates. This chart type is not a standard Excel type and I wanted to see how I could duplicate it. This workbook will be used in a charting seminar that I plan to present in a month or so.

As I looked at the PDF source document, I suspected that it had been originally written in Microsoft Word. To cleanly copy the data into Excel, I use a three-step process to avoid a messy transfer:

• copy the table from the original PDF document.
• paste into Word and re-copy from Word.
• paste into Excel.

This produced data that I could clean-up using Get and Transform (also known as Power Query).  Once cleaned up, I could use the data to look at which states had monuments (Figure 2). You can see that there are 1503 Confederate monuments listed in the data, which does not include approximately 2,570 Civil War battlefields, markers, plaques, cemeteries and similar symbols that commemorate historical events.

While the original data listed 35 location types, its graphic grouped the data into three categories: (1) monuments at schools, (2) monuments at courthouses, and (3) other. I followed their lead.

Figure 2: Confederate Monument Summary Table.

Unfortunately, the original document only has dedication dates for 856 of the 1503 monuments. While this is a serious shortcoming, this is the data that they plotted, and I will do the same.

I plotted the dedication dates using the format used in the original document, which is not a standard Excel chart type. However, I was able to make a plot that looks very similar to the original by using some formulas in cells. Please see my workbook here for details.

Figure 3: Plot of Confederate Monuments By Year of Dedication and Location.

Note that the original table added callouts for various important civil rights events. I decided that I wanted to keep the chart simple and did not include these in Figure 3.

There are some observations that we can make about Figure 3:

• Confederate monuments began being dedicated before the Civil War was even finished.
• There was a large surge in monument dedications around 1910.
• There was smaller surge of monuments dedicated during the 1960s. The only unusual thing about these dedications is that many were at schools. Previous dedications were only rarely at schools.

Save