Making a graph in Excel 2008

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.



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.

 

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.

 

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:

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.

 

Thanks for the judgment call on what is idiotic.

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.

 

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.

 

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.

 

Do any of you guys have the problem where after creating a few charts Excel becomes insanely slow?Like if you click "Galley" to open the chart pain it crawls down?

 

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!

 

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.

 

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

 

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.

 

this is officially the stupidest thing microsoft has ever done.

 

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

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.