As a big movie fan, I can tell you that no horror or thriller has ever had as much effect on me as watching JAWS as a child. I remember begging my parents to let me watch it, and I remember vowing that I wouldn’t be scared. Guess what? I was terrified. There’s something about that those two bass notes repeated over and over faster and faster that is just chilling to the soul. I even made my mum check under the bed twice before I would go to sleep. (Even though I was old enough to know that sharks needed water to breathe, and of course there was no water under my bed.)
That being said, a JAWS chart is not so scary. In fact once you know how, it is actually very very easy! What is a Jaws chart? A Jaws chart allows you to compare two sets of data. Both data sets are plotted as bar charts, but one is placed on the bottom x axis, the other on the secondary top axis.
Step 1: Set up the data
So first you need 2 sets of data. This could be the sales of 2 products over time; it could be sales of the same product over two different years. But in the end, you need two columns of data with a third column to make the labels for the horizontal axis
Step 2: Create a normal column chart
Highlight your three columns of data and click on Insert > Column > 2D Clustered Column chart
You should have a chart like the one below:
Step 3: Move the second series onto the secondary axis
Left click on the second series and press Ctrl+1 to bring up the formatting window.
Move the chart onto the secondary axis and reduce the gap width to 50%. Before closing the window, left click on the first series and reduce its gap to 50% as well. Then close the window.
The chart should now look like this:
Step 4: Add a secondary vertical axis and adjust
With the chart selected, click on the layout tab. Then click on Axes > Secondary horizontal axis > Show left to right axis. The chart should update as shown below:
Double click on the secondary VERTICAL axis. In the menu box that appears, adjust the axis options so that the “values are in reverse order” and the Horizontal axis crosses at axis value “0”.
Then adjust the maximum scale value to a value that allows you to see the up and down columns without them overlapping. Note: You will also have to adjust the left primary vertical axis to the same amount. In my case, I changed both values to “100”. The graph should then look like the one below:
Click once on the secondary horizontal axis and then delete it. Repeat the procedure for the secondary vertical axis and the primary vertical axis. Adjust the fill colours of each series as you wish.
Right click on the first series and choose add data labels. Repeat for the second series. Then left click on the first set of data labels and press Ctrl+1. Change the label position to inside end. Then click on the home tab and adjust the font colour and size. Repeat for the second series and then press the close button.
Your chart should now look like the one below. What do you think? Can you compare the two series easily? You can download the excel file here, or view it on my Sky Drive below.
- Create a dollar graph in Excel (alesandrab.wordpress.com)
- Create a column chart using a background picture (alesandrab.wordpress.com)
- Dataviz Challenge #3: Alternate Solution by Paul Denninger (emeryevaluation.com)
- Dataviz Challenge #3: The Answers! (emeryevaluation.com)
- Jaws, 1975 (alwaysareasontoparty.wordpress.com)