Andy T
Lv 4
Andy T asked in Computers & InternetSoftware · 4 years ago

How do you change the default border/fill-color/font-color selected in new Excel (2010) books?

Doing my research I found that you can change things about the default new-book in Excel by creating/editing a Book.xltx (& Sheet.xltx) file.

However, in testing this, this only changes the default formatting of the sheets themselves, not the selected options. That is, even before when I opened a new Excel book there was no border, no fill, & the font color is black if I start typing. Saving the Book template file with no border/no fill/black selected does not affect whether they re selected when you open a new book. The book has that formatting, but the options up in the ribbon are always defaulted to underline-border, yellow fill, red text.

What I use Excel for most often is working with sports-stats, and so when I paste tables from online sources, I want to get rid of any border/fill/off-color-text that tables may have. It would be easier to just give 3 clicks to those 3 options, if the defaults were different (rather than opening each menu & choosing different options).

I understand they re probably the defaults that they are, because it s expected you want to add unique formatting to the default things you ve typed yourself (in no-fill, black)...but it d be really helpful if you could change these defaults.

1 Answer

  • 4 years ago

    I would suggest that you use a VBA method to reformat all cells in the worksheet on the worksheet change event. You could specify: no borders, row height, interior fill color, font color. Then, any time you paste data into the worksheet, it would automatically reformat without any user interaction.

    In order to make this work easily, you would need to implement the VBA code, then save template workbook as an .xltm file (Excel Macro-Enabled Template). You should save this to your desktop, or location you specify, as Excel automatically bury saved template files deep in the bowels of the Excel files, difficult to locate later.

Still have questions? Get your answers by asking now.