Solved

Sir, please help me in creating line graph for certain keywords and their ranks?

Posted on 2011-03-02
6
388 Views
Last Modified: 2012-05-11
Hi,

I have been provided with certain set of keywords with their respective ranks in the sample excel sheet. I have been asked to create some sort of Line Graph by my boss so that these can be tracked on a weekly basis.
I will truly appreciate if I can get some kind of line graph for each keyword so that I can track how the keyword will perform over time. So, I can record the ranking for them, and the output would appear in the form of a line graph - lower numbers would show higher in the graph. The graph should be such that if the keyword is getting higher ranking (lower number in the sample excel sheet) over time to check the performance of that particular set of keywords.


Sincere regards,
Amita
 sample-data.xlsx
0
Comment
Question by:deoyagya
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

in your sample file, first replace all the - entries in the cells with no values:

- select the data from cell A1 to D47
- hit Ctrl - H
- in the Find What field enter a - sign
- in the Replace with enter nothing
- click "Replace all"

Now let's create the chart.

- Select A1 to D47
- on the Insert ribbon, click Line and select the top left line chart type

Let's get the legend out of the way.

- click the legend, then click "Format Selection" on the Layout tab of the Chart Tools ribbon
- select "Bottom" in the legend options
- leave the dialog box open!!

- click the Y axis (or a text on the Y axis) to select it. The Format dialog will switch to "Format Axis"
- Make sure "Axis Options" is selected in the side panel of the format dialog and tick "Values in reverse order". Now the X axis shows on top of the chart.
- tick "Maximum axis value" in the "Horizontal axis crosses at" panel of the dialog.
- close the dialog box with the Close button

You now have a chart that shows the position of the keywords for each series, with the low values showing high in the graph.

See attached.

cheers, teylyn


Copy-of-sample-data.xlsx
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Your data does not have any indication of time, so it is not possible to show how a keyword develops over time. To show this, you need a data set that has the elements

date, key, search engine, rank

From a data table with these four elements and many rows of data, it will be easy to create a pivot chart to show how keywords rank over time for different search engines.

But without any indication of a date, it is not possible to show the development of ranking over time.

cheers, teylyn

0
 

Author Comment

by:deoyagya
Comment Utility
Hi Teylyn,

I appreciate your time and solution on this. However, the graph is missing some data points. I have highlighted the same in the 'yellow color' in the excel.
Can you please let me know as my boss would like to have all the data points on the graph.

Regarding the "indication of date", I have not been provided. So, I am not sure how he is going to validate the development of ranking over time.



Thanks,
Amita

sample-data-1-missing.xlsx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:deoyagya
Comment Utility
Hi Teylyn,

I also would like to check if it is possible to have the search engines 'google, yahoo and bing positions' in the drop down option so that I can select a search engine and only the line graph for the selected search engine should be displayed.

Thanks,
Amita



0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Hello,

>> the graph is missing some data points. I have highlighted the same in the 'yellow color' in the excel.

Here's how to adjust the chart:

- click the chart, then click the Design tab on the Chart Tools and then "Select Data".
- click the "hidden and empty cells" button and tick "Connect empty data points with line"

With regard to having a drop-down to show only one data series: this is a completely different issue (dynamic charting) and you will need to post this in a new question.

cheers, teylyn


0
 

Author Closing Comment

by:deoyagya
Comment Utility
I truly appreciate the expert advise and easy to follow instructions!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now