This tip involves a tale of how I discovered the information for this tip and not in my usual format.
(The tip is below if you don’t want to read how I found this unique information)
Background:
Excel’s default Blank Workbook and blank Sheet templates do not exist as a template files such as Normal.dotm. When you click Blank Workbook, Excel opens a workbook to Calibri Font and Font size 11. If you click the + icon by the sheet tab to insert a sheet, it to will insert with the default Calibri Font at size 11. You can over ride Excel’s default templates by creating your own custom templates which must be labeled Book.xltx and Sheet.xltx and placed in Excel’s XLSTART folder. With those templates in that folder when you click Blank Workbook Excel will open a copy of the Book.xltx instead of the default template formatting. If you remove the Book.xltx, when you click Blank Workbook Excel will open a workbook using the default template formatting since no Book.xltx to refer to.
There has been discussions that Excel’s default templates must have template files somewhere. My discoveries prove that at least the default Font and Font size of those “templates” comes from settings in the Registry and not a template file.
Situation:
I have one Desktop and one laptop that have the exact same version of Windows 10 Home 22H2 and have the exact same install of Office 2019 C2R. To keep things straight I call the Desktop Desk and the laptop Lap. On the Desk I created a Book.xltx and Sheet.xltx with the Font and size I like (Tahoma size 11). A long time later I created a new Book.xltx of a different Font and Font size, which I used for a while. When I deleted the new Book.xltx file I expected the Blank Workbook to revert to its default formatting of Calibri size 11, but to my surprise the Blank Workbook default setting had changed to the Tahoma size 11. I verified that Book.xltx was no where on my system, so that setting that is different form the install default formatting had to be stored somewhere.
I confirmed that was not the way the default formatting should act.
On the Lap I created a Book.xltx with different formatting which did change the format of the Blank Workbook when it opened. I deleted the Book.xltx and the Blank Workbook reverts to its default format of Calibri size 11.
What is different on the Desk and the Lap.
Investigation:
Suspecting the values were in the Registry, so I performed searches for the keys that held values and data for those settings.
Searched the Desk Registry for “Tahoma” as part of any key, value , or data. Besides the Registry entries I expected I found a Value in the following key
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options
the value is: Font Reg_SZ Tahoma,11
This is the exact Font and size as the default being used.
I looked at the Registry of the Lap which defaults to the Calibri and found that the Value above was not in the Lap’s Registry.
I do not know how that registry value got there; but I suspect when I did a repair of office while the original Book.xltx with the Tahoma size 11 formatting was there it added that key.
Anyways, I tested it and the Value Data in that Font value of that Options key controls the Font and Font size of the Blank Workbook and Added Blank Sheet.
Change Font and Font Size in Excel’s Default Blank Workbook and Sheet
Open the Registry Editor
Copy the following key address
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options
Paste in the address bar of the Registry Editor and press the Enter key to go to that key with Opitons highlighted and the values for that key displayed in the right pane.
Right click a blank area in the Value pane and select New> String Value which will open a new value name box below the other values there. Enter Font and press the Enter key to name the value Font.
Now double click this Font value to open so you can enter the Value data.
In the Value data box enter the font by name followed by a comma followed by the number of the font size you want. No spaces between name and size. Example Arial,12 or Tahoma,16. Press Ok to save your changes.
The effects are immediate and you can change the value when ever you want.
HTH, Dana:))
