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: 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 bordersBorders 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: File, Page Setup, Sheet
Supress gridlines with VBA (#suppress)Activewindow.displaygridlines=false
Toggle gridlines with VBA (#toggle)Sub Gridlines() <! keep the macro for documentation though> ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines End SubYou can create put your macro on a toolbar button . [2003-08-29], later in the same thread
Dave Peterson identified builtin button: 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:
- Select all cells (ctrl+A), or a range of cells,
- Format, Cells, Borders
- automatice=black, if ou choose white you won't see them but they will override gridlines.
- Then coose the inside and outside borders.
Something thinner than gridlines. You can't chose something thinner, but you can choose a dotted border, start as above.
- choose dotted border, and the color, then
- Choose inside and outside borders last, then click on okay.
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.
- Select all sheets by rightclick on sheet tab, Select all sheets
- Tools, options, view, gridlines
- click on sheet tab, Ungroup sheets
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
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.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved