Gridlines and Borders


Location: http://www.mvps.org/dmcritchie/excel/gridline.htm      
 [View without Frames]
Borders and Gridlines are not the same. 

Gridlines are 1 pixel wide and you can have them visible or not you can have them print or not.  Their main purpose is so that you can see the cell boundaries when working on the worksheet.  You can change the color with Tools, Options, View

A very frequent complaint is loss of gridlines in display or in printing.  Gridlines are lost when
you color a cell, or a border.   This includes changes done with Conditional Formatting.

Borders can have various width, or doubling of lines, and can be colored (format, Cells, borders).  Borders will always print with normal printing.

Printing of gridlines:  (#printing)

Printing of gridlines: Tools, Options, View,
     Gridlines (checked), color: automatic

Cell color and/or Borders wipe out gridlines:
Any color within a cell such as white, or any color borders such as white, will wipe out gridlines.

Draft quality wipes out gridlines and interior color as can be previewed in Print Preview.

To Make sure you do not have color within a cell   (#wipeout)

Cells with color override gridlines, to remove:  Select All cells (Ctrl+A), Format, Patterns (tab), cells color shading (none)

To Make sure you do not have color borders

Borders override gridlines, to remove:  Select All cells (Ctrl+A), Format, Borders (tab), Borders (none), Color (automatic)

Turn off Draft quality to see gridlines (#draft) Your printer and your print drivers may have "Draft Quality", but Excel itself has a draft quality option:
  Page Setup, Sheet (tab), (uncheck) Draft Quality option

Display of gridlines:  (#display)

Display of gridlines: File, Page Setup, Sheet
     Gridlines (checked)

Supress gridlines with VBA   (#suppress)

Activewindow.displaygridlines=false

Toggle gridlines with VBA   (#toggle)

 Sub Gridlines() 
 ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
 End Sub
You can create put your macro on a toolbar button smiley Gridlines.  [2003-08-29],  later in the same thread
Dave Peterson identified builtin button:  Tooggle Grid Tools|customize|commands Tab|Forms category (near the bottom),
so you don't need the macro after all.

Formatting of interior or background color: (#pattern)

Formatting of interior (background color) will suppress affected gridlines.:
     Select all cells, format, cells, patterns, Cell shading: no color
        (white interior color will wipe out adjacent gridlines, but not borders)
    When using Interior color in Conditional Formatting consider also including borders.

Formatting of borders:   (#format)

Formatting of borders will override gridlines:

Something thinner than gridlines.  You can't chose something thinner, but you can choose a dotted border, start as above.

Setting gridlines for all sheets (#allgridlines)

To set on by default for new sheets you would need to setup your book.xlt and sheet.xlt templates.

For existing sheets: 

WARNING:  (severe tire damage) you Must ungroup sheets when finished.  Failure to do so means that any change you make such as to cell content will be done for all grouped sheets and will destroy preexisting data.  Any change made to one cell will be done to all sheets in the grouping.

Setting borders for all cells (#allborders)

If you want to put borders on all cells in your current (activesheet) you would select all cells (ctrl+A, except in Excel 2003) then format, cells, borders, choose color, choose each border.  For most border formatting the borders toolbar button will work just fine.

Draw Borders was added in Excel 2002, it does what you would expect it to.  If you do not have the borders toolbar button on your toolbar, you can add it from tools, customize, commands, format.

A flash demonstration of formatting cells is one of many presentations by Mike Alexander at his DataPig Technologies.com - Computer Training and Consulting page or go direct to the Formatting Tricks (flash presentation)     [possible trouble viewing].  These videos remain free but site is becoming a membership site.

If you want to do the same for all sheets in the workbook then right click on the worksheet tab, select all sheets.  With the sheets now grouped any change to the visible sheet will update all of the group worksheets with the same keystrokes.  then as before: 
select all cells (Ctrl+A) then format, cells, borders, choose color, choose each border.

Must ungroup the sheets as any change to the visible worksheet will also be done to the other sheets in the group, and would destroy the content of your workbook very quickly.
  right click on worksheet tab, UNGROUP sheets

If you want to make this so that all future workbooks have borders to start with you would create a  book.xlt  in your XLSTART directory and if you want to add any more sheets to any existing or future workbooks you would create a sheet.xlt worksheet as your default. 
See Templates, book.xlt, sheet.xlt, and beyond

Accessibility Option   (#accessible)

Don't think accessibility options will affect gridlines, but included here anyway.  Accessibility Options would affect all Microsoft Office applicaitons, not just one worksheet.

MSKB 320531 -- OFF: Changes to Fill Color and Fill Pattern Are Not Displayed
Incorrect/missing colors may occur with use of High Contrast under Display tab of Accessibility Options.  Colors don't show on monitor.

When you try to change the fill color or the fill pattern, the changes that you selected may not be visible; however, you may be able to see the changes in print preview and on a printed page.

Control Panel, Accessibility Options. Display tab, clear the Use High Contrast check box, OK.


This page was introduced on June 17, 2000. 

 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved