Months comparison charts

This took some doing. The data was in bad shape.

First of all, it was strung out instead of normalized. Strung out data is OK as a final report, but it means that each month has to be looked at separately.

So I normalized the data. See the attached zip file. It contains the instructions, code and a sample of how to do this.

Once I had the data normalized, I had to convert the date strings into actual dates. The string had embedded spaces and line breaks, so it took a bit of playing with the MID function to parse out the year and month.

Once I got that done, I then made a pivot table of it.

Now if you want to do a side-by side comparison (2014 on the left and 2015 on the right), you could make a chart directly off this pivot table. However, if you want to stack the individual months side by side, you will have to make the chart manually.

Select a cell not in the pivot table. Go to Insert and insert the kind of chart you want.

Then right click in the blank chart.

Select select data.

Add a series

Give the series the name 2014 Call Center.

For the values, highlight Cells B16 to B16.

Add another series

Give the series the name 2015 Cell Center

For the values, highlight Cells B18 to B28

And so on for each item.

Finally, edit the X-axis and highlight cells A6:A16.

In the sample I did Call Center and Desk.

If you are sure that you will always have data for each month, then these fixed positions will work. Otherwise, you may have to make named dynamic ranges.