creating a chart

[cmar6]

I have a column a with dates which I want to be the X-axis and column ab which should be the data. Chart shows Source Data as Series a1924:a2130
Name= "a1924:a2130"
Values = {1}
Category (X) axis labels = {"a1924:a2130}

Chart has correct dates but at top, instead of bottom horizontal axis. I cannot get data series to be plotted (col. ab) into chart.

Any recommendations?
Thanks, CMA

[Huron]

Click on the icon to the right of the Values={1} in the Source Data - Series Menu. This will take you to your data. Highlight the cells you want to use in column AB and press enter. They should now appear on the chart. Repeat this for the Name.

[cmar6]

I had to type in ab1924:2130 since I was not taken to any data rows. But I got an error message, so I changed my input to {"ab1924:ab2130"}, which was accepted. However, the only "data" appearing on the chart are the dates (a1924:a2130) wrong place--at the top instead of at the bottom.
Since this method does not work, is there a way to use a Wizard?

[teylyn]

Hi, Could you maybe post a small data sample so we can have a look at what you're doing? Remote troubleshooting charts is a bit difficult because so much depends on the data layout.

Copy a few lines of data into a new sheet, try to create the chart off this data and attach that file here. You can upload a file by clicking "Go Advanced" below and then the paper clip icon.

if you file is much bigger than 100kb, please zip it before uploading.

cheers

[cmar6]

Hi, Could you maybe post a small data sample so we can have a look at what you're doing? Remote troubleshooting charts is a bit difficult because so much depends on the data layout.

Copy a few lines of data into a new sheet, try to create the chart off this data and attach that file here. You can upload a file by clicking "Go Advanced" below and then the paper clip icon.

if you file is much bigger than 100kb, please zip it before uploading.



cheers

I deleted everything irrelevant to get the size down to 147KB, in which the key columns are A: and C:
I can't find how to zip a file in Win XP. Where is the "Go Advanced" button?
Thanks, CMA

PS: I found something called "Manage Attachments" for uploads. This allows 1000 KB. Do you think Telyn meant 1000KB, not 100 KB?

[Andy Pope]

Info on attachments
http://www.excelforum.com/faq.php?fa...b3_attachments

[cmar6]

Thanks, Andy, but this does tell how to find "Go Advanced" which is what I need to upload.
"You can upload a file by clicking "Go Advanced" below a"

[Andy Pope]

The "Go Advanced" button is displayed if you where to Edit an existing post.

Just create a new post within the thread and use the Manage Attachments button.

[cmar6]

I found the "Go Advanced" button. The key is to edit, as you say, not reply.
I also found the paperclip icon. It does the same thing as "Manage Attachments".
But can I upload 147K .xls file. I was given the 100K limit but the "Manage Attachments" dialog says 1000K?

[Andy Pope]

Have you actual tried to attach the 147kb file?

[teylyn]

The Go Advanced button shows when your are reading a post. it sits directly underneath the Quick Reply box. You cannot attach a file using the quick reply box, so you need to click Go Advanced to open the bells and whistles version. Yes, Go Advanced takes you to the same screen as the Reply button.

The 100kb limit is my own, not the forum's. I'm on a data plan and if attachments are much bigger than 100k, I normally won't open them. Looking at dozens of posts a day I just can't afford too many half MB files

Sorry if that was confusing for you. On the good side, you've caught the attention of the charting master Andy, so rest assured that a solution to your issue will be forthcoming as soon as the file is up.

cheers

[cmar6]

Thank you Teylyn, I didn't know if I was allowed to upload a 147K file, which I will do now for the charting master.

[teylyn]

OK, as far as I understand your problem you want the x axis to appear at the bottom of the chart. To do that, double click the Y axis to open the Axis format dialog, click the Scale tab and enter -26000 in the field for "Category (X) axis crosses at"

Next, you want to add a data series for column B. To do that, click the chart, then

- on the chart menu click Source Data
- click the series tab
- click Add to add a new series
- in the Values field enter

=TechInd!$B$1924:$B$2126

click OK

Now you still don't see the percentages. You now need to put the new series on the secondary Y axis.
- activate the Charting toolbar.
- click the dropdown box for the Chart Objects and select Series 2
- click Format - Selected data series
- click the Axis tab
- select Secondary Axis and click OK

the rest is just formatting. Can I leave you with that?

cheers

[cmar6]

Teylyn:
Superb job! You really know your Excel charting. My only guide to Excel is "MS Excel 2000 Formulas" by John Walkenbach.
After enough time and blundering around, I might have figured out "click Scale tabe and enter -26000 in field for 'Category (X) axis crosses at".
It is odd that Excel defaults to putting the X-axis scale at the top of the chart so that one must know the trick you explained.
I do know how to add a 2nd data series. However, never in 1000 years would I have figured out to make it show up on the chart with its own scale on the right-Y-axis. Excel is very tricky unless one is an expert like you.
BTW, would you recommend I stick with Walkenbach's book or is there another one for Excel 2003 (or should I wait till I get Excel 2007, if it is much improved)?

[cmar6]

One final refinement. This solution requires constantly changing the range of both series and the date series in the chart, as I add more data each day.
Is there a way to use named ranges so that the chart automatically picks up new data in the series each day?

[teylyn]

It is odd that Excel defaults to putting the X-axis scale at the top of the chart so that one must know the trick you explained.

Excel does not do that, it just appears this way. By default, the X axis crosses the Y axis at 0, and since all your data points are -0, ....

BTW, would you recommend I stick with Walkenbach's book or ...

no opinion, I don't know the book. But some recommendations for charting resources:

www.peltiertech.com
www.tushar-mehta.com
and our own valued mod www.andypope.info

This solution requires constantly changing the range of both series and the date series in the chart...

You can create dynamic range names for the data that is to be charted and refer the range names in the data series instead of absolute cell addresses.
Read how to do that here http://peltiertech.com/Excel/Charts/Dynamics.html

If you find that your y axis also frequently changes values, i.e that -26000 is not always the point where the X axis should cross, you need to involve some VBA to set the crossing point with the click of a button instead of manually manipulating the axis every time the data changes.

Read how to do that here: http://peltiertech.com/Excel/Charts/...nkToSheet.html

hth

[scaffdog845]

cmar6
Take a look at this http://blog.contextures.com/archives...arts-in-excel/