Dashed Gridline

**pg300** · 11-02-2011, 05:16 AM

Hi all please see the attached excel chart. I got it from here:
http://www.databison.com/index.php/b...roup-in-chart/

Link to excel sheet is at bottom of that page Ill post here as well: http://www.databison.com/wp-content/...p-in-chart.xls

I'm trying to get the same gridline look. When I download his excel file and check the line style on his gridline it says "No Line" and I'm so confused, how did he get this look on his gridlines?

Also: if you look at his chart in step 5 and compare it to step 6. In step 5 chart, if you select the bars then select one bar and change the fill, then there is like 2px overflow of the bar on the x-axis. But if you change the fill in his step 6 chart there is no overflow. I can't figure this one out either, please help.

Thanks!

**teylyn** · 11-02-2011, 07:13 AM

Hello,

the chart uses a pattern fill for the grid line. Pattern fills are available in Excel 2003, but no longer in later versions.

If you can get your hands on Excel 2003 or earlier, you should be able to re-create the effect.

cheers,

**teylyn** · 11-02-2011, 07:16 AM

I can't find any step numbers in the article. Can you pinpoint this more exactly? Again, it may be due to the change of the charting engine with Excel 2007. A LOT has changed with that version.

**Andy Pope** · 11-02-2011, 07:51 AM

in xl2007 you can set the gridline to solid and pick your colour.
On the line style tab change the dash type to Round or square dot.

try setting the zoom to 100%. Do you still get the overflow?

Not sure I like the fact that the base line for the column and area chart is a negative value.

**pg300** · 11-02-2011, 08:10 AM

Thanks so much. Is there some other way to apply that pattern fill? Because if I copy paste the chart to '07 it looks fine.

Oh sorry the steps are in the xls document.

Thanks Andy! I actually see the overflow only at 100% it doesn't show on others, that's weird do you know how I can fix that?
Can you please show me how i can set the base line to 0. I've been trying that and haven't been able to get it.

**Andy Pope** · 11-02-2011, 08:13 AM

ignore the pattern fill approach, unless you have xl2003. Instead use the dashed gridlines suggested.

I don't see any overflow at 100%

Format Y axis to have Automatic cross at point.
Format X axis labels to be Low rather than Next to axis.

**pg300** · 11-02-2011, 08:30 AM

thanks andy and teylyn again!

I'll try to set it to 0 on the axis right now thanks!

Edit:
So I tried out the 0 technique but I still have "-" as my cross point and I have overflow at 100% can you please see my excel file.

**Andy Pope** · 11-02-2011, 09:03 AM

The '-' instead of zero is your number format.

Your data set does not have negative values so do not worry about axis crossing.

Add a border to the columns

**pg300** · 11-02-2011, 09:44 AM

Originally Posted by teylyn

Hello,

the chart uses a pattern fill for the grid line. Pattern fills are available in Excel 2003, but no longer in later versions.

If you can get your hands on Excel 2003 or earlier, you should be able to re-create the effect.

cheers,

Originally Posted by teylyn

I can't find any step numbers in the article. Can you pinpoint this more exactly? Again, it may be due to the change of the charting engine with Excel 2007. A LOT has changed with that version.

Originally Posted by Andy Pope

in xl2007 you can set the gridline to solid and pick your colour.
On the line style tab change the dash type to Round or square dot.

try setting the zoom to 100%. Do you still get the overflow?

Not sure I like the fact that the base line for the column and area chart is a negative value.

Originally Posted by Andy Pope

The '-' instead of zero is your number format.

Your data set does not have negative values so do not worry about axis crossing.

Add a border to the columns

Thanks again Andy. But I'm not able to get rid of that -. Even a "-" gets plotted in the data labels.
Is it because the array formulas can't be a blank? If i delete the array formulas for the blank cells in "Monthly Average Profit" the data labels don't show "-", it just shows on the one wehre there is a number.

But I still get the negative "-" instead of 0 at bottom left.

**Andy Pope** · 11-02-2011, 09:48 AM

Number format for axis and cells,

_(* #,##0_);_(* (#,##0);0;_(@_)

instead of the current one, where zero is displayed as -
_(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

**pg300** · 11-02-2011, 10:41 AM

Originally Posted by Andy Pope

Number format for axis and cells,

_(* #,##0_);_(* (#,##0);0;_(@_)

instead of the current one, where zero is displayed as -
_(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

Oh wow! Thanks so much! I'll go learn about custom formats now.

Dashed Gridline

LinkBack

Thread Tools

Rate This Thread

Display

Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Re: Dashed Gridline

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions