提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何锁定和保护Excel中的公式?

在创建工作表时,有时需要使用某些公式,而不希望其他用户更改,编辑或删除公式。 防止用户使用公式的最简单和最常用的方法是锁定和保护工作表中包含公式的单元格。 请去锁定和保护公式如下:

使用格式化单元格和保护工作表功能来锁定和保护公式

使用工作表设计锁定和保护公式 好idea3

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。

箭头蓝色右泡 使用格式化单元格和保护工作表功能来锁定和保护公式

默认情况下,工作表上的所有单元格都被锁定,所以您必须先解锁所有单元格。

1。 选择整个工作表 按Ctrl + A,然后右键单击,选择 单元格格式 从上下文菜单。

2。 和一个 单元格格式 对话框将弹出。 点击 保护,并取消选中 锁定 选项。 点击 OK。 整个工作表已被解锁。

文档保护,formulas1

3。 然后点击 主页 > 查找和选择 > 去特别, 去特别 对话框将会出现。 检查 公式 低至 选择 选项,然后单击 OK。 看截图:

文档保护,formulas2

4。 所有包含公式的单元格将被选中。

5。 然后去锁定选择的单元格。 右键单击所选单元格,然后选择 单元格格式 从上下文菜单,和一个 单元格格式 对话框将会显示。 点击 保护, 检查 锁定 复选框。 看截图:
文档保护,formulas3

6。 然后点击 评论 > 保护工作表和一个 保护工作表 对话框将弹出,您可以在中输入密码 密码解除表单 框。 看截图:

文档保护,formulas4

7。 然后点击 OK。 而另一个 确认密码 对话框将会出现。 重新输入您的密码。 然后点击 OK.

文档保护,formulas5

然后包含公式的所有单元格都被锁定和保护。 在这种情况下,您不能修改公式,但可以编辑其他单元格。


箭头蓝色右泡 使用工作表设计锁定和保护公式

如果你已经安装 Kutools for Excel,您可以使用快速锁定和保护配方 工作表设计 效用。
Kutools for Excel, 与超过 300 方便的功能,让您的工作更轻松。

安装后 Kutools for Excel,请按照以下步骤操作:(免费下载Kutools for Excel!)

1。 点击 企业服务 > 工作表设计 使之成为可能 设计 组。 看截图:

doc保护公式1

doc箭头

doc保护公式2

2。 然后点击 突出显示公式 突出显示所有公式单元格。 看截图:
doc保护公式3

3.选择所有突出显示的单元格并单击 选择锁定 锁定公式。 弹出一个对话框提醒您,只有在保护表格之后才能锁定公式。 查看截图:

doc保护公式4doc保护公式5

4。 现在点击 保护工作表 键入密码以保护表单。 查看截图:
doc保护公式6

注意

1。 现在公式被锁定和保护,您可以点击 关闭设计 禁用 设计 标签。

2。 如果你想取消保护表,你只需要点击 工作表设计 > 取消保护表.

在“工作表设计”组中,可以突出显示解锁单元格,名称范围等。


选项卡式浏览和编辑多个Excel工作簿/ Word文档,如Firefox,Chrome,Internet浏览10!

您可能熟悉在Firefox / Chrome / IE中查看多个网页,并通过轻松单击相应的选项卡在它们之间切换。 此处,Office选项卡支持类似的处理,允许您在一个Excel窗口或Word窗口中浏览多个Excel工作簿或Word文档,并通过单击其选项卡轻松切换它们。 单击免费获得Office Tab的30天试用!

擅长

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Imadi · 1 years ago
    Useful guide, however, I don't think if this is responding to my need. to explain a bit of my need I wanted to lock cells with formulas and formats but I want the formulas to generate what they are supposed to do. i.e. I have VLOOKUP formula which I want to vlookup value from another sheet based on the ID so when I add the unique ID the VLOOKUP formula doesn't return since the cell is locked.. in short, I want my formulas to work but don't allow other people to modify and delete the formulas!

    Much appreciated any clear guidelines
  • To post as a guest, your comment is unpublished.
    Junior · 2 years ago
    Hi
    I would like to protect cells that has formulas but when I lock the sheet with a password, I cannot group and un-group cells that are grouped.
    How do I protect the sheet and still have the functionality to group and un-group.
    I have inserted the following VB code:

    Sub EnableOutlining()
    'Update 20140603
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    Dim xPws As String
    xPws = Application.InputBox("Password:", xTitleId, "", Type:=2)
    xWs.Protect Password:=xPws, Userinterfaceonly:=True
    xWs.EnableOutlining = True
    End Sub

    It works but when I open and close the file I need to run the code each time.
    Is there a code where this is not required?
    Thanks
  • To post as a guest, your comment is unpublished.
    Srikanth C · 2 years ago
    very useful good explanation thanks
  • To post as a guest, your comment is unpublished.
    Heiko · 2 years ago
    This is the only page that showed step 5. And all my formulas are protected. But how do I protect formulas AND all the text I put in. The Workbook I created is an invoice ledger. So I have cells with text, that is not protected. All I want them to do is fill in the ledger with the purchase dollars. Not to be able to change category names, or pager titles.
  • To post as a guest, your comment is unpublished.
    Philip Hales · 2 years ago
    This locks the cell with the formula, but also is preventing changing FONT colour and Strikethrough etc?
  • To post as a guest, your comment is unpublished.
    Philip Hales · 2 years ago
    Have gone through this half a dozen times, yes! it is locking cells containing formula, but all formatting is greyed out.

    I have an Allocation Sheet, which has DAYS and NIGHTS on if we do DAYS Nights formula is strikethrough, and vice versa, but change DAYS to NIGHTS, but not from strikethrough!
    Cannot change FONT colour either.
  • To post as a guest, your comment is unpublished.
    Lisa · 2 years ago
    Thanks! This was a massive help, I should have turned to you straight away instead of battling on for hours!
  • To post as a guest, your comment is unpublished.
    pat · 3 years ago
    What I need is a way to use 2 different passwords on a shared worksheet. I need to be able to lock my formulas and then when someone puts in the data they need to lock and protect with another password. Is this possible?
  • To post as a guest, your comment is unpublished.
    XZa · 3 years ago
    asdlkfjsdlk alsdkjflas fka klsdlfkjasdl adjlasdfkj lasdf
  • To post as a guest, your comment is unpublished.
    raj · 3 years ago
    si want to freez the formula in one sheet of the book , like daly am receiving the files day wise as summry-15 & summry-16, i need certain summry so i made summry in one sheet (in the same book) using vlookup & hlookup ,but when am pasting the formula in summry-16 formula is taking the data from suury15 only i need formula has to take from the current book only(which my summry sheet part of the book).
  • To post as a guest, your comment is unpublished.
    Amir Muzaffar · 3 years ago
    Awesome Tut
    and well explanation
    Thanks for this help
  • To post as a guest, your comment is unpublished.
    shahzada umer · 4 years ago
    i want to know about how to lock the cell in excell sheet. that nobody intrupt the specific cell figure.
  • To post as a guest, your comment is unpublished.
    accountant · 4 years ago
    Good and clear explanation
  • To post as a guest, your comment is unpublished.
    Ani · 4 years ago
    Hello I tried This But my other cells are also locked which dont have any formula
    kindly help.
  • To post as a guest, your comment is unpublished.
    Omar · 4 years ago
    Hi if i protect the formula in excel before send it by email the person who recive the email can use the sheet with formulas or not??
  • To post as a guest, your comment is unpublished.
    Natasha · 4 years ago
    In step 5, user should be selecting Locked AND Hidden in order for the formula not to show and be able to be edited by others without changing formula.
  • To post as a guest, your comment is unpublished.
    SA · 4 years ago
    Thank you very much,
    this is of great help!
  • To post as a guest, your comment is unpublished.
    Nyiko · 4 years ago
    Thank you. Finally, i got the answer
  • To post as a guest, your comment is unpublished.
    Jamie · 5 years ago
    I want to have users be able to edit column 1 and column 2 but not be able to edit anything in column 3 (the formula) yet have it add up. I would like to lock only the column with the formula. When I do this it does not allow me to edit the other cells (which the formula adds up).Please help!
  • To post as a guest, your comment is unpublished.
    Shyam Sunder Singh · 5 years ago
    Awesome description !

    Very helpful, thank you so very much!!Blessings !
  • To post as a guest, your comment is unpublished.
    measbunna · 5 years ago
    Thanks very helpful for me. :)
    Best regards
  • To post as a guest, your comment is unpublished.
    firdoush alam · 5 years ago
    Thanks..satisfied, is there any possibilities of querry through e-mail.
  • To post as a guest, your comment is unpublished.
    Clau · 5 years ago
    Very helpful, thank you so very much!!Blessings!
  • To post as a guest, your comment is unpublished.
    Yu wai · 5 years ago
    Thank a lot.
    It is convience for all.
  • To post as a guest, your comment is unpublished.
    Yu wai · 5 years ago
    Thanks a lot..
    Glad to know to lock the cells
  • To post as a guest, your comment is unpublished.
    sexy_ella · 5 years ago
    hi...thank you so much...this was HELPFUL...... :)
  • To post as a guest, your comment is unpublished.
    Nato · 5 years ago
    Dear author, thank you very much for such a useful guide.
    Please, advise if it is possible to lock the formula cells in excel so that it is possible to delete other bulk data from the file without deleting the formulas? With the method above, it is not possible to select whole data with ctrl+A and delete.

    Thank you!
  • To post as a guest, your comment is unpublished.
    Ali Khan · 5 years ago
    Thank you! it was helpful.

    Ali
  • To post as a guest, your comment is unpublished.
    Peter Fisher · 5 years ago
    Thanks very much. This was most useful.
  • To post as a guest, your comment is unpublished.
    Muhammad Tahir · 5 years ago
    It is wonderful guide, I have ever seen.
    Thanks so much for saving my hardwork.
  • To post as a guest, your comment is unpublished.
    Marty · 5 years ago
    Protect Formulas in Excel Spreadsheet
  • To post as a guest, your comment is unpublished.
    Omen · 5 years ago
    it is very usefully thanks
  • To post as a guest, your comment is unpublished.
    GG · 5 years ago
    The BEST explanation I've found to do
    This. Thank you!!
  • To post as a guest, your comment is unpublished.
    DEEPAK KHANAL · 5 years ago
    After long period i found my problem i.e. locking/protection single sell on excel. Thank you very much www.extendoffice.com
  • To post as a guest, your comment is unpublished.
    sanjaya · 5 years ago
    ;-) thank you very much... :-)
  • To post as a guest, your comment is unpublished.
    Umer · 5 years ago
    only one cell like to lock which kept the formula
  • To post as a guest, your comment is unpublished.
    raja · 5 years ago
    I want to lock the formula cell but it has use to drop and copy
  • To post as a guest, your comment is unpublished.
    vinay4125 · 5 years ago
    Hi, please help me out, i have 3 columns (Ticket Number, Site ID, Time) i need to copy Site ID and Time according to the Ticket Number from 1st Excel sheet to 2nd excel sheet

    For Example : I Have Ticket Number 425665 with Three Site ID's UW_GJ_2904 , UW_GJ_1995 , UW_GJ_0960 , So i need to copy all three site id's from 1st Excel sheet to 2nd excel sheet .... But as per vlookup only first site ID is Reflecting UW_GJ_2904 is Reflecting in all three places, which it should not(in some cases for single ticket Ex: 425771 : only one site UW_GJ_0514 will be there, it was reflecting properly, The problem is for each ticket which has more than one site ID is not reflecting properly)

    First Excel Sheet :
    Ticket Site id time
    425665 UW_GJ_2904 1/21/14 4:51 PM
    425665 UW_GJ_1995 1/21/14 4:51 PM
    425665 UW_GJ_0960 1/21/14 4:51 PM

    Second Excel Sheet:
    ticket site id time
    425665 UW_GJ_2904 1/21/14 4:51 PM
    425665 UW_GJ_2904 1/21/14 4:51 PM
    425665 UW_GJ_2904 1/21/14 4:51 PM

    if it is only one id its Reflecting properly
    First excel sheet:

    425771 UW_GJ_0514 1/21/14 7:44 PM

    Second Excel Sheet:

    425771 UW_GJ_0514 1/21/14 7:44 PM
  • To post as a guest, your comment is unpublished.
    JOSHY BOY · 5 years ago
    thanks alot it really helped
  • To post as a guest, your comment is unpublished.
    JOSHY BOY · 5 years ago
    thank you this really helped :lol:
  • To post as a guest, your comment is unpublished.
    RIJESH · 5 years ago
    :roll: NICE EXPLANATION GOOD ONE....
    • To post as a guest, your comment is unpublished.
      Howard Walker · 2 years ago
      [quote name="RIJESH"]:roll: NICE EXPLANATION GOOD ONE....[/quote]
      Well it looks good, but I have followed it at least 6 times and each time it does not lock any of my formulaes.
  • To post as a guest, your comment is unpublished.
    Karl · 5 years ago
    :-) Great that really helps. My errant pen mouse would sometimes scrape the cell box and the formula would pop up, and not knowing, sometimes I had changed it. Caused many headaches while doing banking in the totals column. thanks again.
  • To post as a guest, your comment is unpublished.
    George Coyle · 5 years ago
    Excel protection for the PC
  • To post as a guest, your comment is unpublished.
    Tyler Stein · 6 years ago
    Thank you, this was helpful!
    • To post as a guest, your comment is unpublished.
      brong · 2 years ago
      when i use formula of excel, but i do not want another people see the formula of me. so how to do it ?