Excel Formatting Styles

01
of 02

Basic Excel 2010 Ecourse Tutorial Step 12

Formatting with Cell Styles in Excel
Formatting with Cell Styles in Excel. © Ted French

Using Formatting Styles

In this step we will use Excel's pre-set formatting styles to add some color to our worksheet. Doing so not only gives it a more polished look, but it can also make it easier to read and interpret the worksheet data.

Specifically, we will apply shading to the worksheet headings in rows 2 and 7 as well as rows 3 and 6 using the formatting Styles options located on the Home tab of the ribbon.

To make our efforts more efficient, we will select non-adjacent cells of data using the Ctrl key on the keyboard. This will allow us to apply the formatting to all highlighted cells at the same time.

For this tutorial:

Adding Shading to Worksheet Headings

  1. Click on the merged title cell in row 2.
  2. Press and hold down the Ctrl key on the keyboard.
  3. Drag select cells A7 to F7 to highlight them as well as the merged title cell.
  4. Release the Ctrl key.
  5. Click on the down arrow at the end of the available styles list.
  6. Choose the Accent 3 option from the available styles.
  7. Both the merged title cell and the headings in row 7 should now have a green background white text.
  8. To make the white text a little more visible, click on the Bold icon on the ribbon.
  9. The bold icon is the black letter B under the font section of the ribbon.

Adding Shading to Rows 3 and 6

  1. Drag select cells A3 to F3 to highlight them.
  2. Press and hold down the Ctrl key on the keyboard.
  3. Drag select cells A6 to F6 to highlight them as well as cells A3 to F3.
  4. Click on the down arrow at the end of the available styles list.
  5. Choose the 40% - Accent 3 option from the available styles.
  6. Cells A3 to F3 and A6 to F6 should now have a light green background with black text.
02
of 02

Basic Excel 2010 Ecourse Tutorial Step 12

 

Cell Styles Overview

A cell style in Excel is a combination of formatting options - such as font sizes and color, number formats, and cell borders, and shading - that is named and saved as part of worksheet.

Excel has many built-in cell styles that can be applied as is to a worksheet or modified as desired. These built-in styles can also serve as the basis for custom cell styles that can be saved and shared between workbooks.

One advantage to using styles is that if  a cell style is modified after it has been applied in a worksheet, all cells using the style will automatically update to reflect the changes.

Further, cell styles can incorporate Excel's lock cells feature that can be used to prevent unauthorized changes to specific cells, entire worksheets, or entire workbooks.

Cell Styles and Document Themes

Cell styles are based on the document theme that is applied to an entire workbook. Different themes contain different formatting options so if a document's theme is changed, the cell styles for that document also change.

Applying a Built-in Cell Style

To apply one of the built-in formatting styles in Excel:

  1. Select the range of cells to be formatted;
  2. On the Home tab of the ribbon, click on the Cell Styles icon to open the gallery of available styles;
  3. Click on the desired cell style to apply.it.

Creating a Custom Cell Style

To create a custom cell style:

  1. Select a single worksheet cell;
  2. Apply all desired formatting options to this cell - a built-in style can be used as a starting point;
  3. Click the Home tab on the ribbon.
  4. Click on the Cell Styles option on the ribbon to open the Cell Styles gallery.
  5. As shown in the image above, click on the New cell styles option at the bottom of the gallery to open the Style dialog box;
  6. Type a name for the new style in the Style name box;
  7. The formatting options already applied to the selected cell will be listed in the dialog box.

To make additional formatting options or modify the current choices:

  1. Click on the Format button in the Style dialog box to open the Format Cells dialog box.
  2. Click on a tab in the dialog box to view the available options;
  3. Apply all desired changes;
  4. Click OK to return to the Style dialog box;
  5. In the Style dialog box, under the section entitled Style Includes (By Example), clear the check boxes for any formatting that is not wanted.
  6. Click OK to close the dialog box and return to the worksheet.

The new style's name is added to the top of the Cell Styles gallery under the Custom heading as shown in the image above.

To apply the new style to cells in a worksheet, follow the steps list above for applying a built-in style.

Copying Cell Styles

To copy a custom cell style for use in a different workbook:

  1. Open the workbook containing the custom style to be copied;
  2. Open the workbook that the style is being copied to.
  3. In this second workbook, click the Home tab on the ribbon.
  4. Click on the Cell Styles icon on the ribbon to open the Cell Styles gallery.
  5. Click on the Merge Styles option at the bottom of the gallery to open the Merge Styles dialog box.
  6. Click on the name of the workbook containing the style to be copied;
  7. Click OK to close the dialog box.

At this point, an alert box will appear asking if you want to merge styles with the same name.

Unless you have custom styles with the same name but different formatting options in both workbooks, which, by the way, is never a good idea, click the Yes button to complete the transfer of the style into the destination workbook.

Modifying an Existing Cell Style

For Excel's built-in styles, it is usually best to modify a duplicate of the style rather than a style itself, but both built-in and custom styles can be modified using the following steps:

  1. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery.
  2. Right-click on a cell style to open the context menu and choose Modify to open the Style dialog box;
  3. In the Style dialog box, click on the Format button to open in the Format Cells dialog box
  4. In this dialog box, click on the various tabs to view the available options;
  5. Apply all desired changes;
  6. Click OK to return to the Style dialog box;
  7. In the Style dialog box, under the section entitled Style Includes (By Example), clear the check boxes for any formatting that is not wanted.
  8. Click OK to close the dialog box and return to the worksheet.

At this point, the modified cell style will be updated to reflect the changes.

Duplicating an Existing Cell Style

Create a duplicate of a built-in style or a custom style using the following steps:

  1. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery.
  2. Right-click on a cell style to open the context menu and choose Duplicate to open the Style dialog box;
  3. In the Style dialog box, type in a name for the new style;
  4. At this point, the new style can be changed using the steps listed above for modifying an existing style;
  5. Click OK to close the dialog box and return to the worksheet.

The new style's name is added to the top of the Cell Styles gallery under the Custom heading.

Removing Cell Style Formatting from Worksheet Cells

To remove a cell style's formatting from cells of data without deleting the cell style.

  1. Select the cells that are formatted with the cell style that you want to remove.
  2. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery;
  3. In the Good, Bad, and Neutral section near the top of the gallery, click on the Normal option to remove all applied formatting.

Note: The above steps can also be used to remove formatting that has been applied manually to  worksheet cells.

Deleting a Cell Style

With the exception of the Normal style, which cannot be removed, all other built-in and custom cell style can be deleted from the Cell Styles gallery.

If the deleted style had been applied to any cells in the worksheet, all formatting options associated with the deleted style will be removed from the affected cells.

To delete a cell style:

  1. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery.
  2. Right-click on a cell style to open the context menu and choose Delete - the cell style is immediately removed from the gallery.
Format
mla apa chicago
Your Citation
French, Ted. "Excel Formatting Styles." ThoughtCo, Dec. 10, 2016, thoughtco.com/excel-formatting-styles-3123463. French, Ted. (2016, December 10). Excel Formatting Styles. Retrieved from https://www.thoughtco.com/excel-formatting-styles-3123463 French, Ted. "Excel Formatting Styles." ThoughtCo. https://www.thoughtco.com/excel-formatting-styles-3123463 (accessed January 16, 2018).