How to leave the top row fixed in excel. Freezing multiple columns

When working with large Excel spreadsheets, it is often necessary to freeze rows or columns. This is necessary in order to always see the names of rows and columns and not confuse cells when scrolling vertically or horizontally.

In this article, you will learn how to freeze a row or column in Excel when scrolling and how to remove a freeze when it is no longer needed. The material will be relevant for everyone modern versions Excel spreadsheet, including Excel 2007, 2010, 2013 and 2016.

First, let's talk about how to freeze one Excel row when scrolling. This method fasteners are most often needed. Indeed, in most cases, the top row of the table is a row with column headings. And for more comfortable work with a table given string should always be visible.

In order to freeze a single row, you just need to go to the "View" tab, click on the "Freeze Panes" button and select the "Freeze Top Row" option. In the screenshot below, the "View" tab and desired option anchorages are marked with arrows.

As a result, a thin line will appear in the table after the first row. This line indicates the border of the anchored part of an Excel table. That is, the part of the table that will always be visible when scrolling. IN this case this line separates only the top line.

Please note that when you click on the "Freeze Top Row" button, any cell of the Excel table can be selected, this does not affect the result in any way.

How to Freeze Multiple Rows in Excel

It is not uncommon for a table to have a large header of many rows, all of which should be visible to users when they scroll. For such a header, the method with fixing one line will not help. Here you need to use the fixing of the entire table area.

To solve this problem, you must first select the correct cell. This must be the cell that is in the first column just below the . For example, if your header consists of 3 lines, then in order to fix it, you need to select cell A4 ( this situation shown in the screenshot below). If the header consists of 5 lines, then to fix it, you need to select cell A6 and so on.

After the desired cell is selected, go to the "View" tab, click on the "Freeze Panes" button and select the "" option.

As a result, the number of rows you need will be pinned to the top of your Excel spreadsheet. Of course, provided that you have selected the correct cell.

How to freeze one column in Excel

In some cases, it becomes necessary to fix not rows, but columns. Typically, this situation occurs in tables in which data is placed in a horizontal plane and there is horizontal scrolling.

This problem is solved in a similar way. For example, in order to freeze one first column in Excel spreadsheet you need to go to the "View" tab, click on the "Freeze Panes" button and select the "Freeze First Column" option

As a result, the first column of the Excel spreadsheet will be frozen when scrolling horizontally. Wherein given column will be separated from the rest of the table by a thin line.

As in the case of a single row, when freezing a single column, the position of the selected cell does not play any role.

How to Freeze Multiple Columns in Excel

You can also freeze multiple columns at once. To do this, select the cell in the first row immediately after the area that should be pinned. For example, if you need to freeze the first two columns, then you need to select cell C1 (this situation is shown in the screenshot below). And if the first three columns, then you need to select cell D1 and so on.

After selecting the cell, you need to go to the "View" tab, click on the "Freeze Panes" button and select the "Freeze Panes" option.

As a result, in the Excel table, the number of columns you need will be fixed when scrolling horizontally.

How to Freeze Multiple Rows and Columns at Once in Excel

The most difficult situation, this is when you need to freeze both rows and columns at the same time. In this case, you need to select a cell that will be at the crosshairs of the fixed rows and columns, but one cell below and one cell to the right. For example, if you need to freeze three rows and two columns, then you need to select cell C4 (this situation is shown in the screenshot below).

After selecting the desired cell, as in the previous cases, you need to go to the "View" tab, click on the "Freeze areas" button and select the "Freeze areas" option.

As a result, the area you need will be fixed and will always be in front of your eyes when scrolling vertically or horizontally in an Excel spreadsheet.

How to unfreeze rows or columns

If you set the wrong pin, then you can remove it using the same button on the "View" tab. Click on "Freeze Panes" and select the "Unpin Panes" option.

It should be noted that removing incorrectly fixed areas using the Ctrl-Z key combination will not work. You need to use the "Freeze Areas" button.

If you want to freeze a specific area in a document, you need to know how to freeze a row in Excel.

Thanks to this, you can adjust the visibility of the cells when scrolling the main sheet.

In Excel, you can freeze both columns and rows of a sheet.

Line pinning

Advice! Using the attachment function, you can leave the necessary columns or lines in sight while scrolling through the file sheet. In the same way, you can fix the formula, cell and all sorts of notes. Fixed elements are visually separated by a solid line.

Thanks to this, they can be flipped through separately from each other.

To freeze a string, run the following actions:

• Create new document programs (or open an existing one);
• Highlight the line you want to attach. To speed up the selection of a large line, click on the starting cell, then Shift key and on final element. Thus, the entire line will be instantly selected;

• Go to the standard "View" tab, which is located in the main program window on the toolbar;
• Locate the window options bar and select the pinning panes key. In the dropdown list, click on the function that fixes the string, as shown in the image below;

Thus, you can easily highlight the table header.

If you look at the figure, you will see that the fixed rows are displayed, even after scrolling the table for two hundred lines.

Also be sure to read:

Freezing a column

To freeze a column using Excel, follow the instructions:

• Select at one time the columns of the table that you want to attach;

• In the "View" tab, find the menu for pinning elements and fix the selected or several columns, as shown in the figure below;

Thus, the table can be scrolled from right to left. The fixed column will be visible to the user at all times.

If you want to unpin a previously selected element, follow the instructions:

1. Go to the "view" window on the toolbar;
2. Unfreeze areas using the menu in the Freeze Elements tab.

Pinning Document Regions

In the Excel spreadsheet, you can fix not only columns and rows separately from each other.

You can also fix individual groups custom elements. Thus, you can significantly speed up the work with complex tables and reports.

To fix several components at the same time, select them and click on the “freeze areas” menu item, as shown in the figure:

After that, the selected elements will remain visible while scrolling the window in different directions.

Consider, how to fix a row, column, table header, heading, link, cell in a formula, picture in a cell in Excel and other.
How to freeze row and column inExcel.
In Excel, you can freeze the top row and the left first column, freeze multiple rows and columns, an area, etc. See the article "How to freeze a row in Excel and a column".
How to pin an image in a cellExcel.
For example, we created a form, a price list with product photos. We need to make sure that the pictures do not move when we use the filter in our price list. To do this, you need to attach pictures, photos to certain cells. How to do this, read the article "Insert a picture into a cell in Excel".
How to freeze a cell in a formulaexcel.
When we copy a formula in Excel, the cell address changes. So that the cell address does not change when copying, you need to write an absolute reference to the cell in the formula. Read about relative and absolute cell references in formulas in the article "Relative and absolute references in Excel".
How to fix a hyperlink inexcel.
IN big table you can make a table of contents to quickly navigate to desired section tables, on desired sheet books. If you do not fix the links, then when you insert rows, columns, links will move. To prevent this from happening, they need to be fixed in a certain place. See the article " Table of Contents in Excel" about this.
Pin printable area toExcel.
We select a range of cells, rows, columns, etc. in the table. If you need to select non-adjacent lines, etc., then select the first line. Press and hold down the Ctrl key and select following lines, cells, etc.
On the "Page Layout" tab, in the "Page Setup" section, click on the "Print Area" function button. In the window that appears, click on the word "Set".
When we set the first print area, the Print Area dialog box will display new feature"Add Print Area".
To remove the print area, click on the "Remove" function.
You can also set the print area as follows. On the Page Layout tab, in the Page Setup section, click the Page Setup button. The button is circled in red in the picture.
In the dialog box that appears, click on the "Sheet" tab.
In the "Print range" line, write the range of cells that you want to print. If you need to print the table header on all sheets, then in the line "Print on each page" next to the words "Through lines" write the range of cells of the table header.
Lock cell size toExcel.
In order not to change the size of rows and columns without your knowledge, you need to put protection. How to set a password, see the article “Password for Excel. Excel Protection» . Before setting the password, select the entire table. In the "Format Cells" dialog box, uncheck the "Protected Cell" box. Click "OK". We allocate desired columns, rows, cells, range, etc. In the "Format Cell" dialog box, check the "Protected cell" function.
Now we set a password. In the "Protect Sheet" dialog box, check the boxes for all functions, except for the functions for changing rows and columns (cell formatting, column formatting, etc.). Everything. You can work in a table, but the size of columns and rows cannot be changed.
How to remove the pinned area inExcel.
To do this, you need to do the opposite.
For example, to remove the fixed print area, go to the "Page Layout" tab in the "Page Setup" section. Click on the "Print Area" function button and select the "Remove" function from the window that appears.
To unfreeze the top rows and first columns, on the "View" tab in the "Window" section, click on the "Freeze panes" button. In the window that appears, select the "Unpin areas" function.
In the formula, unpin a cell - make a relative reference to the cell address instead of an absolute one.
To be able to resize cells, rows, columns, you need to remove the password from the sheet.

Sometimes for work it is necessary that the date be written in text format. How to change the date format, see the article "

Using Microsoft Excel often involves working with a lot of data on a single sheet, such as reconciling and viewing spreadsheets. When the lines go off the screen, it becomes necessary to constantly scroll the page in search of desired value- this is not only uncomfortable, but also irrational, because it takes the user time.

However, to rid yourself of such inconveniences is very simple - it is enough to know How to freeze an area in excel I will tell you where in Excel you can freeze areas, and if necessary Microsoft Excel. We will talk about several versions of the program, namely 2003, 2007, 2010, 2013, 2016.

• Basic definitions
• Pinning and unpinning areas in the 2003 version of the program
• Freeze panes in other versions of Excel
• Detach in the program of other versions
• Addition

Basic concepts used in this article

ribbon- the area of ​​​​the upper part of the Excel window containing tabs.

Tabs– tools that generalize groups of certain actions into categories.

What is the pinning function for? By separating the main part from the rest of the text, you will always see the “header” of the table and understand which line you are viewing. This will prevent mistakes in the work. Cells in a pinned area do not change their position, no matter what you do in the program.

Pinning and Unpinning Regions 2003

Freezing an area in Excel 2003 is quite simple. Anchoring is done by one or more columns to the left and/or one or more rows above the selected cell.

That is, if you need to select 3 columns on the left and three rows on top, you need to select cell C4 and click on the tab Window - Freeze Panes.

If you need to select three columns on the left, select cell D1 and perform a similar action Window - Freeze Panes.

In order to freeze the rows above, you need to select the first cell of the column. To fix the top four rows, you need to select cell A5 and execute the already known command.

To freeze the first row and column at the same time, you need to select cell B2.

How to unpin cells in Excel 2003

Detachment in 2003 version is as follows Window − Remove screen pinning.

How to freeze a region in Excel 2007, 2010, 2013, 2016

In principle, working with pinning in versions of Excel other than 2003 has a similar mechanism. Added buttons to fix the first row and the first column.

The pinning control command is on the ribbon, tab View - Freeze panes.

So, to fix the first column, choose View - Freeze Panes - Freeze First Column.

In order to freeze the first row in Excel, you need to select View - Freeze Panes - Freeze First Row.

Instructions: how to fix a row and a column (several rows and columns) in a table at the same time.

Let's say we need to fix the "Header" 4 top rows and 2 first columns. To do this, select cell C5 View - Freeze Areas - Freeze Areas.

To freeze several columns at the same time, you need to select a cell in the first row after these columns.

To freeze several rows at the same time, you need to select the cell in the first column after these rows.

Now, even if the table is supplemented with hundreds and thousands of new elements, the selected cells will not disappear from view. In older versions of the program, fixing areas is done in the same way on the "Window" tab.

Despite the ease, some users are unable to solve this task due to the lack of a "View" tab on the taskbar. Usually with similar problems collide when using starter version-Excel Starter. Such a program does not support the ability to pin and unpin cells.

Many users are wondering how pinned areas will look in print? Indeed, in Excel it is formed great amount reporting documents, which are sometimes more convenient to provide on paper.

For printing, pinned areas act in the same way as in in electronic format. Everything stays in place. You can verify this with a preview.

Remove a pinned area

In order to remove the pinned area in Excel, you need to select the "View" tab and you will see that the pop-up menu began to display the "Unpin areas" item. After confirming the operation, the rows or columns that were previously fixed will again begin to move when scrolling.

Addition

As you can see, working with pinned areas in Excel does not require much effort or specific knowledge - everything is simple and intuitive, especially if we are talking about modern variations of the program. Using this function will save you time and nerves when working with tabular data.

If you have any questions, ask them in the comments!

In Excel, you 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, at Microsoft Office Excel There are special functions that make it easier to work with the program. One of them is the ability to pin a string - by learning this simple trick, you can view any area of ​​the table without losing sight of the line with column names or the so-called "header" of the table.

How to Freeze the Top Row of an Excel Table

So, you have created 2007 or 2010. Usually the top row contains the names of the columns, 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 "leave" and disappear from view. This problem can be solved by fixing the top row.

Select any cell in your table.

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

In the drop-down list, select "Lock the top row." A bounding line will appear below it. This means that the string 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 the names of the columns. In the event that you need to fix two or more rows, click on the cell that is under the "header". For example, if you want 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". Rows will be locked and will not "run away" up when viewing the table.

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

How to unfreeze rows in Excel

If you have frozen regions in your table, the Unfreeze Regions option will appear in the Freeze Regions menu. It is needed in order to unlock all pinned rows or columns of a 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 columns, but also rows. If the table contains a lot of information, scrolling to the right 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" - "Freeze Areas" tab. 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 the rows and columns of a table is a little different. Here, the tool for pinning the area is in the Window menu. To fix a row, you need to activate the cell below it and select "Window" - "Freeze panes". 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 unpin rows or cells, choose the Unpin Panes 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 fix rows, columns and areas. Regardless of the version office application, for correct operation hotkeys, 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:

• Fix the top line: Alt+o+b+x.
• First "A" column: Alt + o + b + d.
• Region: Alt+o+b+z.
• Unblock: Alt+o+b+z.