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

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.

8. Click OK to save the password.

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

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.

5. Click OK on the warning message that pops up.

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

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.

5. Checkmark the Read-only recommended option on the General Options dialog.
6. Click OK.
7. 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.

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

7. Click the Save button.
8. Click Close to close the Excel VBA Editor.

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.

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.

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

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.

4. 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
5. Enter a password into the Password field.
6. 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.

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.

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