We will analyze use link styles R1C1 and A1 in ExcelWe define when which style is preferable to use, and also learn how to switch between them.

Addressing to sheet cells in Excel is one of the fundamental aspects of the program, and most of the users of the program work with the style of the types of types A1.in which the columns are set by letters, and lines numbers (in this case A. - literary column, and 1 - Row number, respectively, cell - column intersection A. and strings 1 ).
For example, cell D5. stands at the intersection of column D. and strings 5 , Cell F8. At the intersection of column F. and strings 8 etc.
In addition to Excel, the style A1 is used by default, so everyone used to work with it.

Such a coordinate system is also more familiar to us, as it is used, for example, in a chess alignment or in the game " sea Boy»It is quite well acquainted with all of us since childhood:

However, sometimes the use of another style - R1C1where and columns and lines are set by numbers ( R1 means string 1 where R - Row (string translated from English), and C1. means column 1 where C - Column (column translated from English), respectively, cell - crossing string 1 and column 1 ).

How to enable or disable R1C1 links to Excel?

As mentioned above, the default exceced link style A1 is used, so to start working with R1C1 you first need to turn it on. For this go to File -> Parameters and in the tab Formulas And in the block Work with formulas put a tick opposite the field R1C1 link style:

Accordingly, if you need to return to the standard version of the addressing of the type A1, then the R1C1 style can be disabled to remove the appropriate tick in the settings.

Features and differences of addressing styles A1 and R1C1

First of all, when working with cells, please note that for the style of R1C1 in the address first there is a string, and then the column, and for A1, the opposite is first - first the column, and then row.
For example, cell $ H $ 4 will be recorded as R4C8. (and not as R8C4.), so be careful when manual input formulas

Another difference between A1 and R1C1 - appearance window excel programsin which there are different columns on the work sheet (a, b, c for style A1 and 1, 2, 3, ... for R1C1 style) and cell name:

As you know, there are 3 types of links in Excel (you can read more details): Relative (A1), absolute ($ A $ 1) and mixed ($ A1 and $ 1), where the dollar sign ($) serves as a row or column.
In case of style R1C1 You can also use any type of reference, but the principle of their preparation will be several other:

• RC. Relative reference to the current cell;
• R1C1. An absolute link to the cell at the intersection of string 1 and column 1 (analog $ A $ 1);
• RC2.. Reference to the cell from 2 columns of the current line;
• R3C.. Reference to the cell out of 3 lines of the current column;
• RC. Reference to the cell on 4 columns to the right of the current cell;
• R [-5] C. Link to the cell on 5 lines to the left current cell;
• R6C.. Reference to a cell of 6 lines and on 7 columns to the right of the current cell;
• etc.

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

The use of square brackets allows you to make a relative displacement relative to the cell in which the formula is introduced (for example, R [-2] C makes offset by 2 rows down, Rc is a shift to 2 columns to the right, etc.). Thus, the offset down or right is denoted by positive numbers, left or up - negative.

As a result, the main and most important difference between A1 and R1C1 is that for relative references a1 style per reference point takes the beginning of the sheet, and the R1C1 is the cell in which the formula is written.
It is on this that the main advantages of using R1C1 are being built, let's stop more on them.

Benefits of R1C1 Link Style

2 main advantages can be distinguished in which R1C1 is preferable than A1: check formulas (error search) and B. macros.

Consider an example: take simple tablein which we verify the correctness of the calculation of the formula in the last column:

See the error?

At first glance, it may seem that there are no errors and everything is fine, the formulas are similar to each other, nothing is knocked out of the general row. But if you look more carefully, it can be noted that in fact the error is also in 4 and 5 lines of formulas are changed in places with each other.

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

In this case, it is immediately clear that the formulas in 4 and 5 rows are not like others, and it is already much easier to find an error.
On tables bigger size It will be more difficult to seek a mistake, so this method The search can be significantly simplified and accelerated the process.

A similar method can be applied and in the case of work with cells at.
In case, if we need to register for the range of cells of the formula of two columns (as in the example above), then for the style of R1C1, all formulas will be recorded absolutely the same ( \u003d Rc [-2] * rc [-1]) Therefore, in the macro code, you can immediately register the formula for all the ranges of the range. For A1, unfortunately, this method is not suitable, since the records of all formulas differ from each other and each will need to be prescribed separately, which complicates the problem somewhat.

R1C1 in Excel features

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

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

Thanks for attention!
If you have thoughts or questions on the use and benefits of different styles of links - write in the comments.

Team call:
-Group Books / Sheets -Link style

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

Service-parameters - Tab General Link Style R1C1.

Excel 2007:

Menu-parameters Excel-tab Formula-style links R1C1.

Excel 2010-2013:

File-parameters-tab Formula-style link R1C1.

It should be noted that when changing the style of links, the type of column headlines is also changing. In style R1C1 Column headers are indicated (Fig. 1), and when style A1. - Letters of the English alphabet (Fig. 2). Excel can also change the link style without your knowledge. Does it so in case you open a book in which link style R1C1 , and you have a style at that moment A1..
fig.1
fig.2

Excel Maybe without a user's knowledge can change the link style. Does it do so if you open a book in which the R1C1 link style was installed before saving the book. If you open this book on the PC, which is set by the A1 link style, then the A1 style will be replaced with the R1C1 style. And every time there is such cases to go somewhere deep into the Excel menu is not very convenient.

Styles in Excel are a tool that allows you to significantly simplify and speed up the document formatting process. Formatting style can be defined as stored under a specific name of ready-made formats settings. They can be easily assigned to one or multiple cells.

Assigning Formatting Style Cells

Each style defines the following settings:

1. Font (type, size, color, etc.).
2. Number Display format.
3. Borders of cells.
4. Pouring and patterns.
5. Cell protection.

Thanks to the styles, all sheets are easy and quickly formatted. And if we make a change in style, then these changes are automatically assigned to all sheets that are formatted.

Excel provides a library from ready-made thematic styles, as well as there is an opportunity to create their own custom styles.

To use the library of embedded styles you need:

Creating a custom style

Now let's create your user style, but according to the sample is ready:

Thus, on the basis of the built-in style, we created a duplicate, which changed to your needs.

Is it possible to change the style in Excel? In principle, it was possible and not to create a duplicate, and in the context menu immediately select "Change". All changes in any embedded default style would be preserved only in the current book. On the program settings, they do not affect. And when creating a new book, the style library is displayed standardly unchanged.

Is it possible to remove the style in Excel? Of course you can, but only within the same book. For example, how to delete custom styles in Excel?

Select the desired style on the main panel: "Cell styles" and in the "Custom" section click the right mouse button. From the context menu that appears, select the "Delete" option. As a result, the cells are cleaned of the formats of the specified appropriate style.

In the same way, you can remove styles in Excel built-in default in the library, but this change will be distributed only to the current book.

Create Cell Format Style

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

1. Specify empty cell Format with fill and font color change.
2. Now select this source cell and select the tool on the main panel: "Cell Styles" - "Create Cell Style".
3. In the Style window, specify the name of the style "as in the cell1" and click OK.

Now you in the "Custom" styles section displays the name "as in the cell1". The principle is understandable.

All of the above, the described style formats are saved as part of the Excel current book file. Therefore, for further actions, save this book called "Lesson on Styles .xlsx".

Style Copying Other Books

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

1. Create a new book in which we will use the user style "as in the cell1".
2. Open the book "Lesson on Styles .xlsx" from the saved source style required to us.
3. Select a tool on the main tab: "Cell styles" - "merge". And in the "Style Combination" window, specify the book we need and approx.

Now all custom or modified formats from the source book are copied to the current one.

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

Formatting in Excel is a very tedious, but important task. Thanks to the styles, we can significantly speed up and simplify this workflow. Saving at the same time precise copy Cell formats in different sheets and books.

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

In this part you will see how to change Excel styles installed by default, and share them between job books. Here you will find several ideas that will help get a maximum of using styles in Microsoft Excel.

How to change the preset style?

You can change any preset style, however, you will not be able to change his name!

To change the element of one of the style attributes:

How to create your own new style?

Personally, I prefer to create new styles, instead of changing the pre-installed Microsoft styles, for the simple reason that in this case you can give a meaningful name to the style. But this is a completely question of personal choice!

Here are two ways to create a new style:

Method 1: Copy style from 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 box. For this:

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

Helpful advice: Never wait for time on manual setting Formatting cells, use styles in operation, control the formatting settings faster and more efficient using the style settings menu.

Never create the same style twice! Although the style is saved only in the workbook, where it was created, however, there is an opportunity to export (unite) styles into a new book using the association function.

How to accomplish the styles of two working books?

To move styles between jobs:

Helpful advice: You can save the styles of the cells that you like, in a separate workbook, to make it easier to merge with books, and not engage in endless search for files scattered on numerous folders on your computer disk.

How to delete custom style?

Remove the style as simple as creating it. To delete Custom Style:

1. Perform: HOME (Home)\u003e Styles. (Styles)\u003e Cell Styles. (Cell styles) on the Microsoft Excel ribbon.
2. Right-click on the style to be deleted.
3. In the menu, select the command Delete. (Delete).

All elementary! No one will deny the simplicity of this tool!

Obviously, each person will individually determine the methods in which this tool can be used to improve efficiency. To give you food for reflection, I will give some of my own ideas of styles in Microsoft Excel.

Have you decided to use styles in Microsoft Excel? I am sure that this tool can improve and improve your effectiveness. Why does he remain so unpopular? - This question really confuses me !!!

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

1. Allocate the A4 cells again: A12, then right-click them and select the command in the context menu Format cells.

2. In the dialog box that opens, expand the tab. Alignmentshown in fig. 9.11.

3. In the drop-down list horizontally Select on the left edge (indent),

4. Use the arrows to enter into the counter indent 1 amount to set a small indent number from the left edge of the cells.

5. Click on the button OK.

6. In the toolbar Formatting Click on buttons Bold and Italics. Spreadsheets Excel can also be formatted using styles.

Fig. 9.11. Setting up text alignment in Excel

Application of styles in Excel allows you to quickly assign a certain predetermined design to the cells. Style change leads to instant update of the formatting of all cells that this style is assigned. Let's configure the style for headers and signatures and appoint it to two groups of the combined cells of the leaf costs.

7. As a basis again created style Always selected the format of the active cell. Click on the combined B1: G1 cell to select its style format as source.

8. Run the command Format\u003e Style.

9. Enter the word inscription in the drop-down list of the dialogue window of the opened window. It will be the name of a new style. In the dialog box, its initial parameters will appear (that is, the B1: G1 range formatting mode).

10. Click on the button Change.

11. In the dialogue window that opens Format cells On the tab Font Select a 12-point bold font and click on the button OK.

Fig. 9.12. Style Settings Window in Excel

12. In the dialog box Style Reset all flags except the font, as shown in Fig. 9.12 So that the new style affects only the font of the cells and has not changed other parameters of the design.

13. Click on the button OK. The header of the company's expenses will increase in accordance with the new font size.

14. To change the signature font, the average table value, select B14: F14 cells.

15. Follow the command Format\u003e Stylein the drop-down list Style name Select the inscription point and click on the button. OK.

Note To create new document Based on the template with additional styles and decoration, select the command File\u003e CreateOpen in the dialogue window Creating a document A tab with the desired template and double-click on its icon. Anyone excel Book You can use as a template, after saving it in the XLT format file.

The current document was created without using a template, nevertheless there are several standard Excel styles:

* Normal - Standard cell style;
* Percentage - the style of cells containing percentages;
* Financial - cash (rubles and kopecks);
* Financial - money values \u200b\u200b(only rubles only);
* Monetary - monetary values \u200b\u200b(rubles, kopecks and a monetary unit sign);
* Monetary - Cash values \u200b\u200b(only rubles and a monetary unit sign).

16. To set the number of numbers in the table, select the cells B4: H12.

17. Click ctrl key And without releasing it, click on the F14 cell. This cell will be attached to the selection.

Fig. 9.13. Formatting text in Excel

18. Select Team Format\u003e Stylelisted Style name Select Monetary and click on the button OK. The table will become such as shown in Fig. 9.13.