How to freeze a cell in an Excel formula

When writing an Excel formula, the $ sign in a cell reference confuses many users. But the explanation is very simple: this is just a way to fix it. The dollar sign in this case serves only one purpose - it indicates whether the link should be changed when copying. And this short guide provides complete information on the ways in which you can lock a cell address so that it does not change when you copy the formula.

If you are creating a formula for only one cell in your Excel table, then the problem of how to freeze the cell does not concern you. But if it needs to be copied or moved around the table, then this is where the pitfalls are hidden. In order not to break the calculations, some cells should be fixed in the formulas so that their addresses do not change.

As mentioned earlier, relative cell references are the default for any formula created in Excel. But their main feature is that they change when copied and moved. In many cases, it is necessary to fix the cell address in the formula so as not to lose this reference when the table is changed. Below we will look at the following methods:

How to fix a cell manually.
Using a function key.
Selective commit by row or column.
Fix the cell address with a name.

To prevent changes to cell, row, or column references, use absolute addressing, which differs in that a dollar sign $ is placed in front of the row or column coordinate.

Let's explain with a simple example.

=A1*B1

Relative links are used here. If we move this expression 2 cells down and 2 to the right, then we will see

=C3*D3

The column letter has changed by 2 positions and the row number by 2 units.

If in cell A1 we have information that we need to use in many cells of our table (for example, the dollar exchange rate, the size of the discount, etc.), then it is advisable to fix it so that the link to cell A1 never “breaks”:

=$A$1*B1

As a result, if we repeat the previous operation, we will obtain the formula

=$A$1*D3

The link to A1 is now not relative, but absolute. You can read more about relative and absolute links in this article on our blog.

This is the solution to the problem of fixing a cell - you need to turn the link into an absolute one.

Now let’s take a closer look at how you can pin a cell, row or column in a formula.

What is an Excel link

A sheet consists of cells. Each of them contains certain information. Other cells can use it in calculations. But how do they know where to get the data? This helps them make links.

Each link identifies a cell using one letter and one number. A letter indicates a column, and a number indicates a row.

There are three types of links: absolute, relative and mixed. The second one is set by default. An absolute link is one that has a fixed address of both column and column. Accordingly, mixed is one where either a separate column or a row is recorded.

Method 1

In order to save addresses of both columns and rows, you must complete the following steps:

Click on the cell containing the formula.
Click on the formula bar for the cell that we need.
Press F4.

As a result, the cell reference will change to absolute. It can be recognized by the characteristic dollar sign. For example, if you click on cell B2 and then click on F4, the link will look like this: $B$2.

1 2

What does the dollar sign before the cell address part mean?

If it is placed before a letter, it means that the column reference remains the same no matter where the formula is moved.
If there is a dollar sign in front of the number, this indicates that the row is pinned.

Method 2

This method is almost the same as the previous one, only you need to press F4 twice. for example, if we had cell B2, then after this it will become B$2. In simple words, in this way we were able to fix the string. In this case, the letter of the column will change.

It is very convenient, for example, in tables where you need to display the contents of the second cell from the top in the bottom cell. Instead of doing this formula many times, it is enough to fix the row and let the column change.

Fixing rows

Simply filling in the cells in this case is the least we can do. In addition, you must create a table. To do this, you can use either an automatic creation tool, or draw all the boundaries manually using the built-in utility. Otherwise, you will not be able to secure some objects inside. Let's look at how to do this step by step.

Open an Excel document with the existing table and its borders. Select an arbitrary line or one that should be fixed in your case. Next, go to the View tab, move to the Freeze areas item and select the desired option in the pop-up list. In our case – Freeze areas.

After that, while scrolling the sheet, you will see the area that you originally pinned. If you need to edit it, you can make changes directly in the pinned form.

In addition to freezing cells in an Excel document, you can use the Split Panes feature. This will help bring certain boundaries to the visible part of the sheet on which you are working.

Method 3

This is completely similar to the previous method, only you need to press the F4 key three times. Then only the column reference will be absolute, and the row will remain fixed.

Method 4

Suppose we have an absolute reference to a cell, but here we need to make it relative. To do this, you need to press the F4 key so many times that there are no $ signs in the link. Then it will become relative, and when you move or copy the formula, both the column address and the row address will change.

Method two

The second method will be for a different type of formula, in particular if they have the form, for example, R1C1. I propose to consider all the nuances in detail:

if you need the entire cell to be fixed, it should look like this: R4C6;
if you want to make only the line non-slip, in this case R, the formula will look like R4C[-1];
to fix only column (C), write RC6;
To completely fix the entire range, highlight the formula bar and press F4 on the keyboard.

Pinning cells for a large range

We see that the above methods are not difficult to implement at all. But there are specific tasks. And, for example, what should we do if we have several dozen formulas at once, the links in which need to be converted into absolute ones.

Unfortunately, standard Excel methods will not achieve this goal. To do this, you need to use a special addon called VBA-Excel. It contains many additional features that allow you to perform standard Excel tasks much faster.

It includes more than a hundred user functions and 25 different macros, and is updated regularly. It allows you to improve your work with almost any aspect:

Cells.
Macros.
Functions of different types.
Links and arrays.

Among other things, this add-on allows you to pin links in a large number of formulas at once. To do this you need to do the following:

Select range.
Open the VBA-Excel tab that will appear after installation.
Open the “Functions” menu, where the “Lock formulas” option is located.

6
Next, a dialog box will appear in which you need to specify the required parameter. This addon allows you to fix a column and a column separately, together, and also remove an existing fix by a package. After you select the required parameter using the corresponding radio button, you need to confirm your actions by clicking “OK”.

One column

When designing a table, sometimes you need to set the header on the left, and not on the top. To make headers visible when scrolling horizontally:

Select any cell. Click on the Freeze Tool (View tab) and activate the “Freeze First Column” item.
The columns will be delimited by a vertical line.

Example

Let's give an example for greater clarity. Let's say we have information that describes the cost of goods, their total quantity and sales revenue. And we are faced with the task of making the table, based on quantity and cost, automatically determine how much money was earned without deducting losses.

In our example, for this you need to enter the formula = B2*C2.

It's quite simple, as you can see. It is very easy to use her example to describe how you can fix the address of a cell or its individual column or row.

You can, of course, in this example try to drag the formula down using the autofill marker, but in this case the cells will be automatically changed. So, in cell D3 there will be another formula, where the numbers will be replaced, respectively, by 3. Further, according to the scheme - D4 - the formula will take the form =B4*C4, D5 - similarly, but with the number 5, and so on.

If this is how it should be (in most cases it turns out this way), then there is no problem. But if you need to fix the formula in one cell so that it does not change when you drag it, then this will be somewhat more difficult to do.

Let's say we need to determine dollar revenue. Let's indicate it in cell B7. Let's be a little nostalgic and indicate the cost of 35 rubles per dollar. Accordingly, to determine revenue in dollars, it is necessary to divide the amount in rubles by the dollar exchange rate.

Here's what it looks like in our example.

If we try to write a formula in the same way as in the previous version, we will fail. Likewise, the formula will change to the appropriate one. In our example it will be like this: =E3*B8

. From here we can see. that the first part of the formula has turned into E3, and we set ourselves this task, but changing the second part of the formula to B8 is of no use to us. Therefore, we need to turn the link into an absolute one. You can do this without pressing the F4 key, just by putting a dollar sign.

After we turned the reference to the second cell into an absolute one, it became protected from changes. Now you can safely drag it using the autofill marker. All fixed data will remain the same, regardless of the position of the formula, and unfixed data will change flexibly. In all cells, the revenue in rubles described in this line will be divided by the same dollar exchange rate.

The formula itself will look like this:

=D2/$B$7

Attention!
We specified two dollar signs. This way we show the program that it needs to capture both the column and the row.

Possible problems and solutions

Knowing how to not change a cell in a formula in Excel can simplify complex calculations and avoid mistakes. In this case, various difficulties may arise that prevent the work from being completed:

Unable to commit data. Make sure you carry out the work according to the instructions above. Try restarting the application to eliminate temporary errors. You may also need to restart the PC/laptop itself.

The position of the absolute link changed during operation. Many people believe that if you fix a cell, its position will not change. But that's not true. In practice, the absolute reference in Excel changes when the user adds/deletes rows/columns in a worksheet. As a result, the location of the fixed cell is also adjusted. For example, if you insert a line at the top of the tables, then the address will move down in all formulas from which the link comes. This point must be taken into account when making changes or abandoning adjustments altogether.
Nothing happens when I press F4. Make sure the function key works. Sometimes to activate it you need to press the Fn+F4 combination. This depends on the type of keyboard you are using.

Now you know how to freeze a table section in Excel to avoid constantly moving data. To do this, use the manual method and the F4 function button. You can control the correct operation by the position of the dollar symbol, which appears before the number or letter.

In the comments, tell us whether the proposed instructions were useful to you, and what other methods can be used to solve the problem.

Cell references in macros

A macro is a subroutine that allows you to automate actions. Unlike standard Excel functionality, a macro allows you to immediately set a specific cell and perform certain actions in just a few lines of code. Useful for batch processing of information, for example, if it is not possible to install addons (for example, you are using a company computer, not a personal one).

First you need to understand that the key concept of a macro is objects that can contain other objects. The Workbooks object is responsible for the electronic book (that is, the document). It includes the Sheets object, which is a collection of all sheets of an open document.

Accordingly, cells are a Cells object. It contains all the cells of a specific worksheet.

Each object is qualified using arguments in parentheses. In the case of cells, links to them are given in the following sequence. The row number is indicated first, and then the column number or letter (both formats are acceptable).

For example, a line of code containing a reference to cell C5 would look like this:

Workbooks("Book2.xlsm").Sheets("Sheet2").Cells(5, 3)

Workbooks("Book2.xlsm").Sheets("Sheet2").Cells(5, "C")

You can also access a cell using a Range object.

In general, it is intended to provide a reference to a range (the elements of which, by the way, can also be absolute or relative), but you can simply give the name of the cell, in the same format as in an Excel document.

In this case, the line will look like this.

Workbooks("Book2.xlsm").Sheets("Sheet2").Range("C5")

It may seem that this option is more convenient, but the advantage of the first two options is that you can use variables in brackets and give a reference that is no longer absolute, but something like a relative one, which will depend on the results of the calculations.

In this way, macros can be used effectively in programs. In fact, all cell or range references here will be absolute, and therefore can be used to capture them as well. True, it is not so convenient. Using macros can be useful when writing complex programs with many steps in the algorithm. In general, the standard way of using absolute or relative links is much more convenient.

How to unpin areas

To unfreeze areas, you do not need to make any selection of cells; everything is much simpler here. All you need to do is click on the “Window” button in the “View” tab and in the options that open, click “Unlock areas”.

Note: Similar to the situation described above, if the size of the program window allows, there will be no intermediate “Window” tab, and we can immediately find all the buttons responsible for freezing areas in the “View” tab.

As a result of this procedure, all areas in the document will be unpinned.