Using styles in Excel. Cell styles in Excel and their controls. Is it possible to remove a style in excel?

A cell style is a set of formatting options, such as fonts and font sizes, number formats, borders, and cell shading. To prevent other users from changing specific cells, you can also use a cell-locking style. includes several built-in cell styles that you can apply or customize. You can also change or duplicate the cell style to create your own custom style.

Cell styles are based on the document theme applied to the entire sheet. When you select a different theme, the cell styles change accordingly.

To apply multiple formats at once and still ensure consistent cell formats, you can use a cell style. There are several built-in cell styles that you can apply and modify. You can also create custom cell styles by changing or copying a style. Cell styles are based on the document theme that applies to the entire workbook. When you switch to a different document theme, the cell style changes to match the theme.

How to apply a cell style?

• In the open worksheet window, select the required cells.
• Go to the "Home" tab and in the "Styles" group, expand the "Cell Styles" button menu.
• In the list of styles, click on the icon of the desired style (Fig. 3.59).

The idea behind named styles is this:

1. You can create your own set of styles for formatting, such as headings, summaries, and plain text. And then apply ready-made styles to other cells without wasting time on reproducing exactly the same format.
2. If you change the style format, all cells to which this style is applied will be automatically formatted. This way, you can quickly revise any format without wasting time formatting cells individually.

Excel styles allow you to format the following attributes:

• numeric format (for example, number, short date format, telephone number format, etc.);
• alignment (vertical and horizontal);
• font (name, size, color, etc.);
• border (linetype, border color);
• fill (background color, pattern);
• protection (protected cell, hiding formulas).

Applying styles to worksheet cells

Excel comes pre-installed with many built-in styles. You can find them in the menu Cell styles, which is located on the tab home-> Styles.

The style gallery will open (picture on the right).

To apply a style to a selected cell or range, you need to left-click on the desired style. There is also a very convenient preview option: when you hover over a style, you will see how the cell style changes.

After applying a style from the gallery, you can apply additional formatting to the cells.

Creating New Styles

If Excel's built-in styles are not enough, you can create your own styles. It's easy enough to do:

As a result, a new custom style will be added to the active workbook, which will be available in the menu Cell styles.

Changing existing styles

You can change the formatting of an existing style. In this case, all cells to which this style is applied will also change formatting. To change the style you need to:

1. Go to tab home-> Cell styles.
2. Right-click on the style you want to change and select the command Change.
3. A dialog box will open Style, which specifies the formatting to apply to the cell.
4. Click on the button Format, and in the dialog box that appears Cell Format set the required formatting. For example, to change the font size, go to the tab Font, set the desired size and press the button OK.
5. Click the button again OK to close the window Style and apply formatting to the style being changed.

Transfer styles to another workbook

Sorry, the styles are applied to the current workbook. Those. if you open a new workbook, it will not have the new styles that were previously created. To avoid creating styles again, there is a style merging tool. To do this you need:

1. Open a workbook that already contains new styles. And open the file into which you want to transfer these styles.
2. Go to the workbook that contains the new styles and open the styles menu home-> Cell styles.
3. Select a team Merge, a dialog box will open Combining styles.
4. This window contains a list of all open books. Select the book to which you want to copy styles and click the button OK.

Excel allows you to format not only the color and border of cells, but also the font and layout of their contents.

1. Select cells A4:A12 again, then right-click on them and select the command from the context menu Cell Format.

2. In the dialog window that opens, expand the tab Alignment, shown in Fig. 9.11.

3. Dropdown horizontally select item left-aligned (indent),

4. Use the arrows to enter into the counter indentation value 1 to set the numbers to be slightly indented from the left edge of the cells.

5. Click the button OK.

6. In the toolbar Formatting click on the buttons Bold And Italics. Excel spreadsheets can also be formatted using styles.

Rice. 9.11. Setting text alignment in Excel

Using styles in Excel allows you to quickly assign specific, predefined appearances to cells. Changing a style immediately updates the formatting of all cells to which that style is assigned. Let's set up a style for the headings and captions and assign it to two groups of merged cells in the Expenses worksheet.

7. The format of the active cell is always chosen as the basis for the newly created style. Click merged cell B1:G1 to select its style format as the source.

8. Run the command Format > Style.

9. Enter the word Caption in the drop-down list field of the dialog box that opens. This will be the name of the new style. Its initial parameters will appear in the dialog window (that is, the formatting mode for the range B1:G1).

10. Click the button Change.

11. In the dialog window that opens Cell Format on the tab Font select 12 point bold font and click the button OK.

Rice. 9.12. Style settings window in Excel

12. In the dialog window Style clear all checkboxes except font, as shown in Fig. 9.12, so that the new style affects only the cell font and does not change other design parameters.

13. Click on the button OK. The heading Company expenses by month will increase in accordance with the new font size.

14. To change the font of the Table Average caption, select cells B14:F14.

15. Run the command Format > Style, in the drop-down list Style name select the inscription item and click on the button OK.

Note To create a new document from a template with additional styles and appearance, choose File > New, open in the dialog window Creating a document tab with the desired template and double-click its icon. Any Excel workbook can be used as a template by first saving it in an XLT file.

The current document was created without using a template, but it does have several standard Excel styles:

* Ordinary- standard cell style;
* Percentage- style of cells containing percentage values;
* Financial- monetary values ​​(rubles and kopecks);
* Financial- monetary values ​​(rubles only);
* Monetary- monetary values ​​(rubles, kopecks and currency sign);
* Monetary- monetary values ​​(only rubles and currency symbols).

16. To set the table cells to display numbers in a monetary style, select cells B4:H12.

17. Press the Ctrl key and, without releasing it, click on cell F14. This cell will be attached to the selection.

Rice. 9.13. Formatting text in Excel

18. Select a team Format > Style, in the list Style name select item Monetary and click on the button OK. The table will become as shown in Fig. 9.13.

Styles in Excel are a tool that can significantly simplify and speed up the process of formatting a document. Formatting styles can be defined as being stored under a specific name as preset format settings. They can be easily assigned to one or multiple cells.

Assigning formatting styles to cells

Each style defines the following settings:

1. Font (type, size, color, etc.).
2. Number display format.
3. Cell boundaries.
4. Filling and patterns.
5. Cell protection.

Thanks to styles, all sheets can be easily and quickly formatted. And if we make a change to the style, then these changes are automatically assigned to all sheets that are formatted by it.

Excel provides a library of ready-made thematic styles, as well as the ability to create your own custom styles.

To use the library of built-in styles you need to:

Creating a custom style from a sample

Now let’s create our own custom style, but based on a ready-made one:

Thus, based on the built-in style, we created a duplicate, which we changed to suit our needs.

Is it possible to change the style in Excel? In principle, it was possible not to create a duplicate, but to immediately select “Change” in the context menu. All changes to any default built-in style would be saved only in the current workbook. They do not affect the program settings. And when creating a new book, the style library is displayed as standard without changes.

Is it possible to remove a style in Excel? Of course you can, but only within the framework of one book. For example, how do you remove custom styles in Excel?

Select the desired style in the main panel: “Cell Styles” and in the “Custom” section, right-click. From the context menu that appears, select the “Delete” option. As a result, the cells will be cleared of the formats specified by the corresponding style.

In the same way, you can remove the default Excel styles in the library, but this change will only apply to the current workbook.

Create a style based on cell format

But what if you need to create a style based on a custom cell format that is specified in the usual way.

1. Format an empty cell by changing the fill and font color.
2. Now select this original cell and select the tool in the main panel: “Cell Styles” - “Create Cell Style”.
3. In the Style window, name the style as Cell1 and click OK.

Now you have the name “Same as cell1” displayed in the “Custom” style section. The principle is clear.

All style formats described above are saved within the current Excel workbook file. Therefore, for further actions, save this book called “Lesson on styles.xlsx”.

Copying a style to other books

Sometimes it becomes necessary to use current styles in other books. To do this, you can simply copy them:

1. Create a new workbook in which we will use the custom style “As in cell1”.
2. Open the book “Lesson on styles.xlsx” with the original style we need saved.
3. Select a tool on the main tab: “Cell Styles” - “Merge”. And in the “Combining Styles” window, point to the book we need and OK.

Now all custom or modified formats from the original workbook are copied to the current workbook.

If you need to often use one style in different books, then it makes sense to create a special book with your own styles and save it as a template. This will be much more convenient than copying every time... And we will look at all the advantages of templates in more detail in the following lessons.

Formatting in Excel is a very tedious but important task. Thanks to styles, we can significantly speed up and simplify this workflow. While maintaining an exact copy of cell formats across different sheets and workbooks.

In addition to the usual tools, you can use tab elements to work with the table Constructor(See Fig. 11.15). This tab automatically appears when you select any table cell and also automatically hides when you select any cell outside the table.

You can apply a different design style to the table.

You can also click the button to change the table style Format as table in Group Styles tabs home and choose a design style.

In the tab Constructor in Group Table Style Options(Fig. 11.17) you can customize the table design parameters.

To enable or disable the header bar, select or clear the check box Title bar.

To enable or disable the summary row, select or clear the check box Total line.

To display special formatting for the first table column, select or clear the check box First column.

To display special formatting for the last column of a table, select or clear the check box Last column.

To display even and odd lines differently for easier reading, select or clear the check box Alternating lines.

To display even and odd columns differently for easier reading, select or clear the checkbox Alternating Columns.

Converting a table to a range

The table can be converted to a regular data range. All established formatting will be preserved.

1. Select any table cell.
2. Click the button Convert to Range in Group Service tabs Constructor(See Fig. 11.16 or Fig. 11.17).
3. In the conversion request window, click the button Yes.

Using Styles

About styles

Using styles ensures a consistent design of data and cells throughout the workbook, allows you to quickly set a selected set of formatting options, and instantly change the design of all cells to which the same style is applied.

A style is a set of parameters for formatting data and cells: number format; alignment; font; border; fill; protection. A style does not have to include all formatting options.

When you change style settings, the appearance of all cells to which the changed style is applied automatically changes.

For cells to which any style is applied, you can also apply any other design. Appearance options set after applying a style will not automatically change when you change the style.