Here is an example that you might be given at work.
Your team is curious to see how the total conversions are performing for their business. Your task is to present a readable comparison chart to your team that shows the year over year breakdown for your company. Your boss also asks if you can break the data down by quarter by quarter since you are selling coats and their is a seasonal effect with the summer vs the winter. This is where a pivot table and comparison chart will come in handy for your assignment. Let’s begin by creating the pivot table with all of the information you were given by your boss.
Create the Pivot Table
Here is the data you are given. You are handed a list of dates, the campaign name, and the amount of conversions that happened each month. We will just call conversions sales.
To create a pivot table, you will need to select all of the data. A quick shortcut on your PC is to simply start with the top cell (which is A1 in this example) and perform this simple formula. This formula below will select everything from left to right and down. This is a good habit to get into when creating a pivot table. If you don’t perform this formula, you might miss some data and thus have an incomplete pivot table later on.
=((CTRL+SHIFT+Right Key) +(CTRL+SHIFT+ Down Key))
Break out the Dates in the Pivot Table
This is how I have my pivot table set up. My boss asked me to break out years and the quarters. As a note, I could move years above the date in the row section to have the data broken out by years instead of quarters, but I am going to keep it as it is. I am going to change this data later on to make the chart, so I am not that concerned with how it looks at the moment.
As a note, you can change how the date is set up. Simply right click on the date in the pivot table and head over to group. From there, you can change the date to show any variation you want. For this example, I have years and quarters checked off.
Create the Comparison Chart in Excel
Now that we have the data set up how we want, we are going to head over to our graph tool. Since I want to show a comparision for YoY with the quarters, I am going to show a line graph. This is how mine looks below. This chart is a perfect visualization for the company to look at. Things that I see are the seasonal effect with the warmer weather, and I also see that 2015 is already outpacing the other two years. This should make the team happy to see that we are already on pace for the best year in business.
The big thing I want to point out with my chart is the red arrow that points to years. If I did not move years to the legend area, I would see a chart like this below. This chart below might be good to see how conversions have looked since we started business, but I want to show how every year compares to the other with the quarters to show the seasonality factor. Since I want to do that, I am going to stick with the other chart above.
The best part of using a pivot table to create the comparison chart is that we are able to change the data at anytime. I can change quarters to months, have months with years, and much more. The pivot chart also allows me to add any new data to my original file and just simple update the pivot table to show the new numbers. This data is very clean and easy to read for everyone.