Course contents · Excel interface and options

Excel Options

Open the practice in the trainer →

Almost all of Excel's behaviour is set in File → Options (quickly: Alt, F, T). Changes take effect after OK; Cancel undoes them.

Sections of the dialog

Generalfont and number of sheets for new workbooks, user name (it goes into comments and file properties), Office Theme
Formulascalculation mode (automatic / manual — then recalculate with F9), iterative calculation, R1C1 reference style, Formula AutoComplete, error checking rules
Datadate grouping in PivotTables, automatic data conversion (leading zeros, long numbers)
ProofingAutoCorrect options (if Excel "fixes" what it shouldn't), dictionaries
Savedefault format, AutoRecover every N minutes and the AutoRecover folder
Languagedisplay language and authoring languages
Advancedthe biggest section: direction after Enter, fill handle, decimal and thousands separators, showing the formula bar, gridlines, zeros, sheet tabs, Set precision as displayed
Customize Ribbonturn on hidden tabs (Developer), create your own tab and group
Quick Access Toolbaradd your own commands to the title bar; same as the ▾ menu on the toolbar itself
Add-insat the bottom Manage: Excel Add-ins → Go… — turns on the Analysis ToolPak and Solver
Trust Centermacro settings, Protected View, trusted locations

Common problems and where to fix them

Formulas do not recalculateFormulas → Workbook Calculation → Automatic
Columns are numbered, formulas look like =RC[-2]*RC[-1]Formulas → clear R1C1 reference style
Enter moves the cursor the wrong wayAdvanced → After pressing Enter, move selection → Direction
The formula bar or gridlines are goneAdvanced → Show formula bar, Show gridlines (or the View tab)
No Developer tabCustomize Ribbon → Developer check box
No Data Analysis on the Data tabAdd-ins → Excel Add-ins → Go → Analysis ToolPak

Excel for Mac. Options are Excel → Settings (⌘+,), a window of section icons. R1C1 is under General, calculation mode under Calculation, the direction after Return under Edit, the Developer tab under Ribbon & Toolbar, AutoRecover under Save. Add-ins are turned on in Tools → Excel Add-ins…, and on a Mac the Analysis ToolPak shows up as the Analysis Tools button on the Data tab.

Tasks in the Excel emulator

  1. Open the Excel Options dialog.
  2. Turn on the Developer tab and click OK — it appears on the Ribbon.
  3. Turn on the R1C1 reference style, look at the column headers and the formula in D2 — then switch back to A1 style.
  4. Make the cursor move right after Enter, then click a cell in the table and press Enter.
  5. Turn on the Analysis ToolPak add-in and find its button on the Data tab.
  6. Set AutoRecover every 5 minutes.
  7. Add Quick Print to the Quick Access Toolbar.

Solve the tasks in the trainer →

Check yourself

  1. A colleague says: "formulas don't recalculate until I press F9". What do you check?
  2. Where do you turn on the Developer tab?
  3. When do changes in the Excel Options dialog take effect?