Excel allows you to format your spreadsheet as you preferred by providing an array of features. Cell style is a one of these wonderful features available in Excel that formats a cell. This ultimate guide covers the essentials of how to use cell styles in Excel as per your preference.

Cell style is the simplest way of formatting a cell quickly in Excel. It also facilitates applying several formats using only one click or creating consistent formatting for cells.

Various types of formatting are available in Excel to style a cell to change its look and feel.

• Fonts
• Font sizes
• Number formats
• Cell borders
• Cell shading

Excel also provides a cell style to lock a particular cell preventing others from modifying that cell. A combination of above types of formatting also applicable easily on a cell. For an instance you can apply all the above formatting characteristics for a single cell. You can also ensure the consistency for multiple cells by applying the same style.

However, Excel doesn’t deter you from creating your own cell style. Simply you can duplicate or modify an existing cell style just to create your own style in your way.

It’s certainly not difficult to utilize all these features as much as it may sound. This step-by-step tutorial will guide you on how to apply any cell style easily and efficiently on your spreadsheet.

How to apply a new cell style in Excel?

Let’s format office supply sales records data using built-in cell styles in Excel.

Now you should select the cell or cells that need to format. Let’s format a cell first which is A1, “Order Date” by applying “Heading 3” style.

Simply navigate to “Home” tab and “Styles” group. Click on the dropdown arrow which is bottom right corner that indicates “More”.

A dropdown will be visible consisting of a group of styles like below.

Under “Titles and Headings” select “Heading 3”. The style will now get quickly applied to the selected cell like below.

Whenever you want to apply the same style for a range of cells, simply select a range of cells and apply a cell style following the above steps.

Following image depicts how cells from A1 to G1 styled with “Heading 3” cell style.

Built-in cell styles in Excel

There are a bunch of categories available in the “Styles” group. It’s important for you to have a proper understand as to utilize them for an easy styling of cells.

Good, Bad and Neutral

This category of built-in styles helps you to mark cells to identify them depending on the cell value. This is a quick way of evaluating cells by means of a visualization. Also, this is rather easy than applying fill colors to cells manually just to indicate them good, bad, or neutral.

Sales records are marked as good, bad, and neutral to differentiate them depending on the amount of total.

• Total that is more than 600, marked as “Good”
• Total that is in between 500 and 600, marked as “Neutral”
• Total that is less than 500, marked as “Bad”

Data and Model

This category from the styles gallery simply helps to identify the cells which have different values. The values could be anything, an input, warning text, output etc.

Let’s apply some of them to our example as below.

Units and unit cost are the inputs that used to calculate the total of each item. So, they can be styled as “Input” and the total is styled as “Output” under the category of “Data and Model” in styles gallery.

Titles and Headings

You can simply style headings and titles using one of items from the “Titles and Headings” in styles gallery.

“Total” in our example can be easily styled with the title, “Total” in the “Titles and Headings”.

Themed Cell Styles

Excel makes it easy for you to apply a color theme to cells as per your preference. For that, you can select a color theme from “Themed Cell Styles” in styles gallery.

Number Format

You can instantly apply thousand separator, percentage, or currency for all the cells that you need to style. It’s just a matter of selecting the preferred format from “Number Format” in styles gallery.

Let’s apply a currency format G1:G8 cells indicating the figures with dollar sign.

How to create a cell style in Excel?

A built-in cell style in Excel may be not exactly what you want. However, there is no need to stress yourself to style cells in the way you want, spending much time.

Because Excel allows you to create your own cell style, save it and apply it same as a built-in style whenever you want. Let’s create a new cell style in Excel!

1. Navigate to “Home” tab and “Styles” group. Click on the dropdown arrow which is bottom right corner that indicates “More”.
2. Click on “New Cell Style” from the dropdown that will be visible like below.
3. You must name your own style to identify it. Provide a name under “Style Name” in the dialog box that popped up. Let’s name it as “My Cell Style”.

A set of formatting are already there to choose as per your preference. The check boxes in front of each formatting are checked by default, meaning that all those cell styles are applied to the new style.

Simply uncheck any cell formatting to remove it from your own style.

4. Click on “Format” to do adjustments to each type of cell style that you want.
5. Each cell formatting is visible by a tab. You just need to do necessary changes in each formatting of style by navigating to the particular tab.

In our own cell style, let’s create currency number format using bold font, center aligned, an outline border, and a shaded blue fill color.

Go to the tab “Number” and select “Currency”.

Select currency from the options under “Symbol”.

Note: If you want to increase or reduce the number of decimal places and format negative figures of the selected currency, you can do changes under “Decimal places” and “Negative numbers” respectively.

Go to “Alignment” tab and select “Center” under “Horizontal” field.

Let’s wrap larger figures to fit them within the cells. You can simply check the text box next to “Wrap text” under Text control”.

Go to “Font” tab and select “Bold” under “Font style”.

Go to “Border” tab and select “Outline” to create an outer border for the cell. You can see a demo is given under “Border” to check the changes that you make for each border of a cell.

Go to “Fill” tab and select and click on “Select Colors…”.

In the “Custom” tab, increase the lightness of the blue color by moving the arrowhead upwards on the bar. If you have a hex code for a color, you can simply copy the code to the box next to “Hex”.

You can navigate to “Standard” bar if you want to apply a solid color quickly.

Navigate to “Protection” tab and keep the check box checked to lock the cells that prevents others from reformatting the cells when the sheet or workbook protected.

If all the formatting is done and completed, click “OK” to finish the task of creating a new cell style.

Now you can view every change that you did for each formatting under “Style includes” on the “Style” box. Uncheck any formatting if you do not wish to add it to your own style or simply click again on “Format” to reformat your style.

We are good to proceed as we have completed formatting to create our style, “My Cell Style”. So, click “OK” to finish the task.

Now you can see our own style is visible on the style group under “Custom” section as it is a custom style that we created in Excel.

Note: Custom styles in Excel are only available to use in the workbook that it is created but it’s available to use in any worksheet within your Excel workbook.

Now let’s apply our new cell style to the column of “Total” in the sales records data.

Note: Any modifications that you do for a cell style, will affect for all the cells that has the same style. For an example, if you modify the font color to white in new style, it will affect all the cells G2:G8.

How to create a cell style by modifying a built-in style in Excel?

If you need just to add another formatting to an existing style, Excel allows you to accomplish that by modifying that style.

However, you can do this in two ways as follows.

Modifying an existing cell style

Let’s modify the border of cell in the cell style, “Total” under “Titles and Headings”. Right click on “Title” and select “Modify”.

Click on “Format” to modify the formatting of border.

Go to “Border” tab. Select black color under “Color” and click on “Outline” to draw complete border of the cell.

You can simply check the sample under “Border” to view the changes that you made. Then click “OK” to apply the changes.

If changes are completed, click “OK” to finish the task.

Now apply the modified cell style to “Total” of sales records data by selecting it from “Styles” gallery.

Note: Any changes that you will do for “Total” cell style in future, will affect to the cells that it was applied previously.

Duplicating a cell style

Excel allows you to duplicate a built-in cell style and modify it as per your preference. So, you can simply duplicate an existing style, rename it, modify it, and reuse it.

Let’s duplicate “Heading 3” style to add some modifications to the titles of sales records data.

Right click on “Heading 3” cell style under “Titles and Headings” from “Styles” gallery and select “Duplicate”.

Rename the cell style as you need. Let’s rename the cell style as “New Heading 3” and click on “Format”.

Note: Whenever you duplicate a built-in cell style, it’s recommended to rename it with an appropriate name. However, if you do not rename a built-in cell style, any changes will affect to the built-in cell style as well.

Go to “Font” tab and change the font color to black by selecting the color on the dropdown under “Color”.

You can preview the changes that you do for the font under “Preview”.

Go to “Border” tab and select black color on the dropdown under “Color”. Click on the icon that indicates bottom border of a cell. It will draw a black color bottom border on the cell when the style is applied.

Once all the modifications formatting are completed, click on “OK”.

Uncheck the “Number” formatting under “Style includes” as it’s not necessary for a heading style. Once done with selection of formatting, click on “OK” to finish the task.

You can find the duplicated cell style under “Custom” cell style in the “Styles” gallery.

Now let’s apply the style on the titles of sales records data. Select cells A1:F1 and select “New Headline 3” cell style from the “Styles” gallery to apply the style on the cells.

How to merge cell styles in Excel?

It’s certainly a time-consuming task to recreate any cell style that you did for a previous workbook. Excel allows you to merge a custom cell style to another workbook without recreating it.

Let’s create a new workbook “Sales Data Version 2” and make sure both “Sales Data” and new workbook are opened.

Note: New workbook has only a copy of sales records data without any style.

Go to “Styles” gallery on “Home” tab in new workbook and select “Merge Styles”.

You must determine the workbook in which the custom styles are created by selecting its name in the dialog box. Then click “OK” to import the custom style from the selected workbook.

You will be asked to confirm merging all the styles that have same names in the previous workbook. Click “OK”.

You can see the custom styles that we created in “Sales Data” workbook, are now available under the “Custom” in “Styles” gallery.

Now you are free to apply the styles as you wish by using the custom styles that created in your previous workbooks.