Chart with Y-axis on the top?

[KarenYT]

Please see attached chart.
I want to put the Y-Axis on the top instead of placing on the right-hand side of the chart ?
I have to put to use this Bar-type per our office's request and I have been trying to look for how to place the Y-axis on the top but no luck.

I would appreciate if you could help please.

Karen

[HansV]

Does this do what you want? I had to recreate the chart from scratch.

chart1.xlsx

[HansV]

I forgot to mention that I had to change the values in column A from "Week 19", "Week 20" etc. to 19, 20, ... to accommodate the XY scatter charts. I then applied the custom format "Week "0 to make the values display as they originally were, while the underlying values are numbers.

[KarenYT]

Yes !!! Hans.
Would you please let me know how ?
I need to learn that. I did this when I was using 2003, with this 2007, I got lost.

[HansV]

It's rather tricky - mixing bar charts and XY scatter charts is not natural to Excel because the axes are differently orientated.

I started by creating a bar chart based on columns A:C (I had to add column D later on).
I selected the LinesPerHour series, and changed it to an XY Scatter chart.
The X- and Y-axes of the scatter chart were the wrong way round, so on the Design tab of the ribbon, I clicked Select Data.
I then selected the LinesPerHour series and clicked Edit.
I switched the ranges used for the X- and Y-values:

S0077.png

After clicking OK, the series looked right, but the scale was wrong, so I adjusted the scale of the secondary axis.
Finally, I added the AVG series. It automatically used the same settings as the LinesPerHour series.

[KarenYT]

Really does sound complicated. I will do some practices on it. I wouldn't think of XY Scatter chart would help in this instance at all !
Again Thanks for your good explanation as always !

[HansV]

We can't use a line chart here because the y-axis of a line chart is ALWAYS vertical. The only chart type in which horizontal and vertical axis are interchangeable is the XY scatter chart.

[KarenYT]

Thank you !!!

Karen

[KarenYT]

Hans,

I am sorry I have to come back to you on the same subject.
Please see attached chart that I had followed your instructions, no matter how I tried it, I couldn't get the lines shown on the chart ???

What did I missed.

Please help.
Thanks
Karen

[HansV]

In your worksheet, A2:A35 still contain the text values "Week19" ... "Week 52". As I mentioned in my second reply in this thread, you have to change this to the numeric values 19 ... 52. A scatter chart only works correctly with numeric values. To display "Week 19" etc, apply the custom number format "Week "0 to A2:A35.
The primary vertical axis then displays 19 ... 52, but you can tell it to link to the number format of the source range:

S0079.png

The AVG series currently uses =Sheet2!$D$14:$D$35 as X-values. That should be =Sheet2!$D$2:$D$35.

See the attached version.

chart.xlsx

PS Your Lines per Day series currently has a shadow. In my opinion, that makes the chart less clear, but it's up to you to decide about it.

[KarenYT]

Hans,
I did follow all from your instructions. The Chart I sent might have been messed around after so many trials without success.
Now I tried again, I even compared with the one you corrected and I couldn't see any difference, I still couldn't get what's supposed to be ?? I am really sorry to (again) raise the same question...excuse my stupidity !

Please point out to me (again) what's wrong I have done on the attached chart ?

Appreciate your patience to me,
Karen

[HansV]

The range A2:A35 in your workbook contains the text values "Week 19" etc. You have to remove these values and replace them with the numbers 19, 20, etc.
For example:

- Select A2:A35.
- Press Ctrl+H to activate the Replace dialog.
- Enter the word Week followed by a space in the 'Find what' box.
- Leave the 'Replace with' box empty.
- Click 'Replace All'.
- Click OK to confirm the replacement, then click Close to close the Replace dialog.

At this point, the two scatter chart series will be displayed, but you'll see the numbers 19, 20, etc. in A2:A35 and in the primary vertical axis. To correct this:

- With A2:A35 still selected, press Ctrl+1 to activate the Format Cells dialog.
- If necessary, activate the Number tab.
- Select Custom in the Category list.
- Enter "Week "0 in the Type box.
- Click OK.

[KarenYT]

I was completely concentrating on the changing of the Chart itself without thinking about the Excel table itself.
Thanks very much, Hans.
Appreciate very much for your patience and detailed elaboration ! I finally (completely) understand how scatter chart works !

Best regards,
Karen