Unwanted default dark cell borders - new sheet or clearing all formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2482494%22%20slang%3D%22en-US%22%3EUnwanted%20default%20dark%20cell%20borders%20-%20new%20sheet%20or%20clearing%20all%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2482494%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20bunch%20of%20Excel%20files%20that%20were%20created%20with%2097-2003%20as%20an%20xls.%20When%20a%20new%20sheet%20is%20added%20all%20cells%20have%20black%20borders.%20Also%20if%20the%20cell%20formatting%20is%20cleared%2C%20using%20Editing%20%26gt%3B%26gt%3B%20Clear%20%26gt%3B%26gt%3B%20Clear%20All%2C%20the%20cell%20borders%20are%20black.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20change%20to%20an%20xlsx%20but%20I%20still%20get%20the%20black%20borders.%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20no%20scripting%20in%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20has%20something%20to%20do%20with%20default%20cell%20formatting%20but%20have%20yet%20to%20find%20an%20existing%20solution%20on%20the%20web.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20Office%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20creating%20a%20new%20sheet%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dfugate_0-1624557702632.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291002i245A89607A393573%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dfugate_0-1624557702632.png%22%20alt%3D%22dfugate_0-1624557702632.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20clearing%20any%20existing%20formatting%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dfugate_1-1624557763674.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291003iB454B55BA032E3C0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dfugate_1-1624557763674.png%22%20alt%3D%22dfugate_1-1624557763674.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%20all%20cells%20were%20selected%20and%20the%20borders%20were%20set%20to%20%22no%20borders%22.%20Then%20a%20range%20was%20selected%20and%20the%20formatting%20was%20cleared%2C%20now%20black%20borders%20show.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dfugate_2-1624557931526.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291005i8823266475115DD8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dfugate_2-1624557931526.png%22%20alt%3D%22dfugate_2-1624557931526.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20on%20how%20to%20get%20the%20formatting%20back%20to%20factory%20defaults%20so%20when%20a%20sheet%20is%20added%20or%20a%20cell%20formatting%20is%20cleared%20it%20reverts%20to%20the%20Excel%20default%20and%20not%20these%20black%20borders%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2482494%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2483007%22%20slang%3D%22en-US%22%3ERe%3A%20Unwanted%20default%20dark%20cell%20borders%20-%20new%20sheet%20or%20clearing%20all%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2483007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087358%22%20target%3D%22_blank%22%3E%40dfugate%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EQuit%20Excel.%3C%2FP%3E%0A%3CP%3EIn%20File%20Explorer%20(assuming%20you're%20on%20Windows)%2C%20enter%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%25appdata%25%5CMicrosoft%5CExcel%5CXLSTART%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ein%20the%20address%20bar%20and%20press%20Enter.%20If%20you%20see%20Excel%20templates%20named%20Book.xltx%20and%2For%20Sheet.xltx%20in%20the%20XLSTART%20folder%2C%20delete%20them%20or%20move%20them%20out%20of%20this%20folder.%3C%2FP%3E%0A%3CP%3EStart%20Excel%20again%20and%20see%20if%20the%20problem%20is%20gone.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2528528%22%20slang%3D%22en-US%22%3ERe%3A%20Unwanted%20default%20dark%20cell%20borders%20-%20new%20sheet%20or%20clearing%20all%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2528528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20XLSTART%20folder%20was%20empty.%3CBR%20%2F%3EI%20had%20three%20others%2C%20using%20different%20machines%2C%20try%20the%20workbook%20and%20they%20got%20the%20same%20results.%3C%2FP%3E%3CP%3EIf%20I%20open%20a%20new%20workbook%20and%20copy%20a%20range%20of%20blank%20cells%20then%20paste%20them%20in%20to%20the%20older%20work%20book%20I%20get%20the%20black%20borders.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20must%20be%20settings%20in%20the%20workbook%20that%20define%20the%20default%20formatting%20but%20I%20have%20not%20found%20where%20that%20is.%3C%2FP%3E%3CP%3EThere%20are%20multiple%20workbooks%20that%20have%20this%20same%20issue.%20All%20appear%20to%20have%20first%20been%20created%20from%201999-2003.%20Converting%20to%20xlsx%20does%20not%20fix%20the%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20the%20the%20new%20workbook%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dfugate_0-1625756624515.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294413i3FB3C1FC17F5E190%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dfugate_0-1625756624515.png%22%20alt%3D%22dfugate_0-1625756624515.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20result%20of%20pasting%20into%20the%20old%20workbook.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dfugate_1-1625756703622.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294414iF59B1AC798E94A3F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dfugate_1-1625756703622.png%22%20alt%3D%22dfugate_1-1625756703622.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a bunch of Excel files that were created with 97-2003 as an xls. When a new sheet is added all cells have black borders. Also if the cell formatting is cleared, using Editing >> Clear >> Clear All, the cell borders are black.

 

I have also change to an xlsx but I still get the black borders. 

There is no scripting in the workbook.

 

I think this has something to do with default cell formatting but have yet to find an existing solution on the web.

 

Using Office 365.

 

When creating a new sheet

dfugate_0-1624557702632.png

 

When clearing any existing formatting

dfugate_1-1624557763674.png

 

In this example all cells were selected and the borders were set to "no borders". Then a range was selected and the formatting was cleared, now black borders show.

dfugate_2-1624557931526.png

 

Any ideas on how to get the formatting back to factory defaults so when a sheet is added or a cell formatting is cleared it reverts to the Excel default and not these black borders?

3 Replies

@dfugate 

Quit Excel.

In File Explorer (assuming you're on Windows), enter

 

%appdata%\Microsoft\Excel\XLSTART

 

in the address bar and press Enter. If you see Excel templates named Book.xltx and/or Sheet.xltx in the XLSTART folder, delete them or move them out of this folder.

Start Excel again and see if the problem is gone.

@Hans Vogelaar 

The XLSTART folder was empty.
I had three others, using different machines, try the workbook and they got the same results.

If I open a new workbook and copy a range of blank cells then paste them in to the older work book I get the black borders.

 

There must be settings in the workbook that define the default formatting but I have not found where that is.

There are multiple workbooks that have this same issue. All appear to have first been created from 1999-2003. Converting to xlsx does not fix the issue.

 

This the the new workbook

dfugate_0-1625756624515.png

 

 

Here is the result of pasting into the old workbook.

dfugate_1-1625756703622.png

 

 

@dfugate 

Strange...

You can attach one of the old workbooks that demonstrates the problem if you wish; you can remove all data.