Saturday, August 12, 2006

Working with files

Once you have explored the Excel application, the next step would be to
start working with it. This section contains

  1. Introduction to files
  2. Creating and saving files
  3. Opening files
  4. Save As
  5. Save As Webpage
  6. Save as Workspace
  7. File Search
  8. Renaming files
  9. Recovering files
  10. Autosave files
  11. Notify files to readwrite
  12. Sharing files
  13. Know your file
  14. File tips
Introduction to files
All the information you enter into Excel is saved as files. A file can be defined as data stored as a named unit on a data storage medium. A file is a just a logical device and nothing such exists on a computer since everything is saved in binary format. When you enter data in Excel, it is stored in a specific file format with a specific file extension. The default file extension for Excel is "xls". Of course, you may save the files in other formats also. File is what we call workbooks in Excel. For more information on files and file system, you may read this article. For more on computer basics, you may visit this website.

Creating and saving files
To work with files you may be using the standard toolbar a bit too often

Move your mouse over the toolbar icon to know the toolbar name. If it doesn't show
Go To Tools->Customize->Options and check(tick) the "Show screen tips on
toolbars" box and click OK.Now the screen tips would show up

You should create a new file to start working with. By default Excel opens a new workbook by the name "Book1". You way create your own file by clicking the new buttonon the standard toolbar. This creates a new workbook. The next step is to save the file. This could be done by clicking the save button When you click save, a box similar to the one below with appear.
In the Save in box, select the drive or folder path to save the file.
Under the filename box, give a name to the file.Click OK to save the file. Note you must be saving a file in a folder only when you first create it. Once you save it, subsequently the file would be saved in the same location.
All the icons in the Excel Save Dialog is similar to those found in other applications.
You could password protect your files. When saving the file click the the Tools icon and select General Otions. You would be prompted with to enter a password after which your files could be opened only after entering the password. You could also make your files read only by checking the readonly box so that others could see your file but not modfiy it. You could also back it up by checking the create back up button so that you could retrieve the file when you forgot your password. You could also explore the Web Options feature if you like.

Password protecting your file protects your file being modified or looked into by others inside Excel. However it doesn't protect you from someone deleting your files. You may look into Windows sharing to solve this problem

Opening Files
The next step is opening and working with files. The Open Command in Excel is similar to other applications in functioning. You could trigger the Open command by clickingthe open button.
This opens a dialog similar to the save as dialog from which you could select your file to work with and click Open. You could notice a arrow mark to the right of the open button
Clicking this would give you the following options.
  • Open - Opens the file
  • Open Read-Only - Opens the file in read-only mode.
    You cannot make changes to the file.
  • Open as Copy - Opens a copy of the file. Changes you
    make to this file is not reflected in original file
  • Open in Browser - Opens the file in a browser
    provided it is a html file(Normal excel files cannot be opened)
  • Open and Repair - Opens and repairs the file(This option could be used when Excel terminates suddenly or your data appears garbled)

You could change the default file path of Excel(the default directory when you use the open command).Go to Tools->Options->General and in the Default file location box enter the path of your choice. Quit and restart Excel

Save As
You might face a situation where you would be working on other's file. In such cases, it would be better to save a copy of the original file and work on it instead of actually working in the original file. You can do this by using the Open as Copy option but the problem with this option is that the copy would be saved in the same location of the original file. Thus, if you file is saved in D:\ the copy would also be saved in D:\. To overcome this problem, you could use the Save As option. Go toFile->Save As and follow the procedure you normally do when saving files. Thats it. Save As could also be used to password protect your files when you failed to protect them at the time of creating it.

Save As Webpage
You could also save your excel webpage as an interactive webpage. ClickFile->Save as Webpage. In the dialog box, select your preference to publish either the selected range or worksheet or the entire workbook as webpage and click publish. If you have formulas in the workbook and want to retain them in the webpage, Check the Add Interactivity box. You could change the title that would appear in the webpage in the Change Title box. When you click the Publish box, an another box would appear prompting further changes.
  • Under the choose box, select what to publish
  • Under the add interactivity box, select spreadsheet
    functionality to preserve you formulas in the webpage.Select pivot table
    functionality to preserve your pivot table in the webpage
  • In the title box, enter the title that would be
    displayed in the webpage.
  • In the filename box, enter your filename and choose
    where to save
  • Check "Autorepublish everytime workbook is saved" box
    to republish your webpage whenever your file is saved.
  • Check "Open publish webpage in a browser" to see your
    changes immediately in your default browser
  • Click Publish to publish and view your webpage
To disable the autorepublish feature, Goto File->Save as webpage->Publish and then from the choose box, select previously published items and click remove. Normally Excel files aren't saved as webpages. Either they are sent as attachments are viewed in a Excel viewer.

Save as Workspace
Sometimes, you might be working with a particular set of files. Assume you work with 4 sales reports files located in different computers or locations. You may find it embarrasing to open the files every time by going to a specific location. Excel could do this for you. Open all the files you need to work(just for the last time). Go toFile->;Save as Workspace and follow the procedure you normally do when saving files. Thats it, when the next time you open the workspace all the 4 files are automatically opened. If along with those files, other files have opened, close those files and again save the workspace. Take care or else the workspace would be opening too many files for you.

You could open a set of files when you open Excel.Go to Tools->Options->General.In the "At startup ,open all files in" box, enter the path form where you want to open files.Do this with a bit of care as you may end up with a Excel opening a lot of files.Quit and restart Excel

File Search
You could search inside the text of files. Simply Go toFile->File Search and enter the text you want to search and click OK. Files containing the text you enter would show up

Renaming a File
You could rename a file the same way you do with other applications. Sometimes, when you save you may get the following warning message "The following file already exists.Do you want to replace the exisitng file" would be displayed. This is because you are trying to overwrite a file. If you are sure that the old file may be replaced, click YES. Otherwise click NO and save your file under a different name.

Recovering your file
Sometimes when you are working with your file, you may lose it suddenly due to power cuts or sudden termination of Excel. You would really be kicking you as the data you have entered is lost. But its not so. Excel saves everything you type. When you lose your data(you might think you have lost data) suddenly, the next time you open Excel, it displays the list of files you worked but not saved for your review. You could review the files and save them if necessary or discard them.

Autosave files
You could automatically save your files after a specified period of time. This facility is called Autosave. To autosave your files, go to Tools Menu and click Options and select the Save Tab. Under the "Save autorecover info every" box, select the time period to save your files. The default is 10 minutes and don't enter a very small value such as 1 minute as it saves all the open files.See the box is checked and for god's sake, don't uncheck it since it would disable the autorecover feature. Under the "Autorecover save location", enter the location where the files would be automatically saved(This is the location where your files would be temporarily save to recover it in case of sudden termination or computer hangup.After the specified time, it would automatically save in the actual location of the file). The "Disable Autorecover" option is a workbook specific option and can be checked when you don't want to save your workbook automatically. It should be seen that both the autorecover and autosave options are the same and you guessed it right.

Notify files to readwrite
Sometimes when you open a file, you may be prompted with a message that the workbook is being used by an another user.This is because your workbook may be viewed by others. The message would prompt you with three options "Read only","Notify","Cancel".Selecting readonly opens the file to read only, selecting notify notifies you when the other user has closed the file and selecting cancel cancels the operation. When you click notify, you get a notification when the file is closed so that you could read write. It is advised that under such circumstances instead of waiting you Save As the file and work on it.

Sharing your files
The normal rule is that no file can be edited at the same time by more than one user. But in Excel, you may do so. Go toTools->Share Workbook and check the allow changes by more than one user tab. We may see this in detail in a forthcoming post

Know your file
You could know more about your files(though necessarily not) by clickingFile->Properties.
This is just for information purpose.
The File properties have five tabs
  • General which gives information about
    • File Name
    • File Size
    • File Type
    • File location
    • Attributes
  • Summary which gives information about
    • Title
    • Subject
    • Author
    • Keywords
  • Statistics which gives information about
    • File created date
    • File modified date
    • File last accessed date
    • File last saved by
  • Contents which gives information about
    • The worksheets in the workbook and their names
  • Custom properties that can be set about
    • Name
    • Type
    • Value

The General,Statistics and Contents are generated by Excel and cannot be changed manually while the Summary and Custom properties are editable. You may use these properties to tell more about your file such as your department, the file content and more but they aren't used commonly.

More file tips
  1. You cannot create two files under the same name in a
    single folder. If you need so, you should create a new folder
  2. You cannot open two files with the same name at the
    same time. You need to lauch another Excel Application to achieve this.
  3. Only one user can edit a file at a time.
  4. No changes can be made to a read-only file.
  5. You cannot save or rename a file when it is used by
  6. Files are logical devices. No such things really
  7. To give a preview of a workbook as a picture, GotoFile->Properties->Summary and check the Save Preview Picture box.
  8. To quickly know the name of the active file type this into a cell =CELL("FILENAME")
  9. To know your recent file list, go to files or look into the task pane.

You can increase or decrease the number of recently opened files. To do this, Go to Tools->Options->General. Under the Recently used file list type a number between 1 to 9 and make sure the box is checked

Previous->Getting started with Microsoft Excel
Index->Excel Basic Tutorials
Next->Working with cells

No comments: