{"id":469076,"date":"2022-06-29T21:00:43","date_gmt":"2022-06-29T18:00:43","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-lock-formulas-in-microsoft-excel\/"},"modified":"2022-06-29T21:00:43","modified_gmt":"2022-06-29T18:00:43","slug":"how-to-lock-formulas-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-lock-formulas-in-microsoft-excel\/","title":{"rendered":"#How to Lock Formulas in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a3584b0ef94a\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a3584b0ef94a\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-lock-formulas-in-microsoft-excel\/#%E2%80%9CHow_to_Lock_Formulas_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Lock Formulas in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-lock-formulas-in-microsoft-excel\/#How_Do_You_Lock_the_Cells_Containing_Formulas_in_Excel\" >How Do You Lock the Cells Containing Formulas in Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-lock-formulas-in-microsoft-excel\/#Lock_a_Formula_Cell_in_Excel\" >Lock a Formula Cell in Excel<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Lock_Formulas_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Lock Formulas in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731027\" data-pagespeed-no-defer=\"\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel Logo,\" width=\"1200\" height=\"675\"\/><\/p>\n<p>To avoid getting your formulas messed up, you can lock the cells that contain formulas while keeping all other cells unlocked in your Microsoft Excel spreadsheets. We\u2019ll show you how to do just that.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Lock Cells in Microsoft Excel to Prevent Editing<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_Do_You_Lock_the_Cells_Containing_Formulas_in_Excel\"><\/span>How Do You Lock the Cells Containing Formulas in Excel?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>By default, when you <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/357597\/how-to-protect-cells-from-editing-in-microsoft-excel\/\">protect your worksheet, Excel locks all the cells in your sheet and not just the ones containing formulas. To get around that, you\u2019ll have to first unlock all your cells, select the cells containing formulas, and then lock these cells with formulas.<\/p>\n<p>This way, users can edit the values of all cells in your worksheet except for the ones that have formulas in them.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Lock_a_Formula_Cell_in_Excel\"><\/span>Lock a Formula Cell in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To avoid getting your formula cells altered, first, launch your spreadsheet with Microsoft Excel.<\/p>\n<p>In your spreadsheet, select all cells by pressing Ctrl+A (Windows) or Command+A (Mac). Then right-click any one cell and choose \u201cFormat Cells.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810722\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/1-format-all-cells.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose &quot;Format Cells&quot; from the menu.\" width=\"650\" height=\"403\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>On the \u201cFormat Cells\u201d window, from the top, select the \u201cProtection\u201d tab. Then disable the \u201cLocked\u201d option and click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810723\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/2-unlock-all-cells.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"505\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>All cells in your worksheet are now unlocked. To now lock the cells that contain formulas, first, select all these cells.<\/p>\n<p>To do that, in <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/714701\/how-to-switch-back-to-the-classic-ribbon-in-microsoft-office\/\">Excel\u2019s ribbon at the top, click the \u201cHome\u201d tab. Then, from the \u201cEditing\u201d section, choose Find &amp; Select &gt; Go To Special.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810724\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/3-excel-go-to-special.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select Find &amp; Select &gt; Go To Special.\" width=\"617\" height=\"433\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the \u201cGo To Special\u201d box, enable the \u201cFormulas\u201d option and click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810725\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/4-select-all-formula-cells.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"418\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In your spreadsheet, Excel has highlighted all the cells containing formulas. To now lock these cells, right-click any one of these cells and choose \u201cFormat Cells.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810726\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/5-format-formula-cells.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"636\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>On the \u201cFormat Cells\u201d window, access the \u201cProtection\u201d tab. Then enable the \u201cLocked\u201d option and click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810727\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/6-lock-formula-cells.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"505\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The cells containing formulas in your worksheet are now locked. To prevent their modification, from Excel\u2019s ribbon at the top, select the \u201cReview\u201d tab.<\/p>\n<p>In the \u201cReview\u201d tab, click the \u201cProtect Sheet\u201d option.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810728\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/7-protect-excel-sheet.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select &quot;Protect Sheet.&quot;\" width=\"650\" height=\"339\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll see a \u201cProtect Sheet\u201d box. Here, optionally, enter a password in the \u201cPassword to Unprotect Sheet\u201d field. Then click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810729\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/8-enter-protection-password.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"293\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you used a password in the previous step, then in the \u201cConfirm Password\u201d box that opens, re-enter that password and click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810730\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/9-re-enter-protection-password.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"470\" height=\"259\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>And that\u2019s it. All the cells containing formulas in your worksheet are now locked. If you or someone else attempts to alter the contents of these cells, Excel will display an error message.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810731\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/10-excel-error-locked-cells.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Excel's error message for attempting to alter locked cells.\" width=\"650\" height=\"103\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Later, to allow users to edit these formula cells, then unprotect your worksheet by accessing the \u201cReview\u201d tab and choosing \u201cUnprotect Sheet.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-810732\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/06\/11-unprotect-excel-sheet.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose &quot;Unprotect Sheet.&quot;\" width=\"650\" height=\"321\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>And that\u2019s how you avoid getting your formulas messed up in your Excel spreadsheets. Very useful!<\/p>\n<hr\/>\n<p>Don\u2019t want your charts moved around in Excel? If so, there\u2019s a way to lock the position of your charts in this spreadsheet program. Check out our guide to learn how to use it.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Lock the Position of a Chart in Excel<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/810715\/lock-formulas-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Lock Formulas in Microsoft Excel&#8221; To avoid getting your formulas messed up, you can lock the cells that contain formulas while keeping all other cells unlocked in your Microsoft Excel spreadsheets. We\u2019ll show you how to do just that. RELATED: How to Lock Cells in Microsoft Excel to Prevent Editing How Do You&#8230;<\/p>\n","protected":false},"author":1,"featured_media":469077,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-469076","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/469076","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=469076"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/469076\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/469077"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=469076"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=469076"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=469076"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}