{"id":2817,"date":"2026-10-23T09:18:25","date_gmt":"2026-10-22T22:18:25","guid":{"rendered":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?p=2817"},"modified":"2026-10-23T09:18:28","modified_gmt":"2026-10-22T22:18:28","slug":"how-to-lock-cells-in-excel","status":"publish","type":"post","link":"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-lock-cells-in-excel","title":{"rendered":"How to Lock Cells in Excel (and Protect Formulas)"},"content":{"rendered":"\n

To lock cells in Excel, select the whole sheet, press Ctrl + 1, and on the Protection tab untick Locked. Then select only the cells you want to lock, press Ctrl + 1 again and tick Locked. Finally, go to Review > Protect Sheet, choose what users can still do, add a password if you want one, and click OK. Locked cells can no longer be edited until you go to Review > Unprotect Sheet.<\/strong><\/p>\n\n\n\n\n\n\n\n

Locking cells stops people (including you) from accidentally typing over formulas, headings or reference data. It is one of the simplest ways to make a shared spreadsheet safer. This guide shows you how to lock specific cells, protect only your formulas, unlock cells again, and how locking a cell reference in a formula (with $) is a different thing altogether.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

What does locking cells in Excel do?<\/h2>\n\n\n\n

Every cell in a new worksheet already has the Locked<\/strong> setting turned on, but it does nothing until the sheet is protected. Locking is a two-part process: first you decide which cells are locked or unlocked, then you switch protection on with Protect Sheet<\/strong>. Once the sheet is protected, locked cells cannot be edited, and unlocked cells stay open for data entry.<\/p>\n\n\n\n

How do you lock specific cells in Excel?<\/h2>\n\n\n\n

Because every cell starts out locked, you unlock the whole sheet first, then lock only the cells you want to protect:<\/p>\n\n\n\n

    \n
  1. Click the Select All<\/strong> triangle (top left of the grid) or press Ctrl + A<\/strong>.<\/li>\n\n\n\n
  2. Press Ctrl + 1<\/strong> to open Format Cells<\/strong> and go to the Protection<\/strong> tab.<\/li>\n\n\n\n
  3. Untick Locked<\/strong> and click OK<\/strong>. Every cell is now unlocked.<\/li>\n\n\n\n
  4. Select the cells you want to lock (for example your formulas and headings).<\/li>\n\n\n\n
  5. Press Ctrl + 1<\/strong>, tick Locked<\/strong> on the Protection<\/strong> tab and click OK<\/strong>.<\/li>\n\n\n\n
  6. Protect the sheet (next section).<\/li>\n<\/ol>\n\n\n\n
    Ctrl + 1 > Protection tab. Untick Locked for input cells, tick it for cells you want to protect.<\/figcaption><\/figure>\n\n\n\n

    How do you protect the sheet so locked cells cannot be changed?<\/h2>\n\n\n\n
      \n
    1. Go to the Review<\/strong> tab and click Protect Sheet<\/strong>.<\/li>\n\n\n\n
    2. Under Allow all users of this worksheet to<\/strong>, choose what people can still do. Leave Select locked cells<\/strong> and Select unlocked cells<\/strong> ticked so they can still click around; tick extras such as Format columns<\/strong> or Sort<\/strong> only if needed.<\/li>\n\n\n\n
    3. Optionally type a password, then click OK<\/strong> (and confirm the password).<\/li>\n<\/ol>\n\n\n\n

      Now try typing in a locked cell: Excel shows a message saying the cell is protected. Unlocked cells still accept data as normal.<\/p>\n\n\n\n

      \"The
      Review > Protect Sheet switches locking on. Choose what users can still do, and add a password if needed.<\/figcaption><\/figure>\n\n\n\n

      A word of caution on passwords: Excel cannot recover a lost sheet password, so store it somewhere safe. Sheet protection is also designed to prevent accidental changes, not to secure confidential data.<\/p>\n\n\n\n

      How do you lock only the formulas in Excel?<\/h2>\n\n\n\n

      Instead of selecting formula cells one by one, let Excel find them for you:<\/p>\n\n\n\n

        \n
      1. Unlock the whole sheet first (Ctrl + A, Ctrl + 1, Protection, untick Locked<\/strong>).<\/li>\n\n\n\n
      2. Go to Home > Find & Select > Go To Special<\/strong>, choose Formulas<\/strong> and click OK<\/strong>. Excel selects every formula on the sheet.<\/li>\n\n\n\n
      3. Press Ctrl + 1<\/strong>, tick Locked<\/strong> (and Hidden<\/strong> if you also want to hide the formulas from the formula bar) and click OK<\/strong>.<\/li>\n\n\n\n
      4. Go to Review > Protect Sheet<\/strong> and click OK<\/strong>.<\/li>\n<\/ol>\n\n\n\n
        \"The
        Home > Find & Select > Go To Special > Formulas selects every formula, ready to lock in one step.<\/figcaption><\/figure>\n\n\n\n

        How do you unlock cells or unprotect a sheet in Excel?<\/h2>\n\n\n\n

        Go to Review > Unprotect Sheet<\/strong> and enter the password if one was set. All cells can be edited again. To permanently open up particular cells, select them, press Ctrl + 1<\/strong>, untick Locked<\/strong> on the Protection<\/strong> tab, then protect the sheet again.<\/p>\n\n\n\n

        How do you lock a cell reference in an Excel formula?<\/h2>\n\n\n\n

        This is a different meaning of “lock”. When you copy a formula, Excel adjusts cell references automatically. To stop a reference changing, make it absolute with dollar signs: $A$1<\/strong> locks both the column and the row. Click the reference in the formula bar and press F4<\/strong> to cycle through $A$1, A$1<\/strong> (row only), $A1<\/strong> (column only) and A1<\/strong>.<\/p>\n\n\n\n

        For example, =B2*$E$1<\/strong> multiplies each value in column B<\/strong> by the rate in E1<\/strong>. When you fill it down, B2<\/strong> becomes B3<\/strong>, B4<\/strong> and so on, but $E$1<\/strong> stays fixed.<\/p>\n\n\n\n

        What is the difference between locking cells and protecting a workbook?<\/h2>\n\n\n\n