add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
As an Excel user, you know how many precious work hours you spend customizing and personalizing Excel, including changing the format of a style and saving it (see Chapter 5, Styles), updating headers and footers for printing, changing default font settings, changing customized lists, and so on.
In this chapter, you will become thoroughly familiar with the range of options in the Options dialog box, saving a workbook as a template for reuse, and more.
Changing the Window View
The default settings for the window view in Excel are determined by the Windows operating system and Excel. Minimize Excel, and right-click the desktop. Select Properties, and select the Appearance tab.
In addition to the changes described in this chapter, you can make several others through the Item menu. The changes described here are the most common and useful ones.
© Changing the font size of the Sheet name – the Sheet tabs in a workbook are part of the Scroll Bar. Change the font size in the Scroll Bar option to change the font size of the Sheet name in the tab.
© Changing the format of cell comments and icon Tool Tips on the toolbar – select Tool Tip, and change the background color and font.
Options Dialog box
In Excel, from the Tools menu, select Options. The Options dialog box has a number of tabs, each of which contains default settings that were determined by Microsoft. It is very important to become familiar with the definitions in the Options dialog box.
Displaying zero values (View tab)
Unchecking the Zero values box prevents the digit 0 from being displayed on the worksheet. This option is useful for printing.
Displaying gridlines (View tab)
Gridlines are controlled in two different places. To control if gridlines are displayed on the screen, use the View tab of the Options box. Uncheck the Gridlines box to view the spreadsheet without gridlines. This setting is great for getting a good look at your borders and lines.
By default, gridlines will not print whether they are displayed or not. To force Excel to print the gridlines, select File, Page Setup, Sheet. Then check the Gridlines box.
Recently used files list (General tab)
You can find the list of recently used files in the File menu. Change the default number to the maximum setting, nine files.
Set the number of sheets in a new workbook (General tab)
Set the number of sheets that open in a new workbook to avoid an excessive number of sheets. Set this number as low as possible to meet your needs. Working in a workbook with a small number of sheets is much easier.
Standard font (General tab)
The font Arial, size 10, is the standard font for Excel workbooks. Change the font and its size as desired.
Default file location (General tab)
A new Excel file is automatically saved for the first time (if you have not specified a location on the hard disk) in the My Documents folder on the C: drive. Change the default location as desired.
User name (General tab)
Type your name in this box. The name in the box appears in any comment you create in the sheet. See the section pertaining to comments in Chapter 10, Information. It also appears in the file properties for any worksheet that you create.
Custom lists, in the Custom Lists tab
Add lists to the Custom Lists. With Custom Lists, you can add a list to the sheet by entering a name from this list into a cell. Copy it by dragging its handle. There is no need to type the list.
After you enter the text into the cell and copy it (by dragging the handle), Excel checks whether the text exists in one of your custom lists. If it does, Excel imports the list from Custom Lists.
Use Custom Lists to sort data according to the order of the list, instead of sorting it alphabetically. See Chapter 17, Sorting.
Example:
Type the text January in Cell A1. Drag the handle in the lower right corner of the cell to copy it to Cell A12. A list of the months of the year is entered into Cells A1:A12.
Adding a list to Custom Lists
1. In the cells, type the list you want to save in Custom Lists.
2. Select the range of cells with the list.
3. From the Tools menu, select Options and then the Custom Lists tab.
4. In the box to the left of the Import button, check the selected range of cells. Click Import.
Changing the paper size to A4/Letter(International tab)
New
In 2002
In the United States, the standard paper size is Letter, and in Europe, it is
A4. Excel 2002 allows you to adjust the print area from one paper size to the
other.
Saving (Save tab)
Automatic backup files are created every 10 minutes. If your computer crashes, you can restore the files from the list of restored files in the Task Window.
Error checking (Error Checking tab)
A nice innovation in Excel 2002 is the provision of various error-checking options. If there is an error in a formula, a Smart Tag is attached to the cell. Open the Smart Tag menu to display the type of error and evaluate the formula. For more information, see Chapter 7, Formulas.
Checking spelling (in the Spelling tab)
Select the dictionary language to check spelling in the sheet and access additional options.
Security (Security tab)
A new tab (see p. 218) in Excel 2002 allows you to prevent a workbook from being opened by setting a password to open it (you still have the option of setting a password from the File menu – select Save As, Tools, General Options). On this tab, you can also add a digital signature, set the level of virus protection with a macro, and even set a password to share work on a network. For an in-depth discussion of the subject of security, see Chapter 9, Security and Protection.
New
In 2002
Customizing Toolbars
Clicking an icon on a toolbar activates a macro. Adding icons to the toolbars increases the number of commands that you can activate and use. However, many useful icons are not located on the toolbars. Add important icons to toolbars based on your needs, or create a new toolbar.
Adding icons to the toolbars
Example: Add the Page Setup icon to the Standard toolbar (the first toolbar).
1. Select a toolbar.
2. Right-click, and from the shortcut menu, select Customize.
3. Select the Commands tab, and then click on the Data category.
4. In the Commands dialog box, click the Page Setup icon, and drag it to the Standard toolbar. Release the mouse.
Removing icons from toolbars
Select the icon you want to remove, and drag it off the toolbar while the Customize dialog box is open (to open this dialog box, see Steps 1-4 above, Adding icons to the standard toolbar).
Tip – Remove icons from the toolbar without opening the Customize dialog box
Select the icon and drag it off the toolbar while pressing Alt.
Tip – Remove icons that perform duplicate tasks from the toolbar
Some of the icons on the toolbar perform a double job.
For example, press Shift and the Sort Ascending icon. The sort is performed in descending order.
The duplicate icons that can be eliminated using Shift
On the Standard toolbar –
Print and Print Preview.
Sort Ascending and Sort Descending.
Open and Save.
On the Formatting toolbar –
Increase Indent and Decrease Indent.
Underline and Double Underline.
Center and Merge and Center.
Increase Decimal and Decrease Decimal.
Left-to-Right and Right-to-Left (Excel 97).
Adding icons to the menu bar
The Excel menus include a number of useful subjects, such as Page Setup, Paste Special, Custom Views, Macro, and more. Each one of them includes a variety of additional options, either through additional tabs or through drop-down lists. Add these necessary icons to the Excel menu bar. You will find them in the usual categories or in Built-in Menus in the Customize dialog box on the Commands tab.
Preventing icons from disappearing
With Excel, you can arrange the icons on the toolbars any way you like. As explained above, you can add and remove icons according to your needs. When the Customize dialog box is open, the entire toolbar is displayed with all its icons. After clicking OK, sometimes some of the icons on the right end of the toolbar disappear, because there is not enough space to display them all. To prevent these icons from disappearing, place the icons you need on the left end of the toolbar, and remove other icons that you do not need.
Adding a new customized toolbar
In addition to the regular toolbars, Excel offers the option of adding a new toolbar, giving it a name, and saving it with the existing toolbars.
1. In the Customize dialog box, select the Toolbars tab.
2. Click New.
3. Type a name for the toolbar.
4. Click OK.
5. Add icons to the toolbar.
6. Click Close in the Customize dialog box.
Saving your toolbar in the workbook
When your new toolbar is attached to a workbook, it is opened and displayed when the workbook is opened.
1. In the Customize dialog box, select the Toolbars tab.
2. Select Attach.
3. In the Attach Toolbars dialog box, select the toolbar you want from Custom toolbars.
4. Click Copy, OK, and Close in the Customize dialog box.
5. Save the workbook.
Removing customizations from toolbars
Select the Toolbars tab in the Customize dialog box, and click Reset. Selecting this option removes the additional toolbars you created.
Displaying a menu
Select the Options tab in the Customize dialog box.
Menu animations – select one of the various options for displaying a selected menu. Selecting None displays the entire menu as quickly as possible. The other options display the menu more slowly.
Customizing an icon
Select one of the toolbars, right-click, and from the shortcut menu, select Customize. Select any icon on a toolbar, and right-click.
The menu enables the following:
© Changing the icon name.
© Changing the icon image.
© Copying the icon image.
© Attaching a macro.
and a wide range of additional options.
Adding icons to an Excel menu
You can add an icon to a menu or sub-menu by using the same method you used to add icons to the toolbars or create new toolbars.
Make sure the Customize dialog box for toolbars is open. Drag the relevant icon to the menu or sub-menu. The sub-menu opens to allow you to place the icon there as desired.
When you add icons such as the Custom View icon to the Excel menu, you save toolbar space, so you can add smaller icons. See Chapter 11, Printing.
Saving changes to toolbars and menu
The changes made to the menu and toolbars are saved in a file called Excel.xlb in Excel 2002 and the username + the xlb extension in Excel 97 and 2000. When Excel is started, the file Excel.xlb opens the toolbars and menu as they were last saved.
To ensure that the changes to the toolbars and menu are saved (if you change computers, remove the Excel program and reinstall it, and so forth), locate files with the xlb extension by using the Search function, and copy them to an external backup (disk, diskette, Internet, and so forth).
Template
A workbook template creates a perfect copy, or image, of itself upon opening.
A template can be used as a substitute for office forms such as a timesheet report. Prepare a timesheet report, including formulas and formats, and save it as a template by using the method described below.
This section explains how to save a customized workbook as a template with additions and changes made according to your preferences. This includes special formats that you saved in the Style dialog box, headers and footers that you saved for printing and more. A template file is saved with the extension xlt, and a regular Excel file is saved with the extension xls. When you open a template file, a new file is created with the xls extension, and the name of the file receives a numeral.
Example:
In the course of work with Excel, you open a regular workbook by using the shortcut Ctrl+N. The new workbook is a perfect copy of a template workbook called Book.xlt (the basic Excel workbook template). The name of the new workbook is Book1.xls, and an additional workbook would be called Book2.xls.
Saving a customized workbook as a template
Open a new workbook and make changes to its default settings.
© Format – create or insert formats into the workbook by creating styles. See Chapter 5, Styles.
© Printing – add headers and footers. Change the default setting for the First Page Number. See Chapter 11, Printing.
© Changing default settings – from the Tools menu, select Options. Make the desired changes. See the beginning of this chapter for the various options.
© Add formulas and values to the Name box. See Chapter 6, Name.
Save the workbook with the changes as a template.
1. From the Excel menu bar, select File, Save as.
2. Select Save as Type – Template.
3. In File name, enter the name MyWorkbook (this name is only a recommendation and not mandatory).
4. Click Save.
5. From the Excel menu bar, select File, Close (you must close a template file after saving it).
The file is saved in the Templates folder.
Opening a template
A workbook template that you create is saved in the Templates folder. From the File menu, select New, and open the MyWorkbook file (the template you created and saved in the previous example). In Excel 97 and 2000, a newwindow is opened.
In Excel 2002, the Task Window is opened.
New
In 2002
Double-click the MyWorkbook file to open a new workbook called MyWorkbook1.xls.
A workbook template called MyWorkbook is saved as a template with the name MyWorkbook.xlt.
The workbook is opened as a regular workbook with the xls extension, and the
name of the workbook is MyWorkbook1.xls. Start working in the workbook, and
save it with any name you like, just as you would save any other workbook.
Opening a template file automatically upon starting Excel
There are two options for opening a workbook template automatically upon starting Excel.
First option
Save the workbook called Book.xlt, which you customized, in the sub-folder called XlStart. When Excel is started, a customized workbook Book1.xls is opened from within the template you saved, instead of the standard Book1.xls.
The XlStart sub-folder is located in the same folder as the Office program on the hard drive.
To open a new workbook, press Ctrl+N or in the Standard toolbar, click the New icon.
Second option
Save the template file you created as MyWorkbook.xlt in any folder, and enter the name of the workbook template in the Options dialog box.
1. From the Tools menu, select Options.
2. Select the General tab.
3. In the Alternate startup file location box, enter the full path where you saved the MyWorkbook file.
4. Click OK.
Note
Do not use the two selected options simultaneously, unless you have created more than one workbook. Use only one of the two options (Excel tries to open the file with the same name twice).
Inserting a sheet into the workbook from the template
After you have customized a new worksheet and saved it as a template, you can add a new sheet to the regular workbook from the saved workbook template.
Option 1 - Inserting a regular sheet to the workbook.
1. Select the sheet name of the sheet tab in the workbook.
2. Right-click, and from the shortcut menu, select Insert.
3. In the Insert dialog box, double-click Worksheet.
The sheet tab you inserted is a regular Excel sheet.
Option 2 – Define a new customized worksheet to be used as the default inserted worksheet.
1. To insert a sheet from a template, right-click on the sheet tab. From the shortcut menu, choose Insert, and then double-click Sheet.xlt.
2. Delete all of the sheets except for one.
3. Save the workbook as a template, as explained above, with the name Sheet.xlt, and close it.
Insert this customized sheetinto the workbook:
1. From the menu, select Insert and Worksheet.
The sheet that you have inserted is your customized worksheet template.
Option 3 – Define more than one customized worksheet and select from them.
1. Open the customized workbook that you saved as a template.
2. Delete all the sheets except for one.
3. Save the workbook as a template, as explained above, with the name MySheet (or any name you choose), and close it.
Insert the saved MySheet into the workbook.
1. Select the sheet name in the workbook.
2. Right-click, and from the shortcut menu, select Insert.
3. Double-click MySheet.
Explanation
If you select the workbook MyWorkbook from the Insert menu, all the template sheets are inserted into the existing workbook. If you save the workbook with one sheet as a template, you can insert only a single sheet into the existing workbook.
Updating a workbook template
1. Open a workbook called MyWorkbook.
2. Make any necessary changes.
3. Save the workbook as a template, and select the template MyWorkbook.
4. The following message appears: “The file MyWorkbook.xlt already exists. Do you want to replace the existing file?” Click Yes.
5. Close the workbook.