{"id":965,"date":"2020-02-16T21:10:36","date_gmt":"2020-02-16T13:10:36","guid":{"rendered":"http:\/\/excelchamp.net\/?p=965"},"modified":"2022-07-11T06:27:38","modified_gmt":"2022-07-10T22:27:38","slug":"protect-cells-from-any-unauthorized-changes-in-excel","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/","title":{"rendered":"Protect Cells from Any Unauthorized Changes in Excel"},"content":{"rendered":"

Protecting your Excel Files from any unauthorized changes is crucial.<\/strong><\/p>\n

When you email or share your Excel files with others, there are always chances that they may change some key information, that shouldn’t have been changed.<\/p>\n

This can happen because, by default, all cells in Excel are available for modifications by anyone<\/strong> having access to the Excel file.<\/p>\n

But the strange thing is that if you were to check Excel’s cell protection setting under Format Cells<\/strong>, you’d find that each cell is marked as Locked<\/strong>.\u00a0 However, Microsoft is quick to inform you that<\/p>\n

Locking cells or hiding formulas has no effect until you protect the sheet.<\/p><\/blockquote>\n

Quite a strange setting, I believe. However, you can use this setting to your advantage.<\/p>\n

Scenario:<\/strong> Preparing a Protected Travel Expense Claim Form in Excel.<\/h2>\n

Let’s say you are creating a Travel Claim Form for all staff to use in the company when they use their own transport for office meetings or customer visits.<\/p>\n

You’d only want the staff to key in their travel expenses, dates, and the type of expenses. You wouldn’t want them to modify the rate which you use to calculate the claim amount. However, you would still want yourself or HR manager to be able to adjust the rate periodically, based on the policy.<\/strong><\/p>\n

To achieve this, you will have to create an Excel Spreadsheet, with careful consideration of the data protection. Your Excel file must have:<\/p>\n

    \n
  1. Some cells are protected completely (no changes allowed by anyone)<\/strong> These could be formulas and any master data like names of departments, cities etc. More like master data that does not change often.<\/li>\n
  2. Some cells that can only be modified by you or HR.<\/strong> These could be the Rate of reimbursement etc. You could even set a password to allow only authorized people with the right password to key in this information.<\/li>\n
  3. Some cells that anyone can modify.<\/strong> These would be the cells where the staff is going to key in their expenses. Mostly, such cells would be the ones accepting the data entry values only.<\/li>\n<\/ol>\n

    Here are the detailed steps that you can take to protect the information and make a spreadsheet that everyone can use, without fear of getting inadvertent changes to the file, and maintaining full privacy and security.<\/p>\n

    Step 1: Unlock all Cells in your Excel Template.<\/h3>\n

    To do this, Select All Cells (you can press Control A<\/strong> or press the small rectangle above row 1 on the top-left area of the spreadsheet. Once all cells are selected, right-click to activate the menu, and go to Format Cells. Shortcut is Control 1<\/strong> for this option.<\/p>\n

    Now move to the Protection tab, and uncheck the Locked checkbox<\/strong>.<\/p>\n

    Click OK<\/strong> and come out of this popup. Now all cells are unlocked.<\/p>\n

    Step 2: Lock the Calculations & Totals Row\/Columns<\/h3>\n

    Select the cells that have any calculations. To select multiple cells, highlight them, and hold the Control key to multi-select several formula and total ranges<\/strong>.<\/p>\n

    Now go to Format Cells<\/strong> by right-clicking, or press Control 1<\/strong> again. This time, you can check both checkboxes on the Protection Tab. Lock and Hide the formulas and cells completely.<\/strong><\/p>\n

    Step 3: Select Cells That can only be modified by authorized Users<\/h3>\n

    Finally, it is time to select the cells that need to be protected from unauthorized changes. So select those cells, again by using the Control Key to multi-select the cells.<\/p>\n

    Once the cells are selected, go to Format Cells > Protection Tab, and Lock them. Click OK.<\/p>\n

    Step 4: Set a Password to cells that only an Authorized User can modify.<\/h3>\n

    Go to Review<\/strong> Tab on the Excel menubar. Select Allow Users to Edit Ranges<\/strong>, from the Changes group<\/strong> on this menu.<\/p>\n

    Click New<\/strong>, and key in a name for the cell range. Set a password<\/strong> that will be required to be keyed in whenever anyone tries to change such cells. You have to key it in 2 times to confirm the password.<\/p>\n

    Step 5: Protect the Sheet<\/h3>\n

    The previous 4 steps will have no effect until you protect the entire sheet.<\/p>\n

    To do so, go to Review > Protect Sheet.<\/strong><\/p>\n

    You can then, optionally, set a password. Although I say optionally, I’d recommend that you definitely do it as a best practice. otherwise, any user can modify your template, simply by unprotecting the worksheet, make any change, and protect it again.<\/p>\n

    You will be required to key in the password twice, to confirm that it is the correct password.<\/p>\n

    Step 6: Test the Protected Sheet, and deploy it.<\/h3>\n

    Now you are ready to test your protected Excel Template.<\/p>\n

    Users should be able to key in their particulars, expense, mileage etc. But they would not be able to see the formulas behind the calculations.<\/strong><\/p>\n

    Neither will they be able to edit or change these totals.<\/p>\n

    And best of all, they can never edit the reimbursement rate. Even if they click it inadvertently, Excel will ask them for a password.<\/p>\n

    As for HR, they should have the password set by you. With this password, they should be able to edit the reimbursement rate, and then save the template for use in the future, with the newly revised rates.<\/p>\n

    Voila, quite a sophisticated Excel template, with worksheet protection, hidden formulas and authorised user-editable cells created in no time.<\/strong><\/p>\n

    Give it a try. I’m sure you’d love this simple method to protect your data files from unauthorised changes.<\/p>\n

    Written By Vinai Prakash,<\/strong>
    \nFounder of ExcelChamp.Net & Intellisoft Systems<\/strong><\/em>
    \nVinai has over 30 years of experience working with spreadsheets and teaches Excel, Advanced Excel, Excel Data Analysis, Excel Dashboarding Techniques at Intellisoft Systems in Singapore, and online at ExcelChamp.Net.<\/p>\n

     <\/p>\n

     <\/p>\n

     <\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Protecting your Excel Files from any unauthorized changes is crucial. When you email or share your Excel files with others, there are always chances that they may change some key information, that shouldn’t have been changed. This can happen because, by default, all cells in Excel are available for modifications by anyone having access to … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"generate_page_header":"","footnotes":"","_jetpack_memberships_contains_paid_content":false},"categories":[6],"tags":[109,110,108],"yoast_head":"\nProtect Cells from Any Unauthorized Changes in Excel - ExcelChamp<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Protect Cells from Any Unauthorized Changes in Excel - ExcelChamp\" \/>\n<meta property=\"og:description\" content=\"Protecting your Excel Files from any unauthorized changes is crucial. When you email or share your Excel files with others, there are always chances that they may change some key information, that shouldn’t have been changed. This can happen because, by default, all cells in Excel are available for modifications by anyone having access to ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"ExcelChamp\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/profile.php?id=100079767634794\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/profile.php?id=100079767634794\" \/>\n<meta property=\"article:published_time\" content=\"2020-02-16T13:10:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-10T22:27:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash-e1654524348231.png?fit=331%2C314&ssl=1\" \/>\n\t<meta property=\"og:image:width\" content=\"331\" \/>\n\t<meta property=\"og:image:height\" content=\"314\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vinai Prakash\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vinai Prakash\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/\"},\"author\":{\"name\":\"Vinai Prakash\",\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\"},\"headline\":\"Protect Cells from Any Unauthorized Changes in Excel\",\"datePublished\":\"2020-02-16T13:10:36+00:00\",\"dateModified\":\"2022-07-10T22:27:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/\"},\"wordCount\":944,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\"},\"keywords\":[\"cell protection\",\"password protection in excel\",\"worksheet protection\"],\"articleSection\":[\"Excel In-Built Functionality\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/\",\"url\":\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/\",\"name\":\"Protect Cells from Any Unauthorized Changes in Excel - ExcelChamp\",\"isPartOf\":{\"@id\":\"https:\/\/www.excelchamp.net\/#website\"},\"datePublished\":\"2020-02-16T13:10:36+00:00\",\"dateModified\":\"2022-07-10T22:27:38+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.excelchamp.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Protect Cells from Any Unauthorized Changes in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.excelchamp.net\/#website\",\"url\":\"https:\/\/www.excelchamp.net\/\",\"name\":\"ExcelChamp\",\"description\":\"Solving Everyday Problems With Excel\",\"publisher\":{\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.excelchamp.net\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\",\"name\":\"Vinai Prakash\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1\",\"width\":487,\"height\":720,\"caption\":\"Vinai Prakash\"},\"logo\":{\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/\"},\"description\":\"Vinai Prakash is the founder and master trainer of ExcelChamp.Net. Vinai has over 30 years of experience in using Excel, and is a Data Warehousing & Business Intelligence Professional. Vinai has coached and trained over 30,000 professionals in using Excel, Power BI, Python, SQL, and regularly runs Excel Data Analysis, Excel Dashboard MasterClass, Excel for Beginners, Advanced Excel MasterClass online and all over the world. Vinai's Videos on YouTube channel and his blog articles and newsletters are read by thousands of subscribers all over the world. Vinai lives in Singapore with his wife and 2 kids.\",\"sameAs\":[\"https:\/\/ExcelChamp.Net\",\"https:\/\/www.facebook.com\/profile.php?id=100079767634794\",\"https:\/\/www.linkedin.com\/in\/vinaiprakash\/\",\"https:\/\/www.youtube.com\/user\/ExcelChamp\"],\"url\":\"https:\/\/www.excelchamp.net\/author\/ppfdd\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Protect Cells from Any Unauthorized Changes in Excel - ExcelChamp","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/","og_locale":"en_US","og_type":"article","og_title":"Protect Cells from Any Unauthorized Changes in Excel - ExcelChamp","og_description":"Protecting your Excel Files from any unauthorized changes is crucial. When you email or share your Excel files with others, there are always chances that they may change some key information, that shouldn’t have been changed. This can happen because, by default, all cells in Excel are available for modifications by anyone having access to ... Read more","og_url":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/","og_site_name":"ExcelChamp","article_publisher":"https:\/\/www.facebook.com\/profile.php?id=100079767634794","article_author":"https:\/\/www.facebook.com\/profile.php?id=100079767634794","article_published_time":"2020-02-16T13:10:36+00:00","article_modified_time":"2022-07-10T22:27:38+00:00","og_image":[{"width":331,"height":314,"url":"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash-e1654524348231.png?fit=331%2C314&ssl=1","type":"image\/png"}],"author":"Vinai Prakash","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Vinai Prakash","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#article","isPartOf":{"@id":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/"},"author":{"name":"Vinai Prakash","@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba"},"headline":"Protect Cells from Any Unauthorized Changes in Excel","datePublished":"2020-02-16T13:10:36+00:00","dateModified":"2022-07-10T22:27:38+00:00","mainEntityOfPage":{"@id":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/"},"wordCount":944,"commentCount":0,"publisher":{"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba"},"keywords":["cell protection","password protection in excel","worksheet protection"],"articleSection":["Excel In-Built Functionality"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/","url":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/","name":"Protect Cells from Any Unauthorized Changes in Excel - ExcelChamp","isPartOf":{"@id":"https:\/\/www.excelchamp.net\/#website"},"datePublished":"2020-02-16T13:10:36+00:00","dateModified":"2022-07-10T22:27:38+00:00","breadcrumb":{"@id":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.excelchamp.net\/protect-cells-from-any-unauthorized-changes-in-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.excelchamp.net\/"},{"@type":"ListItem","position":2,"name":"Protect Cells from Any Unauthorized Changes in Excel"}]},{"@type":"WebSite","@id":"https:\/\/www.excelchamp.net\/#website","url":"https:\/\/www.excelchamp.net\/","name":"ExcelChamp","description":"Solving Everyday Problems With Excel","publisher":{"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.excelchamp.net\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba","name":"Vinai Prakash","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/","url":"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1","contentUrl":"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1","width":487,"height":720,"caption":"Vinai Prakash"},"logo":{"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/"},"description":"Vinai Prakash is the founder and master trainer of ExcelChamp.Net. Vinai has over 30 years of experience in using Excel, and is a Data Warehousing & Business Intelligence Professional. Vinai has coached and trained over 30,000 professionals in using Excel, Power BI, Python, SQL, and regularly runs Excel Data Analysis, Excel Dashboard MasterClass, Excel for Beginners, Advanced Excel MasterClass online and all over the world. Vinai's Videos on YouTube channel and his blog articles and newsletters are read by thousands of subscribers all over the world. Vinai lives in Singapore with his wife and 2 kids.","sameAs":["https:\/\/ExcelChamp.Net","https:\/\/www.facebook.com\/profile.php?id=100079767634794","https:\/\/www.linkedin.com\/in\/vinaiprakash\/","https:\/\/www.youtube.com\/user\/ExcelChamp"],"url":"https:\/\/www.excelchamp.net\/author\/ppfdd\/"}]}},"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts\/965"}],"collection":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/comments?post=965"}],"version-history":[{"count":1,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts\/965\/revisions"}],"predecessor-version":[{"id":1671,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts\/965\/revisions\/1671"}],"wp:attachment":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/media?parent=965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/categories?post=965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/tags?post=965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}