Registry: HKEY_CURRENT_USER\Software\Microsoft\Office\_version

The registry key HKEY_CURRENT_USER\Software\Microsoft\Office\_version_\Excel\Options specifies some options for Excel.

For the value of _version_, see HKEY_CURRENT_USER\Software\Microsoft\Office\X.Y.

AltStartup

The value should be a directory. When Excel starts up, it tries to open all files (even non-Excel files) in that directory.

DefaultCPG

Apparently, DefaultCPG is supposed to specify the default Windows code pages (for example when importing data into excel).

The value 65001 means UTF-8.

DefaultFormat

DefaultFormat specfies the default format in which Excel sheets are saved.

The values of DefaultFormat correspond to the ones found in the xlFileFormat enumeration. 52 (hex 34) corresponds to xlsm.

DefSheets

The value of DefSheets determines the number of worksheets with which a new workbook is created.

In absence of this value, it defaults to 1.

This value can be queried in application.sheetsInNewWorkbook.

DeveloperTools

If the value of DeveloperTools is set to 1, Excel displays the developer tools (which is required(?) to record macros).

Compare with the same value under HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Word\Options.

OPENn

The values of OPENn seem to correspond to Excel add-ins (.xla* files) that were automatically opened when Excel was started.

Options

The value of Options seems to be a bit-field that stores several options for excel.

For example, bit nr. 2 seems to store if the formula bar is displayed.

PersonalTemplates

This value points to the directory in which Excel templates are stored.

The default value seems to be %USERPROFILE%\Documents\Custom Office Templates.

Setting values in the command line

The following to scripts can be used to set these values from the console (either cmd.exe or PowerShell).

cmd.exe / reg.exe

The following script can be used to change some values with reg in cmd.exe:

set EXCEL_VERSION=15.0

@reg add HKCU\Software\Microsoft\Office\%EXCEL_VERSION%\Excel\Options /v DefaultCPG     /t REG_DWORD /d 65001 /f > nul
@reg add HKCU\Software\Microsoft\Office\%EXCEL_VERSION%\Excel\Options /v DefaultFormat  /t REG_DWORD /d    34 /f > nul
@reg add HKCU\Software\Microsoft\Office\%EXCEL_VERSION%\Excel\Options /v DeveloperTools /t REG_DWORD /d     1 /f > nul

Github repository about-Windows-Registry, path: /HKEY_CURRENT_USER/Software/Microsoft/Office/application_version/Excel/Options/setOptions.bas

Note that the Office version needs to be adjusted in the respective variable in the batch file.

PowerShell

With PowerShell, the same thing can be achieved with

$officeVersion = (get-item hklm:\Software\Classes\excel.application\curVer).getValue('')  -replace '.*\.(\d+)', '$1'

$eatMe = new-itemProperty hkcu:\Software\Microsoft\Office\$officeVersion.0\Excel\Options -name DefaultCPG     -value 65001 -force
$eatMe = new-itemProperty hkcu:\Software\Microsoft\Office\$officeVersion.0\Excel\Options -name DefaultFormat  -value    34 -force # default default format is 33
$eatMe = new-itemProperty hkcu:\Software\Microsoft\Office\$officeVersion.0\Excel\Options -name DeveloperTools -value     1 -force

Github repository about-Windows-Registry, path: /HKEY_CURRENT_USER/Software/Microsoft/Office/application_version/Excel/Options/setOptions.ps1

Registry: HKEY_CURRENT_USER\Software\Microsoft\Office\_version_\Excel\Options