applied tips : february 2009
microsoft excel : use styles for consistent formatting
Creating spreadsheets with consistent formatting can be a challenge in Excel, especially when you want to create complex formatting for a variety of uses such as headers, totals, comments, labels, and other types of information.
Excel offers us a way to create and manage a variety of Styles which store the way a cell is formatted. We can then apply a style to a cell, share styles among workbooks, and even modify a style and automatically adjust all of the cells that were assigned that style.
Creating a Style
The easiest way to create a new style is to format an existing cell
exactly in the way you want your style to look. Be sure to set up its
borders, number formatting, font and font size, font and fill colors,
and anything else you want to set.
Then, click the Format menu and choose Style to display the Style dialog box. Type a new name into the "Style Name" text field and press "Add".
Applying a Style to a Cell or Range
Start by selecting the cell (or cells) that you wish to affect. Remember you can select an entire row or column by clicking the row/column heading, or select non-adjacent cells by holding down Control as you click and drag across cells.
Return to the Style dialog box (Format menu, then Style) and choose from an existing Style using the pull-down menu. Optionally, check or uncheck the effects from the style you wish to apply to the selected cells. Then, press OK.
Modifying an Existing Style
If
you decide to change the way a style looks, Excel will automatically
change all of the cells that you’ve marked with that style into your new
look! This can save you a tremendous amount of time if you decide, for
instance, that you want the background fill of your headers to be green
instead of blue.
Open the Style dialog box (Format menu, then Style) and choose from an existing Style using the pull-down menu. Now click Modify and make your changes using the familiar Format dialog box. When you close that window and press OK, all occurrences of your style will be updated.
You can also modify the built-in styles: Normal, Comma, Currency, and Percent. Every cell in your workbook starts with the Normal style, so changing this will affect everything! Changing the other three pre-built styles will affect the look of cells when you use the Comma, Currency, and Percent icons in the Formatting toolbar.
Deleting a Style
If a style is in your workbook that you no longer need, delete it to avoid confusion and save a little space. From the Styles dialog box (Format menu, then Styles), select the name of the style in the Style Name field and choose Delete. This will not affect cells already marked with that style.
If you delete the existing styles called Comma, Currency, or Percent, you will no longer be able to use the matching icons from the Formatting toolbar in that workbook.
You cannot delete the "Normal" style.
Sharing Styles between Workbooks
One of the best features of Styles is that you can easily import styles you’ve created from one workbook into another. This helps you maintain consistency even while working on different files.
You’ll need to have both workbooks open, and then activate the destination workbook (the one without the style). Now open the Styles dialog box (pull down the Format menu, choose Styles) and click Merge.
Excel will show you a list of all open workbooks, so choose the source workbook that contains the styles that you wish to import. Excel will automatically import all Styles into your active workbook. This is a feature that you cannot undo.
If the active workbook has a Style with a matching name, Excel will prompt you if it should overwrite the existing style with the imported one. If you say yes, all of your cells that were assigned that style will now take the appearance of the imported version.
You might want to delete unused styles from your source workbook before importing them to avoid too many unwanted imported styles.
upcoming classes on excel
Classes on Microsoft Excel are scheduled at University of the Pacific this March, and you can attend! Review the Upcoming Classes for more information.
schedule a class on excel
Request an on-site Excel class from Applied Office. Sessions are inexpensive and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time. Learn more here