How to copy and protect a sheet in Excel — the external link a copy can create, and locking all but a few cells

Copy a sheet — right-click the tab > Move or Copy > Create a copy

When you need the same layout again (next month, another team), copying the whole sheet is the quickest way. Right-click the sheet tab at the bottom and choose Move or Copy. In the dialog, pick where the copy goes under Before sheet ('(move to end)' for the last spot) and tick Create a copy at the bottom — without that tick the sheet is moved, not copied.

Copying the Nov sheet right after itself in the practice file created a sheet named 'Nov (2)' (our test copy in the Korean-language Excel was '11월 (2)', the same pattern). You can also hold Ctrl and drag the tab sideways; dragging without Ctrl moves it. Double-click the new tab to rename it — call it 'Dec'.

The copy kept formulas, formatting and column widths. C3's =IF(B3="O",Settings!$B$1,0) still pointed at the Settings sheet in the same file, the total was the same 40,000, and the widths stayed A 12 · B 10 · C 12 · D 22. Copying a range instead is different:

Trap 1 — copy a sheet to another workbook and its formulas point back to the original

Change To book in the Move or Copy dialog to (new book) or another open workbook and the sheet goes to a different file. That's where formulas that read another sheet cause trouble.

Copying only the Nov sheet to a new workbook gave a file with just that one sheet, and C3 had become =IF(B3="O",[fees.xlsx]Settings!$B$1,0) (our test workbook was named fees.xlsx; with the practice file it's [practice.xlsx]). The new file has no Settings sheet, so Excel added the original file's name in brackets — an external link (Excel also listed fees.xlsx as a linked source). The value still showed 40,000, so nothing looks wrong at first.

The new file is now tied to the original: move, delete or leave out the original and the formula loses what it reads. The fix is to copy the sheet it depends on as well. Click the Nov tab, Ctrl+click the Settings tab to select both, then Move or Copy to (new book). Both sheets arrived together, C3 stayed =IF(B3="O",Settings!$B$1,0), and there was no external link.

Protect a sheet — let people edit only some cells

Say you hand out the fee sheet and only want the Paid? column changed. The thing to know first: every cell starts out Locked (on this PC, both B3 and C3 reported Locked). Locking only takes effect once the sheet is protected — so the order is 'unlock the input cells, then protect the sheet'.

1. 1Select the input cells B3:B8, press Ctrl+1 (Format Cells) > Protection tab > clear Locked > OK. The same tab has Hidden — with it on, a cell's formula isn't shown once the sheet is protected.
2. 2Review > Protect Sheet > OK. A password is optional. By default only selecting locked and unlocked cells is allowed, so formatting, inserting rows and sorting are blocked (all three were off on this PC).

Trap 2 — sheet protection doesn't stop the sheet from being deleted

It's easy to assume a protected sheet is safe. In our test a protected sheet could still be renamed and deleted from the tab's right-click menu. Sheet protection guards cell contents; adding, deleting or moving sheets belongs to the workbook structure.

Turn on Review > Protect Workbook for the structure and all four were blocked while the sheet list (Nov · Dec · Settings) stayed as it was. When you hand a workbook out, combine the three: unlock the input cells, protect the sheet, protect the workbook.

Common snags

• The original sheet disappeared: Create a copy wasn't ticked, so it was moved — Ctrl+Z and try again
• The copied file's formulas have [file name] in them: you copied a sheet whose formulas read another sheet — copy that sheet too
• Pasted table has squashed columns: a range copy resets widths to 8.38 — copy the whole sheet
• After protecting, nobody can type anywhere: all cells are Locked by default — unlock the input cells before protecting
• The protected sheet got deleted: sheet protection doesn't cover that — Review > Protect Workbook

Practice

Open the practice file (a made-up book club, November fees) and try these steps.

1. 1Right-click the Nov tab > Move or Copy > tick Create a copy > OK, rename the copy 'Dec' and clear its Paid? cells (B3:B8).
2. 2Copy only the Nov sheet to (new book), look at C3's formula (the bracketed file name), then close it without saving.
3. 3On Nov and Dec, unlock B3:B8, turn on Hidden for C3:C9, then Review > Protect Sheet.
4. 4Change B5 to O and check the total becomes 50,000; try typing into C3 and see it refused.

The answer file has a Dec sheet copied from Nov (Paid? cleared); both sheets have only Paid? unlocked, Amount formulas hidden and sheet protection on with no password — Review > Unprotect Sheet opens them.

FAQ