Solved

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

Posted on 2011-03-02
6
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 50
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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 (Microsoft MVP / EE MVE) 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

617 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