Change the default style in Excel. Working with Excel cell formatting styles. Copying a style to other books

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, format phone number and so on.);
• 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. Click right click mouse over 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.

Let's look at the usage R1C1 and A1 link styles in Excel, we will determine when which style is preferable to use, and also learn how to switch between them.

Addressing worksheet cells in Excel is one of the fundamental aspects of working in the program, and most program users work with a cell style like A1, in which the columns are specified by letters and the rows by numbers (in this case A- column letter, and 1 - row number, respectively, cell - column intersection A and strings 1 ).
For example, cell D5 stands at the intersection of the column D and strings 5 , cell F8 at the intersection of the column F and strings 8 etc.
In addition, Excel uses the A1 style by default, so everyone is used to working with it.

A similar coordinate system is also more familiar to us, since it is used, for example, in a chess arrangement or in the game “ sea ​​battle» quite familiar to all of us from childhood:

However, sometimes a different style is used - R1C1, where both columns and rows are specified by numbers ( R1 means line 1 where R—Row(line translated from English), and C1 means column 1, where C—Column(column translated from English), respectively, the cell is the intersection of the line 1 and column 1 ).

How to enable or disable R1C1 link style in Excel?

As mentioned above, Excel uses the A1 reference style by default, so to start working with R1C1 you first need to enable it. To do this we go to File -> Options and in the tab Formulas and in the block Working with formulas put a tick next to the field R1C1 link style:

Accordingly, if you need to return to the standard addressing type A1, then the R1C1 style can be disabled by unchecking the corresponding box in the settings.

Features and differences between A1 and R1C1 addressing styles

First of all, when working with cells, pay attention that for the R1C1 style the address first contains a line, and then a column, and for A1 it’s the other way around - first a column, and then a row.
For example, cell $H$4 will be written as R4C8(not like R8C4), so be more careful when entering formulas manually.

Another difference between A1 and R1C1 is appearance Excel window, in which the columns on the worksheet are designated differently (A, B, C for style A1 and 1, 2, 3, ... for style R1C1) and the cell name:

As you know, in Excel there are 3 types of links (you can read more): relative (A1), absolute ($A$1) and mixed ($A1 and A$1), where the dollar sign ($) serves to fix the row or column number.
In the case of style R1C1 You can also use any type of link, but the principle of their compilation will be slightly different:

• R.C.. Relative reference to the current cell;
• R1C1. An absolute reference to the cell at the intersection of row 1 and column 1 (analogous to $A$1);
• RC2. Reference to a cell from the 2nd column of the current row;
• R3C. Reference to a cell from row 3 of the current column;
• R.C.. A cell reference 4 columns to the right of the current cell;
• R[-5]C. A cell reference 5 lines to the left of the current cell;
• R6C. A cell reference that is 6 rows and 7 columns to the right of the current cell;
• etc.

In general, it turns out that the analogue of the row or column anchor (the $ symbol) for the R1C1 style is the use of numbers after the row or column symbol (i.e. after the letters R or C).

Using square brackets allows you to make a relative offset relative to the cell in which the formula is entered (for example, R[-2]C makes an offset 2 rows down, RC moves 2 columns to the right, etc.). Thus, a shift down or to the right is indicated by positive numbers, and to the left or up - by negative numbers.

In summary, the main and most important difference between A1 and R1C1 is that for relative links style A1 takes the beginning of the sheet as the starting point, and R1C1 the cell in which the formula is written.
This is precisely what the main advantages of using R1C1 are based on; let's take a closer look at them.

Benefits of the R1C1 link style

There are 2 main advantages for which using R1C1 is preferable to A1: when checking formulas (searching for errors) and in macros.

Let's consider an example: take a simple table in which we will check the correctness of the calculation of the formula in the last column:

Do you see the error?

At first glance, it may seem that there are no errors and everything is normal, the formulas are similar to each other, nothing stands out from the general series. But if you look more closely, you will notice that in fact there is an error and in lines 4 and 5 the formulas are swapped with each other.

Let's now look at the same table with the cell style R1C1:

In this case, it is immediately clear that the formulas in lines 4 and 5 are not similar to the others, and it is much easier to find the error.
On larger tables it will be even more difficult to find errors, so this method search can significantly simplify and speed up the process.

A similar technique can be applied in the case of working with cells at .
If we need to write formulas for a range of cells that are products of two columns (as in the example above), then for the R1C1 style all formulas will be written exactly the same ( =RC[-2]*RC[-1]), so in the macro code you can immediately write a formula for all cells in the range. For style A1, unfortunately, this method is not suitable, since the entries for all formulas are different from each other and each will need to be written separately, which complicates the task somewhat.

R1C1 in Excel Functions

However, Excel has functions in which it is possible to use both addressing styles, regardless of the mode set in the settings. In particular, the functions ( INDIRECT in English version) and ADDRESS (ADDRESS in the English version) can work in both modes.

The style of the links used (A1 or R1C1) is specified as one of the arguments in these functions, and in some cases it may be preferable to use R1C1.

Thank you for your attention!
If you have thoughts or questions about use and benefits different styles links - write in the comments.

A collection of pivot table styles displayed on the contextual ribbon tab Constructor, contains 85 built-in styles. Categorized Light, Average And Dark, this collection allows you to change the color, lines, and other formatting elements of the pivot table. Please note that you can change the icons of each style using the settings from the group PivotTable Style Options(Fig. 1).

Download the note in or format, examples in format

If you check the box before opening the style gallery Alternating Columns or Alternating lines, then you can see which styles are supported this opportunity. If the icon for a particular style does not change after checking the box, it means that the style does not support alternating rows or columns. The Live View feature also applies to PivotTable styles. Therefore, when you hover your mouse over a style icon, the table will take on the formatting specified by that style.

Create your own PivotTable style in Excel 2010

However, if none of the 85 styles suits you, you can create your own. New styles are added to the collection and become available each time you create a pivot table. In this case, the new style is valid only in the current workbook. To move a PivotTable style to another workbook, copy and move the PivotTable with the desired style to the new workbook. The new style will be added to the palette. Apply this style to any pivot table in the new workbook, and then delete the previously moved pivot table.

Let's say you want to create a PivotTable style that alternates fill colors across three rows.

1. Select a PivotTable style from the gallery that supports row alternation. Right click on this style and select command Duplicate (Fig. 2).

2. Enter a new name for the style. Usually Excel program assigns the duplicate the name of the original style, to which it adds the number 2. In our example, this is Greenbar 2.

3. Listed Table element click on the value First line strip. The dialog box will display new section Band size.

4. Select from the drop-down menu Band size value 3.

5. If you want to change the color of the stripe that fills the rows, click the button Format. A dialog box will appear on the screen Cell Format. Go to the tab Fill and select a new color. Click OK to apply your changes and return to the dialog box Changing the PivotTable Style.

6. In the list of table elements, click on the value Second stripe of lines. Repeat the steps described in paragraphs. 4 and 5.

7. If you want this PivotTable style to become the default style, check the box at the bottom of the window Set as the default pivot table style for this document.

8. Click OK. Be prepared that changing the style settings won't do anything. The pivot table stubbornly continues to follow the old style, although the new style has already been created. This is not unusual because you have just created, but not yet applied, a new style based on the original Greenbar style.

9. Open the PivotTable Styles gallery. The new style is added to the top of the collection in the Custom section. Select New Style to change the table formatting.

Rice. 2. Create a new PivotTable style in the dialog box Changing the PivotTable Style

You can specify which style will be used by default when creating pivot tables in the future. In this case, not only the built-in style can be selected by default, but also any custom style created based on the built-in one. On the contextual tab Constructor open the style collection, right-click on the desired style and select the command Default.

The note was written based on the book by Bill Jelen, Michael Alexander. . Chapter 3.

In the second part of the article you will learn more advanced techniques for working with styles in Microsoft Excel.

In this part, you'll see how to change Excel's default styles and share them between workbooks. Here you will find some ideas to help you get the most out of using styles in Microsoft Excel.

How to change the preset style?

You can change any preset style, however, you cannot change its name!

To change an element of one of the style attributes:

How to create your own new style?

Personally, I prefer to create new styles instead of changing Microsoft's preset styles, for the simple reason that in this case you can give the created style a meaningful name. But this is entirely a matter of personal choice!

Here are two ways to create a new style:

Method 1: Copy the style from the cell

To copy cell formatting for a new style:

Method 2: Create a new style in the formatting dialog box

Alternatively, you can create a new style in the formatting dialog. For this:

Both of these methods will create a custom style in your workbook.

Helpful advice: Never waste your time again manual setting cell formatting, apply styles in your work, control formatting settings faster and more efficiently using the style settings menu.

Never create the same style twice! Although the style is saved only in the workbook where it was created, it is nevertheless possible to export (merge) styles into a new workbook using the merge function.

How do I merge the styles of two workbooks?

To move styles between workbooks:

Helpful advice: You can save the cell styles you like in a separate workbook, making it easier to merge with workbooks rather than endlessly searching for files scattered across numerous folders on your computer drive.

How to delete a custom style?

Deleting a style is as easy as creating it. To remove a custom style:

1. Run: Home(Home) > Styles(Styles) > Cell Styles(Cell Styles) on the Microsoft Excel Ribbon.
2. Right-click on the style you want to delete.
3. Select a command from the menu Delete(Delete).

Everything is elementary! No one will deny the simplicity of this tool!

Obviously, each person will individually determine the ways in which a given tool can be used to improve efficiency. To give you some food for thought, here are some of my own ideas for using styles in Microsoft Excel.

Have you decided to use styles in Microsoft Excel? I am confident that this tool can and will improve your efficiency. Why does he remain so unpopular? – this question really confuses me!!!

Calling the command:
-group Books/Sheets -Link style

Changes the display style of cell references in formulas from R1C1(Fig. 1) by the look A1(Fig. 2). This can be done through the parameters:
Excel 2003:

Tools-Options-General tab-R1C1 link style.

Excel 2007:

Menu - Excel Options - Formulas tab - Link Style R1C1.

Excel 2010-2013:

File - Options - Formulas tab - Link style R1C1.

Please note that when you change the link style, the appearance of the column headings also changes. With style R1C1 column headings are indicated by numbers (Fig. 1), and with the style A1- letters of the English alphabet (Fig. 2). Excel itself can change the link style without your knowledge. It does this if you open a book in which the link style R1C1 , and at this moment you have a style set A1.
Fig.1
Fig.2

Excel itself can change the link style without the user's knowledge. It does this if you open a book in which the R1C1 link style was set before saving the book. If you open such a book on a PC on which the A1 link style is installed, then the A1 style will be replaced by the R1C1 style. And every time such cases arise, going somewhere deeper into the Excel menu is not very convenient.