Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Making a graph in Excel 2008

Y

yg17

macrumors Pentium
Original poster
Aug 1, 2004
15,030
3,009
St. Louis, MO
This seems like something that should be so bloody simple but isn't.

I need to make a very, very simple line graph. In column A, I have a list of years. In column B, I have the data....numbers that correspond to something in that year. Simple, right? It really couldn't be any less complex. I selected both columns, then I went to create a new line graph, and it created a graph that had the years (from 0-2500 even though the actual range is 1973-2005) on the Y axis, and the data on the X axis. And each column of data was its own series, so I had 2 lines, which, due to the scales, was basically 2 horizontal lines. I removed the extra series (the year series), and was able to get the line to look right. It also put the column B data on the Y axis like I want, but on the X axis, it just counts from 1-33. But I want the years on the X axis. How the hell do I do this? I'm 99% positive Excel 2004 let you set the X axis labels.....did they really remove it in 2008?

This seems like something that should be so simple, I shouldn't have to post on a forum to figure out....anyone have any ideas? Thanks
 
MS really messed up charting in Excel08. Yes, you could do it in '04, but they've removed a LOT of the functionality in '08. The only way I was able to do what you are trying to do is to open a file created in '04 that has things charted similar to how I want it and change the data.

The charting limitations are what made me uninstall Office08 and move back to Office04.

There's hope that they'll fix it, but I'm not holding my breath. They rushed this product to market and have left out far too much. It's probably best (for me, at least) to wait for Office2010 (or whatever it will be) to see if they release a usable product.
 
As a possible work around, use another column next to the dates and use that as the x-axis.

If the dates are starting in B2, then in A2, put this formula for month/year:

=TEXT(B2,"mmm-yyyy")

or put this formula for month/day:

=TEXT(B2,"m/d")

or put this formula for month/day/year:

=TEXT(B2,"m/d/yyyy")

and copy down.

I found this handy even when Excel (2000/2002/2003 on Windows and 2004 on Mac) solves many other issues of formatting, etc.

Then, to make it look better, change the alignment (it is automatically set to 0) by setting it to 1, then back to 0 (to eliminate the auto format angle). Now the x-axis will always be horizontal. To help even more select the graph, and choose Format Object, and on the Fonts tab, uncheck the Autoscale for fonts, and set font to size 8. That way no matter how much you resize the chart, the fonts will always be consistent and the x-axis will always be horizontal.
 
Do you want something that looks like this?
I'm on Office 2007 on my PC at work, but I bet it's roughly the same as 2008.
It was certainly non-obvious to get this looking right.

greenshot_2008-02-18_09-52-39.png

When I selected the two columns of data and chose line plot, it initially gave me two lines like you described. To fix it, I did the following.
1. Remove Series1 (the year data)
2. With Series2 selected, click on the edit button under Horizontal (Category) Axis Labels
3. Select the year data again from your worksheet

That ought to do it. PITA.
 
Yep, that's exactly what I want.


I did manage to get what I needed in Numbers, but I can't say that was intuitive either.
 
The reason it is no-intuitive is that Excel assumed the years were actually numbers, not dates. However, if they were entered as 1/1/1997 and 1/1/1998, etc. then Excel would properly recognize them as dates and graph it accordingly.
 
exegete77 said:
The reason it is no-intuitive is that Excel assumed the years were actually numbers, not dates. However, if they were entered as 1/1/1997 and 1/1/1998, etc. then Excel would properly recognize them as dates and graph it accordingly.
Click to expand...

This is an idiotic way to handle plotting, IMO. It shouldn't matter what format it's in, since I'm just as likely (or more likely) to want to plot one column of data as a function of another, rather than plot them independently.
 
I was struggling with this last week. Here's what I found:

  1. Click on your chart.
  2. Click the Layout tab under Chart Tools.
  3. Click Axis Titles in the Labels group.
  4. Point to Primary Horizontal Axis Title and select Title Below Axis.
  5. Click in the formula bar and enter = (the equals sign).
  6. Click the cell in your worksheet that contains the label Property and then press [Enter].
  7. Click Axis Titles in the Labels group.
  8. Point to Primary Vertical Axis Title and select Vertical Title.
  9. Click in the formula bar and enter = (the equals sign).
  10. Click the cell in your worksheet that contains the label List Price and then press [Enter].
You can now select the axis titles and format them any way you wish.
Click to expand...

Microsoft really dumbed down this feature! I'm thinking of downgrading or going the Open-Office/Neo-Office road. I can't understand why they did that at all.
 
gauchogolfer said:
This is an idiotic way to handle plotting, IMO. It shouldn't matter what format it's in, since I'm just as likely (or more likely) to want to plot one column of data as a function of another, rather than plot them independently.
Click to expand...

Thanks for the judgment call on what is idiotic. :rolleyes:

So inquiring minds want to know... regarding post #3 and #6

I have been working with Excel 2003 for several years in a Fortune 50 company (60,000+) making fully automated charts (700 in one project alone, all linked to pics in PPT for distribution), all tables and charts are automated, 13 week rolling data, pulling data from the mainframe. Once the data is pulled, I change one cell on each workbook (to support 20-30 worksheets and charts), and it is done.

All axes are setup exactly this way that I have described, because it avoids any kind of MS-trying-to-force-me-into-their-mold kind of setup. I haven't touched most of those charts in 18 months, even though I update the data weekly (and could do it daily, if necessary). I don't have time to reconstruct them every week, nor do I have time to fine tune them every time we have data updates "because Excel developers think they know better than me what is needed". This approach gives me exactly what is needed without any variation thrown at me by Excel.

Total time for the 700 chart project? 15-20 minutes, most of which is waiting for the data from the mainframe. I haven't told you all the details and other time savers involved, but you should get the idea. My previous Director did just a small portion of this one project, and she used to spend 10-15 hours per week, just organizing data and what 4-5 others were doing with their data/charts (each one spending 5-10 hours per week). And yes, they were all convinced that they knew better, until they saw this approach in action. She wasn't sure about my approach, but wanted me to relieve her of that 10 hours/week portion. The first week after automating her data and charts plus what the 4-5 others did, the TOTAL time (data pull to updating linked files into PPT) was 15 minutes.

Another project involves rolling 53 week views of the same data. Again, same amount of time to accomplish.

In fact, this approach has dramatically changed what I report; so much so that my VP and Director wanted the entire department of 1500+ people to change to this method. So I have been commissioned to train the reporting analysts in the department.

So, perhaps before making an assessment of what is appropriate or idiotic, you might want to find out a little bit more about what someone proposes and the reasons behind the suggestions.
 
exegete77 said:
Thanks for the judgment call on what is idiotic. :rolleyes:

So inquiring minds want to know... regarding post #3 and #6

--SNIP Self-serving Rant--

So, perhaps before making an assessment of what is appropriate or idiotic, you might want to find out a little bit more about what someone proposes and the reasons behind the suggestions.
Click to expand...

His comment was not referring to what you said as being idiotic. It is in regard to MS's exclusion of the method that has worked for numerous versions to create these types of graphs. The indicated new method does NOT work for every instance or is excessively cumbersome when compared with the old way of doing things. That MS decided to leave this ability out, is in fact idiotic.
 
Okay, that makes sense. The referent in the comment seemed to be my approach, not MS. But now that you explain it, it can be read that way. Sorry about the rant. :)

However, previous versions of Excel did have similar problems, and that was why I went the route I did.
 
exegete77 said:
Okay, that makes sense. The referent in the comment seemed to be my approach, not MS. But now that you explain it, it can be read that way. Sorry about the rant. :)

However, previous versions of Excel did have similar problems, and that was why I went the route I did.
Click to expand...

As has been pointed out, my comment was directed at Excel, not you. Your approach seems fine, it's just frustrating that people have to go to such lengths to get their work done. That is all.
 
How to change category name of the data on a graph?

Hi everyone,

I'm having trouble changing the category name of the data. I'm doing something extremely simple: imagine you have a column graph, there are 3 vertical bars that belong to dogs, cats and fish respectively. In Excel 2008, it automatically assigns the categories as '1-3' and I can't find a way to change the category name to dogs, cats and fish. I searched for the help section. It points me to the 'ledger sheet' under the 'formatting palette' saying that there is a section called 'My category' where i can change it. But I never find this 'ledger sheet' no matter what I click. Any thoughts? Any help will be greatly appreciated. thanks!
 
I don't have XL 2008, but I assume it is similar to earlier versions. Click on the Chart then CTRL -Click to choose Source Data. On the Series tab, for the category axis, click/point to the range of cells that have the category names. Click OK
 
An easy solution

Hi there,

I went through all the responses to this query and tried a bunch of suggestions, without success (some of them I couldn't make sense of simply because I couldn't find what the respondent was referring to).

I assume your ran into this difficulty because, like me, you set up your data table like this:

year data
1987 11
1988 8
1989 6
1990 8
etc, etc, etc

BUT, the solution is as simple as removing the "year" header:

Still select the empty box in the header row when you make your graph, but that's all there is to it.

It took MS tech support over 1.5 hours to figure this out.

Good luck!
 
Man, I'm glad I'm not the only one who has had a turkey of a time using charts in Excel '08. It took me about an hour to make a simple line graph with dates on the X axis and dollar amounts on the Y axis. I still don't have the shading and fill colors right, but I'm quitting while I have a working chart.
 
Thank you LAJ!

I *knew* it should be easy to get the years to act like years. I followed LAJ's suggestion, just left the year cell empty, and it worked fine.

As for the rest of the formatting, I'm having so much trouble it's easier to make changes in Illustrator.

BTW, I used to be able to copy and paste into InDesign or Illustrator, and that's gone with 2008 too.

I now export charts as PDFs and open them in Illustrator. Very annoying workaround.

Here are the figures: PROBLEM..... and PROBLEM SOLVED. Thanks LAJ!
 

Attachments

  • problem.jpg
    problem.jpg
    174.3 KB · Views: 444
  • problem solved.jpg
    problem solved.jpg
    196.3 KB · Views: 446
I'm just going to throw my two-cents in. I think it's retarded to make a graphing software that doesn't allow you to put a title on an axis.

Retarded is the only word.

I wish there was somebody at Microsoft whom I can directly blame for this, and yell at him.


I'm sorry to say it, but Microsoft Office on Windows is miles ahead of its Mac equivalent.
 
epheterson said:
I think it's retarded to make a graphing software that doesn't allow you to put a title on an axis.
Click to expand...

You can put a title on an axis. Select your chart, then select "chart options" in the Formatting Palette. Select the axis, and enter your title. There's a drop-down menu there with plenty of other options for giving titles to things, so make sure you take a look at that to see if that's what you want.

If you search the Excel help for "axis title", one of the search results is "format a chart", which has helpful screen captures to show you exactly where to look in the Formatting Palette.

Regards,
Nadyne.
 
Whew (after an aggravating experience)

nadyne said:
You can put a title on an axis. Select your chart, then select "chart options" in the Formatting Palette. Select the axis, and enter your title. There's a drop-down menu there with plenty of other options for giving titles to things, so make sure you take a look at that to see if that's what you want.

If you search the Excel help for "axis title", one of the search results is "format a chart", which has helpful screen captures to show you exactly where to look in the Formatting Palette.

Regards,
Nadyne.
Click to expand...

Nadyne, thanks. I have spent the last hour mucking around with Office 2008. The answer--once you point it out--is easy. I might add that I did not find anything in Help.
 
laup said:
Nadyne, thanks. I have spent the last hour mucking around with Office 2008. The answer--once you point it out--is easy. I might add that I did not find anything in Help.
Click to expand...

If you happen to find that our help isn't helpful, please tell us what would better help you out. At the bottom of every help page in Office 2008, there's a question asking whether this topic helped you. When you click on an answer, you can give more information about it.

Your comments are sent directly to our help team, and they monitor those comments to see what kinds of changes that they can make to improve the help. We're continually publishing new and updated help for Office 2008, as well as determining what changes we need to make to the help for the next version of Office, so this is hugely useful to us.

Regards,
Nadyne.
 
Thanks, but here's another thing, Nadyne....

nadyne said:
You can put a title on an axis. Select your chart, then select "chart options" in the Formatting Palette.
Click to expand...

Ah, so THAT is where it has gone. It used to be in Chart Options, under the Chart menu. I looked everywhere - except in the Formatting Palette, which I routinely turn off because it is so huge. You can't see it if it isn't there!

When you do know, it is easy, but finding it was not easy, and I didn't find a reference to the Formatting Palette in Help (it just told me to select the object I want to edit... which is tricky if it isn't there).

It would be nice to put Chart Options back in its old (sensible) place, and (here is my 'another thing') also to allow keyboard selection of items in dialogs (like Excel2004 used to do, and Word 2008 still does).

I paste special > values a lot, and so have remapped Shift-Alt-S to Paste special. In Excel2004 I could then hit V and return. Now I have to mouse. Is a hidden way of re-enabling this that I've not found yet....?
 
Check out Omnigraph Sketcher....

http://www.omnigroup.com/applications/omnigraphsketcher/

It gives you more control over the graphs you make. You can literally sketch the axis and add the points to the graph manually, if you want. Or you can import data from Excel, make the graph, and then manually edit.

This is also great if you want to make a sketch for a presentation that shows a theoretical curve, rather than based on actual data.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back