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
When you format a cell in a worksheet, you can define cell shading, font color, font, font size, borders, underlining, customized text or number formatting, cell locking, hidden text when protected, and much, much more. In short, there are many definitions that can be set for a single cell.
In Chapter 2, Text; Chapter 3, Formatting Numbers; and Chapter 4, Date and Time, you learned how to format and save special formats using the Format Cells dialog box, Number tab, and Custom in the active workbook.
In Custom formatting you save all the formatting you have customized. The main limitation is that all custom formatting is only saved in the current workbook. Without defining a name for the format, it will be difficult for you to find and reuse it. Furthermore, when using this method, you cannot save definitions such as font, patterns, borders and any of the wide variety of formatting options in the Format Cells dialog box.
From the Format menu, select Style. The Style dialog box allows you to save numerous styles with names. Each style saves the full range of options that appear in the tabs in the Format Cells dialog box.
Note
The formatting you save as a Style is only saved in the current workbook. You can save styles for use in other workbooks by creating a Template (see the Template section of Chapter 13, Customizing Excel).
Copying Formatting
Copying the formatting of one cell to a different cell or to a range of cells, row and column, including row or column width, is an operation commonly performed by Excel users.
Copying formatting with the Format Painter
You can copy and apply formatting by using the Format Painter icon that appears on the Standard toolbar.
Painting a format repeatedly
Double-click the Format Painter or use the F4 keyboard shortcut, see below.
Using F4 to copy formatting to adjacent and non-adjacent ranges
Select a cell in the sheet (or a range of cells, row or column), and apply the formatting you like. Select additional cells (the selection will include the cell with the formatting as the first active cell in the selected region) by using the SHIFT key to select adjacent cells or the Ctrl key to select non-adjacent cells. Now press F4. The formatting of the active cell will be copied to all of the selected cells.
The F4 key can be very useful when copying formatting such as borders, background color or font color.
Using Paste Special to copy formatting
Paste Special is very useful when you want to copy formatting from one sheet to another. Copy a cell or sheet and select a different cell, range of cells or sheet. Now right-click, and from the shortcut menu, select Paste Special. Select Formats and click OK.
Changing the Default Style in a Workbook
The name of the default style is Normal.
Changing the definitions of the style will modify the default format of text or numbers in the workbook.
1. Choose Format, Style or use the Alt+’ shortcut.
2. In the Style name box, select Normal.
3. Click Modify.
4. Select the Number tab.
5. In the Category box, select Custom. In the Type box, enter the following format (recommendation only): #,##0;[Red]-#,##0;0;@
6. Select the Font tab.
7. Select the font and font size you want.
8. Click OK.
Explanation
The formatting suggested by default in the workbook allows you to enter both text and numbers into cells. Formatting a cell for text only by selecting the Text format from the list of standard formats appearing in the Category box in the Number tab of the Format Cells dialog box will allow only text and not numbers to be displayed in the cell. The opposite is also true. If you format a cell for numbers only, you will not be able to display text in the cell.
Number formatting is made up of four parts. See a more detailed explanation in Chapter 3, Formatting Numbers. The fourth part of the number format is designed to allow text to be displayed by the inclusion of the @ sign.
Preventing the display of 0
In the third part of the number formatting is the digit 0. You can leave this part empty (by not entering anything between the ;; signs). By doing this you prevent zeroes from being displayed in cells without data.
Creating and Saving Custom Styles
In the Style dialog box, you can create a wide range of complex styles and save each one separately with a unique name. These styles can then be applied repeatedly.
Example: Format a number so that a negative number displayed in brackets and zero (0) is replaced with a dash (-). Choose the font and font size.
1. Choose Format, Style or press Alt+’.
2. In the Style name box, enter Negative Numbers with brackets, 0=-.
3. Click Modify.
4. Select the Number tab, and in the Category box, select Custom.
5. In the Type box enter the following number format #,##0 ;[Red](#,##0);- ;
6. Select the Font tab.
7. In the Font box, select Arial. In the Font style box, select Regular, and in the Size box, select 10.
8. Click OK.
9. In the Style dialog box, click OK.
Using the same technique, create many styles in which numbers are rounded to the nearest thousand, underlined with a single line, or underlined with a double line.
Copying (merging) styles from one workbook to another
Excel saves the styles you define in the workbook in which you created and saved them. To use the styles in a different workbook, you need to merge them, or copy them, from one workbook to another.
Open a new workbook.
1. Choose Format, Style.
2. Click Merge.
3. In the Merge Styles dialog box, select Book1 (assuming that this is the workbook that contains the styles you created and want to copy).
4. Click OK.
5. If a dialog box appears asking if you want to Merge Style with Same Format, click OK (only click OK if you are sure that you have not created custom styles with the same names, because they will almost surely be overwritten in the update).
6. In the Style dialog box, click OK.
Note
You can only merge styles between open (active) workbooks. Before merging, be sure that the workbook containing the styles you want to merge is open.
Applying a style to a cell or cells in a worksheet
Add the Style icon to the Formatting toolbar.
1. Select a toolbar.
2. Right-click, and in the shortcut menu select Customize.
3. Select the Commands tab. In the Categories box, select Format.
4. From the Commands box, select Style. Drag the command to the Formatting toolbar, and drop it near the Font icon.
See in the figure,
The Formatting toolbar with the Style icon.
Tip – Create styles directly from cells
After you format a cell, you can quickly and easily save that format as a style by entering the name of the style in the Style box and then pressing Enter.
Note
After you have added the Style icon to the toolbar, using the Alt+’ shortcut lets you select the font in the icon itself. It does not open the Style dialog box.
Displaying statements rounded to thousands
By changing the style you can quickly change data in financial statements or any report so that figures are displayed rounded to thousands of dollars in the worksheet, allowing you to print statements that are rounded to thousands.
Select the columns with the data that you want to round, using the technique for selecting non-adjacent columns.
The technique: select the first column, and continue selecting the other columns while pressing the Ctrl key.
After you have selected the columns, go to the Style box, and select Round to Thousands. Want to reapply the previous style so that the data is displayed with cents? Just select another style from the Style box.