When reporting on data that has dramatically different scales but is correlated, it can sometimes be difficult to easily create visual charts that are meaningful. For example, you might have a marketing spend in the thousands of dollars per quarter for a program that generates significantly less than 100 leads per quarter. To visualize if there is a correlation between spend and leads, simply using Excel to create a two-line chart won’t work.
Since the scale on the example chart will automatically adjust to accommodate figures as large as $15k, the detail in changing leads generated is completely lost. And while its possible to do some fancy VBA coding to make an official “dual scale” graph, for those uncomfortable with VBA or simply in a hurry to see correlation, that may not be an option.
To easily get those two figures onto the same graph in a meaningful way, instead of graphing the raw data, calculate the percent each data point represents of the total quantity over the time period you are graphing. Simply insert two lines and enter the formula for the cells in the new line, “=Data_cell/$Summation_Cell”, as in the screen shot below.
Once you fill this data throughout all the cells, right click on the graph and choose “Select Data”. Identify the cells with the percentages of total, instead of the values. Don’t forget, holding down “Ctrl” allows you to select multiple cells or cell ranges.
Lastly, select a chart type that does not have axis scale labeled but does have data points labeled. While the default point labels will be the percentages, edit the labels such that each label is “=Data_cell” referencing the cell with the actual quantity, not the percentage.
Once you’ve changed all the label points, add a little color and possibly some formatting, and you’re done! You now can easily see the correlation (or lack thereof) between the two different sets of data, displayed on the same graph despite having such different scales! As you can see, the chart is referring to the percentages (highlighted in pink) for the shape of the lines, but referring to the values for the labels (highlighted in yellow).