Protecting Excel Spreadsheets with a Password

Microsoft Excel includes the ability to password protect workbooks in a couple of different ways. While the vast majority of spreadsheets usually don’t require the protection of a password, there may be cases where you want to ensure that others can’t gain access to a spreadsheet that contains sensitive information.

Excel allows you to password-protect spreadsheets in two different ways. The first is to apply a password that must be supplied in order to open a file, and the other is to apply a password that must be supplied in order to modify a file. In other words, an Excel spreadsheet can be protected with 2 passwords – the first allows the user to view the file (but not make changes to that file), while the other allows users who enter the correct password to modify the file as required.

The process by which password are applied to Excel files is simple to perform, but not particularly obvious. To begin the process, click File > Save As. In the upper-right corner of the Save As window click the Tools drop-down menu, and then select General Options. In the File Sharing section, enter a password to open the file (if necessary) along with a password to modify the file (if required). Either (or both) types of passwords can be applied, depending on your needs. When you click OK you’ll be prompted to confirm the passwords you’ve just entered. As always, don’t forget the passwords you’ve entered…

Change Excel’s Default Font and Font Size

Excel uses a 10pt Arial font by default, but that doesn’t mean that you have to live with it. Sure you can change the font manually at any time by simple selecting a new font style and size from the Formatting toolbar, but if you want Excel to always use a different font style by default, it’s an easy switch.

To make the change to the new font style and size that you prefer, simply click Tools > Options and then click the General tab. Use the drop-down arrow next to Standard font to pick your preferred font, and then choose a new font size from the Size option. Click OK to implement your new settings. From then on, Excel will use the font style specified for all new worksheets you create.

Renaming Cells in Excel

Working with Excel and it’s Battleship-like grid coordinates is easy once you get the hang of it, but worksheets can be tough to navigate when loaded with tons of information across scores of rows and columns. For example, you might be using cell H37 as the location of a “total” value, but how intuitive is it to remember that cell H37 serves that purpose? Wouldn’t it be easier to simply give the cell a name that makes it both easier to identify and to navigate to? Excel makes it easy to name individual cells, as well as ranges of cells if necessary.

I’ll stick to the example of a single named cell here. Click on the cell that you wish to name, and then click your cursor into the Name Box (it’s directly above column A, and will be the coordinate name of the cell you’ve just clicked on). With the current coordinate name highlighted, type a new intuitive name for the cell, for example Total. Press Enter.

What’s the benefit of going this route? Try this: Click on another unnamed cell, and then click the drop-down arrow in the Name Box. Select the name you entered in the previous step, and you’ll notice that that cell is now selected. Benefit number 1 is thus easier navigation.

The second major benefit is even more useful – once cells are named, those names can be used in formulas rather than traditional (A1) cell references. In other words, with appropriately named cells, you could have a formula that literally reads:

=subtotal+tax

or similar. A few rules to note include the fact that names must be unique within a worksheet, and you cannot include spaces in cell names. If you decide that you want to delete or edit a named cell (misnaming perhaps), head to Insert > Name > Define to make your changes.

Fitting Excel Spreadsheets to the Printed Page

Like most Excel users, you’ve probably experienced a situation where you’ve attempted to print an Excel spreadsheet only to find that your columns extend beyond the “printable” boundaries of the page. In most cases, this becomes obvious when the pages are output – the first page appears, followed by the next, followed by any of the right-most columns that wouldn’t “fit”. If the worksheet your trying to print contains hundreds of rows, this is more than just a simple annoyance, and trying to line up all of those misprinted pages to get the rows together is an exercise in frustration.

The obvious solution, and one that most users are familiar with, is to change the orientation of the printed page from portrait to landscape. This doesn’t always solve the problem, especially in cases where even landscape can’t quite make all of the columns fit. There are alternative solutions to this Excel printing woe, however.

One is to try to resize all of the fonts in your worksheet, but this isn’t always practical. As an alternative, I generally suggest one of two things – either scale down the size of the printed worksheet, or begin playing with your spreadsheet’s margin settings.

Scaling down a printed document in Excel is easy. Prior to printing, click File > Print Preview to see how “close” to fitting your document is right now. Click the Setup button, and then choose Portrait or Landscape as your Orientation as appropriate. Next, use the Adjust option in the Scaling section to select a new value, for example 90%. Click OK and return to the Print Preview to check whether everything fits, and then readjust the Setup Scaling value as appropriate. Unless your spreadsheet columns are far too wide to fit on a single page, you should be able to scale the document and make it fit in a readable printed format without too much effort. Barring that, it’s time to start adjusting margins or upping the ante to a larger paper size like 8.5×14 or larger.

Increase Screen Size in Excel

I hate to watch people squint at the screen when they use Excel. It’s no secret that we’re all getting older, and that generally speaking, our eyesight isn’t getting any better. Even with a reasonable screen resolution, Excel’s default font and cell sizes can seem tiny. While it may be tempting to start changing font sizes throughout a spreadsheet, there’s a better solution for working with Excel comfortably – using the program’s Zoom feature.

You’ve probably already played with the magnification settings on Excel’s standard toolbar, for example changing the “zoom” size to 200%. What most people don’t realize, however, is that there’s an even easier way to change the zoom in Excel, as long as you have a mouse with a scroll wheel.

To zoom an Excel worksheet to a higher magnification, simply press and hold down one the Ctrl key in the lower right of your keyboard. With the Ctrl key depressed, scroll the wheel on your mouse up to enlarge content, or down to make things smaller. Better news still? This trick also works with web pages – try it right now to increase or decrease the size of text on this page.

Hiding and Protecting Excel Formulas

After taking the time to create an Excel spreadsheet laden with formulas that actually work correctly, the last thing you want to have to deal with is some adventurous rogue user messing with your hard work. While the majority of people will leave formulas that they don’t understand alone, others will attempt to tinker with formulas or even overwrite them completely. For cases where you’re creating Excel spreadsheets for other users and don’t want your formulas messed with, one of your best options is to hide the formulas completely.

Hiding your formulas and preventing changes by other users is a multi-step process. Begin by right-clicking on the cell (or multiple cells) where the formula exists and select Format Cells. Next, click the Protection tab. Ensure that both the Locked and Hidden checkboxes are checked, and then click OK.

At this point you’ll notice that the formula is still visible when you click on its cell. To change this you need to complete step 2, where you actually protect the worksheet. To do this, click Tools > Protection > Protect Sheet. In the Protect Sheet window, you can either click OK to protect the sheet without a password, or enter a password that will need to be supplied to unprotect the sheet and make changes and then click OK. Note: don’t forget the password or you’ll lock yourself out!

After enabling protection, you’ll notice that the formula in the cell you applied the Locked and Hidden settings to is no longer visible. If a protection password was not applied, any user will be able to select Tools > Protection > Unprotect Sheet to make the formula visible once again. If a password was applied, it will be needed to unprotect the sheet to make changes.

Resizing Cell Widths in Excel the Easy Way

I can’t tell you how many time I’ve watched users struggle to resize all of the columns in an Excel spreadsheet individually, and manually. The need to resize the width of Excel columns is probably one of the most common tasks every Excel user needs to perform – most users do want the information in individual cells to be completely visible in most cases. When data in a cell exceeds the width of a cell and cell directly to its right also has data entered (ie, is not empty), Excel will automatically “cut off” the cell’s contents.

Let’s say that you have data entered in cells A1, B1, and C1, and all of the data extends beyond the right-hand edge of the cell. The contents of cells A1 and B1 will be cut off, while the contents of C1 will be completely visible, at least until data is entered in cell D1. Most users will try to resize the columns one at a time by placing their cursor between the A and B columns, left-clicking, and then dragging the cursor to the right until all of the data in column A is visible. Then they’ll repeat the process by resizing column B, then C, and so forth.

The easier solution? To automatically resize every column in your Excel spreadsheet such that all text is visible, click the small square at the intersection of Excel’s column letters and row numbers. When you click this cell, the entire Excel spreadsheet will be selected. To “automagically” resize every column on your sheet, just double-click between columns A and B at this point. It’s a huge time saver!

Changing Where Excel Saves Files

When you attempt to use the Save or Save As command in Excel to save a newly created file, Excel will automatically assume that you want to save it to your My Documents folder on your local PC. While this is a reasonable default for most users, it can be a pain to have to constantly change folders if you want these files saved to an alternate location. Rather than drilling through folders to pick a new location to save files, why not configure Excel to go to that location automatically? It can be done, with next to no effort.

A good reason to do this is found in corporate environments. Typically, only files saved on the server are backed up. So, if your personal space on the server is designated as drive U: (for example), then it would be better to have your files saved to that location rather than your My Documents folder, which presumably is not backed up. Changing the default “save” location can also be handy in situations without a server. Let’s say that you want to save all of your Excel workbooks into a folder named “Excel” within My Documents. By making this change, Excel’s “Save” dialog box will point to the location you specify automatically.

To change the location where Excel attempts to save files, click Tools, select Options, and then click the General tab. Towards the bottom of this window you’ll find an entry marked Default File Location, which probably points to a location like C:\Documents and Settings\Dan\My Documents right now. If you want your files stored to a sub-folder of My Documents named “Excel”, simply change this to read C:\Documents and Settings\Dan\My Documents\Excel. To change it to a network drive, simply enter the drive letter in question, for example U:\. Click OK once complete, and then click File > Save As. The Save As dialog box should now be pointing to the new location you’ve specified, and will for every new file try to Save. Additionally, Excel will open to this location when you use the File > Open command.

Creating Organization Charts and Diagrams with Excel

If you work in Human Resources, then you’ve no doubt spend your fair share of time creating (and changing, and changing again) organizational or “org” diagrams. Rather than fight with a program like Microsoft Word to create your organization charts, I strongly suggest that you take a look at the capabilities built into Excel first. Excel’s Organization Diagram capability makes drawing out these hierarchical relationships a breeze, even if you don’t particularly care for your own location on the corporate totem pole.

To create a Organization Diagram in Excel, click the Insert menu and select Diagram. In the Diagram Gallery window click the Organization Chart and click OK. Click to Add text (names, locations, etc) into the default boxes displayed, and then use the Organization Chart toolbar (or right-click) to add additional shapes that form the relationships you need to be displayed such as co-workers, subordinates, and so forth.

It will probably be necessary to resize your chart as the number of shapes you add increases. Use the dragging handles on the bounding box around the chart to re-size it as necessary.

To add the your Organization Chart to a Word document, click outside of the chart to de-select it, right-click on the chart area, choose Copy, and then Paste it into your Word Document.

Changing the Name Associated with Excel Comments

When you add comments to an Excel spreadsheet, you may be annoyed by the fact that your name doesn’t appear as the bolded commenter name by default. This revolves around the configuration of Excel, and the name that was supplied during the original installation process. This is especially true in corporate environments, where IT folks typically use imaging technologies to make all desktop systems the same.

Thankfully, changing Excel to supply your name in the header of a comment is easy enough. Just open Excel, click the Tools menu, select Options, and then click on the General tab. Towards the bottom of this window you’ll find an entry marked User name. Enter the name that you’d like to appear when you add comments to a cell, and then click OK. The next time you enter a comment in an Excel cell, the name that you entered will appear as the commenter name by default.

Want to know more about using Comments in Excel? See Adding Comments to Excel Worksheets