The Ultimate Guide to Read-Only Files in Excel

The Ultimate Guide to
Read-Only Files in Excel

Sometimes we may have an Excel workbook that would be sensitive to changes.

For instance, Jake, who reached out to us and asked:

I have an Excel file with some important functions. How can I share this file with my clients without allowing them to make changes to it?

Jake Kurtz, jakekurtz.us

In this case, it will be necessary to protect the workbook from any changes intentional or otherwise by other users that have access to it.

We may still want to allow a user to make changes and save those with a new filename or to a different location.

A great way to ensure the integrity of such a workbook file would be to enable the ‘read-only recommendation’.

Kasper Langmann, Co-founder of Spreadsheeto

Note: This can be done in previous versions of Excel, but we will be performing the following steps in Excel 2016 for Windows.

How to create a ‘READ-ONLY’ file

Let’s say we maintain a monthly report that we make changes to as new data becomes available.

We now need to share the updated report to users that need the new data and metrics.

However, we want to make sure that any changes made by other users do not get saved to the original file. Instead, these users will be forced to rename the file – or to save it to a different location.

Kasper Langmann, Co-founder of Spreadsheeto

We can do this by making sure other users can choose the option of ‘read-only’ mode to decrease (but not eliminate) risk of the file being changed.

The first step in how to make a file ‘read only’ is to enable the setting

1: Go to the ‘File’ tab and select ‘Save As’.

Save As menu

2: Select ‘Browse’

Browse

3: Click on the ‘Tools’ dropdown at the bottom of the ‘Save As’ dialog box.

Tools drop-down

4: Select ‘General Options’ from the list.

5: Make sure the ‘Read-only recommended’ box is checked in the ‘General Options’ dialog box and click ‘OK’

Read-only checkbox

6: Select ‘Save’.

It’s that simple.

Now every time the file is opened, the user will be prompted to ‘Open as read-only?’.

Open as read-only prompt?

Note: This method allows the choice of ‘Yes’, ‘No’, or ‘Cancel’

If the user clicks ‘Yes’ to the question to open as ‘read-only’, no changes to the original file (name) can be made.

This doesn’t quite mean that we cannot make changes to the open file.

For anyone wondering how to change a ‘read-only’ file, there actually is a bit of a workaround.

Note in the next figure that if we try to save a ‘read-only’ file that it prompts us to change the file name or location.

Change name or save location

So that’s how to change a ‘read-only’ file!

We simply rename the ‘Save As’ filename or save it to a different file location.

It’s a pretty simple procedure to save a workbook as ‘read-only’ and it adds some extra control over the workbook.

Kasper Langmann, Co-founder of Spreadsheeto

But it’s really not protecting it from changes since each user that opens the workbook has the option not to open in ‘read-only’.

It’s more of a reminder to anybody who might forget that “hey, don’t mess this copy up!”.

Now that we have covered the steps to make a workbook file ‘read-only’ let’s look at how we remove the ‘read-only’ option…

How to remove ‘read-only’

Let’s now turn to the task of how to remove ‘read-only’ recommendations from the file.

For instance, say it’s to add new data and make some calculations. This updates the metrics for the stakeholders that use the report.

We need to have a way to remove ‘read-only’ from our file in order to easily save the changes to the current filename.

Kasper Langmann, Co-founder of Spreadsheeto

One thing to note is that our workbook must not be open in ‘read-only’ mode for us to be able to actually save our changes after we remove ‘read-only’.

Remember…

When we open a ‘read-only recommended’ file that it prompts us for a ‘Yes’ or a ‘No’ to the question of whether we would like to open as ‘read-only’ or not.

We need to click ‘No’ in order to be able to save our changes.

1: When opening the file, select ‘No’ when prompted.

Read-Only prompt

2: Click on ‘File’, then ‘Save As’ and then click on ‘Browse’.

Browse

3: Click on ‘Tools’ at the bottom of the ‘Save As’ dialog box and select ‘General Options’.

4: Once the ‘General Options’ dialog box appears, uncheck the box next to ‘Read-only recommended’.

Uncheck read-only recommended box

5: Then click ‘OK’ and the workbook is no longer in ‘read-only’ mode.

There are a couple of things to note about workbooks that are opened as ‘read-only’…

Kasper Langmann, Co-founder of Spreadsheeto

First, if we open a file in ‘read-only’ there is an indication at the top of the file in the filename that the workbook is, in fact, ‘read-only’.

Read-only indication in filename

Another thing to note is what will happen if we try and save changes to the same file that is in ‘read-only’ mode.

Cannot save as that name

As you saw earlier, the only way to save changes to a ‘read-only’ file is by renaming it or saving it to a different location.

Conclusion

So, there it is. The basic steps to both enabling and disabling ‘read-only’ mode in Excel 2016.

It’s a simple technique that can provide more control over the integrity of files without going to the extremes of things like password protection.

Furthermore, it still allows other users to make changes but ensures they can only do so by renaming the file or saving it to a different location than the original file.

Kasper Langmann, Co-founder of Spreadsheeto

Have you seen our Free Excel Training?

Head over and join +30,000 people who’ve already enjoyed our free training.
3 actionable HD-lessons are waiting for you.

Free Excel Training
Learn more about our free training here
2017-05-19T12:16:55+00:00

Send this to friend