Just Google It: Panel Graphs in Excel
I got a data task last week. I love my data. At my last job I once got lost writing a report on our evaluation data and didn’t look up from my computer until my stomach growled and I realized I stayed an hour later than I usually go home.
Anyway, I had the task of plotting some pretty disparate numbers. Things like website traffic (maximum at tens of thousands) per week as well as acceptances (maximum under 10) per week…plus some numbers in between. I thought this would be easily solvable with a secondary Y axis but after plotting the data that way, I realized it wasn’t the best solution. I either needed to blow up the graph to billboard size or have a third or fourth Y axis to make it work.
Then I tried taking out website traffic as a whole since it was the outlier, but then my next biggest variable became a problem since it was an outlier compared to the rest of my data. My next solution was to plot all of the variables separtely. Since the data is all plotted by week, I just had to stack them on top of each other, and the trends (spikes and dips) would still show even though it wouldn’t be as visually appealing. I made all 5 graphs before realizing that was a dumb idea. Frustrated, I told myself that there are entire professions based around data analysis and visualization, and there has to be a way to display these numbers in the same graph.
I then turned to my best friend, Google. I can remember taking a computer applications class in middle school where we learned the “proper” way to search – using keywords, plus signs, etc. That is never the way I search, though. I’m a fan of typing full sentences or phrases of my stream of consciousness. In the pre-Shazam or Siri days, I found many a song title and artist by typing in the lyrics of songs I heard on the radio.
This is what I typed in, and I felt it like destiny that someone else had used the same phrase that I had. The forum led me here which is where I found my answer.
The feeling of fate continued as I read the post. The writer laid out all of the ways people attempt to display data like this, which mirrored the attempts I had just made.
- You can plot everything on the same graph with one Y axis, but that leads to everything but your outlier looking like a flat line.
- You can plot a second Y axis, but then you have to remember which lines go with which axis (and still wasn’t enough in my case)
- You can plot all of the data on separate charts and try to line up the X axis
Or you can make a panel graph! I copied the format of the data and the formulas in the step by step instructions (accounting for the fact that I needed 5 panels rather than 3). Since the example data is so small compared to mine, I had to write down a list of conversions (example E9 = my G44) to get the formulas right after quite a few mistakes that had my graph looking wonky. But then I had it! Five variables worth of data aligned along the same X axis each with its own Y axis for scale.
The point of all of this is that it’s very unlikely that the problem you’re experiencing is something only you will encounter. The internet is a wonderful place that can help you with many problems from fixing your blender (boil the blade kit to get it unstuck) or helping you make a panel graph for the first time.