Exists various ways moving around the sheet. You can use the arrow keys, scroll bars, or the mouse to move from one cell to another, or to different areas of the worksheet.

Excel has improved user interface With high speed scrolling, convenient transition to the end of the range and tooltips that indicate the location on the sheet. Drop-down lists in dialog boxes can be scrolled with the mouse.

Move around the sheet using the arrow keys

To jump to a given cell on a worksheet, click it or use the arrow keys. In this case, the cell to which the transition is made becomes the active cell.

Note: When the SCROLL LOCK key is pressed, the Excel status bar displays the text scroll lock. When SCROLL LOCK is enabled, pressing the arrow keys scrolls a row up or down and a column left or right. To use the arrow keys to move between cells, SCROLL LOCK must be turned off. To do this, press the SCROLL LOCK (ScrLk) key on your keyboard. If your keyboard does not have this key, you can disable SCROLL LOCK using the on-screen keyboard. To open the on-screen keyboard, press the button Start at work Windows desktop and enter a query screen keyboard . To disable the SCROLL LOCK mode, press the key ScrLk and close the on-screen keyboard.

Move around the sheet with scrollbars

If there are no scrollbars, follow the steps below.

The following table describes the various ways in which scroll bars can be used to navigate a worksheet.

Notes:

Scrolling and zooming with the mouse

Some mouse models and other pointing devices, such as the Microsoft IntelliMouse, have built-in scroll and zoom functionality that you can use to pan and zoom while working on a worksheet or chart sheet. You can also scroll through drop-down lists in dialog boxes with the mouse. See the pointing device description for more information.

additional information

You can always ask a question to the Excel Tech Community , ask for help in the Answers community , and also suggest new feature or improvement on the website

When working with large amounts of tabular data in Excel program for reasons of convenience, it may be necessary to fix a certain section of the table - a header or data that should always be in front of your eyes, no matter how far the table scrolls.

Working with Excel 2003

This feature is available in every Excel variant, but due to the difference in the interface and the location of menu items and individual buttons, it is configured differently.

Line pinning

If you want to fix the header in the file, i.e. the top line, then in the "Window" menu, select "Freeze areas" 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 leftmost cell is selected next to the lines being fixed.

Freezing a column

Fixing a column in Excel 2003 is carried out in the same way, only the cell in the top row of the column or several columns following the fixed column is selected.

Pinning an area

The Excel 2003 software package allows you to fix both columns and rows of a table at the same time. To do this, select the cell following the pinned ones. Those. to fix 5 rows and 2 columns, select a 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 lock the file header in place.

Line pinning

For this:

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 after the fixed ones. After that, everything in the same paragraph is selected "Freeze areas".

Important! The function of fixing sections of a table 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 the first row has been added. In this case, it does not matter which cell the cursor is in, the desired row/column will still be fixed.

Freezing a column

To fix a column, in the "Freeze areas" section, you must select 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 paragraph, select the first scrollable column and click the "Freeze areas" button.

Pinning 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 will remain in place. To do this, the first scrollable cell is selected with the mouse.

After, fix the area.

Those. if, for example, the first row and the first column are fixed, it will be a cell in the second column and the second row, 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.

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

Cancel pinning

It happens that it is necessary to fix part of the table only for the time of filling, and it is not necessary for subsequent use. Just as easily as a row or column was fixed, the fixation is also undone.

In Excel 2007 and 2010, this function is located in the same View section and the Freeze Panes item. If there is some fixed area - a column, a row or an entire area, then in this paragraph the button "Unpin areas" appears, which removes all fixation of table elements on the sheet as a whole.

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

Conclusion

Fixing elements - useful feature, which greatly facilitates and simplifies the work with extensive tables. This function is set up in an elementary way - all possible options for fixing table sections in the program 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.

Today we will talk about how to freeze columns in Excel. While working with various tabular data, it is often necessary to see the headings of rows or columns in front of you, throughout the process, at every moment of time, regardless of the given position of the scroll pointer. Luckily, said app has a special feature that can help us.

Training

In order to solve the question of how to fix columns and rows in Excel, we launch a spreadsheet editor and open a file with the necessary data in it. Next, go to the desired sheet of the document.

Instruction

To begin with, we will tell you how to fix a column in the application version 2010 or 2007 is used. We fix the left column of the sheet. To do this, go to the tab called "View". We open which allows you to fix the area. It is placed in a special group of commands, united under the name "Window". We need the bottom line of the list. She is responsible for fixing the first column. Click on it with the mouse cursor or select using the key with the letter "y".

We leave to the right

Now let's look at how to freeze columns in Excel, if only the first of them is not enough. Select the appropriate column. It should be located to the right of the last column of the pinned group. For this purpose, left-click the title of the described column, that is, the cell above the top row. While hovering over the required area, it changes appearance and turns into a black arrow. Expand the "Lock areas" list, which is located in the "View" tab, you can find it in the main menu of the editor. This time we choose top line. It is also called "Lock Regions". This item may not be on the menu. This is possible if the sheet already contains other anchored areas. If so, the specified place in the list will be occupied by the function of disabling pinning areas. Let's choose it first. Then we open the list again and use the "Lock areas" function that has returned to its rightful place. If the version of the table editor "2003" is used, the menu is arranged differently here. Therefore, in this case, selecting the column that follows the pinned one, expand the "Window" section. Next, select the line under the name. When it is necessary to fix not only a certain number of columns, but also several rows, we follow the steps described below. We select the first cell from the unpinned area, that is, the top, left. At the next stage of the application version 2007 or 2010, we repeat the steps described above. As for the 2003 edition, here you need to select the "Freeze areas" item in the "Window" section.

Additional features

Now you know how to freeze columns in Excel, but you also need to add new rows or columns to your document. So, we launch a document in which such modifications are planned, and create the necessary table. Save the material by giving it an arbitrary name. In the cell, which is located on the right in the table, enter a value or text. Add a column with the mouse. Move the label to the right, which changes the size. It is located at the bottom of the table. So the columns will be added. It is necessary to determine a place for placing new elements in advance. We highlight the right place. The number of selected cells must be equal to the number of empty columns that are planned to be added. If you want to paste nonadjacent elements, hold down the CTRL key while making a selection. Go to the "Home" tab, use the "Cells" group. Click on the arrow next to the "Insert" function. So we figured out how to freeze columns in Excel.

In tables with large quantity columns is quite inconvenient to navigate through the document. After all, if the table width goes beyond the boundaries of the screen plane, then in order to see the names of the rows in which the data is entered, you will have to constantly scroll the page to the left, and then return to the right again. Thus, these operations will take an additional amount of time. In order for the user to save his time and effort, the program Microsoft Excel it is possible to freeze columns. After completing this procedure, the left side of the table, in which the names of the rows are located, will always be visible to the user. Let's understand how to freeze columns in Excel application.

Pinning the leftmost column on a sheet, or table, is quite simple. To do this, you need to be in the "View" tab, click on the "Freeze the first column" button.

After these steps, the leftmost column will always be in your field of view, no matter how far you scroll the document to the right.

Freezing multiple columns

But what if you need to freeze more than one column into several? This question is relevant if, in addition to the name of the row, you want the values ​​​​of one or more of the following columns to be in your field of vision. In addition, the method that we will discuss below can be used if, for some reason, there are more columns between the left border of the table and the left border of the sheet.

Highlight the topmost cell on the sheet to the right of the column area you want to freeze with the cursor. All in the same "View" tab, click on the "Freeze Areas" button. In the list that opens, select the item with exactly the same name.

After that, all columns of the table to the left of the cell you selected will be fixed.

Detach columns

In order to unpin columns that have already been pinned, again click on the “Freeze Panes” button on the ribbon. This time, the list that opens should contain the button "Unpin areas".

After that, all the pinned areas that were on the current sheet will be undocked.

As you can see, the columns Microsoft document Excel can be pinned in two ways. The first one is suitable only for fixing one column. Using the second method, you can fix both one column and several. But, there are no fundamental differences between these options anymore.

Excel involves working with large volumes numerical information. And when viewing large tables, the question arises, how to fix a row in Excel when scrolling? After all, such data always implies the presence of headers and totals. This is the information that is convenient to see in front of you on the screen all the time, even when scrolling. That is what we will talk about in this article.

How to fix a line

If you need to fix the header in Excel, you can use special team on the toolbar. To execute it, set the pointer to 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 set the necessary options.


Select the second item - "Freeze the top line." Try to scroll 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 an area in Excel

Obviously, to fix when scrolling the range from the middle Excel tables it is forbidden. So when it comes to area, it includes all rows from the first to the top docking and the columns from the first to the left docking.
The screenshot shows an example area. Fragments fixed during scrolling are highlighted in orange.


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

Freeze Multiple Excel Rows

Place the pointer on the FIRST cell of the row following the one being pinned.


Then choose the Freeze Pane command from the View panel.


Notice the thin line that marks the border of the pinned area.

Freeze Multiple Excel Columns

Place the pointer on the FIRST cell of the column following the anchored in the FIRST ROW.
If you, as in our example, have a union for all columns, then it will also fall into the region fixed when scrolling. To avoid this, remove the join and move it to loose columns. .


Run the same command again.


The columns will be fixed on scroll.

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

Freeze regions in Excel

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


After that, execute the "Freeze area" command again. Everything will be ready.


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

How to freeze a cell in a formula in Excel

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


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


Such a reference to a cell (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 trying to "stretch" the formula to the end, we get the following result.


This is because the second address is also shifted.


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


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


Then press Enter and copy.


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

1. The first is full address fixation: $C$38
2. The second is fixing 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.
3. The third push fixes the column name: $C38. When copying, the column will remain unchanged, and only the number will change: C39, C40, etc.
4. The fourth press is the removal of fixation.

This way you can manage your formulas. In conclusion, we recall that you can freeze while scrolling a column, row, or area from the first column and first row to the current ones. You can't freeze a cell while scrolling. But you can fix the cell address to avoid shifting when copying. We wish you good luck with your work in Excel!

Have a great day!