Format & Arrange:
Cell Style Box

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

Creating and applying styles is rather awkward with the Style Dialog. Using a toolbar control would be easier. For some reason Excel does not include the Style Box on the default Formatting bar like Word does.

Style box with list of style droppedFirst you will learn how to add this control to a toolbar. Then you will create and apply styles with the Style Box.

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & Arrange
    Format Cells Open arrow to subtopics
    footprintApply Formatting
    footprintCopy Formatting
    footprintPartial, AutoFormat
    footprintStyle - Dialog
    footprintStyle - Style box
    Format ChartTo subtopics
    ArrangeTo subtopics
    Exercises To subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step

Step-by-Step: Cell Style Box

 Icon Step-by-Step

What you will learn:

to customize a toolbar
to restore a toolbar
to apply a style with the Style Box
to create a Style with the Style Box
to show and print formulas

Start with: Class disk trips13.xls (saved in previous lesson)

Toolbar: Customize

  1. Popup Menu:ToolbarsRight click on any toolbar. The popup menu of toolbars appears. The ones with checkmarks are currently displayed.
  2. If necessary, display the toolbar you plan to customize by clicking on its name.
  3. Click on Customize at the very bottom of the menu. The Customize dialog appears.

    This command can also be found with  Tools  |  Customize... .

  4. Click on the Commands tab. This tab lists the categories of commands on the left and the commands in each category on the right. Each command can be placed on a toolbar or in a menu.

    Dialog: Customize - Commands tab

  5. Click on each category on the left in turn and scroll the list of its commands on the right. You may see several commands that you wish were on a toolbar. Sadly, there is only room for so many at a time.

  6. Select the Format menu, on the left.
  7. Drag the Style box from the Commands list up to Excel's menu bar, or to the Formatting bar, if you have room on it.
  8. Drop it in the blank area of the bar.

Animation: Drag Style Box from Customize dialog to Menu bar

    The Style box is now ready to use.

    Excel will remember your toolbar from now on, unless your network resets such things once you log out. Only the styles created for the document or those in the underlying template will be in the list - just like in Word.

    If you are using a resolution larger than 640 x 480, you may have room for this control on the Formatting bar, where it logically belongs. Add other buttons if you like.

    You can make more room on a toolbar by removing buttons that you don't use much. Just drag a button to the Customize dialog and drop it. Then you can drag a command you like to the toolbar in its place.

    [If you are working on a network where you have your own password, the computer should remember your settings. You can leave the toolbars the way you like. If you don't have a password and are sharing the computer, you may need to restore the toolbar. (See below) If you add and remove a lot of buttons, the next user may get quite confused!]

  1. Click on OK to close the Customize dialog.
  2. Menu: Style Box with list displayedOpen the Style box list, that you just put on the toolbar, by clicking the down arrow at the end of the box to see the short list of styles available. Press ESC to close the list without applying a style.

 Style: Apply with Style box

  1. Edit cell C27 to read  Number of people .
  2. Select ranges A25:E25, A27:D27, A37:D37. (Hint: hold the CTRL key down while selecting.)
  3. In the Style box select the style  Label-white on green  to apply it to the selected ranges. Wasn't that easy?

    The cells you have styled have lost some formatting that had been applied earlier.

  4. Lower table with new cell styleCenter cells C4, C25, A27:D27, and C37.
  5. Select cells E25 and D37. Apply the Currency style and Decrease Decimals twice.
  6. AutoFit Columns D and E again.

Style: Create in Style box

It is much easier to create and use a style when the Style box is available.

  1. Select cell A1, which is already formatted, of course.
  2. Click in the Style box you just added to the Menu bar.
  3. Type  Company Title  and press ENTER.
  4. Drop the Style box list to see your new style.
    Tip The style name is not displayed using its formatting as recent versions of Word do. So name your styles in a way that keeps it clear what they look like or what they are for.
  5. Apply your new style Company Title to cell A2 to see if it does what you expected, but don't press Enter yet. Hmmm. Not very helpful actually. Let's not apply this formatting after all.
  6. Cell A2 with Company Title formattingClick Undo to return the cell to its previous state.
  7. Open  Page Setup  and check Portrait orientation.
  8. If necessary, open  Page Break Preview  and remove the page break.

  9. Preview: trips14.xls after cell styles appliedOpen Print Preview. Both tables should display on one page.
  10. Class disk Save as trips14.xls to the excel project3 folder on your Class disk.
    full floppy diskHow to handle a full disk .

Print: Formulas

Sometimes you want to look at the formulas being used in your cells without having to click on each cell. You might even need to print this out so you can study what is going on.

  1. Select from the menu  Tools  |  Options  |  View  
  2. Click on Formulas under Window Options. Click on OK. The columns widen to display the full formula in each cell.
  3. Check your formulas. Are they all correct?

trips14.xls with formulas displayed

  1. Open    Page Setup  to the Page tab and change to Landscape Orientation.
  2. Switch to Page Break Preview and remove any page breaks you find. Your sheet will actually fit on one page.

    Warning If you widen the column when you show formulas, when you return to showing values, your columns will stay wide. If you leave the column widths alone, the sheet will return to the exact state it was before showing formulas.

  3. Print Print.
  4. Icon: Keyboard combo Switch back to showing values instead of formulas with the key combo CTRL + `. (That's a left quote mark. On the English keyboard this symbol is on the far left key of the row with the numbers keys.) This key combo works to toggle the showing of formulas on and off. Of course, you could also go back through the menu and uncheck the checkbox you checked earlier. 
  5. Switch the page orientation back to Portrait  File  |  Page Setup  |  Page .
  6. Class disk Save. [trips14.xls]
    full floppy diskHow to handle a full disk .

Preview: trips14.xls after showing formulas

Toolbar: Restore

You don't need to do this next section if you are working on your own computer, or if you are on a network with passwords that will remember your personal settings.

Dialog: Customise Toolbar

  1. To return a customized bar to the defaults, select  Tools  |  Customize...  
  2. Select the toolbar in the list that you changed and now want to restore.
  3. Click on the Reset button. Then close the dialog.
  4. Class disk Save. [trips14.xls]
    full floppy diskHow to handle a full disk.