Freezing a column in excel. Video instructions for pinning a row in Excel. Fix the top line

Excel can create huge tables that contain a lot of information. To work with such a volume of data, you need to know some life hacks that will help you navigate the endless space of columns and cells.

Fortunately, Microsoft Office Excel provided special functions, simplifying the work with the program. One of them is the ability to pin a line - having learned how to do this simple trick, you will be able to view any area of the table without losing sight of the line with the column names or the so-called “header” of the table.

How to freeze the top row of an Excel table

So, you've created 2007 or 2010. Typically, the top row contains the column names, and the tables themselves are oriented vertically so that they can be scrolled from top to bottom.

When scrolling down, the top row of the table will “move away” and disappear from view. This problem can be solved by fixing top line.

Select any cell in your table.

IN top menu select the "View" tab and the "Freeze Panes" option.

In the drop-down list, select “Fix top row”. A boundary line will appear below it. This means that the line is fixed and will be visible on the screen even while scrolling the page.

How to Freeze Multiple Rows in Excel

It may be that in your table, not one, but several rows are reserved for column names. If you need to pin two or more rows, click on the cell that is located under the “header”. For example, if you need to fix rows 1 and 2, you need to activate the cell in row 3, as shown in the figure below.

After that, go to the “View” tab, and in the drop-down list select the first item - “Freeze areas”. The rows will be locked and will not “run” up when viewing the table.

After pinning several rows, a line will also appear to show the boundary of the pinned area. Now you can scroll through the file, but pinned lines will always be visible. In addition, the columns that are to the left of the selected cell will be frozen. This can be determined by the vertical line along the fixed columns. If you want to freeze only the rows, activate the cell in the first column before using the tool from the View menu.

How to unfreeze rows in Excel

If your table has frozen areas, the Freeze Areas menu will include an Unlock Areas option. It is needed to unlock all frozen rows or columns of the table.

How to Freeze a Column in Excel

Sometimes tables have horizontal orientation and are viewed from left to right. Then they have signed not only the columns, but also the rows. If the table contains a lot of information, then when you scroll to the right, you will lose sight of the first column. However, it can also be fixed.

To lock the first column in the table, go to the “View” tab - “Lock areas”. Select the last menu item, “Freeze First Column.”

To freeze multiple columns, you can use the Freeze Panes feature.

Freeze a row in Excel 2003 or 2000

In MS Office Excel 2003 or 2000, the process of locking table rows and columns is a little different. Here, the tool for docking an area is located in the Window menu. To freeze a row, you need to activate the cell below it and select “Window” - “Freeze Areas”. If you want to freeze a column, select the cell to the left of it.

To lock only the first row, click on cell A2, and if you want to lock only the first column, activate cell B1.

To unfreeze rows or cells, select the “Unfreeze Regions” tool from the “Window” menu.

Freeze an Excel row with a keyboard shortcut

Both old and current builds of MS Office understand special combinations keys with which you can freeze rows, columns and areas. Regardless of version office application, For proper operation hot keys, the Russian keyboard layout must be enabled.

In version 2003, you can pin an area by pressing Alt+o+z.

Excel 2007 and 2010 use the following keyboard shortcuts:

Freeze the top line: Alt+o+b+x.
First "A" column: Alt + o + b + th.
Region: Alt+o+b+z.
Cancel blocking: Alt+o+b+z.

Excel assumes working with large volumes numerical information. And when watching large tables The question arises, how to freeze a row in Excel when scrolling? After all, such data always assumes the presence of headings and summaries. This is the information that is convenient to see in front of you on the screen all the time, even when scrolling. This is exactly what we will talk about in this article.

How to freeze a line

If you need to pin the header in Excel, you can use special team on the toolbar. To perform this, place the pointer on any cell in the table.

Go to the "View" tab and click on the "Freeze Panes" button.

A menu will appear in which you can configure the necessary options.

Select the second option – “Lock top row”. Try scrolling the table and you will see that the first line does not move - it is fixed.

Only in our example, this is not the data that makes sense to capture when scrolling. We need a hat. In this case, we must fix the area.

How to freeze a region in Excel

Obviously, fix the range from the middle when scrolling Excel tables it is forbidden. That's why when we're talking about About the area, it includes all rows from the first to the dockable top and columns from the first to the dockable left.
The screenshot shows an example area. Fragments frozen during scrolling are highlighted in orange.

To get this fixation when scrolling, you need to set the pointer correctly by first selecting all required lines from the cap, and then, holding ctrl key select the required columns.

Freeze multiple rows in Excel

Place the pointer on the FIRST cell of the row AFTER the one you are freezing.

Then select the Freeze Pane command in the View panel.

pay attention to thin strip, which marked the boundary of the pinned area.

Freeze multiple Excel columns

Place the pointer on the FIRST cell of the column AFTER the one you are docking in the FIRST ROW.
If, as in our example, you have a union for all columns, then it will also fall into the area that is fixed when scrolling. To avoid this, remove the join and apply it to the unattached columns. .

Run the same command again.

The columns will be frozen when scrolling.

To freeze the first column in Excel, proceed in the same way as with the first row, selecting the appropriate item.

Freeze panes in Excel

Place the pointer on the FIRST cell AFTER the frozen column and the frozen row.
In the screenshot, the area is filled with color, and the first cell outside it has the address B3.

After that, run the “Freeze Region” command again. Everything will be ready.

It is not possible to freeze cells separately when scrolling in Excel.

How to freeze a cell in a formula in Excel

Another one important feature The program you need to know about is fixing the cell address in the formula. When creating a formula, you indicate their addresses, the data from which is involved in the calculations.

When copying these formulas, the addresses are shifted in the appropriate direction. For the example from the screenshot, copying the formula down will give the calculations C4/B4 for the next cell.

Such a cell reference (C4 or B4) is called RELATIVE. In our example, in the last column the value is calculated as the ratio of the actual volume for the day to the total for the year.

When we try to “stretch” the formula to the end, we get the following result.

This happens because the second address is also shifted.

But if we go to the cell with address B39, we will naturally find that it is empty. After all, the amount is counted only once.

In this case, you need to create ABSOLUTE link to cell C38 in the very first formula. This will allow you to fix it when copying by stretching it.
When entering a formula, after clicking the second cell in the formula, immediately press F4. The address will be surrounded by dollars.

After that, press Enter and copy.

All calculations will be completed.
Cycling F4 while entering a formula will give different results:

The first is full address fixation: $C$38
The second is to fix the line number: C$38. When copying, the column will change, and the second part of the address will remain at position 38 - D38, E38, etc.
The third click fixes the column name: $C38. When copying, the column will remain unchanged, and only the number will change: C39, C40, etc.
The fourth press is to release the lock.

This way you can manage your formulas. In conclusion, let us remind you that you can freeze a column, row, or area from the first column and first row to the current ones when scrolling. You cannot freeze a cell while scrolling. But you can fix the cell address to avoid offset when copying. We wish you good luck working in Excel!

Have a great day!

When working with large amounts of tabular data in Excel program For reasons of convenience, it may be necessary to record specific area tables - a header or data that should be constantly in front of your eyes, no matter how far the table is scrolled.

Working with Excel 2003

This feature is available in every version of Excel, but at the expense of differences in the interface and layout of menu items and individual buttons not configured the same way.

Freeze a row

If you need to attach a header to a file, i.e. top line, then in the “Window” menu you should select “Freeze Areas” and select the cell of the first column of the next line.

To fix several rows at the top of the table, the technology is the same - the leftmost cell next to the rows being fixed is highlighted.

Freeze a column

Fixing a column in Excel 2003 is done in the same way, only the cell in the top row of the next column or several columns after the one being frozen is selected.

Freeze an area

The Excel 2003 software package allows you to record both columns and rows of a table at the same time. To do this, select the cell next to the ones being assigned. Those. To freeze 5 rows and 2 columns, select the cell in the sixth row and third column and click “Freeze Regions”.

Working with Excel 2007 and 2010

Later versions software package Excel also allows you to freeze the file header in place.

Freeze a row

For this:

When you need to fix not one, but another number of lines, you need to select the first scrollable line, i.e. the one that will be immediately behind the assigned ones. After that, in the same item, select “Lock areas”.

Important! The function of fixing table sections in Excel 2007 and 2010 has been significantly improved. In addition to the fact that it is now located not in the “Window” section, but in the “View” section, the ability to separately fix the first column or first row has been added. In this case, it does not matter in which cell the cursor is located, the required row/column will still be fixed.

Freeze a column

To freeze a column, in the “Freeze Regions” section, you must check the option to freeze the first column.

If you want to keep several table columns visible when scrolling, then, by analogy with previous paragraph, highlight the first scrollable column and click the Freeze Panes button.

Freeze an area

The two options mentioned above can be combined by making sure that when scrolling the table horizontally and vertically, the necessary columns and rows will remain in place. To do this, select the first scrollable cell with the mouse.

Afterwards, fix the area.

Those. if, for example, the first line and the first column are fixed, this will be the cell in the second column and the second line, if 3 rows and 4 columns are fixed, then you should select the cell in the fourth row and fifth column, etc., the operating principle should be understandable.

Important! If there are several sheets in the file, then you will have to freeze and unfreeze parts of the table on each one separately. When you press buttons that fix columns, rows and sections of the table, the action is performed only on one sheet that is active (i.e. open) at the moment.

Unpin

It happens that you need to fix part of a table only while filling it out, but this is not necessary for subsequent use. Just as easily as a row or column is committed, it can be uncommitted.

In Excel 2007 and 2010, this function is located in the same “View” section and the “Freeze Panes” item. If there is any fixed area - a column, row or an entire area, then the “Unfreeze areas” button appears at this point, which removes the entire fixation of table elements on the sheet.

It will not be possible to partially remove the fastening. To do this, you will first have to cancel the fixation everywhere, and then fix the necessary sections of the table with a new one.

Conclusion

Fastening elements – useful feature, which greatly facilitates and simplifies working with large tables. This function can be configured in an elementary way - all possible options in the program for fixing table sections are placed in one menu item, the names of the buttons correspond to the functions, and explanations are given next to the buttons, as a result of which even inexperienced user will not make mistakes in formatting tabular data.

When working with large volumes of tabular data in Excel, for reasons of convenience, it may be necessary to fix a certain section of the table - the header or data, which should be constantly in front of your eyes, no matter how far the table is scrolled.

Working with Excel 2003

This function is available in every version of Excel, but due to the difference in the interface and the location of menu items and individual buttons, it is not configured in the same way.

Freeze a row

If you need to attach a header to a file, i.e. top line, then in the “Window” menu you should select “Freeze Areas” and select the cell of the first column of the next line.

To fix several rows at the top of the table, the technology is the same - the leftmost cell next to the rows being fixed is highlighted.

Freeze a column

Fixing a column in Excel 2003 is done in the same way, only the cell in the top row of the next column or several columns after the one being frozen is selected.

Freeze an area

Working with Excel 2007 and 2010

Later versions of the Excel software also allow you to fix the file header in place.

Freeze a row

For this:

Freeze a column

To freeze a column, in the “Freeze Regions” section, you must check the option to freeze the first column.

If you want to keep several columns of the table visible when scrolling, then, by analogy with the previous point, select the first scrollable column and click the “Freeze areas” button.

Freeze an area

Afterwards, fix the area.

Unpin

It happens that you need to fix part of a table only while filling it out, but this is not necessary for subsequent use. Just as easily as a row or column is committed, it can be uncommitted.

It will not be possible to partially remove the fastening. To do this, you will first have to cancel the fixation everywhere, and then fix the necessary sections of the table with a new one.

Conclusion

Pinning elements is a useful feature that makes working with large tables much easier and simpler. This function can be configured simply - all the possible options in the program for fixing table sections are placed in one menu item, the names of the buttons correspond to the functions, and explanations are given next to the buttons, as a result of which even an inexperienced user will not make mistakes in formatting tabular data.

Still have questions after reading the article? together we will find the answer.

When working with large databases, the user scrolls the worksheet up and down from time to time. At the same time, the most an important part, i.e. The table header becomes invisible. It seems like a small thing, but sometimes this can cause difficulties, especially if the table has many columns. The solution to this problem is very simple: you just need to know how to freeze a row in Excel.

Video instructions for pinning a row in Excel

How to freeze a row in Excel?

So, the essence of the problem is as follows: the user has enough large base data in Excel, and you want the first row to remain visible at all times when scrolling down. Before you begin this task, there are 2 things to note:

You can only fix those lines that are at the top (i.e., either only the first, or the first two, the first three, etc.).
If the worksheet is protected or in use, then this procedure will be unavailable (to exit this mode, press the “Esc” key).

Fixing the table header in Excel differs slightly depending on the version of the program. In order for the first row in Excel 2007 (or 2010) to be visible all the time, you need to click on the field that is located below it (in in this case this will be box A2) and then go to the View tab in the menu bar. After that, in the “Window” group, you need to click on the “Freeze Panes” button and specify that the top line is always displayed at the top (even when scrolling the worksheet up and down). Another way is to select the entire row by clicking on its number. After this, you need to execute the already familiar commands in the menu bar.

If everything was done correctly, now the table header should always be at the top. You can verify this by scrolling down the worksheet. This is also confirmed by a barely noticeable black stripe, which emphasizes the recorded range.

In more old version table processor(2003) there are 2 ways to fix the table header.

The first method is exactly the same - select the desired field (i.e. A2), and then click on the menu bar “Window - Freeze areas”. If the specified range is underlined with a black line, then everything has been done correctly.

The second method is a little more complicated. First, you need to click “Window - Split” in the menu bar. After this, 2 lines will appear: vertical and horizontal. Vertical line can be deleted by double-clicking on it with the left mouse button. A horizontal line must be moved so that it emphasizes the first row. Then you need to select the “Freeze areas” command again in the same item in the menu bar, after which the header will always be displayed on top.

In order to remove the fixation, you need to select the appropriate command in the “Window” item, which is called exactly the same.

Freezing Columns and Ranges

To fix a row in Excel 2007 and 2010 versions, use the “Freeze Areas” button. As you may have noticed, there were as many as 3 options in the drop-down list. In addition to a row, you can also mark a column or a specific range of cells.

The fixation procedure follows exactly the same principle as for strings. The user can mark only the left columns: for example, the first, first two, first three, etc. In order to mark a column, you need to select the corresponding cell, which is located to the right of it. For example, if you want only the first column to be visible, then you need to select field B1 and click on the “Fix column” item.

The area is marked similarly (i.e., row and column at the same time). For example, if you select field B2 and select the “Freeze Areas” command, Excel will freeze the first row and first column.