Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

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

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
deoyagya
Asked:
deoyagya
  • 3
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
deoyagyaAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
deoyagyaAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
deoyagyaAuthor Commented:
I truly appreciate the expert advise and easy to follow instructions!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now