A couple of weeks ago, I started making the graphs in the Financial Times’ Visual Vocabulary in Microsoft Excel. Based on the Graphic Continuum work I’ve done with Severino Ribecca, the FT’s Visual Vocabulary contains about 70 graphs with a definition of each. I had particular fun figuring out how to create the Arc (or what I call the Arc-Dot) chart in Excel. In this post, I show you how to create the chart in Excel using the distribution of Members in the 116th US House of Representatives.
The Arc-Dot Chart
The Arc-Dot chart lays out dots in a semi-circle to show the distribution of politicians in different political parties. The version I create here shows the 435 Members of the 116th U.S. House of Representatives, of which 235 are Democrats, 197 are Republican, and 3 are (currently) vacant.
The basic philosophy is pretty straightforward: Use a scatterplot to position points on an x- and y-dimension around a half-circle. Thus, we need to figure out how many rings we want (I’ll use 11 here) and how many dots on each, the total of which sums to 435.
Here’s a quick screenshot of the entire worksheet. I’ll go through each section so you can see how this is built. You can download my Excel file at the end of the post.
The Lookup Table
I set up a little lookup table to the side of my main data in columns R-Z. The first four columns are where I can play around with setting up the number of points in each ring, so there isn’t a consistent formula in these columns. I set the radius of each ring in column X, which I’ll use in my main data table in a moment. In this version, I add 0.1 to each ring, which I thought looked good—between that distance and the size of the circles, you’ll need to decide what looks best.
The most important part of this lookup table is the Scalar series in column Y. The formula in these cells is =180/(S3-1), which is 180-degrees divided by the number of dots in each ring minus one. We’ll use these scalars to push the x- and y-positions of each dot to the right.
The Main Data Table
Let’s now go to the main data table, in columns A-P, and define the first few:
- Column A: The observation number. This counts up by one to 435 observations.
- Column B: Ring Number. Cell B3: =VLOOKUP(A3,$U$2:$V$13,2,1). I use an approximate match VLOOKUP formula to assign the Ring Number to each observation.
- Column C: Radius. Cell C3: =VLOOKUP(B3,$R$2:$Y$13,7,0). Again, an approximate match VLOOKUP to assign the radius for each ring.
- Column D: Scalar. Cell D3: =VLOOKUP(B3,$R$2:$Y$13,8,0). We built this scalar earlier in the lookup table, so I just use an exact match VLOOKUP here.
- Column E. Dummy. Cell E4: =IF(OR(A4=$U$3,A4=$U$4,A4=$U$5,A4=$U$6,A4=$U$7,A4=$U$8,A4=$U$9,A4=$U$10,A4=$U$11,A4=$U$12,A4=$U$12),0,1). I want to place a zero in the first occurrence of each ring and a one thereafter, so this formula (there is probably a better/more concise way to do this) compares the observation number in column A to the Cumulative values in column U. (Note: I hard-coded cell E3 here).
- Column F. Number. Cell F4: =IF(E4=0,0,D4+F3). The points in each ring will start at zero (the first observation in each ring) and then stack up using the Scalar value calculated earlier. Here then, the first observation in each ring is zero and each subsequent cell adds the scalar amount.
Now we start moving things into polar coordinates to build the circle:
- Column G. Radians. Cell G4: =(F3/180)*PI(). The basic circle to convert to radians.
- Column H. X-position. Cell H4: =C3*COS(G3). Multiply the radius of each ring by the cosine of the radians.
- Column I. Y-position. Cell I4: =C3*SIN(G3). Multiply the radius of each ring by the sine of the radians.
- As you can see in the screenshot, the x-position of the first observation of each ring is equal to the radius and the y-position is zero.
Create Different Groups
You could simply plot the series in columns H and I, and then color each of the 435 circles by hand, but that seems tedious. Instead, let’s use some formulas to create three separate series for each group (Democrats: xDEM, yDEM; Republicans: xREP, yREP; Vacancies: xVAC, yVAC) and plot them simultaneously.
We need a couple more lookup tables to do this. I did this semi-consistently in columns AB-AF by setting the number of Democrats in each ring by dividing the number of points in each row in half. I decided that I wanted the vacancies in the first, fourth, and eighth row, so set those ones manually. The number of Republicans is then just the residual.
I create a cumulative count of the points in each ring in columns AH-AL. I’ll use these to assign the x- and y-positions for each of the three series.
- Column AH. Repeats each ring number three times.
- Column AI. Repeats each party/group three times.
- Column AJ. Cell AJ3: =HLOOKUP(AI3,$AD$2:$AF$13,AH3+1). Uses an HLOOKUP formula and uses the “Splits” lookup table to assign the number of dots in each ring for each party.
- Column AK. Cell AK: =AK3+AJ3. This cumulatively adds the position values (cell AK3 is hard-coded to zero).
- Column AL: Repeats an initial for each group; I’ll use this in a VLOOKUP in a moment and the initial is used just for convenience.
Now that we have these two lookup tables, let’s go back to our main data table.
- Column J. Cell J3: =VLOOKUP(A3,$AK$3:$AL$36,2,1). Use a VLOOKUP formula to look up whether each observation (in column A) will be a Democrat, Republican, or Vacant (as initials).
- Columns K-P. Cell K3: =IF(J3=”D”,H3,NA()). For the rest, we use a simple IF formula to check the initial of each party and if it matches the condition, then we assign the x- and y-position; if not, it is assigned an “#N/A”, which Excel will then ignore when the chart is built.
Build the Chart
Phew! Still with me? I know, this is a lot, but we’re almost there. We’ll now create a scatterplot with three series (columns K-P). I set the size of each circle marker to 12, color and fill as is usual. This is now easier to do because I can select each series as opposed to doing each point one by one.
I could add the middle label (“435”) with a text box, but I prefer to use data, so I have a final scatterplot series with an x-position of 0 and a y-position of 0.36 (manually set where I want it). The marker is hidden and a data label is added with the name of the series and sized as I want it.
I admit, this is not the easiest chart to make in the world, but now that it’s done, it’s relatively easy to automate, though adding more groups is kind of a hassle. You may not need all of the formulas as I’ve laid them out here if you want to do some of this manually, but I find that setting the whole thing up first like this makes life easier down the road.
If you’d like to make your own Arc-Dot chart, you can download my Excel file. I’ll publish the entire Visual Vocabulary in Excel next week.