How To Make a Sheet Read-Only in Microsoft Excel

Are you tired of unexpected changes messing up your precious Excel data? Learn a simple trick today! In today’s Excel tutorial, I’ll show you how to make an Excel sheet and workbook read-only.

No more worries about accidental edits or data mishaps. It’s a quick and easy process that’ll keep your data safe and sound. Whether you’re a spreadsheet novice or an Excel pro, this is a must-know skill. So, let’s dive right in and safeguard your Excel files!

Reasons to Make an Excel File Read Only

Find below why you might want to convert your Excel worksheets or workbooks to read-only:

  1. Making a file read-only ensures that only authorized users can modify its content, maintaining data accuracy.
  2. Protecting your Excel file as read-only shields complex formulas and calculations from accidental alterations, preserving spreadsheet integrity.
  3. When collaborating, enforcing read-only status ensures that everyone works with the same dataset, preventing discrepancies.
  4. By setting Excel workbooks as read-only, you can retain historical data for analysis or auditing purposes over time.
  5. Read-only status adds a layer of protection, preventing unauthorized access to sensitive or confidential information.
  6. It reduces the risk of unintentional changes, errors, or deletions that could compromise data accuracy.
  7. You can safely share information for reference, knowing it won’t be accidentally modified by recipients.
  8. You can also maintain an untouched master copy of essential data, unaffected by unintentional edits in shared copies.

How to Make an Excel Workbook Read-Only

There are multiple ways to convert a read-and-write Excel workbook to read-only. Find below the most popular ones that you must know:

Using Protect Workbook

Convert Excel workbook to read only
Convert Excel workbook to read-only
  1. Once you’re done editing your Excel workbook, click the File tab.
  2. A left-side navigation panel will show up.
  3. There, click the Info menu.
  4. Now, you should see the Protect Workbook button on the right side of the Info menu.
  5. Click Protect Workbook and choose Always Open Read-Only.
  6. For added security, you can add a password so persons with the right credentials can only open and edit the file.
  7. Click Encrypt with Password and supply a password of your choice.
Protect with password
Protect with password
  1. Click OK to save the password.
Read-only pop up
Read-only pop-up

From now on, if anyone else tries to open the Excel workbook, they first need to supply the right password to open the file on the Excel desktop application. Then, there will be a warning pop-up to open the file as read-only or in the edit mode.

To disable the feature, go back to the Protect Workbook menu and click Always Open Read-Only again.

Using Mark As Final

Marked as final option on protect workbook
Marked as final option on protect workbook
  1. On your Excel workbook, click File on any of the worksheets.
  2. Click Info on the left-side navigation panel.
  3. Choose Protect Workbook on the right side.
  4. Click Mark as Final option on the context menu that shows up.
Mark as final confirmation dialog box
Mark as final confirmation dialog box
  1. Click OK on the warning message that pops up.
Marked as final ribbon
Marked as final ribbon

The Excel workbook will show a yellow ribbon menu as shown in the above image. The workbook also immediately enters the read-only mode. You must click the Edit Anyway button if you wish to edit the content of the Excel workbook.

Using Save As Tools

Use Save as Tools to save Excel as read-only.png
Use Save as Tools to save Excel as read-only.png
  1. When your Excel workbook is open, click File and choose Save As from the left-side navigation panel.
  2. Click Browse and choose a location to save the Excel workbook.
  3. You can enter a new name for the workbook in the File Name field.
  4. Now, click the Tools menu and choose General Options.
General options
General options
  1. Checkmark the Read-only recommended option on the General Options dialog.
  2. Click OK.
  3. On the Save As dialog box, click the Save button at the bottom to save the Excel workbook in read-only mode.

Now, whenever you or someone else tries to open this Excel workbook, Excel will show the Open as read-only opt-in. Now, you can click Yes to access the file with view-only rights or click No to get edit rights.

Using an Excel VBA Script

If you need to apply the read-only mode to Excel workbooks in bulk, you can use an automatic way made possible by Excel VBA. You just need to add an Excel VBA script to the workbook and execute the code.

The script will rewrite the existing Excel workbook file with ReadOnlyRecommended property to True.

You don’t need to manually access the Protect Workbook or Save As Tools options to activate the ReadOnlyRecommended property for an Excel workbook.

Creating a VBA macro that will overwrite the Excel workbook as read-only
Creating a VBA macro that will overwrite the Excel workbook as read-only

Find below the script and the steps you must use:

  1. Click the Developer tab on the Excel ribbon menu.
  2. Click the Visual Basic button inside the Code commands block.
  3. Now, the Excel VBA Editor interface will open.
  4. There, click the Insert button on the VBA Editor menubar.
  5. Choose Module in the context menu.
  6. Copy and paste the following VBA script inside the blank module:
Sub SaveWorkbookAsReadOnly()

Dim wb As Workbook
Dim filePath As String

'Get the active workbook
Set wb = ActiveWorkbook

'Get the file path of the workbook
filePath = wb.Path & "\" & wb.Name

'Save the workbook as read-only
wb.SaveAs filePath, ReadOnlyRecommended:=True

End Sub
  1. Click the Save button.
  2. Click Close to close the Excel VBA Editor.
Running an Excel VBA macro
Running an Excel VBA macro

Now, follow these steps to execute the VBA macro that you just created:

  1. Press Alt + F8 to call the Macro dialog box.
  2. There, select the SaveWorkbookAsReadOnly macro.
  3. Click Run to execute the code.
Rewrite with read-only attribute
Rewrite with read-only attribute

Excel might ask you to overwrite the existing Excel workbook to activate the ReadOnlyRecommended:=True property. Click Yes, if you see the above dialog box.

Open as read only warning
Open as read-only warning

Now, close the Excel workbook and reopen it. You should see the Open as read-only dialog box. You can click Yes to access the workbook in read-only mode or click the No button to access the file with editing rights.

How to Make an Excel Sheet Read-Only

You can only apply the read-only attribute to an Excel workbook. Excel doesn’t come with built-in features to assign the read-only tag to individual worksheets.

What you can do is password-protect the selected worksheets so no one can edit the sheet without entering a password.

Using Protect Sheet

Protect Sheet in Review tab
Protect Sheet in the Review tab
  1. Go to the worksheet on your Excel workbook that you want to make read-only.
  2. Click the Review tab.
  3. Click the Protect Sheet button on the Protect commands block.
Activating worksheet level protection
Activating worksheet-level protection
  1. The following options should be checked on the Protect Sheet dialog box:
    • Select locked cells
    • Select unlocked cells
    • Protect worksheet and contents of locked cells
  2. Enter a password into the Password field.
  3. Click OK to save the changes and lock the worksheet.

Now, simply close the Excel workbook and click the Save button on the Save Workbook dialog box. The next time you or your colleague opens the workbook, Excel won’t allow edits in the locked worksheet.

Using an Excel VBA Script

If you wish to automate the above method, you can use the following Excel VBA script.

Make worksheet read-only VBA script
Make worksheet read-only VBA script
Sub MakeSheetsReadOnly()
    Dim ws As Worksheet
    Dim password As String
    Dim protectedSheets As Variant
    Dim sheetName As Variant
    password = "1234"
    protectedSheets = Array("Sheet1", "Sheet2")
    For Each ws In ThisWorkbook.Worksheets
        If IsInArray(ws.Name, protectedSheets) Then
            On Error Resume Next
            ws.Unprotect password
            On Error GoTo 0
            ws.Protect password, UserInterfaceOnly:=True
        Else
            On Error Resume Next
            ws.Unprotect password
            On Error GoTo 0
        End If
    Next ws
    MsgBox "Selected sheets are now read-only. Others are editable.", vbInformation
End Sub

Function IsInArray(val As Variant, arr As Variant) As Boolean
    Dim element As Variant
    For Each element In arr
        If element = val Then
            IsInArray = True
            Exit Function
        End If
    Next element
    IsInArray = False
End Function

Follow the steps mentioned in the section Using an Excel VBA Script to add this Excel VBA script to your workbook.

Read only confirmation dialog box
Read only confirmation dialog box

The script will selectively apply the read-only mode to the worksheets mentioned within the script. The code will also create the dialog box shown above to inform you which worksheets have been assigned the read-only tag.

Here’s how you can modify this script:

  • Change "1234" to a different password that you want to set to the worksheet.
  • In the Array("Sheet1", "Sheet2") code element, add your worksheet names within quotes separated by commas.

Conclusions

In conclusion, making an Excel sheet read-only is a simple yet effective way to protect your data from unwanted changes.

By following the steps outlined in this guide, you can ensure that your important spreadsheets remain intact and secure.

Whether you’re sharing your Excel file with colleagues, clients, or friends, setting it as read-only adds an extra layer of safety to your valuable information.

So, don’t hesitate to apply these easy techniques to safeguard your Excel sheets and keep your data in pristine condition.

Also, don’t forget to leave a comment below. Whether you’ve tried the tips and techniques mentioned in this article or have your own valuable insights to share, your comments are greatly appreciated

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃