The Preferences Dialog in Excel
Most people looking for this are on a Mac. That is because the Windows version of Excel calls the same menu "Options" instead. If you open Excel and go to File > Preferences, you are already where you need to be on macOS. The keyboard shortcut is Command + comma. That opens a dialog with tabs for General, Editor, Calculations, Views, Errors, Save, and Print. On Windows, the equivalent is File > Options. The layout is different from the Mac version, but the settings map onto each other closely enough. The most commonly used section is Formulas, where you can toggle automatic calculation, iterative calculation, and show formulas in cells. On the Mac side, those same settings live under Preferences > Calculations. I ran into a problem last year where a colleague was importing CSV files and every date came in as text. He spent two hours going through File > Options > Data > Text Import Wizard, trying to set the column format manually for each import. The real fix was simpler. In Preferences > General, there is a checkbox labeled "Ignore DDE." Unchecking it, combined with setting your default file location to a local folder rather than OneDrive, stopped Excel from trying to auto-detect and reformat incoming data on import. It is not a perfect fix, but it cut his import time from about forty minutes to under five for a fifty-megabyte daily feed.
Another thing nobody tells you about the Preferences dialog is how much it varies between Excel versions. Microsoft moved the Ribbon customization interface from Preferences on the Mac into the main Excel window around the 2019 update. If you are on an older version, your preferences layout will look different from what a screenshot online shows. The settings themselves stay the same, but the navigation path changes. Here is the part that trips people up. Some preferences are user-level, and some are application-level. User-level preferences are stored in your home directory under Library > Preferences > Microsoft. Application-level preferences are stored inside the Excel application bundle itself and only change if you run Excel as an administrator. If you change a setting and it disappears after restarting, you almost certainly modified an application-level preference instead of a user-level one. The workaround is to hold the Option key while clicking the Excel icon in the Dock, then selecting Preferences from the menu that appears. That forces the user-level dialog. If you need to export or share your preferences with another machine, there is no built-in export button. You have to manually copy the plist files from the user library folder. On Windows, it is a registry key at HKEY_CURRENT_USER > Software > Microsoft > Office > [version] > Excel > Options. Backup that key before editing anything. A wrong value there can break the entire Excel installation, and the typical repair time is about an hour of registry hunting.
The Preferences dialog also does not surface every setting in the program. Things like custom toolbar layouts, add-in registration, and certain sharing permissions are hidden in separate dialogs or require VBA to access. If you are trying to automate preference changes across a fleet of machines, PowerShell with the Excel COM object is the only reliable method. Manual clicking through the dialog for each setting takes far too long and introduces human error. For most users, the useful settings are in General, Calculations, and Errors. General controls your default file format, recently used file list length, and theme. Calculations controls recalculation behavior, which matters a lot if you work with large models that slow down when set to automatic. Errors controls green triangle validation, which some teams disable entirely because it causes more confusion than it prevents. If you are transitioning from Google Sheets or another tool and just want Excel to behave more predictably, start with the Editor tab in Preferences. Disable "Autocorrect Replace text as you type" if you use any technical abbreviations that get mangled. Then go to Calculations and switch to manual if your workbook has more than ten thousand volatile functions. That alone will make the difference between a usable spreadsheet and one that freezes every time you type.
Get the Full Details
