Solved

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

Posted on 2011-03-02
6
435 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
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.

 

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
A WS within a WS 11 41
drag and drop (but keep all) lines 11 46
Combining match and vlookup??? 4 31
Excel Add-in Subscript out of range 5 31
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;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

738 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