How to Use Styles Correctly in Excel

Contents

What is style

A style in Microsoft Excel is a stored collection of cell formatting options. Once you create a style, you can then apply it repeatedly to other cells, instantly styling them in the way you need, which incredibly speeds up your daily work in Excel.

The entire list of styles is available on the tab Home in group Styles (Home — Styles):

It is important to note that style is not only external design (color, font, fill), as many people think. In an Excel cell style, you can save:

Numeric format (currency, percentage, date/time…), including custom (kg, pcs, °C…)
Alignment options (horizontally and vertically), text direction, word wrap.
Font options (color, style, size…)
Fill color and type (solid, gradient)
Cell framing (type, thickness and color of lines on each side and inside)
Protection options (whether to protect a cell and hide formulas in it when sheet protection is enabled)

How to create a style

Select the cell whose appearance you want to keep in the style (or an empty cell if you want to create a style from scratch) and on the tab Home in group Styles select team Create cell style (New cell style).

In the window that opens, you can check the boxes which cell parameters should be saved in the new style and by clicking the button Framework (Format), customize them in more detail.

To create a style based on an existing style, you can simply right-click on the desired style and select the command Duplicate (Duplicate):

In the same place, in the context menu, any style can be deleted or its parameters can be changed.

Everything is very simple and even a little boring, isn’t it? So let’s move on to practical examples.

Style for Delimited Integers

Let’s start simple for the seed.

On the The main tab in a group Number there are buttons for quickly applying basic number formats that I’m sure you’re familiar with:

Personally, I have always been immensely surprised why this set does not contain the most necessary – a format that displays only the integer part of the number with a space after three digits? There is a monetary one, a percentage one, but for some reason there is no convenient format for ordinary numbers.

By the way, the third icon with the “000” icon, which is usually thought of in this case, does absolutely the wrong thing – it does not remove the fractional part, and even for some reason displays a dash instead of zero, as in financial format. Not at all.

Let’s eliminate this shortcoming by creating our own style. We select, as described above, sequentially Home — Styles — Create cell style (Home — Styles — New cell style). In the style creation window, enter its name and uncheck all the checkboxes except the first one so that the style we create does not affect any other cell parameters, except for the number format. Then we press the button Framework (Format) and in the familiar window choose Numerical (number) without decimals and with thousands separator:

We press on everything OK and now we have a style for visual formatting numbers – beauty!

Styles for your units of measurement (pieces, pers, pack, kg…)

Initially, Excel has only currencies and percentages as units of measure. If at work you are faced with the need to use other units of measurement (pcs, people, l, t, kg, pack, etc.), then it makes sense to create a special style for this.

We perform the same actions as in the previous paragraph, but in the Format cells window, select the option (all formats) on the Number tab. In the Type field, enter a mask (template) for our custom format:

# ##0″ pcs”

This template tells Excel that we want to display only the integer part of the number and add a space and PC to it. Please note that the first space here is needed to separate thousands-millions-billions, and the second one is so that the number and text do not stick together. And this second one must necessarily be inside quotes along with PC.

After applying this style, we get a beautiful representation for the pieces:

Similarly, you can make a fractional format, for example, for kilograms. In this case, to display three decimal places (for grams), you only need to add zeros to our template:

# ##0,000″ pcs”

Styles for thousands (K) and millions (M)

Another example of a very common problem is the compact representation for very large numbers. You have probably seen options for thousands with the letter “K” (1289 u1,3d 1K), for millions with the letter “M” (595 u000d 1,6M), or something similar. To create these styles, we again need a custom format, but with a slightly different template:

Pay attention to the space (without quotes) after zero – it is he, being a separator of digits, that rounds our original value to thousands. If you put two spaces, you will get rounded up to millions, three – up to billions, etc.

If you need to show one or more characters after the decimal point (for example, 3,5K or 5,23M), then add zeros, as in the previous example:

# ##0,0 «K»

# ##0,00 «M»

Instead of the international “K” and “M”, you can, of course, use our usual “thousand rubles” and “million $” – as you are more familiar with.

Style with color and percentage for deviation from plan

Let’s assume that we have a table with plan and fact values and a calculated percentage deviation column according to the classic formula:

Let’s create a custom style for the last column, which will be:

Positive values should be displayed in green font color, with a plus sign in front of the percentage value.
Negative values are red and minus, respectively.

We create the style as before, but the format mask will be tricky:

[Flower10]+0%;[Red]-0%;0%

Let’s break it down in more detail:

The format mask here consists of 3 fragments separated by a semicolon. The first is applied if the number in the cell is greater than zero, the second – if it is less than zero, the third – if the number is zero.
We want to indicate deviations from the plan explicitly – with a plus (overfulfillment) and a minus (failure), so we explicitly write the plus and minus signs in the template.
Before the mask in each block, you can specify the font color in square brackets – the word (red, blue, green, yellow, the black, white) or using code (Color1, Color2 etc.) A total of 56 colors are supported – choose any one you like:

After applying the created style, the picture will become much nicer:

Styles with icons and special characters

To make it even prettier, instead of plus and minus, you can display up and down arrow symbols, respectively. To do this, select any empty cell on the sheet and insert our icons there, through the command Insert – Symbol (Insert — Symbol):

Then we select the inserted characters in the cell or in the formula bar and copy them using the combination Ctrl+C.

Now, when creating a style for the differences from the previous paragraph, it remains to insert using Ctrl+V these characters into our format mask instead of plus and minus:

[Flower10]↑0%;[Red]↓0%;0%

After applying this style, our deviation from the plan will become even clearer:

I think it’s clear that such a trick with inserting special characters will allow you to similarly create styles for displaying any non-standard units of measure:

Styles with complex conditions and emoji

Suppose we have a table with the results of student exams or employee evaluations:

The task is to show next to each result its graphical representation in the form of an icon (emoji) according to the following logic:

greater than or equal to 80 points – excellent
from 40 to 80 – good
less than 40 – bad

Create a new style, where in the number format template we set:

Here, the mask also consists of three fragments, and each fragment is applied only if the condition specified in square brackets in front of it is met. If the first two conditions for the cell are not met, then the formatting takes place according to the last, third template.

Emoji can be easily inserted with a keyboard shortcut Win+pointif you have Windows 10 with all updates. If you have an older operating system, you will have to insert them in advance through Insert – Symbol from fonts like Webdings, Windings, etc. and copy as we did in the previous example.

Since there are no 0 and # in the format mask, i.e. Since we do not display the numerical contents of the cell in any way, it is better to make a copy of the rating column (by direct links) and apply the created style to it already:

Song!

Style for protected form fields

Imagine that you need to create a password-protected form for user input. For input cells, it is better to create a style with certain formatting (so that it is immediately clear where you can enter data) and disabled blocking (so that sheet protection does not interfere):

To do this, when creating a style, remove all checkboxes, except for the first one and, by clicking on the button Framework (Format) set a characteristic design (for example, yellow fill, italics, center alignment) and on the tab Protection (Protection) uncheck Protected cell (Locked).

How best to store styles

All custom styles created are stored in the current workbook. If you want to use them in your other files, then there are three options:

Copy cell format with tool Sample format (“brush” or Format Painter).
Load all styles from another workbook into a workbook using the command Home — Styles — Merge Styles (Home — Styles — Merge styles)
Use the Auto Load Styles from Library file tool in the latest PLEX add-on.

Ideas to continue

I hope that the examples described above will be enough for you to start working on your imagination and start inventing styles yourself to simplify your work in Excel. Here are some seeding ideas for you:

Styles with corporate colors and fonts of your company or brand.
Styles with currencies that are not on The main and which for a long time to put through the window Cell format (for example, tenge, somoni, som and other currencies of the union states).
Styles with bullets to mimic bulleted lists in Excel as in Word.
Styles to hide data “white on white” or with a mask of three consecutive semicolons.
Styles for displaying negative numbers without a minus, but in brackets (as is customary in finance for expenses).
Styles for displaying source data, calculations, and referenced cells in financial models.
… etc.

Next – yourself.

Share useful styles in the comments – progressive humanity and karma will thank you 🙂

Custom cell formats in Microsoft Excel
Bulleted and numbered lists in Excel as in Word
Arrows in Excel Sheet Cells
Custom Styles in the PLEX Add-on