Reset Excel to default borders

Question

Ok, so you know what a spreadsheet looks like when you open a new on in Excel; the borders are a light blue. These are only on the screen though, if you print the sheet it will not have borders. Say you've applied some various formatting to the sheet (background color, etc.) and those "default" borders are gone. My question is how to you get them back? Simply doing a Clear Formats will not always work.

Specifically I am talking about Excel 2007 but I believe all versions do this.

Any ideas?

Thanks, Ryan

Answer 1

I had this issue, grid lines appeared to be missing on some cells.

Took me awhile to figure out that the color of those cells were white. I clicked format cell, pattern and then selected "no color" (instead of white) The the grid lines were visible again.

I hope this helps others as it took me a while to figure out why.

Answer 2

If you have applied border and/or fill on a cell, you need to clear both to go back to the default borders.

You may apply 'None' as the border option and expect the default borders to show, but it will not when the cell fill is white. It's not immediately obvious that it has a white fill, as unfilled cells are also white.

In this case, apply a 'No Fill' on the cells, and you will get the default borders back.

That's it. No messy format painting, no 'Clear Formats', none of those destructive methods. Easy, quick and painless.

Answer 3

Just go to Home> Cell Style > Normal

khir

Answer 4

If you're trying to do this from within Excel (rather than programmatically), follow these steps:

1. From the "Orb" menu on the ribbon, click the "Excel Options" button near the bottom of the menu.

2. In the list of choices at the left, select "Advanced".

3. Scroll down until you see the heading "Display options for this worksheet".

4. Select the checkbox labeled "Show guidelines".

   

Answer 5

My best answer for this is to simply use format painter. This might be a bit of a pain, but it works rather well as the problem you are facing is that Gridlines are covered by fill and other effects that are layered on top. Imagine putting a piece of white paper on top of your grid, the grid lines are present underneath, but they just don't show.

So try:

1. Clicking on a cell in the spreadsheet with the format that you want
2. Under the ribons, go to Home and format painter, it should be a smaller icon near the paste button.
3. Now highlight any cell that you want to apply this format to and it will set the font, color, background etc. to the same as the cell selected. The value will be preserved.

From my experience this is the easiest way to do this quickly. Especially when pasting things in and out of excel.

Again this is not the programmatic way of solving this problem.

Answer 6

Another way, There is check box Page Layout tab with Gridlines [ ] View which should be checked.

Answer 7

you just need to change the line color and you can apply it without problem

Answer 8

I was having the same trouble with importing from Excel 2010 to Access, appending an "identical" table. Early on in the wizard it said not all my column names were valid, even though I checked them. It turns out that it saw an "empty" column with no column name. When I tried using the import wizard to create a new table instead, it worked. However, I noticed that it had added a blank column to the right of my data and called it "Field30". So I went back to the spreadsheet I was trying to import, selected the columns to the right of the data that I wanted, right-clicked and chose "clear contents." That did the trick and I was able to import the spreadsheet, appending it to my table.

Answer 9

In Excel 2016 for Mac, I clicked the Excel menu, then clicked Preferences…

I then clicked the View icon.

whereupon I found a Gridlines Checkbox next to a Color Picker.

Regardless of whether the Gridlines checkbox os checked or not, if you change the color in the Color Picker dropdown menu, your cell borders will become that color. (I believe the change took place after i quit excel and opened the document the next day to continue working on it but I can't accurately remember.)

Changing the color picker back to Automatic will return your cell borders to the default (black) color on-the-fly.

N.B. Because I'm a newbie I cannot insert the screen shots I prepared ahead of time.

Answer 10

I understand this is an old post. But it is programmable. Otherwise make sure your fill is set to "No Fill" and your boarders are set to "No Boarder" via the user interface shown in the previous posts.

Sub clear()
Range("A4:G1000").Borders.LineStyle = xlNone 
Range("A4:G1000").Interior.ColorIndex = xlNone 
End Sub()

Answer 11

Select the cells that you need to affect the style and go to Home then click cell style and select Normal as show in the below snapshot

Answer 12

If you have conditional formatting rules, and want to change the background color but keep the default borders, you can add all borders and give them the color #E0E0E0. These are the steps you have to take to do this:

1. Go to your conditional formatting, and edit the format.
2. Edit the formatting rule
3. In the next dialog, go to the "Border" tab and change these values:
   • Press "outline" and "inside"
   • Select the bottommost line style
   • In the color dropdown, press "more colors"
4. In the "Hex" value, add the default color: #E0E0E0.
   
5. Press OK on all the dialogs

The result should look like this: