Solved

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

Posted on 2011-03-02
6
400 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:Ingeborg Hawighorst
ID: 35024743
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:Ingeborg Hawighorst
ID: 35024765
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
ID: 35024950
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:deoyagya
ID: 35024983
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:
Ingeborg Hawighorst earned 500 total points
ID: 35025489
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
ID: 35029733
I truly appreciate the expert advise and easy to follow instructions!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 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

17 Experts available now in Live!

Get 1:1 Help Now