Setting widths at the plot's left and right (inside the plot area)

**simone77** · 12-05-2012, 07:25 AM

Hi all,

I have tried in the chart subforum but I couldn't get any answer. I have a very simple problem but I can't figure out out to sort it out. I have been googling a bit and I have seen that related questions have been asked and answered here.

I have a plot like this:
https://dl.dropbox.com/u/20806248/54.tif

It is a scatter plot with the first series having in the x axis the 1998, 1999,...,2010 "real" x-values and the second series having the 1998.1, 1999.1,....,2010.1 "fake" x-values. I have done it to get them not overlapping. OK. Up to here fine.

Now, I can't imagine a way to get some space between the plot and the y-axis (at its left) and between the plot and the last year (at its right) without having to show "1997" and "2011".
Is there a way, (a macro?) to define the width you want to appear at the left and at the right of your plot without modifying the visualization of the x-axis?

This is driving me crazy, really I would appreciate a lot any help on this.

**MrShorty** · 12-05-2012, 11:44 AM

Here's how I think I would handle your problem. Unfortunately, I'm not familiar with the "ribbon" in 2007, so I don't know where to find these commands in your newer version of Excel.

1) Scale x axis to desired limits. Locate the dialog that controls the "tick mark labels" for the x axis and tell Excel not to show the tick mark labels.
2) Create a dummy series where the x values are where you want the desired axis labels to be (y for this series should place it on top of the existing x axis).
3) Format this dummy series with no lines or markers and tell Excel to add the x values to this data series as data labels.

**simone77** · 12-05-2012, 06:48 PM

Thank you so much for your suggestions.
They worked, but I have something not yet solved:
I need a way to get the ticks for the "fake" x-axis. I thought of some kind of marker for the dummy variable working as x-axis but the available ones don't do the trick (I need outward ticks - not cross).

Here it is the graph as remains after doing what you suggested:
https://dl.dropbox.com/u/20806248/55.tif

**simone77** · 12-06-2012, 11:41 AM

Given this might be somehow considered as a new more specific question I have opened another thread here:
http://www.excelforum.com/excel-char...22#post3036322

To make things simpler I have added there the excel with the data and the chart as it appears now.

**MrShorty** · 12-06-2012, 11:43 AM

Rather than using a marker for the fake axis series, why not use the tick marks from the actual axis? Granted you will probably end up with an extra unlabeld tick mark at the end of each axis, but it seems like the easiest alternative.

If you don't think that is good enough, the alternative that comes readily to mind is to paste an appropriate character onto the fake axis as the marker. It's not difficult -- see http://peltiertech.com/Excel/ChartsH...omMarkers.html Assuming it still works in your newer version, the only thing I would add to Jon's tutorial is that, you can also use a text box. If you use a text box, you can choose any character from your available character sets. So the process becomes one of:
1) create/format/edit text box/autoshape (if you are unfamiliar with text boxes and how to create, format, and work with them, there will need to be some effort spent on learning how to use them). You will need to fiddle with settings like fill/no fill, border/no border, position of text within box, etc.
2) copy and paste onto data series
3) edit/format text box
4) copy and paste onto data series
5) repeat until the "textbox as marker" looks right.

If you have the patience to go through that process, you should be able to come up with a suitable mock up of an outside tick mark.

**simone77** · 12-06-2012, 12:52 PM

Hi,

Thanks for your answer. I believe that in my case I can't just use the real ticks from the real hidden axis because I want the x-axis to start at 1997.5 and finish at 2010.5 (the real data are in 1998, 1999,...,2010) and this way if I use the real ticks, whatever the minor or major unity I define, there is no way to make appear only the ticks under the year selected.

I have tried the other two solutions (shapes or box text). For some reasons when I paste the shapes over the dummy series Excel crashes (Excel 2007, Windows 7). I have found the perfect

solution with the text boxes with an arial "i" at 5.5 of size and with the inside lower width (it is a literal translation from a Spanish version) set to zero.

I still want to improve something but they are just details and I have an idea on how to do that, here it is as it remains:
https://dl.dropbox.com/u/20806248/chart%20ticks.tif

Thanks a lot

**Andy Pope** · 12-07-2012, 04:47 AM

What about using a custom number format, [<1998]"";[<2011]#,##0;
and changing the shuffle values to +/- 0.2 from the year value.

Or you could use a custom marker on your dummy series.

**simone77** · 12-07-2012, 08:32 AM

Originally Posted by Andy Pope

What about using a custom number format, [<1998]"";[<2011]#,##0;
and changing the shuffle values to +/- 0.2 from the year value.

Or you could use a custom marker on your dummy series.

Great! This works really well and let you use the real ticks. This is the best solution for this kind of problems, I didn't know this syntaxis for a custom number format.
I have slightly modified your attached chart and this is how it is now:
https://dl.dropbox.com/u/20806248/56.tif

Thank you