Excel: "Don't Fear The Spreadsheet" - Read Only and More: Podcast #1605

Tyler has an Excel Workbook that she needs to share with her coworkers, but she doesn't want anyone to make any changes to the Workbook. What does Microsoft ...

Transcript of the video:

"Don't Fear The Spreadsheet" - Read Only and More: Podcast #1605

Don't Fear the Spreadsheet, episode number 14: Read Only.

Hey, welcome back to the Don’t Fear the Spreadsheet podcast. I’m Bill Jelen, from MrExcel. With today’s question: Tyler Nash.

Tyler Nash - MrExcel, you're going to be pretty proud of me. I made this perfect workbook that I have to share with my co-workers; and I do not want them making any changes to it, at all. Is there any way that I can make the workbook just, like, read-only?

All right, Tyler. Hey, yeah, there's a couple of ways to go. Microsoft Excel 2010 gives us a brand-new way, but I'm not sure that I like it. So, I'm going to compare and contrast the two of these. The book actually shows the old way and that's the way that seems to work a lot better.

So, we have your document here-- and by the way, hey, nice job; this is from the first case study in the book. Lots of cool formatting there. Nice-- nice job. So, File; Save As; and I'm not going to save it as read only, first. I'm just going to save it as a regular, old file. So, let's just call it “000FirstWay”. Alright, so that's method #1. And then I'm going to save it-- I'm going to show you method #2. File; Save As. We’ll go “000SecondWay”. We’ll have to find these in Windows Explorer later, that's why I'm using the “000” to get them up to the top of the list, an old trick there.

Alright, so the new method is: we go to File, and Info is selected here. Under “Protect Workbook” they have something called “Mark as Final”. Let the readers know that the workbook is final and make it read-only. Well, I mean, that sounds like exactly like what we need. It’s going to be marked as final and then saved; and we click OK, and it says that editing is complete, this is the final version of the document. Yeah, that sounds great. Click OK. Alright, so… now here we are. Let's close both of these.

Alright, so method #2, we use “Mark as Final”. Method #1, you can't actually do in Excel. You have to come out to Windows Explorer and find the file that you just saved. So, here it is. I'm going to right-click that file; and come down here; and choose Properties. Now, in Properties I'm going to mark it as Read-only. Click OK.

Alright, now we're going to compare back in excel how the two of these work. First of all, we'll do the SecondWay file. So, we'll open the SecondWay file and, sure enough, it says, “Hey, it’s marked as final. They marked as final-- marked this workbook as final to discourage editing”. I didn't mark it as final to discourage editing, I marked it as final to prevent editing. And look how easy it is to break this: they give them a big, huge button, that says “Edit Anyway”. BAM! It’s no longer read-only. That seems really, really pointless; I mean if we're trying to protect the file from our coworkers, this isn't protecting it, because it just gives them a big, huge “Edit Anyway”. You want to make it a little bit tougher than that, alright; so, I'm not a big fan of that. I'm sure that started out to be much more-- much tougher than it was, and someone must have talked the Excel team down before the product came out.

But let's go back to the first way; this is one where we set the read-only flag in Windows Explorer. Alright, so, this opens up as Read-Only. If I would make some changes; and even screw something up like, put Saturday here; and then when I go to Save, it says, “No deal, it's read-only”. Ok, so I'm going to use Save As and I'm going to try and write it right back over the file; alright, so I'm still trying to save my changes. Click Save and it says, “Nope. Do you want to replace it?” Yes. Oh no, it's set to read-only, you have to try again with a different name. So this one, using the read-only flag in Windows Explorer seems to be the much better way to actually protect that workbook.

Hey, check out the book “Don't Fear the Spreadsheet”. 150 beginner Q&A. It'll get you from level 1 all the way up to level 3, maybe all the way up to level 4. This book makes “Excel for Dummies” look like it’s written for rocket scientists.

Keywords for this video: Protect my Workbook, Read-Only Workbook, Workbook Protected, accounting, Accounting Major, Bill Jelen, Books, business, Business Major, CPA, Don't Fear The S...

This video is current as of October 10, 2012

For more resources for Microsoft Excel