+ Reply to Thread
Results 1 to 23 of 23

Creating a Graph

  1. #1
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Creating a Graph

    Hi all,

    I'm really struggling with this one and would appreciate some help.

    I have attached a spreadsheet which shows the data I want to display in a graph.

    What I want to do is display 'Rank' on the left handside of the graph, 'Months' on the bottom and then the right 'Keywords' as the series and then have the results plotted on there so we can see how a keyword(s) is performing over the months based on zero to not in the top 100.

    Any help much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    Hi Oliver,

    there are several ways to skin this cat, depending on what you want to show.

    In the attached, Sheet 1 takes your data layout with a little modification and creates a column chart.

    In Sheet2, I've re-arranged the data to put the emphasis on the changes between the keywords from month to month.

    Explore what you want to show and get back for more help, if you need.

    cheers
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    Thanks for that.

    I think based on the volume of keywords it may be best to represent each one individually over the 12 month period.

    I have attached new data and would really appreciate your assitance on how I create a graph, showing rank from 100 to zero on the left handside of the graph, months on the bottom and then the keyword rank on the graph itself?

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    Like this, maybe?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    That is great and really appreciated.

    The only final question I have is how to change the Axis which has 0 - 100 and reverse this so it is 100 going up to Zero? Don't know how to edit this section to do that or where you find the data; therefore it would be great if you could tell me how to achieve this?

    Thanks again.

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    Now we're getting into the trickery!!

    - Insert a helper column where you multiply each value with -1
    - then graph the helper column instead of the real data
    - next, set the x axis to cross at the lowest value, -100 in the attached example.
    - finally, format the y axis number format with custom format

    #,##0;#,##0

    which will suppress the - sign for the negative numbers.

    hth
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    I just want to say a big thank you for all your help.

    Much appreciated and did the job!

  8. #8
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    Sorry, I do have one final senario that I need help with.

    Okay, I want to display a graph for each keyword. So with that in mind say I wanted to create a graph for keyword1, by date (21 Dec 2009) and it has a rank of 9. How can I keep the Axis displaying 0 to 100? As at present it will only go up to 9?

    Thanks again

  9. #9
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    Format the Y axis and in the Axis options set all values to fixed:

    Minimum -100
    Maximum 0
    Major unit 10
    minor unit 2

    hth




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  10. #10
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    I'm stuggling with that; therefore is there any possibility of an example I can review?

    Many thanks

  11. #11
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    what version of Excel are you using? I can flick you a screen shot. Please update your profile.

  12. #12
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    I'm using Excel 2007 and thank you.

  13. #13
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    Now please update your profile.

    then click the Y axis in the chart, hit Ctrl-1 to open the format dialog and compare the settings in the attached screenshot.

    hth
    Attached Images Attached Images

  14. #14
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    When I change this I display negative values going up the Axis and based on the graph type it doesn't plot the keyword on the chart.

    Could you possibly send me an excel example to review please.

  15. #15
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    the screenshot was created from the file attached to post #6 in this thread. I simply changed the values in column C to numbers <10 and then opened the y Axis formatting dialog to put in the settings in the screenshot. So, re-posting that file again won't add any value. Maybe you can post your version for a look?

  16. #16
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    Sure - I have attached the file that I'm working with.

    As you will see all the keywords are in there for that specific day.

    What I will be doing is creating a graph for each keyword and will continue building that for each day that I enter data.

    Thanks
    Attached Files Attached Files

  17. #17
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    I see no graph in your file.

  18. #18
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    No, I tried and failed.

    I have attached what I did; however, as mentioned it doesn't display the data as positive figures and the result isn't shown on the graph.
    Attached Files Attached Files
    Last edited by oliver79; 12-22-2009 at 08:39 AM.

  19. #19
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    You've only selected one data point to plot in the line chart and it is formatted without a data marker, so it is virtually invisible.

    I think you need to take a step back. If you want to show a line chart, you need at least two data points to connect. Your current data table has a few dozen keywords, but only one data point per keyword.

    Ultimately, I guess you want to plot several dates and values for one individual keyword, but you'll have to lay out the data accordingly. Excel charts will not summarize or analyse your data, just plot what you feed them.

    Ultimately you may be better off with pivot charts, if you add data on a regular basis.

  20. #20
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    Okay, as mentioned I'm out of my depth.

    In realtion to the chart I will be adding more dates; however, how to I prevent the negative numbers being displayed on the Axis.

  21. #21
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    read post #6 of this thread again, especially

    - finally, format the y axis number format with custom format

    #,##0;#,##0

    which will suppress the - sign for the negative numbers.
    Before you can start thinking about plotting charts for individual keywords, you will have to come up with a bit of data for each keyword, i.e. more than one row.

    there are very elegant and effective dynamic solutions possible, but with just one row per keyword, it does not compute. So, mock up a data sample with at least five items per keyword, where the order of dates and keywords does not matter, and upload that. You'll be surprised what's possible .... promise!

    Oh, and please update your profile with your Excel version!

  22. #22
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Re: Creating a Graph

    Hi,

    Okay, I have attached a new workbook and I have the graph how I would like to see it; however, I need some final assistance in how I change the numbers of the Axis from being dislayed as negative please.
    Attached Files Attached Files

  23. #23
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a Graph

    how I change the numbers of the Axis from being dislayed as negative please.
    I've already explained it twice. What more can I do?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1