In this article, you will learn everything you need to know about Read-Only mode in Excel. Throughout this article, we will discuss 5 effective ways to make an Excel workbook Read-only. We can make the whole Excel file or some specific cell read-only.
When working with Microsoft Excel, we often have to make the workbook read-only to maintain data integrity. There are a number of simple methods available in Excel for performing this task. In order to make files read-only, we can choose from a variety of options.
Hope this article will help you to find out the ways of making an Excel file Read-only. So let’s get started!
Download Practice Workbook
You can download the practice workbook from here:
The password for the file is- 12345.
What Is Read-Only in Excel?
In Excel, Read-Only refers to a file or worksheet that may be opened and viewed but not changed or updated. Typically, when a workbook is opened in read-only mode, it is either password-protected or marked as read-only by the file system or user.
How to Make an Excel File Read Only
There are several ways to make an Excel file in read-only mode. To demonstrate all the possible ways, we have taken a dataset named SalesRecordOf2021 where there are 7 rows and 3 columns.
1. Use ‘Open as Read-Only’ Feature to Make Excel File Read Only
We can mark our Excel workbook as a Read-Only file. Thus, when someone else attempts to open the Excel file, it will primarily protect the workbook from editing.
- First, go to File>> Info>> Protect Workbook>> Always Open Read-Only.
- This will shift the Excel workbook to Read-Only mode and if someone else will try to open the Excel file, it will show the following dialog box.
- Click Yes to proceed with the Read-Only mode whereas selecting No will enable editing the workbook.
Thus we can use the ‘Open as Read-Only’ feature to make our Excel file read-only.
2. Use General Options to Make Excel File Read Only
There is another option in Excel by which we can set passwords to make the Excel file read-only.
- Click on the File tab.
- Next, go to Save As>> Browse.
- As a result, a dialog box named Save As will open up.
- Now, click on Tools>> select General Options from the drop-down.
- Now set a password to open the file.
- After that, set another password to modify the file. We have entered 12345 in both fields.
- Next, click OK.
- Then, re-enter the password to proceed and click OK.
- Now, re-type the password to modify the file and click OK.
- The Excel file is now in read-only mode, so if we close the workbook and open it again, it will ask for the password.
- Give the password to open the file.
- Next, it will ask for the password to modify.
- Give the password and click Read Only.
Thus we can use general options to make our Excel file read-only with a password.
3. Make Excel Workbook Read Only Using Mark as Final Option
We can make an Excel worksheet read-only from alteration by identifying a version as a final copy. So, when someone opens the Excel file, they will get a warning message saying that it’s the final version and they shouldn’t make any changes to it. Follow the below steps:
- First, go to File>> Info>> Protect Workbook>> Mark as Final.
- Then, a small dialog box will appear. Just hit OK.
- As a result, Excel will automatically mark the Excel workbook as a final version.
- Excel will display the following warning box if someone else attempts to open the workbook.
- Just hit OK to proceed.
- If you want to continue editing the worksheet, just click Edit Anyway.
- Excel will then allow you to edit the workbook.
Thus we can make an Excel workbook read-only by using the “Mark as Final” option.
4. Open Workbook as Read-Only Using VBA
We can open an Excel workbook as read-only using the VBA macro. To know the whole procedure follow the below steps:
- First, press ALT + F11 on your keyboard to open Visual Basic.
- Then click Insert and select Module.
- Insert the following code in the code editor.
Dim File_Explorer As Office.FileDialog
Dim selection_item As String
Set File_Explorer = Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add "Excel File type", "*.xlsx?", 1
.Title = "Choose your file"
.AllowMultiSelect = False
.InitialFileName = "C:\Desktop"
If .Show = True Then
selection_item = .SelectedItems(1)
Set book = Workbooks.Open(Filename:=selection_item, ReadOnly:=True)
- Then close the Module window.
- After that, go to View tab > Macros>> click on View Macros.
- After clicking View Macros, select the macro File_Open_Through_Dialog_Box and click Run.
- As a result, a dialog box will appear.
- Select the file that we intend to open as a read-only and click Open.
- After that, you will see that the file is now open as read-only.
- Thus we can open a workbook as read-only using the VBA macro.
5. Convert an Excel File to a PDF to Make It Read Only
You can also protect an Excel spreadsheet by converting it into a PDF. In many cases, PDF files are more difficult to edit. As a result, we can prevent others from accessing our Excel dataset. Here are the steps you need to follow in order to convert an Excel spreadsheet to a PDF:
- First, go to File>> Export>> select Create PDF/XPS.
- Then give a file name and select Save as type to PDF.
- Click Publish.
- As a result, a PDF file will be created like the below image.
As a result, users won’t be able to easily alter the file, and managing data will be more difficult. Thus we can convert an Excel spreadsheet to a PDF to make it read-only.
Read More: Make Excel Read Only With Password
How to Make a Cell Read-Only in Excel
In this method, we will lock the cells we want to make read-only then we will go to the “Protect Sheet” option to enable the protection. To demonstrate the steps, we have taken a dataset named SalesRecordOf2022 where there are 7 rows and 3 columns. Now follow the below steps:
- Select all cells of the worksheet by clicking on the top left corner or pressing ‘Ctrl + A’.
- Then right-click on your mouse and select Format Cells.
- Now go to Protection>> unmark the box next to the Locked option>> then click OK.
- Now, go to the Excel file again, and select the cell which you want to make read-only. In our case, it is cell B5.
- Then go to the “Format Cells” option again.
- And now mark the box next to the Locked option and click OK.
- Now, go to the Review tab>> Protect drop-down list>>select Protect Sheet.
- Enter the password and enable “Protect worksheet and contents of locked cells”.
- Click OK.
- Re-enter the password to confirm and click OK.
- As a result, if you want to make any changes in cell B5, Excel will show you the warning message like in the image below.
Thus you can make a cell read-only.
How to Remove Read-Only From an Excel File
In this part, we will discuss how you can remove ‘Read-only’ from an Excel file.
1. Use of Edit Workbook Option
- When a file is marked as final, a pop-up will appear.
- To remove read-only, just click Edit Anyway.
- Excel will then allow you to edit the workbook.
2. Remove ‘Read-Only’ Property from a Read-Only Excel file
You can remove ‘Read-only‘ from Excel file properties by following these steps:
- Select the Excel ‘Read-only‘ file.
- Right-click on the file and select Properties.
- Tick the ‘Read-only‘ option from Attributes.
- Then, click Apply>> OK.
You can now open the file again and it will not have the “Read-Only” restriction.
Read More: Make a Cell in Excel Read Only
Benefits of Setting a File as Read Only
Setting a file to read-only has various advantages, including:
- You can protect data privacy and accuracy by setting a file as read-only.
- The read-only mode can prevent accidental modifications to a file.
- It also can help to prevent unwanted people from opening a file.
- The read-only mode can help to ensure that a file is always in a consistent state.
- We need a read-only version for presentations or demonstrations so that the data and formatting remain unchanged, preventing accidental modifications during the presentation.
Drawbacks of Setting a File as Read Only
There are also some drawbacks to setting a file as read-only, including:
- It can be hard to modify a file that is set to be read-only.
- When a file is set to ‘read-only’, it can be hard to share it.
- It can be hard to work together on a file that can only be read.
Frequently Asked Questions
1. Why is my Excel file opening as Read-Only?
There are several reasons why a file may open as Read-Only. It could be due to file permissions set by the operating system, the file being marked as Read-Only, or the file being password-protected. Additionally, if the file is shared or being accessed from a network location, it may open in Read-Only mode to prevent conflicts.
2. How do I change a disk from read-only?
To change a disk from read-only to read-write, you can modify disk properties (windows). To do that:
- Open File Explorer (Windows Explorer) by pressing Windows Key + E.
- Right-click on the disk or drive you want to modify and select “Properties” from the context
- In the Properties window, navigate to the “General” tab.
- If the “Read-only” box is checked, uncheck it.
- Click “Apply” and then “OK” to save the changes.
3. What is read only file system error?
Most of the time, you get the “Read-only file system” error when you try to change or write to a file system or storage device that is set to “read-only.” This error warning means that the operating system or the file system itself won’t let you write to the file system or object in question.
Key Takeaways from the Article
- Throughout this article, we have discussed the different methods for making a workbook read-only.
- First, we have discussed what it is a read-only mode.
- Then we have discussed 5 different ways of making a file read-only.
- We have also explained how to open a workbook using VBA macro.
- Showed a step-by-step procedure of all methods.
- Also showed how to make a cell read-only.
- Explained two easy ways of removing read-only from a file.
- Discussed some benefits and drawbacks of setting a file read-only.
- Provide solutions to frequently asked questions by readers.
Excel Read Only: Knowledge Hub
- [Fixed!]: Excel Files Open as Read Only From Network
- [Fixed!]: Excel Files Opening as Read Only
- [Fixed!]: This Workbook Was Opened in Read Only Mode
This article covers every possible way to make an Excel workbook read-only with some easy ways. Don’t forget to download the practice file. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.