Sorry it has been a few days since my last post, but I have a lot that has been going on with new projects and a lot of travel. Now it is time to get back to business.
In this post, I will show you a few charts on SEO Performance from a recent Friday Challenge that Don developed. Do had 3 different takes on the Friday Challenge. Thanks Don for you submissions. Great job!
Stacked Bar With Distribution Within a Given Range
“I am looking to create a stacked bar with distribution within a given range for SEO keyword performance reporting.
Say there are 10 keywords we are tracking.
5 are within the top 10 results
3 are within the top 20 results
2 are within the top 30 results
This would be one month – one plot on x axis.
The y axis should scale 0-30 at 10 point intervals, so 0, 10, 20, 30.”
– Excel User
Solution 1: Bar Chart
Then highlight the range of A1:B3 and then go to the Insert Ribbon. Then choose the Bar Chart from the Charts Group. From there you can remove the horizontal line by changing the line color on the horizontal axis. You can also add data labels from Layout Ribbon.
Solution 2: Bubble Chart
Then highlight the range of A1:C3 and then go to the Insert Ribbon. Then choose the Bubble Chart from the Other Charts button in the Charts Group. From there you can add vertical gridlines, and add horizontal and vertical axis titles. You can also add data labels from Layout Ribbon.
Solution 3: Stacked Column Chart
You will notice that the data for the X-axis is all the same “9/1/2013”. That is because we want Excel to use the data function for the X-Axis and since the date is all the same date, it will stack it on top of each other. Now you need pick a blank cell that is away from any data point and create a Stacked Column Chart from the Insert Ribbon. If you accidentally are next to any data points, you will have to delete all series from the Select Data dialog box. Now go into the Select Data dialog box by going to the Design Ribbon. From there you want to add a Legend Entry (Series).
Now that you have done that, we just need to clean up the formatting of the chart.
Select the Y_Axis series and set the fill color to No Fill. We are using this series to set the Vertical Axis values. Then Select the SEO series to the fill color that you want (in this case blue) and the border color to Black.
You can watch me recreate these 3 charts that Don created here:
Free Excel Chart Template Download:
You can download the free Excel file here: SEO-Keyword-Performance-2010.xlsx
Thanks Don for submitting your sample file for the Friday Challenge. Tomorrow I will post Pete’s solution, so come back then.