Excel Excelsior

Excel set-up tips

Ten minutes in Settings that make every shortcut work and save time from your first week. Pick your keyboard in the top bar to see yours first.

Menus move around. Microsoft renames and reshuffles Excel's settings between versions. We check these tips periodically, but if you can't find a setting straight away, trust us: it exists! Look nearby, or type its name into the search box in Excel's Settings or Help.

Excel for Windows

Use KeyTips to drive the ribbon from the keyboard

Every ribbon button gets a letter, so you can reach commands that have no shortcut without touching the mouse.

  1. KeyTips are always on in Excel for Windows. Press Alt and letters appear over the ribbon tabs.
  2. Press the letter for a tab, then the letter for a command. Alt, H, O, I autofits the column width; Alt, H, B, A adds all borders.
  3. Press Esc to step back a level, or Alt again to hide them.

Make F1–F12 act as function keys

Many laptops send volume and brightness instead of F2 and F4, so F2 and F4 do nothing.

  1. Select a cell and press F2. If the volume or brightness changes instead, your function keys are in media mode.
  2. To use one once, hold Fn and press the key.
  3. To switch for good, turn on Fn Lock. On many laptops it's Fn+Esc or a key with a padlock; otherwise look in your laptop maker's settings app.

Check calculation is set to Automatic

Workbooks from colleagues are sometimes saved in Manual mode. Formulas then stop updating and you send out stale numbers.

  1. On the Formulas tab, open Calculation Options.
  2. Pick Automatic. If it was on Manual, check your numbers again after switching.

Put your most-used commands on the Quick Access Toolbar

Paste Values, Freeze Panes and Filter each become one keystroke.

  1. If you can't see the toolbar, right-click the ribbon and choose Show Quick Access Toolbar.
  2. Right-click any ribbon command (for example Paste Values in the Paste menu) and choose Add to Quick Access Toolbar.
  3. The toolbar's buttons are numbered in order: Alt, 1 runs the first, Alt, 2 the second, and so on.

Turn on Solver and the Analysis ToolPak

Both come with Excel but are off by default. You need them for optimisation, regression and descriptive statistics.

  1. Go to File → Options → Add-ins.
  2. At the bottom, set Manage to Excel Add-ins and click Go.
  3. Tick Solver Add-in and Analysis ToolPak, then OK. They appear on the Data tab as Solver and Data Analysis.

Save AutoRecover every few minutes

If Excel crashes before you save, you lose at most a few minutes of work instead of an afternoon.

  1. Go to File → Options → Save.
  2. Tick Save AutoRecover information every and set it to 5 minutes.
  3. For files on OneDrive or SharePoint, the AutoSave switch at the top left saves as you go.

Excel for Mac

Use KeyTips to drive the ribbon from the keyboard

Every ribbon button gets a letter, so you can reach commands that have no shortcut without touching the mouse. On a Mac they are off until you turn them on.

  1. Update Excel first (Help → Check for Updates). KeyTips arrived in Excel for Mac in 2024; older versions don't have the setting.
  2. Open Excel → Settings (called Preferences in older versions) and choose Accessibility.
  3. Under KeyTips, set the Activation keystroke to Option (⌥). That matches the Alt key on Windows.
  4. Press ⌥ in a workbook and letters appear over the ribbon. ⌥, H, O, I autofits the column width.

Make F1–F12 act as function keys

Mac keyboards send brightness and volume from the top row by default, so F2 and F4 need fn held down.

  1. Open System Settings → Keyboard, then Keyboard Shortcuts…
  2. Choose Function Keys and turn on Use F1, F2, etc. keys as standard function keys.
  3. Without it, hold fn (the 🌐 key) and press the F-key.

Free up ⌃Space for Excel

If you have more than one keyboard language, macOS uses ⌃Space to switch between them, so ⌃Space never reaches Excel.

  1. Open System Settings → Keyboard, then Keyboard Shortcuts…
  2. Choose Input Sources and untick Select the previous input source.

Tab through dialog boxes

By default Tab skips buttons in Mac dialogs, so you end up reaching for the mouse to click OK or Cancel.

  1. Open System Settings → Keyboard and turn on Keyboard navigation.
  2. Tab now moves through every button and control; Space presses the one in focus.

Check calculation is set to Automatic

Workbooks from colleagues are sometimes saved in Manual mode. Formulas then stop updating and you send out stale numbers.

  1. On the Formulas tab, open Calculation Options.
  2. Pick Automatic. If it was on Manual, check your numbers again after switching.

Put your most-used commands on the Quick Access Toolbar

Paste Values, Freeze Panes and Filter stay one click away, and reachable by KeyTips once those are on.

  1. Open Excel → Settings and choose Ribbon & Toolbar.
  2. Switch to the Quick Access Toolbar tab, pick commands on the left and add them to the right.

Turn on Solver and the Analysis ToolPak

Both come with Excel but are off by default. You need them for optimisation, regression and descriptive statistics.

  1. In the menu bar, choose Tools → Excel Add-ins.
  2. Tick Solver Add-in and Analysis ToolPak, then OK. They appear on the Data tab as Solver and Data Analysis.

Save AutoRecover every few minutes

If Excel crashes before you save, you lose at most a few minutes of work instead of an afternoon.

  1. Open Excel → Settings and choose Save.
  2. Turn on AutoRecover and set it to every 5 minutes.
  3. For files on OneDrive or SharePoint, the AutoSave switch at the top left saves as you go.