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.
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. These are only slightly different between versions of Excel. You can also create your own custom cell styles.
What is a cell style good for?
Step-by-Step: Cell Styles
|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: trips12-Lastname-Firstname.xlsx (saved in a previous lesson)
There are many ways to open an existing file.
Excel 2007: Office button menu > Recent Documents
Excel 2010: File tab > Recent
Excel 2013, 2016: File tab > Open > Recent
Excel 2016 groups recent workbooks by date.
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 in a lot of features that are just not needed for such a small table. Besides, this lesson is on cell styles!
A4 = Customer
This cell got its formatting from the table style Medium 2, but you converted the table back to a range.
If you still see arrows in the row 4 cells, convert the table back to a range.
Table Tools: Design > Convert to range button
the Home tab in the Styles tab group, click the More button to show the gallery of cell styles.
Styles are in groups: Good, Bad, and Neutral; Data and Mode; Titles and Headings; Number Format.
create a custom style, it will show at the top in a new section, Custom.
The dialog shows the formatting for the current cell, A4, including what the table style added. The list of formatting is nicely detailed, except for Fill, which does not name the color. An odd omission!Problem: Dialog shows the default format
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.
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!
Click on OK again to close the Style dialog.
The new style appears in the Styles gallery on the Home ribbon tab and is conveniently listed first.
Click on the cell style Header Row in
the gallery of cell styles.
All of the labels for the second section of data are now formatted like the first table.
Deleting a cell style: If you delete a cell style, any cells formatted with that style revert to the default 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!
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
Click 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
No, they stayed the same! You never applied the cell style to them.
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 import styles even if they have the same name.
Did you think this command would merge two particular cell styles? I did!
Click the following link to download the workbook:
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.
Click on OK to close the dialog.
The Styles gallery now shows two new styles in the Custom section, Dates and Totals.
Notice that the custom cell styles are listed first, in
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.