Understanding Files and Templates

1. Understanding Files and Templates Applications of Spreadsheets

2. Creating a New Workbook • To create a new workbook: • Use the File  New command • Click the New Workbook button on the Standard Toolbar • Pres the Ctrl+N shortcut key combination • Excel automatically creates a new empty workbook with 3 worksheets.

3. Beginning a New Workbook When you choose the “Complete Install” option during setup, Excel 2003 provides the General and Spreadsheet Solutions tabs for organizing your workbook templates .

4. Using a Template • To create a new workbook based on a template, use one of the template options on the New Workbook task pane. • Templates can be found on • Microsoft’s web site • your computer • your own web site

5. Templates Dialog Box • If you select “On my Computer” from the New Workbook task pane, the Templates dialog box opens. • General tab • Workbook • Custom (previously defined) • Spreadsheet Solutions tab • Included with Excel

6. New Template-based Workbook The Sales Invoice template contains a single worksheet tab named “Invoice.”

7. Opening an Existing Workbook • Select a recent file listed at • the top of the New Workbook task pane • the bottom of the File menu • Use the File  Open option • Click the Open button on the Standard toolbar • Press the Ctrl+O shortcut key combination

8. The Open Dialog Box • If you use the Open Dialog box, you must • select the name of the workbook file you wish to open specified in the File Name field • its location or folder specified in the Look In field • The Open button is a drop-down list of additional options • Open (normal) • Open (Read-Only) • Open as Copy • Open in Browser • Open and Repair

9. The Open Dialog Box • Can also open a file by double-clicking on a file • Right-clicking a filename displays the shortcut menu with additional options • Copy • Delete • Modify • Etc.

10. The Open Dialog Box • Look In field: drop-down box that displays your folders; allows you to select a different drive or directory from this list. • Files of Type: drop-down list allows you to change the filter on the Open dialog box to additional file types. • Change your file display preferences by clicking the View icon and selecting from the drop-down list: • Thumbnails • Icons • Tiles • List • Details

11. Opening an Existing Workbook

12. The Tools Menu • Displays a shortcut menu • Search • Delete • Rename • Print • Add to My Places ( “My Network Places” folder) • Map Network Drive • Properties

13. Opening an Excel File Automatically • Whenever you start Excel, you can have a specific workbook open using the XLStart folder located within your Excel document folder • Usual locations listed on Page 110 in your book, but can be different for each computer. • Can also specify an alternate startup folder in addition to the XLStart folder by choosing Tools  Options, selecting the General tab, and entering a new folder name in the field labeled At Startup, Open All Files In.

14. Saving and Closing your Workbooks • Important to save your work frequently. • File  Save command • Save button on the Standard Toolbar • Use the Ctrl+S shortcut key combination • Press the Shift+F12 shortcut key combination • Save the workbook to a new file by • using the File  Save As command or • pressing F12.

15. Closing a File and Exiting Excel • File  Close • Click the Close button on the standard toolbar • Double-click the icon on the left side of the workbook’s title bar. • Press Ctrl+F4 shortcut key • Press the Ctrl+W shortcut key • File  Exit to quit Excel

16. Lists the files that you have most recently worked with The currently selected folder is displayed in the Save in drop-down list box. Excel’s default working folder for storing files The “Advantage” folder is the default folder for storing the student data files . Each folder item represents either a local folder or a shortcut to a remote storage . Lists common desktop shortcuts Provides access to the resources on your computer Enter the workbook’s file name in this drop-down text box Selecta workbook file type from this drop-down list box Lists files and folders stored on your intranet or Internet web server Save As Dialog Box

17. Saving and Closing

18. Navigating the Storage Areas using the Save-in Drop-down List box Along with the resources shown in the My Computer list area, the Save in drop-down list box provides access to other storage areas, such as “My Network Places” and FTP locations.

19. Creating a Workbook Folder

20. Using AutoRecover • Automatically saves a backup copy of your work at a predetermined time interval. • If Excel crashes, you’ll see a list of documents that were open at the time of the crash. • Choose to open the original version, or the AutoRecovered version. • Adjust settings of AutoRecover in the Save tab of the Options dialog box

21. Save Options Dialog Box • On the Save as Dialog box, select Tools --> General Options to set options. • Always Create Backup • existing version of the workbook is renamed before the workbook is saved. • New filename is “Backup of xxx.xlk where xxx represents the original filename. • Password to Open • Password set here will be required to open the workbook • Up to 15 characters • Case sensitive • No way to retrieve the password if you forget it

22. Save Options Dialog Box • Password to Modify • Can specify a required password in order to modify a workbook and save it under the same name • prevents changes to the original version of the workbook • Read-Only Recommended • If checked, Excel presents a dialog box suggesting the file be opened as read-only • Can be overridden by user. • Advanced • Select the type of encryption used to protect your workbook.

23. Workbook Summary Information • When you save an Excel file, additional information is saved • Title • Author • Statistics about the file • Viewable using the workbook’s Properties dialog box (File  Properties) • General • Summary • Statistics • Contents • Custom

24. Safeguarding your Work – Backup Options • Keep a backup copy of the file on the same drive • select the Always Create a Backup Option in the Save Options dialog box. • Keep a backup copy on a different hard drive • Offers more protection in case of a hard drive failure • Keep a backup copy on a network server • Assumes network connection • Keep a backup copy on a removable medium • Safest method if stored in different physical location than original.

25. Understanding Excel Templates • A template is a model that is used to create other workbooks. • Excel supports 3 types of templates • Default workbook template • Default worksheet template • Custom workbook templates • Can create your own template by modifying the template Excel uses to create new workbooks. • Examples - the Spreadsheet Solutions templates.

26. The Default Workbook Template • Can use the workbook template to change workbook defaults: • Open a new workbook • Add or delete sheets to the number you want. • Make any other changes • Column widths • Named styles • Page setup options, etc. • Select File  Save As • In the Save As dialog box, select Template (*.xlt) from the box labeled Save As Type. • Name the new template “book.xlt” to replace Excel’s workbook default template. • Save the file in your \XLStart folder. • Close the file. • Close and restart Excel.

27. The Default Worksheet Template • Can use the worksheet template to change worksheet defaults: • Start with a new workbook and delete all worksheets but one. • Make any changes you want to make • Column widths • Named styles • Page setup options, etc. • Select File  Save As • In the Save As dialog box, select Template (*.xlt) from the box labeled Save As Type. • Name the new template “sheet.xlt” to replace Excel’s workbook default template. • Save the file in your \XLStart folder. • Close the file. • Close and restart Excel.

28. More on your Templates • To edit your templates • Open the template files and edit them just as you would any other workbook/worksheet. • Save and close • To reset the default settings • Delete the book.xlt or sheet.xlt template file form the XLStart folder. • Excel will go back to using its built-in default settings for new workbooks or worksheets.

29. Creating Custom Templates • A normal workbook that uses any of Excel’s features (charts, formulas, macros). • Include everything but the data, which is entered by the user. • Saves you time from repeating work. • When you create a workbook that is based on a template, the default workbook name is the template name with a number appended. • i.e. Sales Report.xlt creates a workbook named Sales Report1.xls

30. Saving Custom Templates • To save a workbook as a custom template • Choose File  Save As • Select Template (*.xlt) from the Save As Type drop-down list. • Specify the file be saved with a preview image. • Select File  Properties  Summary tab and check the box labeled “Save Preview Picture’ • Save the template in your templates folder (selected automatically by Excel).

31. Ideas for Creating Templates • Multiple formatted worksheets • Several settings in the View panel of the Options dialog box • Color palette • Style • Custom number formats • Column widths and row heights • Print settings • Sheet settings