# Excel graph -- Need answer fast. Should be simple. **URL:** \ **Category:** Factual Questions\ **Created:** [June 9, 2012, 2:43pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620 "2012-06-09T14:43:18Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [June 9, 2012, 2:43pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/1 "2012-06-09T14:43:18Z")
I want to make a line graph of the following data: ```auto Year Private Commer ATP Other Total Pilots 2011 195,650 123,900 142,650 36,900 618,660 2010 220,008 139,100 145,464 36,652 627,588 2009 221,619 125,738 144,600 36,566 594,285 2008 222,596 124,746 146,838 35,702 613,746 2006 219,233 117,610 141,935 32,287 597,109 2004 235,994 122,592 142,160 29,977 618,930 2002 245,230 125,920 144,708 29,596 631,762 2000 251,561 121,858 141,596 17,162 625,581 1998 247,226 122,053 134,612 16,366 618,298 1996 254,002 129,187 127,486 16,374 622,261 1994 284,236 138,728 117,434 17,195 654,088 1992 288,078 146,385 115,855 17,857 682,959 1990 299,111 149,666 107,732 17,400 702,659 1988 299,786 143,030 96,968 17,319 694,016 1986 305,736 147,798 87,186 18,125 709,118 1984 320,086 155,929 79,192 17,088 722,376 1982 322,094 165,093 73,471 16,236 733,255 1981 328,562 168,580 70,311 16,817 764,182 1980 357,479 183,442 69,569 16,748 827,071 ``` I’ve already sorted from earliest year to latest. If I highlight all of the cells, the Year becomes a line on the graph. If I highlight all of the cells except the ones in the Year column I get only the data I want, but there is only a default legend on the X-axis. How do I put the years on the X-axis? ---
**Author:** ![vd](https://avatars.discourse-cdn.com/v4/letter/v/b19c9b/32.png) [@vd](https://boards.straightdope.com/u/vd)\ **Post date:** [June 9, 2012, 2:47pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/2 "2012-06-09T14:47:52Z")
Select all of the columns, including Year. Make the chart an “XY (Scatter)” ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [June 9, 2012, 2:55pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/3 "2012-06-09T14:55:39Z")
Excellent! Thanks! ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [June 9, 2012, 3:40pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/4 "2012-06-09T15:40:08Z")
OK, another problem. I want to create a chart (I’m thinking of a column chart, just to be different) to show the historical prices of a Cessna 172. ```auto Year Cessna 172 and Skyhawk 1956 $8,995.00 1960 $9,450.00 1963 $11,590.00 1964 $11,995.00 1967 $13,300.00 1968 $12,750.00 1970 $13,995.00 1972 $14,995.00 1976 $17,890.00 1978 $29,950.00 1982 $33,950.00 1985 $44,000.00 1987 $49,600.00 1998 $124,500.00 2005 $229,750.00 2008 $297,000.00 2009 $297,000.00 2010 $297,000.00 2011 $307,500.00 ``` I highlighted all cells. When I do the column chart, the Y-axis is 0 to 1, descending by 0.1 increments. The X-axis says Cessna 172 and Skyhawk. The title is 2011. To the right, there is a blue box (indicating bar colour) and 2011. A scatter chard yields similar results. 😕 What I want is for the Year to be the X-axis, and the price to be the Y-axis. ---
**Author:** ![vd](https://avatars.discourse-cdn.com/v4/letter/v/b19c9b/32.png) [@vd](https://boards.straightdope.com/u/vd)\ **Post date:** [June 9, 2012, 4:19pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/5 "2012-06-09T16:19:17Z")
Excel can make some weird decisions for charts. Sometimes they are dependent on the order you do things. Did you make the column chart first, then change it to XY? Try starting a new XY chart, then changing it to column. > [@](#): > > Excel graph … Should be simple. How often has \*that \*been said? ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [June 9, 2012, 4:38pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/6 "2012-06-09T16:38:10Z")
The scatter chart worked, but I don’t see how to convert it to a column chart. > [@vd](#): > > How often has \*that \*been said? Ha! Also Access, and Word, and… If I were designing a program, I’d have a wizard that says ‘Select column or row for X-axis’. ‘Select column or row for Y-axis.’ ‘Highlight cells for data.’ ‘Finish.’ ---
**Author:** ![vd](https://avatars.discourse-cdn.com/v4/letter/v/b19c9b/32.png) [@vd](https://boards.straightdope.com/u/vd)\ **Post date:** [June 9, 2012, 4:42pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/7 "2012-06-09T16:42:08Z")
> [@Johnny\_L.A](#): > > The scatter chart worked, but I don’t see how to convert it to a column chart. Select the chart, go to the “Design” tab. The first button should be “Change Chart Type”. (assuming Excel 2007 or 2010) > [@Johnny\_L.A](#): > > If I were designing a program, I’d have a wizard that says ‘Select column or row for X-axis’. ‘Select column or row for Y-axis.’ ‘Highlight cells for data.’ ‘Finish.’ I believe there used to be a program called “Excel” that did this. I wonder why they stopped making it. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [June 9, 2012, 4:51pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/8 "2012-06-09T16:51:50Z")
> [@vd](#): > > Select the chart, go to the “Design” tab. The first button should be “Change Chart Type”. > (assuming Excel 2007 or 2010) OK, that worked. (I’m using 2003 for Macs.) > [@vd](#): > > I believe there used to be a program called “Excel” that did this. I wonder why they stopped making it. Only it doesn’t have that kind of wizard. It seems you have to make the chart first, and then go back and modify it. It would be easier if you could just do it the way I described. (If it does do that, then they should show the user how to do it.) ---
**Author:** ![vd](https://avatars.discourse-cdn.com/v4/letter/v/b19c9b/32.png) [@vd](https://boards.straightdope.com/u/vd)\ **Post date:** [June 9, 2012, 5:00pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/9 "2012-06-09T17:00:25Z")
I meant that I think Excel used to have a wizard similar to what you want, in Office 2000 or so. But I could be remembering wrong. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 1, 2014, 7:11pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/10 "2014-11-01T19:11:04Z")
OK, trying again. See Post #4 for data. I’m using Excel for Mac 2011. I selected the two columns. I clicked on the Chart tab and selected Scatter chart. This gave me the small axis ranges as described earlier. I selected the chart area and swapped the X and Y axes so that Year became the X-axis. Choosing Select data source, I entered the following (Note: The columns and cell numbers are correct for the table I’m using.): X-values =Sheet1!$A$25:$A$43 Y-calues =Sheet1!$C$25:$C$43 I assigned 5,000 to 325,000 for the range of the Y-axis. The X-axis is 1950 to 2020 on its own accord. The graph is blank. What am I doing wrong? 😕 ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [November 1, 2014, 8:04pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/11 "2014-11-01T20:04:44Z")
> [@Johnny\_L.A](#): > > I’ve already sorted from earliest year to latest. If I highlight all of the cells, the Year becomes a line on the graph. If I highlight all of the cells except the ones in the Year column I get only the data I want, but there is only a default legend on the X-axis. > > How do I put the years on the X-axis? If you right-click on the axis, you can select “select data” from the pop-up menu, and then choose your axis label range, which in this case is the left-most column (not including the header). ---
**Author:** ![Peter\_Morris](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/peter_morris/32/359_2.png) [@Peter\_Morris](https://boards.straightdope.com/u/Peter_Morris)\ **Post date:** [November 1, 2014, 8:08pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/12 "2014-11-01T20:08:20Z")
What you’re doing wrong is having dollar signs in the numbers. Do a global replace $ with null. Then highlight the money column. Not the year or the column heading Make a chart from it. Right click on the chart. Select “source data” from the menu Go to series tab Go to Category (x) Axis labels Highlight the years and select. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [November 1, 2014, 8:12pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/13 "2014-11-01T20:12:46Z")
> [@Peter\_Morris](#): > > What you’re doing wrong is having dollar signs in the numbers. Do a global replace $ with null. > > Then highlight the money column. Not the year or the column heading > > Make a chart from it. > > Right click on the chart. > > Select “source data” from the menu > > Go to series tab > > Go to Category (x) Axis labels > > Highlight the years and select. You don’t have to do that. You can format the axis labels as currency, and it will work fine. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 1, 2014, 8:14pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/14 "2014-11-01T20:14:07Z")
> [@Peter\_Morris](#): > > What you’re doing wrong is having dollar signs in the numbers. Do a global replace $ with null. Good god, that’s lame! Excel should be smart enough to figure out it’s a column of numbers. (But then, it should be smart enough to know that when you paste a number containing leading zeros ‘as text’, that you want to keep the leading zeros. :rolleyes: ) Thank you for the answer. I now have a graph. ON PREVIEW: > [@Saintly Loser](#): > > You don’t have to do that. You can format the axis labels as currency, and it will work fine. I tried that earlier, and it was greyed out. . ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [November 1, 2014, 8:17pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/15 "2014-11-01T20:17:06Z")
> [@Johnny\_L.A](#): > > I tried that earlier, and it was greyed out. Strange. I just copied your numbers into an Excel sheet, and it worked fine for me. I’ve got the years on the horizontal axis, and the price range on the vertical axis, with bars reflecting the price for each year. Not sure exactly what you were doing, or why it’s greyed out for you, although I’m using Excel 2010 for Windows. Maybe there’s something different? I’m not at all familiar with the Mac version. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 1, 2014, 8:37pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/16 "2014-11-01T20:37:15Z")
OK, here’s another question. [Here’s the graph](http://s107.photobucket.com/user/llaaksonen/media/Cessna172prices.png.html?sort=3&o=1). [ul][li]How do I get rid of the ‘Chart Area’ boxes?[/li][\*]How do I make the sheet look like [this one](http://s107.photobucket.com/user/llaaksonen/media/Numberofpilots.png.html?sort=3&o=0)?[/ul] ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [November 1, 2014, 8:43pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/17 "2014-11-01T20:43:17Z")
[quote=“Johnny\_L.A, post:16, topic:624620”] OK, here’s another question. [Here’s the graph](http://s107.photobucket.com/user/llaaksonen/media/Cessna172prices.png.html?sort=3&o=1). [ul][li]How do I get rid of the ‘Chart Area’ boxes?[/li][li]How do I make the sheet look like [this one](http://s107.photobucket.com/user/llaaksonen/media/Numberofpilots.png.html?sort=3&o=0)?[/ul][/li][/QUOTE] 1. Right-click on one of the gridlines (I’m guessing that’s what you mean by “boxes”), then “Format Gridlines,” and you’ll have a few options, one of which is “none.” 2. Right-click in the chart (in the plot area), then you’ll have a bunch of options as to fill and color. You’re looking for a medium gray. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 1, 2014, 8:46pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/18 "2014-11-01T20:46:23Z")
> [@Saintly\_Loser](#): > > 1. Right-click on one of the gridlines (I’m guessing that’s what you mean by “boxes”), then “Format Gridlines,” and you’ll have a few options, one of which is “none.” > > 2. Right-click in the chart (in the plot area), then you’ll have a bunch of options as to fill and color. You’re looking for a medium gray. 1. Looks like I just needed to move the cursor for the yellow box with ‘Chart Area’ to go away. 2. What I was getting at was that I want the sheet to look like the other one. That is, no spreadsheet behind the chart. i.e., I just want the chart on the sheet, and nothing else. . ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [November 1, 2014, 8:50pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/19 "2014-11-01T20:50:43Z")
> [@Johnny\_L.A](#): > > 1. Looks like I just needed to move the cursor for the yellow box with ‘Chart Area’ to go away. > > 2. What I was getting at was that I want the sheet to look like the other one. That is, no spreadsheet behind the chart. I think that’s probably a view setting in Excel on your computer, and has nothing to do with the chart. At least that’s how I make my chart look like that. In the View tab, turn off gridlines and headings (probably everything). ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [November 1, 2014, 8:51pm UTC](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620/20 "2014-11-01T20:51:47Z")
> [@Johnny\_L.A](#): > > 1. Looks like I just needed to move the cursor for the yellow box with ‘Chart Area’ to go away Sorry, completely misunderstood what you meant by “boxes.” [Next page](https://boards.straightdope.com/t/excel-graph-need-answer-fast-should-be-simple/624620.md?page=2)