Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Lessons > Jan's CompLit 101 > Working with Numbers > Format > Cells > Cell Styles

Jan's Working with Numbers

    Format: Cells: Cell Styles

All this work with table styles brings up a question. Does Excel have other kinds of styles, like Word has character and paragraph styles. Yes, indeed! Excel uses cell styles instead of character or paragraph styles. They make it easy to format different cells alike when you don't need a whole table.

Ribbon: Home > Styles tab group (Excel 2010)

A cell style, or just style, can include any formatting that can be set for a cell. This includes all of the font characteristics, number formats, alignments, fills, and borders. Excel provides some pre-defined styles in the Styles gallery on the Home tab. You can also create your own custom cell styles.

Gallery of cell styles from the default Blank workbook template (Excel 2010)

What is a cell style good for?

  • To quickly apply formatting to cells that you want to emphasize in a special way.
  • To automatically update the formatting everywhere the style was applied whenever you modify the style.
    You avoid having to hunt around for the cells that need the same styling and then having to fix them individually.
     
    Automatically updating things is what Excel is all about!

Icon Step-by-Step 

Step-by-Step: Cell Styles

 Icon Step-by-Step

What you will learn: to open an existing workbook
to create a cell style  
to apply a cell style
to check what style a cell has  
to modify a cell style
to merge cell styles from another workbook
to wrap text

Start with: Icon: Class disk trips12-Firstname-Lastname.xlsx  (saved in a previous lesson)

Open an Existing Workbook

There are many ways to open an existing file.

  • Recent file:
    • Start menu > Recent Items
    • Taskbar icon - right click > Recent
    • Inside Excel: Office button menu > Recent Documents or File tab > Recent
  • Computer window showing your Class disk, folder excel project3

Recent Items on the Start menu (Windows 7) Right click Excel taskbar icon > Recent items (Windows 7) Recent documents: File tab > Recent (Excel 2010) My Computer window open to folder excel project3

Start menu > Recent Items; right click pinned Excel icon > Recent; File tab > Recent; My Computer window > excel project3

  1. Open   trips12-Firstname-Lastname.xlsx  from your Class disk.
  2. Icon: Class diskSave as trips13-Firstname-Lastname.xlsx.
    Icon: Full disk How to handle a full disk  

Create a Cell Style

The first cell style you will create will duplicate the formatting in the header row, row 4. You could apply the same table style to the bottom section of data, but that pulls is a lot of features that are just not needed for such a small table. Besides, this lesson is on cell styles!

  1. Ribbon: Home > Styles > New Cell Style... (Excel 2010)Select cell A4 =  Customer 
    This cell got its formatting from the table  style Medium 2, but you converted the table back to a range.
  2. On the Home tab in the Styles tab group, click the More button Button: More (Excel 2010) to show the gallery of cell styles.
    Styles are in groups: Good, Bad, and Neutral; Data and Mode; Titles and Headings; Number Format.

    When you have created custom styles, they are at the top in a new section, Custom.

  3. Dialog: Style - Header Row (Excel 2010)Click on New Cell Style...

    The dialog shows the formatting for the current cell, A4.

  4. Type a new Style name in the box: Header Row

    The dialog includes a list of how the selected cell is styled. It is nicely detailed, except for Fill, which does name the color. An odd omission!
     

  5. Dialog: Format Cells - for Header Row style (Excel 2010)Click the Format... button.
    The Format Cells dialog appears, where you can view or modify all of the settings.

    You don't need to modify anything right now.

  6. Click on each tab in this dialog and notice what the choices are and what the current choice is.

    By the way, on the Font tab, the Preview box looks like it has no sample text. The font color is White, so the text is there, as white text on a white background. Invisible!

  7. Click the OK button to close the Format Cells dialog.
     
  8. Ribbon: Home > Styles > new cell style Header Row (Excel 2010)Click on OK again to close the Style dialog.
    The new style appears in the gallery on the Home ribbon tab, listed first.
     


Apply Cell Style

  1. Range A27:E27 with cell style Header RowSelect range A27:E27.
  2. Click on the cell style Header Row in the gallery of cell styles.
     

Which Style?

You cannot tell by looking whether or not a cell has a cell style applied to it. The Style gallery comes to the rescue!

  1. Ribbon: Home > Styles tab group > Header Row style selected (Excel 2010)Select cell E27.
  2. Look at the Styles gallery on the Home tab.
    The style with the gold border is the current style, Header Row.

Modify Cell Style

  1. Right Click Menu: Header Row cell style > Modify... (Excel 2010)Right click on Header Row in the Styles gallery.
    A menu appears.
  2. Click on Modify Style...
    The Style dialog appears.
  3. Click on the button Format...
    The Format Cells dialog appears, open to the Font tab.
     
  4. Dialog: Style > Font = Cambria, 14 pt (Excel 2010)Dialog: Format Cells > Font > Cambria, 14 pt (Excel 2010)Change the Font to Cambria and the Font Size to 14.
  5. Click on OK to close the Format Cells dialog.
    The Style dialog now shows the new Font settings.

    Live Preview does NOT show what effect this change will have.
     

  6. trips13.Firstname-Lastname.xlsx updated with new Header Row cell styleClick on OK to close the Style dialog.
    The Style gallery shows the new formatting. All cells in row 27 to which you applied the Header Row cell style are using the new formatting. Sweet!

    What about the cells in row 4 from which we got the formatting?
    No, they stayed the same! You never applied the cell style to them.

  7. Icon: Class diskSave.
    [trips13-Firstname-Lastname.xlsx]

     

Merge Cell Styles

 Your new custom style is not seen by other workbooks. You can use the Merge Styles command at the bottom of the Styles menu to import styles from another workbook. Both workbooks must be open before you use that command. You can merge styles even if they have the same name.

  1. Dialog: Merge Styles - styles-merge.xlsx (Excel 2010)Styles gallery > Merge Styles (Excel 2010)Open the following workbook: styles-merge.xlsx
    This link opens the copy in the resources folder either from the online site or your local copy of the site. You can also go to where you saved the resource files if you wish.
  2. In trips13-Firstname-Lastnme.xlsx make Sheet 1 the active sheet.
  3. Click the More button Button: More (Excel 2010) for the Styles gallery.
  4. Click on Merge Styles... at the bottom of the gallery.
    The Merge Styles dialog opens.
    The dialog lists the other open workbooks.
  5. Click on styles-merge.xlsx to select it.
  6. Ribbon: Home > Styles > merged styles showClick on OK to close the dialog.
    The Styles gallery now shows two new styles in the Custom section, Dates and Totals.

    Notice that your custom cell styles are listed first, in alphabetical order.

  7. Icon: Class diskSave.
    [trips13-Firstname-Lastname.xlsx]

Apply Cell Styles

  1. Trips13 using merged cell stylesSelect B28:B35, the dates in the bottom table.
  2. Click on the Dates cell style to apply it.
  3. Select cells E25 & D37, the total sales.
  4. Apply the Totals cell style.
  5. Icon: Class diskSave.
    [trips13-Firstname-Lastname.xlsx]
     

Wrap Text

When your text is too long to fit in the cell's width, you can force the text to wrap inside the cell. This is often better than just widening the column enough for the text to fit.

  1. Cells C4 and C27 rewritten as 'Number of people'Select cell C4.
  2. Type Number of people
    This replaces the existing text, # of people.
  3. Similarly, replace the text in cell C27 with Number of people.
    In both C4 and C27 the text is cut off at the right.
  4. Select C4 and C27 at the same time.
    (Hint: Hold CTRL down when you click the second cell.)
  5. Trips13 with text wrapped in C4 and C27On the Home tab in the Alignment tab group, click on the button Wrap Text.
    Rows 4 and 27 immediately get taller to hold a second line of text. The text is still aligned Left.
  6. Icon: Class diskSave.
    [trips13-Firstname-Lastname.xlsx]