Excel graph -- Need answer fast. Should be simple.

Factual Questions
1

I want to make a line graph of the following data:


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?

2

Select all of the columns, including Year.
Make the chart an “XY (Scatter)”

3

Excellent! Thanks!

4

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.


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.

:confused:

What I want is for the Year to be the X-axis, and the price to be the Y-axis.

5

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.

How often has *that *been said?

6

The scatter chart worked, but I don’t see how to convert it to a column chart.

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.’

7

Select the chart, go to the “Design” tab. The first button should be “Change Chart Type”.
(assuming Excel 2007 or 2010)

I believe there used to be a program called “Excel” that did this. I wonder why they stopped making it.

8

OK, that worked. (I’m using 2003 for Macs.)

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.)

9

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.

10

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? :confused:

11

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).

12

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.

13

You don’t have to do that. You can format the axis labels as currency, and it will work fine.

14

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:

I tried that earlier, and it was greyed out.

.

15

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.

16

OK, here’s another question.

Here’s the graph.
[ul][li]How do I get rid of the ‘Chart Area’ boxes?[/li][*]How do I make the sheet look like this one?[/ul]

17

[quote=“Johnny_L.A, post:16, topic:624620”]

OK, here’s another question.

Here’s the graph.
[ul][li]How do I get rid of the ‘Chart Area’ boxes?[/li][li]How do I make the sheet look like this one?[/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.

18

  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.

.

19

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).

20

Sorry, completely misunderstood what you meant by “boxes.”