Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe formules in Excel te vergrendelen en te beveiligen?

Wanneer u een werkblad maakt, moet u soms enkele formules gebruiken en wilt u niet dat andere gebruikers de formules wijzigen, bewerken of verwijderen. De eenvoudigste en meest gebruikelijke manier om te voorkomen dat mensen met uw formules spelen, is door cellen met formules in uw werkblad te vergrendelen en te beschermen. Ga als volgt naar het slot en bescherm de formules:

Vergrendel en bescherm formules met de functies Cellen opmaken en Blad beveiligen

Vergrendel en bescherm formules met Worksheet Design goed idee 3

pijl blauw rechts bel Vergrendel en bescherm formules met de functies Cellen opmaken en Blad beveiligen

Standaard zijn alle cellen op een werkblad vergrendeld, dus u moet eerst alle cellen ontgrendelen.

1. Selecteer het hele werkblad met Ctrl + A, en klik met de rechtermuisknop, kies Cellen opmaken vanuit het contextmenu.

2. En een Cellen opmaken dialoogvenster verschijnt. Klik Bescherming, en vinkte de Vergrendeld keuze. Klik OK. Het hele werkblad is ontgrendeld.

doc-protect-formules

3. Dan klikken Voorpagina > Zoek en selecteer > Ga naar speciaal, en Ga naar speciaal dialoogvenster verschijnt. Controleren Formules van kies optie en klik vervolgens op OK. Zie screenshot:

doc-protect-formules

4. Alle cellen die formules bevatten, worden geselecteerd.

5. Ga vervolgens naar de geselecteerde cellen vergrendelen. Klik met de rechtermuisknop op de geselecteerde cellen en kies Cellen opmaken vanuit het contextmenu, en een Cellen opmaken dialoogvenster wordt weergegeven. Klik Bescherming, controleer de Vergrendeld selectievakje. Zie screenshot:
doc-protect-formules

6. En klik dan Review > Bescherm het bladEn een Bescherm het blad dialoogvenster verschijnt, u kunt het wachtwoord invoeren in het Wachtwoordom het blad onbeschermd te maken doos. Zie screenshot:

doc-protect-formules

7. Dan klikken OK. En een ander Bevestig wachtwoord dialoogvenster verschijnt. Voer je wachtwoord opnieuw in. En klik OK.

doc-protect-formules

En dan zijn alle cellen die formules bevatten vergrendeld en beschermd. In dit geval kunt u de formules niet wijzigen, maar u kunt wel andere cellen bewerken.


pijl blauw rechts bel Vergrendel en bescherm formules met Worksheet Design

Als je hebt geïnstalleerd Kutools for Excel, kunt u formules snel vergrendelen en beveiligen met Werkbladontwerp utility.
Kutools for Excel, met meer dan 300 handige functies, maakt uw werk eenvoudiger. 

Na gratis installeren Kutools voor Excel, doe het als volgt:

1. klikken Kutools Plus > Werkbladontwerp om de Design groep. Zie screenshot:
doc protect formules 1

2. Dan klikken Markeer formules om alle formulecellen te markeren. Zie screenshot:
doc kutools ontwerp 2

3.Selecteer alle gemarkeerde cellen en klik op Selectie vergrendelen om de formules te vergrendelen. En er komt een dialoogvenster tevoorschijn om u eraan te herinneren dat de fomula's pas kunnen worden vergrendeld als het vel wordt beschermd. Zie screenshots:
doc kutools ontwerp 3

4. Klik nu Bescherm het blad om het wachtwoord te typen om het blad te beschermen. Zie screenshots:
doc kutools ontwerp 4

Opmerking:

1. Nu zijn de formules vergrendeld en beveiligd en kunt u klikken Ontwerp sluiten om het Design Tab.

2. Als u de beveiliging van het vel wilt opheffen, hoeft u alleen maar op te klikken Werkbladontwerp > Maak het blad onbeschermd.
doc kutools ontwerp 5

In de werkbladontwerpgroep kunt u de ontgrendelcel, het naambereik enzovoort markeren.



De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
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 · 3 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 · 4 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 · 4 years ago
    very useful good explanation thanks
  • To post as a guest, your comment is unpublished.
    Heiko · 4 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 · 4 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 · 4 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 · 4 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 · 5 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 · 5 years ago
    asdlkfjsdlk alsdkjflas fka klsdlfkjasdl adjlasdfkj lasdf
  • To post as a guest, your comment is unpublished.
    raj · 6 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 · 6 years ago
    Awesome Tut
    and well explanation
    Thanks for this help
  • To post as a guest, your comment is unpublished.
    shahzada umer · 6 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 · 6 years ago
    Good and clear explanation
  • To post as a guest, your comment is unpublished.
    Ani · 6 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 · 6 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 · 6 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 · 6 years ago
    Thank you very much,
    this is of great help!
  • To post as a guest, your comment is unpublished.
    Nyiko · 7 years ago
    Thank you. Finally, i got the answer
  • To post as a guest, your comment is unpublished.
    Jamie · 7 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 · 7 years ago
    Awesome description !

    Very helpful, thank you so very much!!Blessings !
  • To post as a guest, your comment is unpublished.
    measbunna · 7 years ago
    Thanks very helpful for me. :)
    Best regards
  • To post as a guest, your comment is unpublished.
    firdoush alam · 7 years ago
    Thanks..satisfied, is there any possibilities of querry through e-mail.
  • To post as a guest, your comment is unpublished.
    Clau · 7 years ago
    Very helpful, thank you so very much!!Blessings!
  • To post as a guest, your comment is unpublished.
    Yu wai · 7 years ago
    Thank a lot.
    It is convience for all.
  • To post as a guest, your comment is unpublished.
    Yu wai · 7 years ago
    Thanks a lot..
    Glad to know to lock the cells
  • To post as a guest, your comment is unpublished.
    sexy_ella · 7 years ago
    hi...thank you so much...this was HELPFUL...... :)
  • To post as a guest, your comment is unpublished.
    Nato · 7 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 · 7 years ago
    Thank you! it was helpful.

    Ali
  • To post as a guest, your comment is unpublished.
    Peter Fisher · 7 years ago
    Thanks very much. This was most useful.
  • To post as a guest, your comment is unpublished.
    Muhammad Tahir · 7 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 · 7 years ago
    Protect Formulas in Excel Spreadsheet
  • To post as a guest, your comment is unpublished.
    Omen · 7 years ago
    it is very usefully thanks
  • To post as a guest, your comment is unpublished.
    GG · 7 years ago
    The BEST explanation I've found to do
    This. Thank you!!
  • To post as a guest, your comment is unpublished.
    DEEPAK KHANAL · 7 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 · 7 years ago
    ;-) thank you very much... :-)
  • To post as a guest, your comment is unpublished.
    Umer · 7 years ago
    only one cell like to lock which kept the formula
  • To post as a guest, your comment is unpublished.
    raja · 7 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 · 7 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 · 7 years ago
    thanks alot it really helped
  • To post as a guest, your comment is unpublished.
    JOSHY BOY · 7 years ago
    thank you this really helped :lol:
  • To post as a guest, your comment is unpublished.
    RIJESH · 7 years ago
    :roll: NICE EXPLANATION GOOD ONE....
    • To post as a guest, your comment is unpublished.
      Howard Walker · 4 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 · 7 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 · 8 years ago
    Excel protection for the PC
  • To post as a guest, your comment is unpublished.
    Tyler Stein · 8 years ago
    Thank you, this was helpful!
    • To post as a guest, your comment is unpublished.
      brong · 4 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 ?