This tutorial will show you how to freeze a row or column in Excel. Docked areas will always be visible on the screen when scrolling vertically or horizontally.
Let's start with the easiest - we will learn how to fix one row or one column. It's very easy to do. Go to the tab View -> Freeze Areas... Next, select the required action from the drop-down list Pin top line or Freeze first column.
A little more complicated when we need to freeze more rows or columns than one. Let's say you need to anchor the top 2 lines. To do this, select the 3rd line by clicking on its number on the side.
A special division will appear on the border between the second and third lines.
This means that the lines are locked and will stay in place when the document is scrolled down.
You can also freeze a column in the same way. To do this, select the column that is next to the dockable one and click on To fix areas... In my example, this is the second column.
Please note that you cannot freeze both rows and columns at the same time.
To remove pinned areas just go to View -> Freeze Areas and from the drop-down list select the item Unpin regions.
Congratulations, now you know how to freeze a string in Excel.
This tutorial will show you how to combine text in cells in Excel using the Concatenate function. Both of these methods, which we will consider, allow you to concatenate several cells into a single array. In this case, the cells can be both text and numeric.
Working with a complex table in Excel, you may need to copy the finished table to another sheet or to another book. With a simple copy, formatting can be difficult, so in this lesson I will show you how to do it quickly and easily, and also consider how to copy a sheet in Excel to another workbook.
Today I will tell you how to solve the problem when columns are indicated by numbers in Excel. The situation may be familiar to many. You open an Excel file and the column labels have numbers instead of letters. The addresses in this case have a not quite familiar spelling R1C1, R1C3, etc.
Very often in Excel, you need to freeze (freeze) a specific cell in a formula. By default, cells are automatically stretched and resized. Take a look at this example.
We have data on the quantity of products sold and the price for 1 kg, it is necessary to automatically calculate the revenue.
To do this, we write in cell D2 the formula = B2 * C2
If we further stretch the formula down, then it will automatically change to the corresponding cells. For example, cell D3 will have the formula = B3 * C3, and so on. In this regard, we do not need to constantly prescribe the same formula, it is enough just to stretch it down. But there are situations when we need to fix (fix) a formula in one cell so that it does not move when stretching.
Take a look at this example. Let's say we need to calculate the revenue not only in rubles, but also in dollars. The dollar exchange rate is indicated in cell B7 and is 35 rubles per dollar. To calculate in dollars, we need to divide the revenue in rubles (column D) by the dollar rate.
If we write the formula as in the previous version. In cell E2 we write = D2 * B7 and stretch the formula down, then nothing will work out for us. By analogy with the previous example, in cell E3, the formula will change to = E3 * B8 - as you can see, the first part of the formula changed for us as needed to E3, but the cell for the dollar exchange rate also changed to B8, and nothing is indicated in this cell. Therefore, we need to fix the reference to the cell with the dollar rate in the formula. To do this, you need to specify the dollar signs and the formula in cell E3 will look like this = D2 /$ B $ 7 , now, if we stretch the formula, then the reference to cell B7 will not move, and everything that is not fixed will change as we need it.
Note: in this example, we specified two dollar signs $ B $ 7. So we told Excel to freeze both column B and row 7 , there are cases when we need to freeze only a column or only a row. In this case, the $ sign is only specified before column or row B $ 7 (line 7 is committed) or $ B7 (only column B is committed)
In order not to write the dollar sign manually, you can position the cursor on the formula in cell E2 (select the text B7) and then press the key F4 on your keyboard, Excel will automatically lock the formula by prefixing a dollar in front of the column and row if you press the key again F4, then only the column will be fixed, once again - only the row, once again - everything will return to its original form.
Using Microsoft Excel often means working with a large amount of data on a single sheet, such as checking and viewing scaled tables. When the lines go beyond the border of the screen, it becomes necessary to constantly scroll the page in search of the desired value - this is not only uncomfortable, but also irrational, since it takes the user time.
However, it is very easy to save yourself from such inconveniences - it is enough to know how to freeze an area in Excel (Excel). I will tell you where in Excel you can freeze areas, and, if necessary, Microsoft Excel. It will be about several versions of the program, namely 2003, 2007, 2010, 2013, 2016.
- Basic definitions
- Pinning and unpinning regions in program version 2003
- Freezing areas in Excel other versions
- Unpinning other versions of the program
- Addition
Basic concepts used in this article
ribbon- the area at the top of the Excel window containing tabs.
Tabs- tools that summarize groups of certain actions by category.
What is the pinning function for? 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 work. The cells in the docked area do not change their position no matter what you do in the program.
Freezing and unpinning regions 2003
Pinning an area in Excel 2003 is pretty straightforward. Freezing is performed on 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 Areas.
If you need to select three columns on the left, select cell D1 and perform a similar action Window - Freeze Areas.
In order to freeze the lines above, you need to select the first cell in the column. To fix the top four rows, 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
Unpinning in the 2003 version is as follows Window - Unpin the screen.
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 freeze the first row and the first column.
The docking control command is on the ribbon, tab View - Freeze areas.
So, to fix the first column, choose View - Freeze Areas - Freeze First Column.
In order to freeze the first row in Excel, you need to select View - Freeze Areas - Freeze the 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 "Hat" of the top 4 rows and the first 2 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 lines at the same time, you need to select a cell in the first column after these lines.
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, areas are docked in the same way on the "Window" tab.
Despite the ease, some users cannot solve this problem due to the absence of the "View" tab on the taskbar. Typically, similar problems are encountered when using the starter version –Excel Starter. Such a program does not support the ability to pin and unpin cells.
Many users are wondering how the pinned areas will look when printed? After all, Excel generates a huge number of reporting documents, which are sometimes more convenient to provide on paper.
For printing, pinned areas work in the same way as they do electronically. Everything remains in place. You can verify this using the preview.
Removing an anchored area
To remove the docked area in Excel, you need to select the "View" tab and you will see that the pop-up menu began to display the item "Unpinned Areas". After confirming the operation, the rows or columns that were previously committed will again move when scrolling.
Addition
As you can see, working with fixed areas in Excel does not require much effort or specific knowledge - everything is simple and intuitive, especially when it comes to modern variations of the program. Using this function will save you time and hassle when working with tabular data.
If you have any questions, ask them in the comments!
Consider how to freeze a row, column, table header, title, link, cell in a formula, picture in a cell in Excel and other.
How to freeze a row and a column inExcel.
In Excel, you can freeze the top row and 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 a picture in a cellExcel.
For example, we have 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 formula inExcel.
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 cell reference 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 a large table, you can make a table of contents to quickly move to the desired section of the table, to the desired sheet of the book. If you do not fix the links, then when inserting rows, columns, links will shift. To prevent this from happening, they need to be fixed in a certain place. See the article "Table of Contents in Excel" about this.
Freeze print area toExcel.
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 the next lines, cells, etc.
On the "Page Layout" tab in the "Page Settings" section, click on the "Print area" function button. In the window that appears, click on the word "Set".
When we define the first printable area, a new "Add Printable Area" function will appear in the Print Area dialog box.
To remove the print area, click on the "Remove" function.
You can also set the print area like this. On the “Page Layout” tab in the “Page Settings” section, click on the “Page Settings” button. In the picture, the button is circled in red.
In the dialog box that appears, click on the "Sheet" tab. 
In the line "Print range" write the range of cells that you want to print. If you need to print the table heading 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 heading.
Lock cell size toExcel.
In order not to resize rows, columns without your knowledge, you need to put protection. How to set a password, see the article “Password on Excel. Excel Protection ". Before setting the password, select the entire table. In the dialog box "Format cells" uncheck the words "Protected cell". Click "OK". Select the required columns, rows, cells, range, etc. In the "Format Cell" dialog box, put a check mark next to the "Protected cell" function.
Now we set the password. In the "Protect Sheet" dialog box, check the boxes for all functions, except for the functions for changing rows, columns (formatting cells, formatting columns, etc.). Everything. You can work in a table, but the size of columns and rows cannot be changed.
How to remove a docked area inExcel.
To do this, you need to take the opposite action.
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 undo the fixing of the top rows and first columns, on the "View" tab in the "Window" section, click on the "Freeze Areas" button. In the window that appears, select the "Unpinned Areas" function.
In the formula, unpin a cell - instead of an absolute one, make a relative reference to the cell address.
To be able to change the size of cells, rows, columns, you need to remove the password from the sheet.
Sometimes for work it is necessary that the date was written in text format. How to change the date format, see the article "
To fix a line or title in Excel, you need to go to the View tab.
Going to the "View" tab, you need to click on any cell in the Excel table, and then in the top panel click on "Freeze areas". A context menu will open in which you need to select "Freeze Top Row". After that, the topmost visible row will not disappear when scrolling down the table.
Working with Excel 2007 and 2010
This feature is available in every version of Excel, but due to the difference in the interface and the location of the menu items and individual buttons, it is not configured in the same way.
Anchor a line
If you need to fix a header in the file, i.e. top line, then in the "Window" menu, select "Freeze regions" and select the cell of the first column of the next line.
To fix several lines at the top of the table, the technology is the same - the cell on the far left is highlighted in the next cell after the lines to be fixed.
Column Freeze
Freezing a column in Excel 2003 is carried out in the same way, only the cell in the top row of the next column or several columns after the freezing column is selected.
Freeze an area
The Excel 2003 software package allows you to simultaneously fix the columns and rows of the table. To do this, select the cell next to the fixed ones. Those. to freeze 5 rows and 2 columns, select a cell in the sixth row and third column and click Freeze Areas.
Later versions of the Excel software package also allow you to fix the file header in place.
Anchor a line
When it is required to fix not one, but another number of lines, then it is necessary to select the first scrollable line, i.e. the one that will be immediately behind the assigned ones. After that, all in the same paragraph, select "Freeze areas".
Column Freeze
To freeze a column, in the Freeze Regions section, you must select the option to freeze the first column.
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 lines remain in place. To do this, select the first scrollable cell with the mouse.
After, fix the area.
Those. if, for example, the first line and the first column are fixed - this will be a cell in the second column and the second line, if 3 rows and 4 columns are fixed, then the cell in the fourth row and fifth column should be selected, etc., the principle of operation should be understandable.
How to freeze a column in Excel
To fix a column in Excel, you need to go to the View tab.
Going to the "View" tab, you need to click on any cell in the Excel table, and then in the top panel click on "Freeze areas". A context menu will open in which you need to select "Freeze first column". After that, the leftmost column will not disappear when the table is scrolled to the right.
Freezing columns in Excel works in much the same way as freezing rows. You can freeze only the first column, or freeze multiple columns. Let's move on to examples.
Fixing the first column is just as simple, press "View" -
