Excel works well out of the box, but five minutes spent configuring it correctly before your first spreadsheet saves hours of frustration later. This module walks through every important setting in Excel Options — from changing the default font and fixing AutoRecover, to enabling the Developer tab, setting your user name for tracked changes, and controlling how Excel handles calculations and AutoCorrect. These settings persist across all your workbooks.
All configuration settings live in the Excel Options dialog — a single window with 11 categories in the left panel, each containing related settings.
| Category | What It Controls |
|---|---|
| General | User name, default font/size, number of sheets, start screen, Office theme, real-time preview |
| Formulas | Calculation mode, R1C1 reference style, formula AutoComplete, error checking rules |
| Data | Data analysis options, legacy data import, Flash Fill settings |
| Proofing | AutoCorrect settings, spelling check language, ignore words |
| Save | Default file format, AutoRecover interval and location, AutoSave settings, default save location |
| Language | Office display language, editing language, help language |
| Ease of Access | Accessibility settings, feedback animations, ScreenTip style |
| Advanced | Editing options, display options, cut/copy/paste, chart defaults, formula bar size, file locations |
| Customize Ribbon | Add/remove Ribbon tabs and groups, enable Developer tab, create custom tabs |
| Quick Access Toolbar | Add/remove commands from the Quick Access Toolbar (QAT) |
| Trust Center | Macro security, trusted locations, protected view, external content settings |
File → Options → General is the most important starting point. These settings affect the look and behaviour of Excel across every workbook.
The user name stored here appears on comments, tracked changes, and co-authoring indicators — it identifies you as the author.
By default, Excel uses Calibri 11pt for all new workbooks. Change this to match your organisation's standard font:
File → Options → Formulas controls how Excel calculates and displays formulas.
| Mode | Behaviour | When to Use |
|---|---|---|
| Automatic ★ | Every formula recalculates instantly whenever any cell value changes | Normal use — use this setting always unless the workbook is extremely large and slow |
| Automatic Except for Data Tables | Standard formulas update automatically but Data Tables (What-If Analysis) only update on demand | Workbooks with large Data Tables that slow down calculation |
| Manual | Formulas only recalculate when you press F9 (recalculate all sheets) or Shift+F9 (current sheet only) | Very large workbooks (100,000+ formulas) where automatic recalculation makes editing slow. Warning: easy to forget to recalculate — results may be stale. |
=SU shows SUM, SUMIF, SUMIFS, SUMPRODUCT=SUM(A1:A10)) instead of the calculated valueExcel automatically flags possible formula errors with a small green triangle in the top-left corner of the cell. The rules that trigger these flags are configured here:
Leave all these ticked — they catch real errors. To dismiss a specific flag: click the cell → click the warning icon → choose "Ignore Error".
File → Options → Save — configure these settings before you start any important work.
C:\Users\[YourName]\Documents\Excel FilesFile → Options → Proofing
| Tab | What to Configure |
|---|---|
| AutoCorrect |
Correct TWo INitial CApitals — leave ticked (fixes typing like "EXcel" → "Excel") Capitalise first letter of sentences — can be annoying in spreadsheets where sentences are rare; consider unticking Replace text as you type — add your own shortcuts: e.g., type "skailit" → auto-replaces with "Skailit Learning Solutions (Pty) Ltd" Exceptions button — add abbreviations that should NOT trigger capitalisation (e.g., "dept." so it doesn't capitalise the next word) |
| AutoFormat As You Type | Internet and network paths with hyperlinks — when ticked, typing a URL turns it into a clickable hyperlink. Useful in some contexts, annoying in others. Untick if URLs in your data should remain as plain text. |
File → Options → Advanced contains a long list of settings. These are the ones that matter most for day-to-day work.
| Setting | Recommended | Why |
|---|---|---|
| After pressing Enter, move selection | Direction: Down ★ | After typing in a cell and pressing Enter, the cursor moves down to the next row — the most natural data-entry flow. Change to Right if you primarily enter data across rows. |
| Automatically insert a decimal point | Unticked ★ | If ticked with Places: 2, typing 1500 becomes 15.00 — useful only for numeric keypad data-entry in accounting. Leave unticked for general use. |
| Enable Fill Handle and cell drag-and-drop | Ticked ★ | The small square at the bottom-right of a cell (the fill handle) allows you to drag formulas and series down or across. This is essential — leave ticked. |
| Alert before overwriting cells | Ticked ★ | Shows a warning if you drag a cell onto a cell that already has data — prevents accidental data loss. |
| Allow editing directly in cells | Ticked ★ | Double-clicking a cell lets you edit its content directly in the cell rather than only in the Formula Bar. Leave ticked. |
| AutoComplete for cell values | Ticked ★ | When typing in a column, Excel suggests the completion based on existing values in that column. Press Enter to accept or keep typing to override. Very useful for consistent data entry. |
| Zoom on roll with IntelliMouse | Unticked ★ | When ticked, the scroll wheel zooms in/out instead of scrolling. Most users prefer scrolling. Ctrl+scroll wheel zooms regardless of this setting. |
The Developer tab is hidden by default but is needed for recording macros, creating form controls (checkboxes, drop-downs), and writing VBA code. Enable it once and it stays enabled.
| Group | Key Commands |
|---|---|
| Code | Visual Basic (Alt+F11), Macros (Alt+F8), Record Macro, Use Relative References, Macro Security |
| Add-ins | Excel Add-ins, COM Add-ins — manage installed extensions |
| Controls | Insert form controls (button, checkbox, list box, scroll bar) and ActiveX controls |
| XML | XML Source, Map Properties, Refresh Data — for XML-connected workbooks |
File → Options → Trust Center → Trust Center Settings…
The Trust Center controls what Excel is permitted to do — especially around macros, external links, and files from the internet. Incorrect settings here can make Excel refuse to open files or run your own macros.
| Setting | Behaviour | Recommended? |
|---|---|---|
| Disable all macros without notification | Macros are blocked silently — even your own macros won't run | ❌ Too restrictive |
| Disable all macros with notification ★ | A yellow security bar appears when a file with macros opens — you decide to enable or not | ✅ Best for most users |
| Disable all macros except digitally signed macros | Only macros signed by a trusted certificate run automatically | ✅ Best for enterprise |
| Enable all macros | All macros run without warning — including malicious ones in files you receive | ❌ Never use this |
Add folders that contain your own safe files so they open without any security warnings:
Files downloaded from the internet, email attachments, and files from network locations open in Protected View — a read-only mode where macros and editing are disabled as a security precaution.
The QAT provides one-click access to your most-used commands regardless of which tab is active.
Work through this checklist on a new installation of Excel — or when setting up a new computer. These settings make Excel faster, safer, and more aligned with South African office standards.
| Setting | Location | Recommended Value |
|---|---|---|
| User name | Options → General | Your full name |
| Default font | Options → General | Arial or Calibri, size 12 |
| Calculation mode | Options → Formulas | Automatic |
| R1C1 reference style | Options → Formulas | Unticked |
| Formula AutoComplete | Options → Formulas | Ticked |
| AutoRecover interval | Options → Save | 5 minutes |
| Default save format | Options → Save | Excel Workbook (.xlsx) |
| Default local file location | Options → Save | Your main working folder |
| Enter key direction | Options → Advanced | Down |
| Enable fill handle | Options → Advanced | Ticked |
| AutoComplete for cell values | Options → Advanced | Ticked |
| Show formula bar | Options → Advanced | Ticked |
| Macro security | Trust Center → Macro Settings | Disable all macros with notification |
| Developer tab | Options → Customize Ribbon | Ticked (enabled) |
| Office theme | Options → General | Colourful or Dark Grey |
Q1: Your Excel formulas are not updating when you change cell values — the results stay the same until you press F9. What setting is causing this and where do you fix it?
✓ The calculation mode is set to Manual. Fix: File → Options → Formulas → Workbook Calculation → select Automatic → OK. Formulas will now recalculate instantly whenever a cell value changes. This setting is saved per-workbook — check it whenever you open a workbook someone else created.
Q2: A new employee joins your team and will be reviewing shared spreadsheets. She has not changed her Excel user name — it still says "User 1". Why does this matter and how does she fix it?
✓ The user name appears on all comments and tracked changes she makes — if it shows "User 1" instead of her name, no one knows who made those comments or edits. Fix: File → Options → General → under "Personalize your copy of Microsoft Office" → change User name to her full name → change Initials → OK. All future comments and tracked changes will be attributed to her correctly.
Q3: You open an Excel file received by email and see a yellow bar at the top saying "PROTECTED VIEW". The file contains a form you need to fill in. What do you do and why?
✓ Protected View is a safety feature that opens files from external sources (email, internet, network) in a read-only, no-macros mode. Before clicking "Enable Editing": verify the sender is known and trustworthy, check the file extension (be suspicious of .xlsm or .xlsb from unknown senders), and scan the file with antivirus if you have any doubt. If the sender is a known colleague and the file is expected: click Enable Editing to exit Protected View and interact normally with the form.
Q4: Excel has just crashed and you lost 40 minutes of work. After reopening Excel, a "Document Recovery" pane appears. What do you do, and what setting should you change to reduce future data loss?
✓ Click the most recent AutoRecover version in the Document Recovery pane to restore your work — compare it to the current file and save if it contains the missing work. To reduce future risk: File → Options → Save → change "Save AutoRecover information every X minutes" from 10 to 5 minutes → also tick "Keep the last AutoRecovered version if I close without saving" → OK. For maximum protection, save files to OneDrive with AutoSave enabled — cloud saves happen every few seconds automatically.
Q5: After typing, pressing Enter moves your cursor to the right instead of down. This makes entering data in a column frustrating. Where do you fix this?
✓ File → Options → Advanced → Editing options → "After pressing Enter, move selection" → change Direction from Right to Down → OK. The Enter key will now move the cursor down to the next row after confirming a cell entry, which is the standard direction for column-by-column data entry.
Q6: You want to record a macro to automate a repetitive formatting task, but you cannot find the Record Macro button anywhere in the Ribbon. What must you do first?
✓ The Developer tab is not enabled. Enable it: File → Options → Customize Ribbon → in the right panel (Main Tabs), tick Developer → OK. The Developer tab appears in the Ribbon with the Record Macro, Macros, and Visual Basic buttons. Alternatively, you can record a macro via View → Macros → Record Macro, or by clicking the small circle/dot icon in the Status Bar at the very bottom of the Excel window — these work without the Developer tab.