1. Welcome! Please take a few seconds to create your free account to post threads, make some friends, remove a few ads while surfing and much more. ClutchFans has been bringing fans together to talk Houston Sports since 1996. Join us!

Microsoft Excel Experts - HELP!?

Discussion in 'BBS Hangout' started by matty101, Apr 2, 2011.

  1. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    Hey guys,

    I have a set of data which shows the unemployment rates of the US for every month from 2000-2010. I need to know how to table it so that when i turn it into a line graph, I have only 1 line. ATM, with the way I have entered the data, it is coming up with 12 lines - A line for every month. I need unemployment rate on the y axis and years (2000-2010) on the x axis.

    I have tabled the data as follows (this format ends up showing a separate line for each month, but i want one line, with 132 plots along it for each month over the 11 year period.)

    It would look like this if you change the dates in this site from Jan 2000-Dec 2010:

    http://www.tradingeconomics.com/Economics/Unemployment-Rate.aspx?Symbol=AUD

    Here is my data - how should it be tabulated so that I get the result i need - 1 line, 132 plots along it..

    2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010
    Jan 4 4.2 5.7 5.8 5.7 5.3 4.7 4.6 5 7.7 9.7
    Feb 4.1 4.2 5.7 5.9 5.6 5.4 4.8 4.5 4.8 8.2 9.7
    Mar 4 4.3 5.7 5.9 5.8 5.2 4.7 4.4 5.1 8.6 9.7
    Apr 3.8 4.4 5.9 6 5.6 5.2 4.7 4.5 5 8.9 9.9
    May 4 4.3 5.8 6.1 5.6 5.1 4.6 4.4 5.4 9.4 9.7
    Jun 4 4.5 5.8 6.3 5.6 5 4.6 4.6 5.5 9.5 9.5
    Jul 4 4.6 5.8 6.2 5.5 5 4.7 4.6 5.8 9.4 9.5
    Aug 4.1 4.9 5.7 6.1 5.4 4.9 4.7 4.6 6.1 9.7 9.6
    Sep 3.9 5 5.7 6.1 5.4 5 4.5 4.7 6.2 9.8 9.6
    Oct 3.9 5.3 5.7 6 5.5 5 4.4 4.7 6.6 10.1 9.6
    Nov 3.9 5.5 5.9 5.8 5.4 5 4.5 4.7 6.9 10 9.8
    Dec 3.9 5.7 6 5.7 5.4 4.9 4.4 5 7.4 10 9.4

    Any help would be greatly appreciated.. Need it for an assignment!

    Cheers guys.
     
  2. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    ughhh - that table came out a little disjointed but hopefully you understand what it says there?
     
  3. durvasa

    durvasa Contributing Member

    Joined:
    Feb 11, 2006
    Messages:
    37,976
    Likes Received:
    15,450
    Right-click, paste-special, text.

    There will be a little clipboard icon that shows up on the lower-left corner of the pasted-in column. Select that, and choose text-to-columns. Or, select the column, and do text-to-columns from the Data tab.

    Edit:

    Oh, i think what you want to do is just paste the transpose? Select your table, then right-click, paste special elsewhere. Select transpose checkbox.
     
  4. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    There is no 'text' option when i right click and click paste-special... sighhh so lost... :confused:
     
  5. durvasa

    durvasa Contributing Member

    Joined:
    Feb 11, 2006
    Messages:
    37,976
    Likes Received:
    15,450
    I wasn't reading carefully enough what you were asking for before.

    What I said doesn't answer your question. I don't know of a way to do that, other than just joining all the rows manually. It would hardly take a minute.
     
  6. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    Hmm, forgive my ignorance (pretty much first time using excel) but im not sure what you mean by this?? As in, joining all the rows??
     
  7. durvasa

    durvasa Contributing Member

    Joined:
    Feb 11, 2006
    Messages:
    37,976
    Likes Received:
    15,450
    Is this what you want it to look like?

    [​IMG]

    I copied your data, did a paste-special with transpose selected. Then, I joined all the rows into one line (manually).
     
  8. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    Very similar to what i want - and what you have done seems to be much better than what I have. However, I need just one line, joining the top of each of those columns.
     
  9. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    I think what you had there is a bar graph?
     
  10. durvasa

    durvasa Contributing Member

    Joined:
    Feb 11, 2006
    Messages:
    37,976
    Likes Received:
    15,450
    Its a column graph. Bar graph does horizontal bars.
     
  11. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    Ahh correct. Anyway to turn that column graph into a line graph then??
     
  12. durvasa

    durvasa Contributing Member

    Joined:
    Feb 11, 2006
    Messages:
    37,976
    Likes Received:
    15,450
    When you select your table, choose insert line graph instead of column graph.

    [​IMG]
     
  13. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    Thanks so much mate! much appreciated! Now all I have to do, is put a separate line, over the same graph, with Australia's unemployment figures..

    2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010
    Jan 6.5 6.2 7 6.1 5.5 5.1 5.1 4.5 4.2 4.9 5.4
    Feb 6.5 6.2 7 6.1 5.5 5.1 5.1 4.5 4.2 4.8 5.3
    Mar 6.4 6.6 6.6 6 5.7 5.1 5.1 4.6 4 5.4 5.3
    Apr 6.6 6.3 6.4 6.1 5.4 5.2 4.9 4.4 4.1 5.7 5.4
    May 6.5 6.8 6.3 6 5.5 5.1 4.9 4.3 4.2 5.4 5.4
    Jun 6.4 6.8 6.3 6.1 5.3 5.1 4.8 4.3 4.3 5.8 5.2
    Jul 6.2 6.9 6.5 6.1 5.5 5 4.8 4.3 4.2 5.8 5.1
    Aug 6.1 6.9 6.2 6.1 5.5 5 4.7 4.3 4.3 5.7 5.3
    Sep 6.1 6.9 6.3 5.8 5.5 4.9 4.7 4.3 4.1 5.8 5.1
    Oct 6 6.8 6.3 5.8 5.4 5 4.7 4.2 4.3 5.7 5.1
    Nov 6 7.1 6.1 5.8 5.1 5.1 4.5 4.4 4.4 5.8 5.4
    Dec 6.3 6.9 6.2 5.7 5.2 5 4.6 4.5 4.5 5.6 5.2

    Any idea how to do that man??
     
  14. durvasa

    durvasa Contributing Member

    Joined:
    Feb 11, 2006
    Messages:
    37,976
    Likes Received:
    15,450
    Yeah, you just add the joined columns as another row. Then when you graph the table, it will show two lines.
     
  15. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    I will personally donate $10 to the tip-jar if you plot that for me, sir. :grin:
     
  16. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    Do it for the good of clutchfans!!! And maybe for the good of my university grades...
     
  17. durvasa

    durvasa Contributing Member

    Joined:
    Feb 11, 2006
    Messages:
    37,976
    Likes Received:
    15,450


    [​IMG]
     
    1 person likes this.
  18. matty101

    matty101 Member

    Joined:
    Jul 18, 2006
    Messages:
    925
    Likes Received:
    4
    Thanks Durvasa! Repped.
     

Share This Page

  • About ClutchFans

    Since 1996, ClutchFans has been loud and proud covering the Houston Rockets, helping set an industry standard for team fan sites. The forums have been a home for Houston sports fans as well as basketball fanatics around the globe.

  • Support ClutchFans!

    If you find that ClutchFans is a valuable resource for you, please consider becoming a Supporting Member. Supporting Members can upload photos and attachments directly to their posts, customize their user title and more. Gold Supporters see zero ads!


    Upgrade Now