Excel Shortcuts, Calculation and Sharing

This guide groups together three things every regular Excel user runs into but that rarely get a chapter of their own: keyboard shortcuts that remove dozens of mouse trips a day, the calculation engine that decides when a formula’s result is actually kept up to date, and the handful of protection and sharing settings that decide what happens to a workbook once it leaves your own screen. None of these are individual functions like VLOOKUP or SUMIFS; they are the surrounding machinery that formulas run inside.

Every claim below that can be tested was run for real in Microsoft Excel through its automation interface: the shortcuts are shown as the exact object-model call each one performs, and calculation, iteration, protection and paste behaviour are all captured from a live worksheet, not from documentation.

Shortcuts worth actually learning

Excel has hundreds of shortcuts; a handful earn their place in daily use because they replace an action you repeat constantly. Alt+= inserts a SUM formula over the numbers directly above the active cell – run for real here as a plain =SUM(A1:A3), since it is exactly what that shortcut writes into the cell:

Ctrl+Shift+L is exactly Range.AutoFilter() in the object model: run against a small table, AutoFilterMode genuinely flips from “False” to “True” the moment it runs, confirming it is a real on/off toggle and not just a visual change to the header row. Ctrl+` is the same “formulas” view used throughout this guide series: every worked example already shows both the values grid and the formulas grid side by side, which is precisely what that shortcut switches between on a live sheet without changing a single value underneath.

Automatic vs. Manual calculation

Excel recalculates Automatically by default: change any cell, and every formula that depends on it updates immediately. Formulas, Calculation Options can switch this to Manual, which freezes every result until you press F9 (or Ctrl+Alt+F9 for a full recalculation) – and the frozen result looks identical to a correct one, which is exactly what makes it dangerous. Starting from A1 = 5 and B1 = =A1*2:

Circular references and iterative calculation

A circular reference is a formula that depends, directly or through other cells, on its own result. Excel treats this as an error by default. With A1 = =B1*0.5+10 and B1 = =A1, and iterative calculation left off:

B1 is simply held at 0 because Excel cannot resolve the loop, which makes A1 come out to 10 – a number that does not satisfy A1 = A1*0.5+10 at all. Turning on File, Options, Formulas, Enable iterative calculation (with a maximum number of iterations and a maximum change per step) lets Excel repeat the calculation until the values stop moving by more than that threshold:

Solving A1 = A1*0.5+10 by hand gives exactly 20; the sheet settles at 19.99969482 because it stops as soon as a step changes the value by less than the 0.001 limit, not at the mathematically exact answer. A tighter Max Change (or more iterations) gets closer still, at the cost of a slower recalculation.

Protecting cells, sheets and the workbook

Excel has protection at three different levels, and mixing them up is a common source of “why can I still edit this?” confusion. Every cell has a Locked property (checked by default), but Locked does nothing on its own – it only takes effect once the sheet is protected (Review, Protect Sheet). Starting from a sheet where A1 is left Locked and B1 has Locked switched off, then the sheet is protected:

That block is real, not cosmetic: the attempt to change A1 raised an actual error rather than silently doing nothing. This is the standard pattern for a shared template – unlock only the input cells before protecting, so reviewers can fill in numbers but cannot touch the formulas around them.

A second, separate level is protecting the workbook’s structure (Review, Protect Workbook), which stops sheets being added, deleted, renamed, moved or hidden – independently of whether any individual sheet is protected:

Both kinds of protection can optionally take a password, but the password is only a speed bump: it stops a casual user, not a determined one, since sheet and workbook protection are not encryption. Actual encryption is a separate option, File, Info, Protect Workbook, Encrypt with Password.

Preparing a workbook before you share it

Two Paste Special options come up constantly when a workbook is about to leave your hands. Paste Values keeps the numbers a formula produced but throws away the formula itself, which is useful when you want to send a snapshot that a reviewer’s own edits cannot silently break:

Transpose flips a copied row into a column (or a column into a row) on paste, which is faster than retyping data the other way round when a layout needs to change:

Beyond values and transpose, the same Paste Special dialog can paste Formulas only, Formats only, or combine a paste with an arithmetic Operation (Add, Subtract, Multiply, Divide) against what is already in the destination – useful for applying a flat percentage increase to a whole column of prices in one paste.

Collaboration: co-authoring, comments and Track Changes

Saving a workbook to OneDrive or SharePoint turns on co-authoring: more than one person can have the file open at once, and each person’s edits appear for the others within a few seconds, shown with a small coloured flag next to the cell someone else is editing. This only works for cloud-saved files opened in the desktop app, in Excel for the web, or in Excel Mobile – a file saved to a local or network drive falls back to the older “file is locked by another user” behaviour instead.

Track Changes (Review, Track Changes, Highlight Changes) is Excel’s older change-log feature, from before real-time co-authoring existed; it is largely superseded and, in current Excel, only appears once a workbook is put into the legacy Shared Workbook mode. For comparing two versions of a file that were edited separately rather than together, the free Spreadsheet Compare tool that ships with Microsoft 365 (search the Start menu for “Spreadsheet Compare”) lines up two workbooks cell by cell and lists every formula, value and formatting difference between them.

Common mistakes

Mistake 1: trusting a number in Manual calculation mode

A value that has not been recalculated since the last edit looks exactly like a correct one. If a workbook is in Manual mode, press F9 (or check the status bar, which shows “Calculate” when something is pending) before reading any result.

Mistake 2: locking cells without protecting the sheet

The Locked property does nothing by itself. A cell left “Locked” on an unprotected sheet can still be edited freely; protection only takes effect once Review, Protect Sheet is actually turned on.

Mistake 3: enabling iterative calculation to silence a circular reference warning

Iteration hides the warning, but it does not mean the formula is correct – a genuine typo that creates an accidental circular reference will now converge to some number instead of being flagged at all. Turn iteration on deliberately for a model that is meant to be circular (like the interest example above), not as a quick fix for a warning you have not investigated.

Mistake 4: assuming a password on a protected sheet is real security

Sheet and workbook protection passwords are meant to prevent accidental changes, not deliberate ones; they are not the same as encrypting the file. Anyone who genuinely needs the workbook secured should use File, Info, Encrypt with Password instead.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution.

1. A workbook is in Manual calculation mode. You type a new number into a cell that ten other formulas depend on, then immediately take a screenshot to send to a colleague. Is the screenshot guaranteed to show correct results?

Show solution

No. Manual mode means none of the ten dependent formulas update until F9 is pressed (or another recalculation is triggered); the screenshot may show every one of them still holding its previous value.

2. You protect a worksheet without unlocking any cells first. What happens when someone tries to type into any cell on that sheet?

Show solution

Every cell is blocked, because every cell’s Locked property defaults to checked and none were unlocked beforehand. Nothing on the sheet can be edited until it is unprotected, or specific cells are unlocked and it is protected again.

3. Cell A1 holds =B1+1 and B1 holds =A1, and iterative calculation is off. Roughly what would you expect A1 to show, and why is it not a “real” answer to the loop?

Show solution

B1 cannot resolve the loop and is held at 0, so A1 comes out to 0+1 = 1. That number does not satisfy A1 = A1+1 for any value, which is exactly why Excel treats an unresolved circular reference as an error rather than a computed result.

Frequently asked questions

What is the difference between Locked and Protected in Excel?

Locked is a property every individual cell has, checked by default. It has no effect until the worksheet itself is protected (Review, Protect Sheet); only then does Excel start blocking edits to the cells still marked Locked.

Why does my formula still show an old value after I changed its inputs?

The workbook is most likely in Manual calculation mode (Formulas, Calculation Options). Press F9 to recalculate, or switch back to Automatic.

Is it safe to turn on iterative calculation to fix a circular reference error?

Only if the circular reference is intentional, such as a model that is genuinely meant to converge to a fixed point. If the circular reference is actually a mistake, iteration will hide the warning and quietly converge to a wrong number instead of flagging the error.

What is the difference between a Comment and a Note in current Excel?

A Comment (the current default) is a threaded, reply-capable note meant for discussion between people. A Note is the older, single yellow sticky-note style with no reply thread, still available under Review, Notes, and still what older files and macros expect.

Does protecting a sheet with a password make the data secure?

No. Sheet and workbook protection passwords only prevent accidental edits through the normal interface; they are not encryption. To actually secure a file, use File, Info, Encrypt with Password.

Why did my colleague’s edit not show up immediately while co-authoring?

Real-time co-authoring only works for files saved to OneDrive or SharePoint and opened in the desktop app, Excel for the web, or Excel Mobile. A file on a local or shared network drive does not get live updates between editors.