Read an Excerpt
[Figures are not included in this sample chapter]
Sams Teach Yourself Microsoft Excel 2000 in 24 Hours
- 3 -
Organizing Your Files
In the last hour, you saved the budget file you were working on. At thetime, you probably were not aware that you were performing one of the mostimportant functions of any computer program. Saving your work is a key elementof working in Excel or any software. The save process only takes a few secondsand ensures that you can return to your work later. If you exit Excel withoutsaving your worksheet, or the power cuts out, you lose all your work.
If you're using Excel in the office, or over a network, saving becomes evenmore important. Your worksheets might be part of a large project and need to beaccessed by your coworkers. Saving your workbooks to the correct drive andfolder is almost as important as the save itself. After all, you'd hate to beawakened from a sound sleep with a phone call from an irate colleague askingwhere you saved the amortization schedule.
In this hour, you learn the ins and outs of saving your work. During thelesson, you also refresh your knowledge of working with drives and folderswithin Windows. Finally, you learn how to copy files from one location toanother and how to delete them altogether.
Ifyou don't save your workbooks, you're playing a form of computerized Russianroulette. Your unsaved work is un protected. If the power goes off or yoursystem crashes, your worksheet is gone forever. Saving your work takes a fewbrief seconds and ensures that you or your colleagues will be able to open theworkbook later. A saved workbook can be stored indefinitely.
When you save your work in Excel, you need to consider a few points:
The location of the saved file--You can save to your computer's harddrive or to a floppy disk. If you're working on a network, you can save yourfiles to a drive and folder on the network.
The name of the file--The filename should be descriptive so that you orsomeone else can identify it. A cryptic name like SMBUD might make sense at thetime you save the file, but will probably confuse you and everyone else later,especially if you need to locate that one file from a long list.
The format of the file--If everyone in your office is using the sameversion of Excel, you won't have any problems. However, in many offices,multiple versions of Excel are in use. Because Excel file formats are notbackward compatible, you need to save the workbook so that others can open it.
Inmost cases, when you save a file, Excel automatically saves it to the MyDocuments folder located on your computer. However, you can place the fileanywhere you like. If you're working on a network, your network administratormust have granted you access rights to save a file in a networkfolder.
Network folder--A folder that's located on one of the network drives.Network folders can be shared so that everyone on the network can access them,or they can b e private, so that only you or your workgroup can access them.
Many Excel users are confused by the storage system that Windows uses forfiles and folders. Actually, the file system is simple to understand. Windowsuses a file folder metaphor for organizing the files on your computer. Both thefiles you create and the software programs you used to create them are storedin folders. You work with computer folders just the way you work with the paperfiles in your office. You can create a filing system that contains manysubfolders, or you can use a top-level folder to store your files.
The folders are stored on drives that are lettered alphabetically. Acomputer drive is the electronic equivalent of a filing cabinet. The hard driveon your computer is usually called the C: drive. If you're working on a newercomputer with a large hard drive, your hard drive may be partitioned into morethan one drive--say C, D, and E. Network users can have many drivesavailable.
NOTE: The carat character (>) is computerese for drive. So when you see the phrase C:>, you'll know that the phrase refers to the hard drive of a computer.
Picture your drives and folders arranged in one large file room. The orderof your drives and folders is arranged hierarchically. Each filing cabinet inthe room represents another drive. Within each drive is a group of folders. Afolder can contain files, subfolders, or a combination of both.
If you were actually filing papers in a real file room, you'd need to putaway the folders in one filing cabinet before you could open the drawers onanother cabinet. Electronic filing is much the same. You need to navigate upthrough the folders on one drive before you can go to another drive.
NOTE: If you're familiar with the old MS-DOS filing system, a folder is the equivalent of a directory, and a folder within a folder is analogous to a subdirectory.
Thefirst time you save an Excel workbook, you are asked to name your file. Youalso have the option of changing the drive and folder. After the first time yousave the file, you won't be prompted to change any information when you savethe file later.
Don't wait until you've completed the workbook to save it for the firsttime. Because the information on the screen is stored in the temporary memoryof your computer, it doesn't exist in a permanent form until you save it.Therefore, save the workbook for the first time as soon as you enter some basicinformation.
In Hour 2, "Entering Data," you saved a workbook using a few simple steps.In the next exercise, you perfect your saving techniques. Excel should be openand visible on the screen before you begin.
To Do: Save an Excel Workbook for the First Time
1. Click into cell A1 and type Save Practice. Press Enter whenyou're done typing.
CAUTION: If you type the data in a cell and don't enter it, you won't be able to save the workbook. You enter the data by pressing Enter, pressing one of the directional arrow keys, or clicking the green check mark in the Formula bar.
2. Click the Save button on the Standard toolbar. The Save As d ialogbox appears, as shown in Figure 3.1.
3. The default filename in the File Name text box is selected. TypePractice Save.
TIP: If the default filename appears highlighted, you do not need to delete it before you type the name of the file. The new text you type overwrites any text that's selected.
4. Click the Save button. The file extension .XLS is added to thefilename and the Save As dialog box closes. The filename you typed appears onthe title bar.
If the file extensions do not appear in the File name list, there is a wayto show them. You need to go to the Windows Explorer or My Computer, and selectthe folder in which your Excel files are kept. Then, select View, FolderOptions. Click the View tab and verify that there is no check mark in the boxfor Hide file extensions for known file types. If there is a check mark in thebox, click in the box to remove the check mark, and finally, click OK.
TheSave As dialog box, shown in Figure 3.1, contains all the tools you need tosave your file for the first time. The format of the box is very similar to theFile Open box that you see when you open a file that's been saved previously.Table 3.1 shows you the available options in the File Save As dialog box andprovides a brief explanation of what they mean.
FIGURE 3.1 The Save As dialog box provides many options for saving a file.
TABLE 3.1 SAVE OPTIONS
What It Means
Contains buttons to help you manage your files.
Click a folder to select it.
Save In text box
The current folder in which the workbook will be saved. If you want to change to another folder, click the drop-down arrow next to the listed folder. Excel then displays all the drives and folders available to you. If necessary, use the vertical scrollbars to navigate to the drive or folder you want. You can also click the Up One Level button on the toolbar to move up to the next highest folder.
Display of files already contained in the listed folder. Change the view of the files by clicking the Views button on the toolbar.
File Name text box
The name of the file. If you don't specif y a file, Excel assigns the file a name like Book1.
Save as Type text box
The type of the file. By default, Excel saves the file type as an Excel 9 workbook. If you're sharing the file with others who might not have the latest version of Excel, you can save the file in an earlier Excel format. You can also choose a format from another spreadsheet program.
After you've set all the options, click Save to make a permanent copy of the workbook.
Onceyou've done the hard work of setting a filename and determining in which folderthe file is stored, saving the workbook is a breeze. Just click the Save buttonon the Standard toolbar. If the toolbar isn't visible, open the File menu andchoose Save.
Remember to save your work often. Keep reminding yourself that the changesmade to the file on the screen exist only in temporary memory until you savethem. If you exit without saving, you will lose all the changes you made sincethe last save.
Eachworkbook that you create in Excel can be saved with a unique, meaningful name.Filenames can contain up to 256 characters and include both uppercase andlowercase letters, spaces, and punctuation. However, just because you can u seup to 256 characters doesn't mean that each filename should be a long, ramblingsentence. Filenames that are too long are almost as frustrating as filenamesthat are too short! Try to use as few words as possible to name yourworkbooks.
NOTE: Check with your coworkers or network administrator to determine whether your company has adopted naming standards for files. For example, filenames might include a departmental name, the project title, or the year.
CAUTION: If you're using Excel in the office or you're going to share your files with others, make sure that everyone is using a version of Excel (and Windows) that supports long filenames. If not, a long filename is truncated and contains a tilde (~) character at the end, such as LEGALDE~.
Youmay have noticed when you saved the practice file that Excel assigned a periodcharacter and then a three-letter suffix at the end of the filename. Thesoftware program generally assigns file extensions. Excel workbook files areassigned the extension .XLS by default.
TIP: It's not necessary to type the file extension when you're naming a file. Excel looks at the file type that's selected and adds the correct extension.
CAUTION: Make sure that you don't change the file extension. Although you might think that using a file extension like .BUD to identify all your budget files is a good idea, you'll have a hard time finding those files later.
The extension on a filename is a road map for Windows. When you open a file,Windows uses the file extension to determine which program that file belongsto. Table 3.2 shows the most common file extensions used by Excel and explainsa bit about them.
TABLE 3.2 EXCEL FILE EXTENSIONS
What It Means
Microsoft Excel workbook
Microsoft Excel template
HTM or HTML
Microsoft Excel add-in
Microsoft Excel workspace
Eventhough saving a workbook takes only a few seconds, it's easy to forget to clickthe Save button. When you're really busy or under the gun, you might not wantto break your concentration. Unfortunately, Murphy's Law determines that it'sat those crucial times that your system locks up , the electricity goes out, orsome other catastrophe occurs and your work is lost.
Excel's AutoSave feature, also present in other Microsoft Officeapplications, offers some protection against such disasters. AutoSave doesexactly what its name implies--it saves your work at specified intervalswithout any prompting from you.
To Do: Set AutoSave
1. Click the Tools menu and choose AutoSave. The AutoSave dialog box,shown in Figure 3.2, appears.
TIP: If the AutoSave selection is not visible on the Tools menu, you need to install the feature before you can proceed. To do so, choose Tools, Add-Ins, click the Autosave Add-in check box, and click OK.
FIGURE 3.2 Use the AutoSave dialog box to set up automatic file saving.
2. Make sure that Automatic Save Every box is checked and specify thedesired number of minutes between saves in the Minutes box. To change theinterval, click in the Minutes box and type the time interval you want.
3. If you want to see a prompt each time AutoSave runs, check the PromptBefore Saving check box.
4. Click OK to close the AutoSave dialog box and activate thefeature.
CAUTION: If you work on many workbooks at one time, you might be tempted to choose the Save All Open Workbooks option. Although this option offers extra protection against losing data, you'll notice a loss of system resources each time AutoSave runs. You'll probably be better off letting AutoSave save only the active workbook.
5. To deactivate the AutoSave feature anytime you're working in Excel, click the Tools menu, choose Add-Ins, and uncheck AutoSave Add-in.
Afteryou set up and save a workbook, you can save it with a different name. Savingan existing file with a different name preserves the original, pristine copy ofthe file. For example, in the office, you might create an expense report orbudget and then save subsequent updates to different filenames. Or you mightuse different versions of a workbook for forecasting or modeling. Later you caneasily delete the versions of a file you don't need.
In the next To Do exercise, you save the Practice Save file with a differentname. The Practice Save workbook should be open and visible on the screen fromthe first To Do in this hour.
To Do: Save a Workbook with a New Name
1. Click the File menu and choose Save As. The Save As dialog appearswith the name of the file highlighted in the File Name box.
2. If you want to assign a completely new filename, begin typing thename you want. Your text overwrites the existing name in the box.
3. If you want to amend the existing filename, click in the File Namebox and edit the name that's shown. For this exercise, edit the filename toread Practice Save 2.
4. (Optional) Click the drop-down arrow next to the folder name shown inthe Save In box and navigate to a different drive or folder.
5. Click the Save button. The Save As dialog box closes and the newfile name is displayed on the title bar.
TIP: You can also save your workbook to the World Wide Web. You learn all about Excel's exciting tie-in to the Web in Hour 24, "Worksheets and the Web."
Anyonecan sit down at your computer and look through your files. Maybe you share yourhome computer with other family members who can open your files. In today'scomputing climate, most offices and corporations work over a computer network.A network offers many advantages, including the capability to print your workto several different printers and to share your work with others. However,working on a network means that other people can access the files youcreate.
The people who can view your files can also change and delete them. Ifyou're working with sensitive or personal data or you don't want someone toalter your files, you might consider protecting them in a few different ways.
Anytimeyou want to protect a workbook from prying eyes, you can require a password onthe file. However, before you set up file passwords, check with your networkadministrator to make sure that passwords are allowed. Some networks frown onuser-created file passwords and provide other options for securing data.
If you set up a file password, make sure it's one that you can remember.Passwords are serious business; if you lose or forget the password, your filebecomes inaccessible to you. "Cracking" a password is almost impossible.
TIP: Play it safe; jot down the password you assign to a file and store it in a safe place. Make sure that one of your coworkers or your network administrator knows where to find the password in an emergency.
To Do: Set a File Password
1. When the file you want to protect is on the screen, click File, SaveAs. The File Save As dialog box appears.
2. Click the Tools button on the Save As toolbar and choose GeneralOptions. The Save Options dialog box appears, as shown in Figure3.3.
FIGURE 3.3 The password you enter is hidden by asterisks.
3. Type the password in the Password to Open text box. For securitypurposes, the letters you type are shown as asterisks. Click OK when you'redone typing.
4. The Confirm Password dialog box appears, asking you to reenter thepassword, as shown in Figure 3.4. Type the password exactly as you typed itbefore and then click OK. Excel returns you to the Save As dialogbox.
FIGURE 3.4 Confirm the password by typing it again.
5. Click the Save button to save the file and enable thepassword-protection option. Choose Yes when asked whether you want to replacethe existing file.
The next time you open the file, you'll beprompted for the password. If you share the password with others, they too willbe able to open the file.
Assigning Modification Rights
If you're working on very sensitive data, you can include an even greaterlevel of security to password-protected files. In addition to assigning ageneral password, you can add a password that allows only certain people tomodify the file. That way, you can en sure that the data in the workbook can bechanged by only a select group.
To assign a modification password, click File, Save As. Click the Toolsbutton on the Save As toolbar and select General Options. Type a password inthe Password to Modify box and click OK. Type the password again in theReconfirm dialog box and click OK. When you return to the Save As dialog box,click Save to save the changes you made to the file. Choose Yes when askedwhether you want to replace the existing file.
The next time the file is opened, you will be prompted for your password.After you enter your password, a box, similar to the one shown in Figure 3.5,appears. If the correct password is not entered, the user will be able to lookat the file, but not modify the data.
FIGURE 3.5 The password is needed to modify the data.
NOTE: Loose lips sink ships! If the data in your files is sensitive enough to warrant password protection, make sure you provide the passwords to only the people who need to see the files.
CAUTION: Always check with your supervisor or network administrator before you add passwords to your worksheets. In a collaborative work effort, passwords can sometimes create unwanted problems.
Toprotect your data without setting file passwords, you can turn on the read-onlyoption. A read-only file doesn't need password protection. To assign theread-only option, check the box next to Read-Only Recommended in the SaveOptions dialog box (shown in Figure 3.3).
After the read-only option is assigned, anyone who op ens the file will seethe warning box shown in Figure 3.6. When the file is open, the words ReadOnly appear in parenthesis next to the filename on the title bar.
FIGURE 3.6 The Read-Only warning box specifies that the file should not be changed.
Unfortunately, the Read-Only option only recommends that users should viewthe contents of the file and not make changes. Any changes can be saved to anew file with a different name. If someone clicks No in the Read-Only warningbox while they're opening the file, the file opens normally and changes can bemade and saved to the original file.
Youcan't be too careful with your data. If you operate on the premise that anounce of prevention is worth a pound of cure, you'll always be protectedagainst the unexpected. Most of the options discussed here take only a fewseconds to set up. This small time investment will pay for itself.
Backupcopies of important files are essential to your peace of mind. Keeping backupcopies of important files ensures that you can always retrieve workbooks thatare damaged or lost.
Options for the Home User
If you're working with Excel on a home computer or your office computerisn't linked to a network, keeping backup files of your work is essential.Excel offers a backup option that creates a copy of your workbook every timeyou save the file. The backup copy of the file is saved in the same folder asthe original file. If the original file is lost or damaged, you can open thebackup copy.
To set the automatic backup option for the current workbook, click File,Save A s to display the Save As dialog box. Click the Tools button on the SaveAs toolbar and select General Options. Check the box next to Always CreateBackup. Click OK to close the box and return to the Save As dialog box. Nowclick Save to save the file. You need to set the option for each file you wantto backup automatically.
One problem associated with Excel's automatic backup system is that Excelworkbooks can take up a lot of hard drive space. You might not want to fillyour hard drive with duplicate copies of files. In addition, automatic backupscan create a false sense of security. If the hard drive of your computercrashes, the original file and backup will be gone.
Instead of setting automatic backups, consider manually copying the file toa floppy disk. You learn how to quickly copy files later in this hour. Or, ifyou don't already have one, consider adding a tape drive and a backup programto your existing computer. For a relatively small investment, you can back upall your computer files--not only the ones you create with Excel.
Network Users Have Other Options
If you're using Excel over your company's network and saving the file to anetwork drive, check with your network administrator before you worry too muchabout backup copies. Most networks have an automatic backup system that runsevery night. If you need to obtain a backup copy of your file, youradministrator can furnish a recent copy of the file.
Workbooksummary information, such as the name of the person who created the file, thecompany name, and keywords that identify the file are helpful if you work on alot of files. If you're working in a co rporate setting, summary informationtells everyone in a workgroup important details about the file.
Summary information is not completed unless you fill it in manually. To setup Excel to display the Properties dialog box every time you save a file, clickTools, Options and select the General tab. Check the box next to Prompt forWorkbook Properties, as shown in Figure 3.7, and click OK. Subsequently, theProperties dialog box appears each time you save a file for the first time. Ifyou don't want to fill in any information, click OK.
FIGURE 3.7 Set the option to prompt for Workbook Properties each time you save a file for the first time.
To view the summary information about a file, click Save As. Right-click thefile in the list of files (if the file isn't in the current folder, you'll needto navigate to it first). Click Properties from the pop-up menu. The Propertiesdialog box for the file has five tabs across the top: General, Summary,Statistics, Contents, and Custom. Click the Summary tab to view the informationthat's been entered about the file, as illustrated in Figure 3.8.
FIGURE 3.8 The summary information for the current file was entered previously.
You can view or change any of the information that's displayed. Click OK toclose the Properties dialog box for the file. When you return to the Save Asdialog box, click Save if you made changes to the summary information. If not,click Cancel to close the Save As dialog box and return to the file on thescreen.
Sometimesyou open one workbook. Other times, you might need to open several workbooksand switch between them. After you arrange the workbooks on your screen,consider using the Save Workspace command. The Save Workspace command saves theopen workbooks plus their present location on your computer screen.
To use this command, click the File menu and choose Save Workspace. The SaveWorkspace dialog box opens, as shown in Figure 3.9. In the File Name box, typea name for the workspace file. Workspace files have the extension .XLW. Then,click Save.
FIGURE 3.9 Use the Save Workspace dialog box to save your arranged workbooks in a workspace file.
You open a workspace file as you would any Excel file. Just click the Openbutton on the Standard toolbar, and double-click on the workspace file. Excelopens the file and arranges the workbooks the way you had saved them.
Whileyou're working in Excel, you can copy, move, and delete workbook files. You cancreate a new folder on the fly--as you're saving a file. Excel's filemanagement tools are fine for small, one-time jobs. If you're planning toreorganize your filing system or delete many files, Windows Explorer and MyComputer offer more powerful file- management tools.
TIP: Remember that you need to select a file before you can do anything to it. You select a file by clicking it one time.
Asyou learned earlier this hour, it's a good idea to save your work. There are somany ways that files can get lost or damaged. However, not every file youcreate needs to be saved for posterity. For example, you can discard a grocerylist or some calculations that you performed in the office after you're donewith it.
Files that are stored on your computer's hard drive go to the WindowsRecycle Bin when you delete them. You can always go into the Recycle Bin andget them back. Files stored on network drives don't go to the local RecycleBin. Delete a network file only if you're sure you no longer need it.
To Do: Delete a File
1. Click File, Save As to open the Save As dialog box. The Save As (and Open) dialog boxes contain Excel's file-management features.
2. A list of the Excel files saved in the My Documents folder appears.Click the Practice Save file to select it.
3. Click the Delete button on the Save As toolbar.
4. Click Yes to delete the file when the Confirm Delete dialog boxappears, as shown in Figure 3.10.
FIGURE 3.10 Use the Delete button on the Save As toolbar to delete a file.
5. Because you are not deleting any other files right now, click Cancelto close the Save As dialog box and to return to the Excelscreen.
CAUTION: You can't delete a file that's currently open. If you get a message advising you that access to the file has been denied, check to see whether you or one of your coworkers is using the file.
TheSave As toolbar provides many tools to help you organize your files. Table 3.3shows you each button and explains its function.
TABLE 3.3 SAVE AS TOOLBAR OPTIONS
What It Does
Up One Level
Moves you up to the next level of folders on your computer or network.
Search the Web
Launches Internet Explorer. You should be connected to the Internet before you click this button.
Deletes the selected file.
Create New Folder
Creates a new folder beneath the current folder.
Click the drop-down arrow to select a different view of the file listing in the current folder. You can even preview a selected workbook.
Inthis hour, you learned about saving the files you create. You also worked withsome of the options Excel provides for saving your work. You also learned howto delete files you don't need any more and explored the buttons on the Save Astoolbar. In Hour 4, "Managing Your Files and Workbooks," you learn how to op ensaved workbook files and create new ones.
Q I accidentally closed Excel without saving my workbook. Is it gone?
A If you were working on a brand-new workbook and exited Excelwithout saving, the file is gone. If you were working on an existing file, onlythe changes you made since the last save are lost.
Q If I save a workbook file with a new name, what happens to the originalfile?
A Absolutely nothing! At the time you save a file with a new name,the old file closes and remains in the same folder. You can access it at anytime.
Q How can I tell which one of my coworkers worked on my worksheet?
A Unless your coworker completed the file summary information, youwon't be able to determine who changed the file last. However, your networkadministrator may be able to tell you who saved the file last.
Q I know I'm typing the correct password on a protected file, but Excelwon't accept the password. What am I doing wrong?
A Are you sure you're typing the correct password? If so, are youtyping the password with the correct case. Excel passwords are case sensitive.Try it again.