I’ve never tried a dual axis chart. It’s not readily available as an easy option in Microsoft Excel. I don’t think I’ve even noticed examples of dual axis charts anywhere. As a result, I started by googling dual axis charts and discovered that their use is controversial. For example, Cole from the Storytelling with Data website preferred to focus on the most significant aspect of the story although this website presents an interesting alternative that seems to work. This is another example that discusses various ways to portray the same information with different messages.
So, what would I like to try?
One of my previous research projects has been to examine the connection between public opinion of science and federal funding of science. I could try the following combinations:
- Public opinion of science and public opinion about federal funding of science over time
- Public opinion of science and budget estimates for federal funding of science
- All of the above. However, I admit that this might have terrible results.
Initial Experimenting in Excel
Chart 1: Support for federal funding of science (agree scale) and interest in scientific discoveries (interest scale). This didn’t end up being a “real” dual axis chart because there wasn’t really a need for multiple axis. Both variables measure survey responses, just on different scales. The federal funding variable has an additional available year (2006). It’s kind of messy to have two variables on the same chart like this. Maybe the most obvious message from this visual is that 2010 has a dip in the number of survey responses for both variables? The next message might be that “agree” and “moderately interested” have more responses than a stronger sentiment responses (“strongly agree” or “very interested”).
Chart 2: Support for federal funding of science vs federal funding levels of science (in millions). A “real” dual axis chart. However, I’m not getting a clear message from the chart below. The support for federal funding of science seems to dip in 2010 while the federal funding amount peaks. However, the data actually shows a lower total number of responses that year, not necessarily more negative responses.
Chart 2a: Percentage of support for federal funding of science vs federal funding levels of science (in millions). A little data manipulation into percentages to even out the differences in total responses and another attempt. The left-hand axis going up to 120% bothers me, but not sure how to fix it because it seemed to affect the right-hand axis. This version of the chart makes it much clearer that most respondents tend to agree rather than any other response. There also seems to be not much of a relationship between support for federal funding and actual federal funding numbers, even considering a possible delay in effects. For example, higher support in 2008 might mean a higher funding level in 2010. However, what caused the dip in funding in 2014? Not levels of support…
Chart 3: Nope. Too terrible to contemplate.