Excel 365: Linking, Printing, & Protecting Workbooks

In this demo, we'll turn our attention to some aspects of saving Excel workbooks, and we'll see how AutoRecover can help us recover from unexpected crashes that hit our Excel. The dataset that we are going to use for this simple demo is taken from the URL on screen now, we can see that this is a Kaggle URL.

 

[Video description begins] The screen displays the URL address: https://www.kaggle.com/sidtwr/videogames-sales-dataset. [Video description ends]

 

This is the same video games dataset which we have used in a previous demo. Let's just make a few quick formatting changes in order to make the data more clearly visible, so we increase the font size and add in a header row. For this demo, the important aspect that we are going to focus on is the autosaving of Excel workbooks. Now, you can see at the very top left of your Excel screen that AutoSave seems to be turned off. Here is how this works. For files that have not yet been saved or that have been saved to another location using Save As, AutoSave is disabled by default. On the other hand, for files that have been saved to OneDrive or SharePoint online, AutoSave is enabled by default. What exactly does it mean for AutoSave to not be enabled by default? Well, it turns out that even when AutoSave seems to be off, Microsoft is still doing a bunch of work behind the scenes to save us from crashes.

 

Let's go ahead and see how this works. Let's go ahead and click on the File menu over on the top left, this brings up the familiar green startup screen. At the bottom, let's scroll down and click on the Options button, this allows us to customize virtually all Excel options. For now, let's go ahead and click on the Save button in the tab on the left, this is going to customize how workbooks are saved. The first checkbox there determines how AutoSave works for OneDrive and SharePoint online files. Because it's checked, such files are going to be AutoSaved by default. Next, we can specify the format in which AutoSave is going to save files that's set to Excel Workbook. Down below is information about AutoRecover. Note that AutoRecover is different from AutoSave because AutoSave will save files to a specific file name, AutoRecover on the other hand is only going to kick in if there's a crash. We're going to check out how AutoRecover works in just a moment. Note how AutoRecover information is only recovered every 10 minutes, so that's quite a low frequency, that's a period in which you might make quite a few changes.

 

There's a checkbox asking whether we'd like to Keep the last AutoRecovered version if we close without saving, the answer is yes by default. There's also the AutoRecover file location. Down below are various other options. One interesting one is the Default file location, that's C:/Users/admin/Documents. If you would like to have your Excel files by default, open from a different location, you can customize it here. All of these options over in the top portion of the screen relate to all Excel workbooks that's in the save workbook section. Then comes a section which has AutoRecover exceptions only for the current workbook which is called Book1. Here, we can disable AutoRecover for this particular workbook. We don't have any particular reason to do so, so let's leave that checkbox unchecked. Now, let's change the frequency at which AutoRecover information is saved, and we change it from 10 minutes to 1 minute. This is going to allow us to quickly check how AutoRecover works. Once we click OK and change that setting, let's now go ahead and exit out of Excel.

 

We have already made various changes to our Excel workbook. Let's now close Excel by clicking on the X button in the top right corner. And then we are very careful, we do not save any of these changes. So we intentionally click the Don't Save button. Remember that all of this was done with a workbook which had never been saved, and so AutoSave never had a chance to get enabled at all. In any case, let's now reopen Excel we use the Start menu, search for Excel, and open it. Then, once it opens up, we are going to click on Blank workbook. This will cause a new blank workbook to be opened up as usual. Let's now navigate into the File menu, scroll down the green toolbar over on the left and click on the Info tab. There are some handy bits of functionality here, so let's quickly orient ourselves. The first button is Protect Workbook, this controls what types of changes people can make to this workbook we currently have open. Other groups of functionality here include the ability to inspect workbooks, view and restore previous versions, and manage version history, manage workbooks, and browser view options.

 

For now, it's the Manage Workbook button that we are interested in. Let's go ahead and click on it. You can see that there are two sub options here, Recover Unsaved Workbooks and Delete All Unsaved Workbooks. The first option allows us to browse all recent unsaved files, we click on it and we are taken to a file location. You can see from the file ribbon over up top that we are inside the Microsoft Office folder. There's a folder there named UnsavedFiles, inside which we have an unsaved version of our Book1. Also note the identifier over at the end of the name, that identifier allows Excel to distinguish between this and other unsaved versions of the same Book1 workbook. Let's go ahead and click on the Open button, and this causes the file to open up as a RECOVERED UNSAVED FILE. Excel tells us that this is a recovered unsaved file and that it's temporarily stored on our computer, and it offers us the option to save it as a workbook file. All of this is visible in the ribbon just above the formula toolbar towards the middle of your screen. Let's make a small change, something like renaming of worksheet and then let's click on that Save As button. This is going to allow us to save this as a regular Excel file. So it's no longer going to be a recovered unsaved file. And in this we have demonstrated the use of AutoRecover in Microsoft Excel.

 

In this demo, we are going to learn how to create and use Excel templates. Let's say you have a task which you repeatedly performed using the same format for your Excel workbook. An Excel template can be a great productivity booster in such a situation. In order to get started, all you need to do is to first get one Excel workbook into exactly the format that you would like. So for instance here, in order to get our workbook ready to be a template, all we need to do is to clear all of the extraneous contents and set it up as a good template or a prototype for the repeated task which are looking to automate. So for instance, we might clear the data, set the formatting, the borders, and so on. Once all of these tasks have been accomplished, we are ready to save this file as an Excel template. So let's go ahead and click on the File menu. Scroll down towards the center of the screen and click on the Save As button. There we rename this file, so we change its name from SalesReport to Book. Now you should be aware that there is a special significance to the name Book. There are two special Excel templates Book.xltx and Sheet.xltx. As there names would suggest, these serve as templates for workbooks and worksheets respectively.

 

So again, it's important that we name this Excel template as Book. And then where we are prompted for the file type, that's in the drop-down menu over on the right. We use that drop-down menu to pick the file type of Excel Template. Note that the extension is .xltx. You can also see from the options just below this particular option that we could choose to have an Excel macro-enabled template that would have the extension .xltm. There is also a legacy format for Excel 97 to Excel 2003 template files which have the .xlt extension. In case you're wondering why macro-enabled workbooks and templates have different file formats, that's because macros require some additional security features. And that's why Microsoft decided that it made sense for users to always know explicitly that they are working with macro-enabled files. In any case, coming back to this task of creating a custom template, once we've chosen the file type, we've then got to save it into a specific file location.

 

So for that, we click on the Browse button. You can see it towards the lower left corner of our screen now, and there we're going to navigate into the Documents folder. Inside the Documents folder, we find a subfolder called Custom Office Templates. We click on that, and then click the Save button. Notice again that the Save as type is Excel Template. It's important to get this file location right because this is the location where Excel is going to look for Custom Office Templates which we have created. In any case, let's save this file and then we move on. We close Excel by clicking on the X button over on the top right. Then let's go ahead and reopen Excel as before. Once we do this, we will have the opportunity to create a new Excel workbook. This time however, we are not going to go with the Blank workbook which we've been using so far. Let's click on the little More templates button over in the center right of our screen. When we do this, Excel will display two tabs, Office templates and Personal templates. Office templates are those which come preloaded with Excel, but right now let's click on Personal. And there we can see our template, the one titled Book, which appears right down below. We click on that and we've now reopened the Excel template that we created a moment ago.

 

We can now go ahead and work with this workbook just as we would with any other workbook. One final little trick, let's see how we can customize the manner in which Excel opens up. Let's say that we would like this book to open up every time Excel loads. All we need to do is to save this template, which is called Book into a specific location. So we click on the File menu, Save As, and then we browse. So we click on the big Browse button, and we use that to navigate to a very specific location. This is a location which is going to be consulted by Microsoft Excel while opening up. This default location, which you can see on screen now is called the AppData\Roaming folder.

 

[Video description begins] The screen displays the default location: C:\Users\<user>\AppData\Roaming\Microsoft. [Video description ends]

 

If you're wondering why Microsoft has chosen to name it this way, it's because it's meant to contain all of the information for a specific user. And notice how the user name is a part of the path. All specific user information that is going to roam, that is going to be transferred from one device to another.

 

So let's say this particular user logged into a different machine, all of the settings in this AppData Roaming folder would also be transferred to that other machine. So that's the idea of the AppData\Roaming folder. The Microsoft sub folder within this contains all Microsoft specific settings. Let's go ahead here and click on the folder titled Excel. So this is inside the AppData\Roaming Microsoft folder. We are now inside the Excel folder. One level further, we look for this directory called XLSTART, all upper case. And as the name would suggest, this directory is where we've got to save any files or templates which we would like opened up automatically when Excel starts up. So here we go ahead and save our Book Excel template file. Notice once again how the file type is Excel Template. Then let's close out of Excel in order to ensure that this works as planned. Let's reopen up Excel. The Home screen appears. Let's click on Blank workbook.

 

However, even after waiting for a while, we see that Book.xltx is not picked up. In order to ensure that it is picked up, let's navigate into the File menu and open up a new Blank workbook. This time, things work as planned, and Book.xltx is picked up, and we get the screen that we were expecting. This delay might be a little annoying, so let's go ahead and see how to fix that. We click on the File menu, and then scroll down to the very bottom towards the Options. There inside the Options tab, let's navigate down to the very bottom and uncheck the box which says Show the Start screen when this application starts. Now if we hit OK and then close Excel and then reopen it, the changes are instantaneous. Book.xltx is going to be immediately picked up and displayed on screen. The Excel Start folder is a great way to customize the manner in which your Excel opens up.

 

Remember that there are two special Excel templates which we can place in this folder, Book.xltx and Sheet.xltx. In addition, any workbooks which you place inside this Excel Start folder are also going to be opened by Microsoft Excel at startup.

 

In this demo, we are going to work with outlines. These are simply ways to group rows and columns in an Excel spreadsheet in order to reveal different levels of detail. Grouping rows and columns is a great way to make your Excel spreadsheets more readable. One of the chief attractions of grouping rows and columns is that this does not change the data in your spreadsheet in any way. As the name would suggest, it only affects the outlines of the rows and columns. In this way, outlines and group rows and columns are less intrusive than other forms of data summarization, such as pivot tables or subtotals. Let's see what this means in just a moment. The data that we're using in this example is the same General Motors sales data, which we have used in some previous demos. Let's start by grouping some rows. Let's select the rows that correspond to January, February, and March. Then up top, we click on the Data menu item. This of course brings up a whole new set of submenu items. We scroll over to the extreme right, there we click on the button titled Outline.

 

Clicking on this brings up buttons which allow us to group data, ungroup data, and calculate subtotals. For now, let's go ahead and click on the Group button. You can group rows or columns. Here, because we've selected rows, Excel is smart enough to infer that our intention is to select rows. Even so we could change this in the radio buttons that you see on screen now. Let's go ahead and hit OK. And we can now see that rows 3, 4 and 5 have indeed been grouped together. Notice also, the small boxes which read 1 and 2 in the top left of the Data area. These refer to levels of detail in the outline. In level 1, we are asking Excel to show us all of the data. We can change that by clicking on the little - sign. This will collapse the three rows, January, February and March into just one unit. The - sign has changed to a + sign. Clicking on that is a way to reveal those three rows once again. Grouping of rows and columns is quite different from hiding rows and columns. And that's because when you group rows and columns, you see these different levels of detail.

 

Excel supports up to eight levels of detail. And again, this is a great tool, to get the full utility out of this tool. It usually makes sense to also have subtotals of some sort. In any case for now let's go ahead and expand these rows, and see what happens if we try and add or expand the selection. So we've now selected the rows April, May, and June. We again navigate over to the top right, we again click on Outline, and choose the Group option. Once again, we'll be prompted for whether we wish to group rows or columns, we go with the Group Rows option. And what happens next is going to reveal a weakness of data grouping in Excel, instead of there now being two separate groups, group 1 with January, February, and March, and group 2 with April, May, and June. Instead we have just the one group which contains all six months. That's what happens when you try to group contiguous groups of rows. These are going to be compressed into the same group, not into separate groups. A way to get around this is to have subtotals or some kind of structure which separates one group from another.

 

The + and - signs continue to work as before, so we can toggle the visibility of these six rows by clicking on those signs. These are over in the extreme left of the spreadsheet. Now let's see how we can ungroup data. Please note that at this point, we've only selected three of the six rows in our data group. We click on the Outline button in the top right, and now we click on the Ungroup button down below. We are now prompted for whether we would like to ungroup rows or columns. Because we've selected rows, that's the option that's checked by default. Let's hit OK, and now we can see that our group shrinks, only the rows that we had selected are removed from the pre-existing group. So January, February, and March continue to be grouped together, and they continue to have the + and - buttons over on the left, which can be used to expand or collapse their visibility. If we decide that we'd like to get rid of all groups or subgroups, it's easy enough, we click on Outline, Ungroup and then click on Clear Outline. This will remove all groups in rows, and columns and restore the outlines of the rows and the columns to their default appearances.

 

Next, let's go ahead and explore how we can group columns, so, we select one column. Note that it's perfectly possible to have a, one row or one column group. This time we have selected the data in column B. We again click on the Outline button in the top right. Remember that all of this is happening inside the Data menu, and then we click on Group. This time, Excel realizes that we probably wish to group columns rather than rows. It infers this from whether the orientation of our selection is from left to right, or from top to bottom. In any case, we hit OK, and we can now see that column B is in a group by itself. + and - signs appear out up top, note that 1 and 2 now represent levels along the columns, rather than along the rows. Let's go ahead and clear this outline as well. So we click on the Outline button, Ungroup, Clear Outline, and that eliminates the single column group consisting of column B.

 

Now, let's go ahead and try to auto outline our data. So this time, we go ahead and click on Outline, Group, and Auto Outline, and Excel tells us that it cannot create an outline. The reason for this is that auto outline, like the Subtotal functionality requires our spreadsheet to have some discernible structure, that Microsoft Excel can work with. Here, there's no discernible structure, Microsoft doesn't know whether we'd like to group by quarter, or group by year, or by columns rather than rows. And so for that reason, the auto outline operation fails. Let's go ahead and give Microsoft something to work with. Let's introduce some structure, so for this, let's go ahead and introduce Subtotals for every quarter. So let's start by selecting row 6, which has the data for April, let's right click, the Contact sensitive right click menu asks, whether we'd like to insert, and it knows here that we are going to insert a row. We select this option and the inserted row appears above the current row. This row then is going to be populated with the total sales for the month of January, February, and March.

 

For this we make use of the simple worksheet formula SUM. We SUM B3 to B5, and then we copy this formula over into cell C6. So that in the same fashion, we have the sales for January, February, and March of 2018 and 2019 summed up. In similar fashion, we select the row for July, click on Insert and likewise add another total for the months of April, May, and June. Notice now that there is a clear structure in exactly the same fashion. We'll go ahead and add yet another subtotal for the July, August, September quarter. And finally, repeat this exercise for the October, November, December quarter as well. By the end of this process, we are going to have four quarterly subtotals. And this structure is indeed enough for Microsoft to create an auto outline. So now that we have got our spreadsheet into a structure, which Microsoft can recognize, let's go ahead and repeat the experiment. Then we go ahead and click on the Outline button in the top left, from there we select the Group, Auto Outline option. The last time we had tried this auto outline had been unsuccessful, but this time it clearly works. We can now see over on the extreme left, that there are indeed four groups, these groups can be expanded and collapsed using the four + and - symbols. Each one of these groups when fully collapsed reveals the total for that corresponding quarter. And as before, we can get rid of all of this outlining in the same fashion. We click on Outline, Ungroup and Clear Outline that will eliminate all of the groups that we just created. However, it does not eliminate our spreadsheet structure.

 

Once again, this is one of the great advantages of the group and ungroup feature. It only changes the appearance of the view of our spreadsheet. It does not tamper with the data in the rows or columns in any way at all. And in this way, we have now made use of Microsoft Excel's outline feature. We have grouped rows and columns, ungrouped rows and columns, created a structure that Microsoft could work with, and created then an auto outline.

 

In this demo, we are going to continue working on the same data set as before. But we will shift our emphasis to copying and pasting data across spreadsheets and in this way, creating external links in the data within our spreadsheets. Please note that the current workbook we are in is called GM-SalesData, we can see this over in the top green ribbon at the top of the screen. Let's select an arbitrary selection, so let's select all of the data in rows 1 through 6. Next, let's right click and copy this data into the clipboard. The next step is to click on the File menu option in the top left, and then create a new blank workbook. This workbook is of course completely empty, for now, let's just go ahead and paste this in. Microsoft Excel has inferred that because we have something in our clipboard, we probably wish to paste it. And that's why the Paste menu item is helpfully placed on the top left. There are actually many different ways of pasting data in, for now, let's just go with the simplest, which is to click on that big Paste button. You can also see a helpful message down at the very bottom of the screen which reads Select destination and press ENTER or choose Paste.

 

Pasting in this fashion preserves all of the data as values and it also preserves all of the formatting. We can confirm this by clicking around in the different cells. We can see in the formula bar which appears just above the data area that the values in there are hard coded. For instance, we are currently in cell B3 and the contents of cell B3 are the numbers 221912. That's present there as a value rather than as a reference or a formula. Next, let's go ahead and try a different, more complex version of copy paste. We switch back over to our original workbook, that's called GM-SalesData and now let's go ahead and select data in the rows 7 through 10. Nothing changes in the fashion in which we copy this data. We select the data with our mouse, right click, and choose the Copy option. All of this is exactly as before, where things changed is while pasting the data. So let's switch back over to Book4, that's the other Excel workbook which is the destination. Once again, we can see that Excel has picked up the fact that we have something in our clipboard.

 

We know this from two facts. First is the Paste button which appears right up top on the left. And the other is the helpful tooltip in the extreme bottom left of our screen, which reads Select destination and press ENTER or choose Paste. This time we are not just going to paste the data in blindly, instead we are going to make use of something known as Paste Special. So let's right click, and in the right click menu now, we'll get an option called Paste Special, this in turn leads to many different submenu items. These are titled Paste, Paste Values and Other Paste Options. It's worth your while to explore all of these options in detail. For instance, the first option under the Paste heading will paste values, formatting, formulas, everything. The second will paste only formulas. The third will paste values as well as formulas. The fourth which shows a little paintbrush will paint only the formatting in. But in any case, what we are now interested in is one of the options under other Paste options, this is the Paste Link. Let's go ahead and click on this and this results in a link to the data being pasted into our spreadsheet. Now, in case you're wondering what exactly this link is all about, well, let's examine the data a little more closely.

 

You can see first off that we do not have the formatting from the source spreadsheet, so this data has been pasted in here without any of the formatting. What about the data then, is the data in the form of hard coded values as it was a moment ago? Let's click on one of these cells to investigate, so we click on cell B8 and we can see in the formula bar up top that we actually have a link to another spreadsheet.

 

[Video description begins] He highlights the external link: ='[GM-SalesData.xlsx]USA'!B8. [Video description ends]

 

This is an external link. The external link consists of the external spreadsheet name, which is GM-SalesData.xlsx, that is contained between a pair of square brackets. Next is the name of the tab from the external spreadsheet, which is titled USA. The name of the workbook as well as the worksheet are enclosed in single quotes, then is an exclamation point followed by the cell reference, which is B8. All of these delimiters are used in their standard form in Excel. Square brackets and single quotes are used to delimit the names of workbooks. And the exclamation point is used to separate the worksheet name from the cell reference.

 

So if you ever need to create external links without opening the other spreadsheet, this is the way in which you would do so. In any case, let's move on, let's explore another way of pasting data using a link. So we switch back to the original spreadsheet GM-SalesData, let's this time select the data for the months July, August and September as well as the quarterly total. Right click, hit Copy and then switch back over to the other spreadsheet. This time, we are going to click on the Paste button. Specifically, we are going to click on the little arrow just below the Paste button, this will bring up various different paste options. These are the different paste options which we explored a moment ago. This time once again, we are going to click on the Paste Link option which is listed in the section under Other Paste Options. This has the exact same effect as the Paste Link operation we carried out a moment ago. Once again, when we click into one of the cells, we can confirm from the formula box up top that we have the link to the external spreadsheet GM-SalesData.xlsx.

 

The name of this link includes the worksheet name USA, as well as all of the delimiters we discussed a moment ago, such as the square brackets and the exclamation point and single quotes.

 

[Video description begins] He highlights the external link: ='[GM-SalesData.xlsx]USA'!C13. [Video description ends]

 

Note that all of these elements are a part of the formula itself. So now for instance, if you would like to copy paste this formula down within this workbook, we can go ahead and copy.

 

[Video description begins] The displays the following formula: [WorkbookName]SheetName!CellNumber. [Video description ends]

 

So this is cell A14 and then we hit Paste Special and click on fx which will paste formulas. And this, we can see, results in an external link being created in cell A15 as well. We can confirm this from the name box up top, which tells us we are in cell A15, the formula box over somewhat towards the right, which has the same external link format.

 

[Video description begins] He highlights the external link: ='[GM-SalesData.xlsx]USA'!A15. [Video description ends]

 

So the lesson from this is that if we copy paste formulas which have external links embedded within them, Excel knows how to do the right thing. It keeps the external workbook format and worksheet format constant, but it correctly updates the cell references. Let's now try an even more direct way of linking to an external sheet, so we've now positioned our mouse in cell B15, we hit the = sign. And now, this is where it gets interesting.

 

We will switch over to an external workbook. One way of doing this is using the Ctrl+Tab keyboard shortcut on a Windows machine or you could just use your mouse. In either way, we find ourselves back in GM-SalesData and we use our mouse to select an area, so we have selected cells B15 through C18. Now, we switch back to the destination sheet and there, if we hit Enter, all of these values are going to be pasted into the corresponding values here. Now, there's one very important point to note here, notice how cells B15 through C18 of this, the destination worksheet, are all highlighted together. What's more, if we pay close attention to the formula bar up top, we can see that there is an array or a range formula here. And that range formula has absolute cell references.

 

[Video description begins] He highlights the range of cell formulas: ='[GM-SalesData.xlsx]USA'!$B$15:$C$18. [Video description ends]

 

This is denoted by the $ signs preceding the row and column identifiers, B15 and C18. These $ signs mean that if we now were to copy-paste these formulas around on this sheet, the cell references will not be updated by Excel. So the semantics of establishing external links differ based on whether you choose to copy and then paste links, versus whether you choose to just use the = sign and then navigate to an external spreadsheet.

 

In any case, let's quickly move on and make the formatting in this sheet uniform so that it looks exactly the same as the original sheet. Once that's done, let's go ahead and try a little experiment. Let's find a cell in the original sheet and update it and then see whether the link in the destination sheet changes as well. So now let's toggle over from Book4 to the original sheet, GM-SalesData. There let's edit cell B17 from 245303 to 245315, this is for the month of December and the cell is B17. Now, let's toggle back to the destination book, the one titled Book4. And when we go there, we can see that the link has automatically updated. The contents of cell B17 in the destination, book reflect the new value of 245315. We have successfully demonstrated different ways of linking the contents of different Excel workbooks.

 

In this demo, we'll keep going with our exploration of external links. There are a lot of little details which you've got to keep in mind when you're linking one workbook to another. These details can sometimes lead to gotchas. So this is a feature you should use with care. Let's pick up right from where we left off. In the previous demo, we had an original data workbook called GM-SalesData. Then we had linked to this from another workbook called Book4. On screen now is Book4. Let's go ahead and copy the data in this workbook. Now let's go ahead and click on the File menu and create yet another workbook. This one is going to be called Book5. So this is going to be a new blank workbook. And in this Book5, we are going to paste the formulas which we had just copied in from Book4.

 

We now once again click on the little arrow below the Paste button and choose the Paste link option from the Other Paste Options tab. When we do this, we now have all of the data from Book4 copied into Book5. The interesting bit here is that this data now is embedded in the form of references to Book4 and not in the form of references to the original data source, GM-SalesData. We can confirm this for ourselves by clicking on cell A1. In the formula box up top, we can see that the name of the external workbook is Book4. This is enclosed within square brackets. The name of the external sheet in that workbook is Sheet1, then is the exclamation point followed by the cell reference.

 

[Video description begins] He highlights the name of the external link: =[Book4]Sheet1!A1. [Video description ends]

 

As an aside, if you're wondering why there are no single quotes here, that's because single quotes are used when the names of a workbook or a worksheet contains spaces.

 

Again, the important lesson to be learnt here is that if we have an original data source, let's call that workbook A, then we have another workbook B, which links to workbook A. And then a third workbook C, which links to workbook B. The links will not carry forward to the original data source. That's the lesson we learnt from this little example. Let's now move on to another example. We don't need either of these books, so we can exit out of both of them without saving. So we exit out of Book4 as well as Book5 and find ourselves back in the original data source. This is the book titled GM-SalesData. Let's go ahead and copy this data once again. And then let's go ahead and create a new workbook. This is an entirely fresh workbook. We do this by clicking on File, New, and then Blank workbook. This one is going to be named Book6. And once again, we click on the little arrow below the Paste button and choose the Paste Link option.

 

So far everything is identical to the previous examples. But now we're going to do something quite different. First, let's set the stage. We click on an arbitrary cell and confirm that the links are indeed valid. So you can see, for instance, in cell B3, the contents of the formula bar confirm that we have a link to the original data source GM-SalesData.xlsx.

 

[Video description begins] He highlights the external link: ='[GM-SalesData.xlsx]USA'!B3. [Video description ends]

 

Let's just quickly fix up the formatting to make the sheet a little easier to navigate. And once all of that is done, let's click on the Data menu item right up top. This brings up, of course, a new set of submenu items. And there, let's click on the little button titled Edit Links. This is an important menu item. If you're ever going to be working with linked workbooks, you should be thoroughly familiar with the Edit Links dialog box. On screen now, you can see first off, a list of all of the links in the current workbook. The Source column includes the name of the external workbook, GM-SalesData.xlsx. Then we have the Type, which is worksheet, whether it's been updated, and the Status.

 

We can check the status by clicking on the Check Status button over towards the bottom right of this dialog box. Also note that the location of this file is visible as well. We can see that it's on the C:\Users\admin\Desktop folder. This is important if we have multiple copies of the source workbook floating around. This location will tell us what data we are currently working with. Now let's click on the Open Source button. This will cause the original source workbook GM-SalesData to be opened. We can now examine all of the data here. Again, this is a really useful bit of functionality in case you start linking workbooks and then you find that your destination workbook somehow doesn't seem to reflect the data that you think is in the source workbook. Now for an even more interesting bit of functionality. Let's leave this workbook open and then toggle back to Book6. There, let's again click on the Edit Links button. Remember, again, that this is inside the Data menu item. This time, let's go ahead and click on the Change Source button.

 

This is a really important way in which we can change, with one click of the mouse, the source data of all of the linked cell references. Why is it so important you ask? Well, that's because in real world scenarios when you link to a workbook, almost invariably you will find yourself in a situation where you have linked to a stale copy of the original data source. So then clicking on Change Source will allow you to change or refresh the data source with a single click. On screen now, you can see that we've got two copies of the GM-SalesData Excel workbook. The one we were referring to was simply called GM-SalesData. The one that we wish to refer to now is called GM-SalesData(Canada). Let's go ahead and point to the GM-SalesData(Canada) workbook and then hit OK. But wait a minute, this doesn't quite work. Let's read this error message and see what the problem is. Excel is telling us that we've got to check the cell references, range names, defined names, and links to other workbooks. Well, all of that seems to be fine.

 

But it turns out that this arcane error occurred because the new source workbook is still open. So let's hit OK, exit out of the Edit Links box, and switch over and close the GM-SalesData(Canada) workbook. So we find all currently open Excel workbooks by clicking on the Excel button in the toolbar down below. And then let's go ahead and close the GM-SalesData(Canada) workbook. Now let's come back to Book6. Let's go ahead and select all of this data and click on the Edit Links button. Let's retry the Change Source operation. So click on it, navigate to GM-SalesData(Canada). And then try hitting OK. This time everything works just fine. We can see that all of the data values get updated. And we can also see that the Source field of the Edit Links box has been updated as well. It now reads GM-SalesData(Canada). If we close this box, we can see that some cell locations which blank in the new data source just appear as 0.

 

We can see these starting down below from row 15. Let's also take a look at the contents of the formula bar. We can see, for instance, that the formula for cell A1 contains a reference to the workbook C:\Users\admin\Desktop\GM-SalesData(Canada).xlsx.

 

[Video description begins] He highlights the contents in the formula bar: ='C:\Users\admin\Desktop\[GM-SalesData(Canada).xlsx]Canada'!A1. [Video description ends]

 

Now if we go ahead and click on the Edit links dialog once again, this time we will see that the source is listed as GM-SalesData(Canada). What's more, if we click on the Open Source button, it's the GM-SalesData(Canada) workbook that will open up. Let's close the source. Come back to Book6. Again, click on the Edit Links button because there's more functionality there left for us to explore. Next, let's go ahead and customize the Startup Prompt. That's the button over in the bottom left of the Edit Links dialog. This is our way of controlling what users see when they open up a workbook which contains external links. When this workbook is opened, Excel can ask whether or not to update links to other workbooks. There are three options.

 

The default selected option is to let users choose to display the alert or not. The second option is to not display the alert and not update the links automatically. This second option is likely to result in a newly opened workbook full of error messages such as #N/A or #VALUE. The third option is don't display the alert but update the links anyway. This is somewhat mysterious, the user doesn't really know where this data is coming from. In any case, let's go with the default. We like the default, so let's click OK. And then let's go ahead and check the status of this link. We click on the Check Status box, and notice how the contents of the Status field up above get updated from Unknown to OK. Finally, let's check what the Break Link option does. Let's click on that Break Link button. The message which Excel displays is quite self-explanatory. Breaking links will permanently convert formulas and external references to the corresponding values.

 

This cannot be undone. And so if you're going to break links, you are well advised to first save a version which contains the links. Let's go ahead and break these links. And once we do this, we can Close out of the Edit Links dialog. If we now navigate around within our workbook, we can see clearly from the formula box up above that there are no links at all. All of these cells are now hard coded as values. This gets us to the end of our exploration of external links. These are extremely powerful, but as you can see, also somewhat fragile and a little unpredictable. So if you do decide to make use of External Links in your workbooks, please make sure that you thoroughly understand the Edit Links dialog.

 

How is an Excel workbook to be printed? This might seem like a boring question, but trust me, in a pinch, there are situations where this is really valuable knowledge to have. In this demo we are going to focus on printing and customizing the manner in which Excel workbooks are printed. This is a video game data set that we used in a previous demo. Let's go ahead and click on the File menu item on the top left. The green toolbar appears with a set of options, let's navigate down and click on Print. There are several useful options here. Let's for now, focus on the preview which dominates the right hand side of the screen. You can see that this document is going to be printed as three pages. We can use the buttons to navigate and examine what those three pages are going to contain. We can see right away that this is not an ideal page layout. Because for instance, on page 3, we just have the two columns with sales for rest of the world and global. Let's click on the Back button in the top left corner. That takes us back to the workbook.

 

Now let's click on the View menu item. And there, let's try and customize the Page Layout by clicking on the Page Layout button. This gives us a great sense of what the printer is going to output, but this might not be exactly what we have in mind. As you can see, it closely mirrors the preview that we had when we clicked on Print. So now let's try a different tack. Let's go ahead and click on the button, Page Break Preview. This is an important page because it allows us to actually change the page layout. The dark blue dashed lines are movable, we can move them around using our mouse. So for instance, now we have taken the page boundary, over to the extreme right. Now all of the data on screen shows up as page one. Printing can be a little complicated to get right. Notice, for instance, that column B looks a little truncated, all of the years just show up as # marks.

 

Let's change that by resizing column B, but now over on the extreme right we see a new dark blue dashed line has reappeared. So when we resized column B, Excel's Print functionality changed the page layout once again, so that we went back to having two pages with only the global column on the second page. We can confirm that this is the case by clicking on the Page Layout button, and there we can confirm that there are indeed two pages, and the global column appears all by itself on page two. This is not what we had in mind, so let's go back to the Page Break Preview mode, and there let's force Excel to do our will by moving that dark blue dashed line over to the right. Now if we switch back to the Page Layout, we can confirm that everything appears in just one page. All of this was in the View menu. Let's now click on File and go back to the Print menu. Now we can confirm there in the preview over on the right that we have just the one page. Let's explore some of the settings. We can choose to print active sheets. We can also choose to print the entire workbook or only print the selection. If we change this option to Print Entire Workbook, we can see in the preview now that Microsoft thinks that we'd like to print 10 pages.

 

Let's navigate through all of those 10 pages, and we can see very clearly that this is not quite what we had in mind, but this is still a handy way to explore the other aspects of the print functionality. We can choose to print Collated copies, so that if you print multiple copies, successive pages will appear one after another. We can change the orientation from Portrait to Landscape. If we'd like to change the type of paper that we are printing on, we can choose between Letter, A4 as well as more paper sizes, you can see that little button down below. Finally, we can also modify the margins of our printed document. These include Normal, Wide, Narrow as well as various custom settings. The Custom Scaling button over at the bottom is interesting as well, it allows us various choices, the default is No Scaling, but we can also choose to Fit Sheet on One Page, Fit All Columns on One Page, or Fit All Rows on One Page. Next, let's click on the little Page Setup button right below. And that in turn allows us to customize page layout, margins, header and footer information, and the sheet.

 

We can use this Page menu in order to customize the orientation and scaling, as well as the paper size, print quality, and even the First page number which by default is automatically calculated. Let's click on margins. Here you can see that we can exert fine-grained control on the Top, Left, Right and Bottom margins, as well as the Header and Footer. We can also use the check box boxes to center on page either horizontally or vertically. Moving on to the header and footer tabs, these are actually really powerful. Here there are buttons to create Custom Headers as well as Custom Footers. Let's click on the Custom Header button. This takes us into an inner dialog where there are multiple different options available. We can separately customize the left, center and right headers, and each of these can make use of the different built-in bits of information. For instance, we can add the date. Here we've chosen to add the date to the left section, we've chosen to insert the file name into the Center section and let's go ahead and add the total number of pages into the Right section. Each of these is a special value, and the tooltips will give you indications of what special values are allowed.

 

Now hit OK. Let's perform a similar operation for the custom footer. We keep it simple and just add the page number into the Right section. Then we hit OK and navigate into the Sheet tab. This also has potentially interesting information, such as presence or absence of gridlines, colors, draft quality, and so on. Let's add gridlines into our workbook. We are now ready. Let's go ahead and hit OK. We can now see that the Print Layout has been updated accordingly. We now have gridlines as well as the header and footer information that we specified. So we have the date on the top left, the file name in the top center, the total number of pages in the top right, all of those are a part of the header. And then in the bottom right we have the current page number. Now all of this looks great. But what if we have a situation where we would like to only print the specific view of a workbook?

 

So let's say for instance, that we would like to print one version, which includes information for Europe, and another version which includes information for North America, we presumably do not want the users in Europe to have information about North America and vice versa. Now of course, it's possible to accomplish this in a very painstaking manual fashion. But it turns out that Microsoft Excel supports custom views, which will make this process really seamless. Let's begin by highlighting some of the columns which we wish to hide from some users. So we've gone back to our spreadsheet, selected columns F through I. These include data for Europe, Japan, the rest of the world and global sales. Then we right click and hide these columns so that all that's visible now is general information as well as column E, which has information about North America. Now let's go ahead and select this data. And then up top inside the View menu, we click on the Custom Views button. This is important. Once we click on the Custom Views button,

 

Microsoft will first display all of the pre-existing custom views. There are none. Let's go ahead and click on the Add button over on the right We are prompted for a view name, let's type in NorthAmericaSales. Note that down below the checkboxes for Print settings and Hidden rows, columns and filter settings are both checked. Now let's go ahead and hit OK. And our first custom view has come into existence. We can always unhide all of the hidden rows by simply double-clicking on the border lines. Let's go ahead and create yet another view. So this time let's select all of the columns other than Europe and then right click and hide them. That way, the only information visible on screen is going to be general information as well as revenue information for the Europe region. Let's go ahead and click on the Custom Views button again. You can now see that our NorthAmericaSales view appears in there. Let's click on the Add button over on the right and try and create a new view.

 

Let's give this a descriptive name, let's call it EuropeSales. Once again, both of the check boxes down below are checked. Now, let's unhide all of the hidden columns. So we are back to having all data visible. Then let's go ahead and click on Custom Views, but this time we are not seeking to create a new view. Rather we select one of the two pre-existing views, the NorthAmericaSales view, and then choose to show that view by clicking on the Show button on the right. The appearance of our spreadsheet has changed. We can only see all of the North America-specific information. But what's even more important is that if we now navigate to the File menu and choose to try and print this document, the print preview will now only have the views for the North America Sales Users. You can see that we have all of the columns in the North America sales view, but we don't have any of the information about Europe, Japan, or the rest of the world. And in this, we have successfully demonstrated the use of Custom Views to restrict what information is going to be printed when we try and print our Excel workbook.

 

In this demo, we will continue our exploration of the print functionality in Microsoft Excel. This time we are going to try and explore the use of images embedded in the headers and footers, and how the properties of these images can be modified. We'll continue working with the same data that we were using in the previous demo. Let's begin by clicking on the Insert menu up top. This leads to the introduction of many new menu items such as those related to pivot tables, add ins, charts, sparklines, filters, links, comments, and so on. We have encountered some of these already. But for now we are actually interested in the Text button, which is way over to the extreme right. As usual, we click on the little arrow below this Text box and that opens up a submenu. These submenu items have to do with the addition of different text elements to our workbook. So for instance, we could add a text box, modify the header or footer, add WordArt, a signature object, or add objects such as external images or symbols.

 

We are indeed interested in inserting images, but this time we'd like to do so into the header or the footer. So let's click on the Header/Footer section. And that brings us to a dialog where we can customize the header. This ought to look familiar. We have encountered this while creating customized page setups in the previous demo. So we click on Custom Header. This has the same date, file, and page number information that we had in here the last time. All of this seems quite full. So let's go ahead and customize the footer instead. Same dialog opens up and we now click on the second little button over from the right. The tooltip tells us that this is the way to insert pictures. We are now prompted to insert pictures, and we can choose to do so either From a File, or from Bing Image Search, or from our OneDrive - Personal repository. For now, let's just upload an image from file.

 

We navigate to the image. This image has been sourced from pexels.com, you can see the exact URL on screen now. Pexels is a great source of open source images. We hit Insert and that causes this image to be added into our custom footer. You can now click OK. And once you're done with this operation, you will get a preview of the image. Notice how the footer has this special keyword, &[Picture]. We've successfully managed to insert a picture, but it's a different matter whether it shows up as we wanted it to. Let's navigate to the File, Print menu. And there, let's examine the print preview. We can see quite clearly that this image appears a little out of place. Even though the picture has been inserted into the footer, it has clearly overflowed into the data section. So let's go ahead and fix this. Now one way to fix this would have been to use a smaller image. Another alternative is to embed the image into the file itself rather than putting it in the footer.

 

So let's go ahead and remove this image from the footer. We hit the Back button, navigate back to the Insert menu, click on Text, go to the Custom Footer, and there remove this picture. With this, we no longer have an image in the footer. The approach that we are going to take now is instead to embed the image as the background. We can do this from the Page Layout menu up top, hit the button titled Background. And once again, we will be prompted for an image to upload. Once again, we upload this image from the local file. And it's the same image from the Pexels URL a moment ago. Doing this has a dramatic effect. We now have this image as a background, you can see that it's tiled. Unfortunately, we can see that it also obscures all of the data. So let's go ahead and try yet another approach. We Delete Background from the Page Layout menu. Then navigate instead to the Insert menu. This time, instead of the Text, it's the Illustrations button that we click on. This allows us to insert Pictures, Shapes, Icons, 3D Models, SmartArt, and even Screenshots.

 

Let's click on Picture and insert a picture from this device, the same image from the Pexels URL. And this time, once we hit Insert, image appears in just one place so it's no longer tiled. We can now resize this image using the handles at the extremities. We can also control its transparency. Notice how there is now a Picture Format menu up top. There are plenty of options here, Remove Background, apply Corrections, Color, Artistic Effects, Transparency, and so on. For now, let's focus on the transparency. That's an easy way to prevent the image from getting in the way of the data. So we click on it, and that gives different Picture Transparency Options. We select one. This has a transparency of 65% and we decide that this is sufficient for our purposes. There's quite a bit more that we can do. We can apply various artistic effects. And we can also click on the Color button and recolor it so that it fits in better with the background formatting.

 

You can see in this manner that we get the image to appear really nicely blended in with the underlying background. Let's now move to the File menu and try and print this image. We can see that the data has been printed along with the image. Let's move on and let's try another experiment. Let's head back into the spreadsheet, click on Illustrations. And this time, let's try and add a picture in from an online repository. So we click on the Online Pictures option. This brings up a searchable interface. We search for PS4 video games, pick an image which we like, and insert it into our spreadsheet. This image can be resized just like before. We now resize it and place it in the header row. Let's right-click on this image. There are many different options. Let's go with the Format Picture option. The dialog opens up over on the extreme right. Here we can modify the fill or the kind of line outlining.

 

We can also change the transparency, the width, as well as many other properties of this image. We can apply shadows, reflections, and so on. Once we are satisfied with the appearance of our image, let's go into the File, Print menu. And there we can see in the print preview that the image appears beautifully formatted within the data. Let's say that, for whatever reason, we would like to not print the image. Images like this can be heavy duty and we might just not want them in hard copies. That's easy enough to fix, simply right-click on the image. And once the Format Picture dialog opens up, click on the third tab, the one in the middle. This allows us to control the size and properties, and there is a little checkbox there called Print object. This is checked by default. If we now uncheck this and close this out and head back into the print preview, we will find that the picture is not printed.

 

Now all of these changes have been performed in the print preview. Let's go ahead and confirm that they actually translate to a real print. The easiest way to do this is to export to PDF. We've performed this operation before so we hit File and then Export and then click on the Create PDF/XPS button. This causes the file to be published. When we open that file, it opens up in a browser, and we can see that its appearance indeed is exactly as we had in mind. Because we had unchecked the Print object checkbox, this PDF does not contain the image. This brings us to the end of our exploration of the print functionality in Microsoft Excel.

 

In this demo, we are going to explore protecting Excel workbooks so that they cannot be written to except by authorized folks. To begin with, let's click on the Review tab over in the top menu bar and then click on the button titled Protect Sheet. This brings up a dialog box where right up top we are prompted for a Password to unprotect the sheet. We are going to enter a password here in a moment. But before that, let's examine the rest of this dialog box. Below the password box is a check box which confirms that we would like to use the password to protect the worksheet and the contents of all locked cells. Then comes a list of items. Each individual item has a check box next to it. These check boxes will be used in order to allow all users of the worksheet to perform the corresponding actions. By default, all users can select locked as well as unlocked cells. That's because the first two rows are checked. None of the other rows are checked, let's change that. Let's go ahead and check a few more boxes.

 

Let's allow all users to Insert rows, Insert columns, Delete columns, and Delete rows. Please note that all of these checked items are activities which all users are allowed to do whether or not they have the password. The unchecked items are forbidden except for those who have the password with them. Next, let's go ahead and enter a password of our choice. We hit OK, we are prompted for a confirmation so we'll have to re-enter this password. Please note that if you lose this password, there's no easy way to recover an Excel workbook. Also, please note that passwords are case sensitive. So again, please don't lose your Excel password, it won't be easy to open your workbook. In any case, now, if we try and perform edit operations on this protected sheet, we will get an error message telling us that the cell or chart we're trying to change lies on a protected sheet. As the dialog box tells us, in order to make changes, we need to unprotect the sheet.

 

Also note how down below there is a lock icon next to the name of the worksheet. You can see for instance that the tab USA has a lock icon next to it, however the tab Canada does not have that lock icon because we have only protected the sheet USA. If we try to make changes to the Canada sheet, there's nothing stopping us from doing so. So we can go ahead and change the formatting, we can insert cell references, cell formulae. And all of this is allowed by Excel because we had only protected the sheet USA, and not the sheet Canada. We can't edit the contents of the sheet USA even if we try to. Let's change this by unprotecting the sheet. So we click on the Review menu option up top, and now click on the Unprotect Sheet tab. In order to unprotect the currently active sheet, we will need to enter the password which we set a moment ago. Now we will find ourselves at liberty to edit anything at all in the sheet USA.

 

Notice again that there is no lock icon next to the sheet name now at the bottom, and that's why we are able to add these formulae into the cells A16 through C16. Now, what if we'd like users to be able to edit some but

 

[Video description begins] He adds the following range of cells using formula: =SUM(C3:C14). [Video description ends]

 

not all cells in our worksheet? Excel has a way to provide for this. Let's click on the Review tab up top, and then click on the button titled Allow Edit Ranges. This brings up a dialog box where up top we have Ranges which can be unlocked by a password when the sheet is protected. Currently that area is blank. Down below we have a list which can be used to specify who may edit the range without a password. Let's click on the New button over on the right. This allows to define a new range. And then we can use our mouse in order to define a range. Here by default the range is the currently selected range B3 to B14. Also note how this range has been given a name. Range1 is the default name given to this range by Excel.

 

Let's click on the little Permissions box in the bottom left. This allows us to specify users or groups who can edit the range without a password. First we've got to add the users, and then we can specify the permissions. So we click on the button titled Add. This requires us to add users or groups. Let's type admin and click on the Check Names button. Excel will resolve this into the correct user role, that is ADMIN-PC\admin. Once we've defined the users who can modify this range, let's specify the permissions. And for this, let's go ahead and check the box down below, which allows these users to edit the range without a password. This is an example of role-based access control because we have a level of indirection. We have roles, such as the admin, and permissions such as edit without a password. In any case, moving along, let's go ahead and hit OK. We've got to do this a couple of times before we find ourselves back into the top level dialog.

 

Now we can see in the area up top that there is indeed a range that's called Range1. And this range refers to the cells B3 to B14. Notice the use of the $ signs which make this is an absolute cell reference. Now please note that at this point the tab USA is not protected. You can see this from the absence of a lock icon next to the tab name down below in the bottom left. So we've got to change this by clicking on the Protect Sheet button in the bottom left of this dialog box on screen. Once again, we get to the same dialog, we are prompted for a password. We type it in once and then we confirm it a second time. We note it down somewhere so that we don't lose track of it. And now the lock icon appears next to the sheet name USA. At this point, if we try and edit cells in general on this workbook, we will not be able to do so. For instance, if we click on cell C3, and then right click and attempt to Clear Contents, we get an error message. The cell or chart you're trying to change is on a protected sheet.

 

We click OK, and then we try and repeat the experiment. But this time we do it from cell B3, which is a part of the editable range we just created. Now if we right click and try to clear contents, we are indeed able to do so successfully. Let's again try with another cell outside the range B3 to B14, and once again, the same error message results. So it's clearly no coincidence. We only can change the contents of the editable range. Also note that we can undo the change that we just made. Of course all of this only matters because the sheet USA is currently locked as we can see from the lock icon. If we unprotect the sheet by clicking on the corresponding menu item, of course we will need to enter the password. But once we do this, the lock icon goes away. And we are now at liberty to change or clear the contents of any cell in the workbook. We've now demonstrated worksheet level protection in Microsoft Excel, let's move on to two other related topics. These involve the protection of individual cells, as well as of workbooks using passwords and encryption and decryption. That's in the demo coming up ahead.

 

Let's pick up right from where we left off at the end of the last demo. Here, we are going to start by working with cell-level protection. We have two sheets, USA and Canada. Neither of these is currently locked. We can tell this from the absence of the lock icons next to the sheet names in the lower left of your screen. With this as the starting point, let's go ahead and select a pair of cells. These are B and C16. Let's right-click and then choose the Format Cells option. This is how we're going to set up cell-level protection. This actually brings up a general Format Cells dialog. In which we can change many properties of the cell, the Number, Alignment, Font, Border, and Fill. But for now, it's the Protection tab way over on the right that we are most interested in. We click on it and find that there are two checkboxes. The first, which is checked by default, reads Locked, and the second reads Hidden. Let's take a moment to understand these. By default, all cells are locked and no cells are hidden. However, neither of these properties makes any difference unless the sheet has been protected.

 

Let's now change the value of the Locked checkbox. So we uncheck it and then hit OK. Please note that this only applies to the cells B and C16. And we can see that these cells are special in some way because each of these cells now has a little green on the top left corner of each cell. That green border is always worth investigating any time you come across it. Here, however, it appears simply because we have unlocked the cells but yet to protect the sheet. Let's change that by clicking on the Protect Sheet button over on top. We are in the Review menu. As before, we are prompted for a password and then asked to reconfirm the password. Once we do this, the sheet USA is locked. Notice the little lock icon next to the sheet name. And what's more, these two cells now no longer appear with the green border in their top left corner. Now let's try a little experiment. Let's try and edit a cell at random, cell C12. We right-click and attempt to Clear Contents, but we get an error message, the sheet is protected.

 

However, if we now try and edit either one of the two cells we just unlocked, we are actually able to do so. We change the formula so that it sums from B3 to B15 rather than B3 to B14, and there's no problem at all. And with this, we have demonstrated how cell-level protection can be provided and overridden by using the right-click and Format Cells menu items. Our emphasis so far was on worksheet and cell-level protection. Let's now explore workbook-level protection. Click on the Review tab, and then click on the Protect Workbook button. This is right next to the Protect Sheet button. Once again, we will be prompted for a password. This is the password which will be used to protect and unprotect the sheet. Now let's go ahead and click on the File menu item, scroll down, and click on the Info section. And there we will see that there are buttons to protect as well as to inspect workbooks and also work with version history and manage unsaved changes.

 

Let's click on the Protect Workbook button. There are many different options here. The first of these is Always Open Read-Only. The second is to Encrypt with a Password. The third is to Protect a Current Sheet. We can also use these options to Protect Workbook Structure, Add a Digital Signature, and Mark as Final. Each of these is an important bit of functionality. For now, let's click on the second, Encrypt with a Password. We will now be prompted for a password. This is required to work with the workbook. We will have to confirm the password. And as before, these passwords are case sensitive and better not lost. Once we hit OK, the appearance of the workbook changes. A little lock appears here. And we can see that a password is now required to open this workbook. Let's close this workbook. Let's save the changes and try and reopen it by navigating to it on our desktop. When we do this, we will only be able to proceed by entering the password we just set.

 

We type in the password. And once we successfully do so, the sheet is visible to us. Let's now go ahead and try another option. Once again, we navigate into the File menu, click on Info. And there we now again click on the Protect Workbook button. But this time we choose the first option, Always Open Read-Only. As the menu tells us, this is to prevent accidental changes. The workbook can now only be opened in read-only mode. Let's close this workbook, saving all of the changes, and try and reopen it from the desktop. When we click on it, we first need to enter a password. And that's because there are two levels of protection on the workbook. The first is password protection and encryption and the second is the read-only mode. Once we correctly enter the password, we are indeed able to proceed, but this is with an info message. Note that this is an info message and not a warning or an error. We can see that from the blue circle with the small letter i. The author would like you to open GM-SalesData.xlsx as read-only unless you need to make changes.

 

Are you okay to open as read-only, Yes, No, or Cancel. Note that the default option is Yes, but there's nothing preventing us from actually clicking on No. That would open this workbook in regular read/write mode. In any case, for now we opt out. So we click Yes and open it in read-only mode. We are now able to edit this workbook because we opened it by providing the correct workbook password. We can make whatever changes it is that we like. The issue only will arise when we attempt to save these changes. If we try and click on the little floppy disk icon over on the top left, we are going to get a warning message. This message tells us that We can't save GM-SalesData.xlsx because the file is read-only. If we'd like to keep our changes, we'll need to save the workbook with a new name or in a different location. So let's go ahead and hit OK and then do exactly that. Let's browse to a location of our choice and call this file GM-SalesData-Protected.xlsx.

 

Let's hit Save and try and reopen this after closing it from Excel. So we locate this new version on the desktop and click on it. This is GM-SalesData-Protected. And here we find that all of the levels of protection that we put in place on the original workbook apply here as well. Not only is this workbook password protected by the same password which we had entered on the original workbook, but this workbook is also going to open in read-only mode by default. So let's hit Yes. So we are now into our copy of the original data. Let's go ahead to the File menu, click on Info. And there, let's go ahead and turn off the Encrypt with Password option. At this point, this file has indeed been protected with a password. But remember that this password was actually set for a different notebook, for the source notebook from which this one was created. In order to get rid of the password, all that you need to do here is to delete the password in this dialog box. The proof that this password protection has gone away is from the text in the Protect Workbook box on screen now.

 

We can see that it no longer tells us that this workbook is password protected. It does indeed have a note about the file being in read-only mode, though. Let's go ahead and close this file. We'll be prompted to save this in another location. And this is because the file is still in read-only mode. Let's save this as a copy of GM-SalesData-Protected. So this is yet another file. We go ahead and locate it on the desktop. It's the file right on top. And when we open it, we find that the password protection has gone away. We do see the message prompting