How to freeze the top line in openoffice. Working with a column

Single cell

Left-click on a cell. The result will be as shown in fig. 5 on the left. You can verify the correctness of the selection in the Sheet area field.

Range of adjacent cells

A range of adjacent cells can be selected using the keyboard or mouse. To select a range of cells by moving the mouse cursor:

Selecting non-contiguous cells

A range of non-contiguous cells can be selected using the Ctrl + mouse key:

Working with columns and rows

Insert columns and rows

Columns and rows can be inserted in several different ways in an unlimited number.

Single column or row

1. Execute either Insert> Columns or Insert> Rows.

When inserting one new column, it is inserted to the left of the selected column. When inserting one new line, it is inserted above the selected line.

A single column or row can also be inserted using the mouse:

1. Highlight the column or row where you want to insert a new column or new row.
2. Click on the title with the right mouse button.
3. Execute either Insert> Columns or Insert> Rows.

Multiple columns or rows

Multiple columns or rows can be inserted at once, rather than inserting them one at a time.

1. Select the required number of columns or rows and, while holding down the left mouse button on the first of them, move the cursor to the required number of headings.
2. Proceed as you would when inserting one column or one row as above.

Removing Columns and Rows

Columns and rows can be deleted individually or in groups.

Single column or row

One column or row can only be deleted with the mouse:

1. Select the column or row to remove.
2. Right-click on a column or row header.
3. Execute from the context menu Delete columns or Delete lines.

Multiple columns or rows

Multiple columns or rows can be deleted at once, rather than deleting them one at a time.

1. Select the required number of columns or rows by holding down the left mouse button on the first of them, and drag the cursor to the required number of headings.
2. Proceed as you removed one column or row as above.

Working with sheets

Like any other Calc element, sheets can be inserted, deleted, and renamed.

Insert new sheets

There are many ways to insert a new sheet. The first step in all methods is to select sheets, after which a new sheet will be inserted. Then you can use the following steps.

• Open the Insert menu and select Sheet, or
• Right-click the tab and select Add Sheets, or
• Click on an empty space at the end of the row of sheet tabs (Figure 9).

Each method opens the Insert Sheet dialog box (Figure 10). Here you can determine whether the new sheet will be placed before or after the selected sheet, as well as how many sheets to insert.

Deleting Sheets

Sheets can be removed individually or in groups.

Separate sheet

Right-click the tab of the sheet to be deleted and select Delete from the context menu.

Multiple sheets

To delete several sheets, select them as described above, right-click on any tab and select Delete from the context menu.

Renaming sheets

The default name for a new sheet is Sheet X, where X is a number. This works well when there are only a few sheets for a small spreadsheet, but becomes awkward with a large number of sheets. To give the sheet a more meaningful name, you can:

• When creating a sheet, enter your name in the Title field, or
• Right-click on the sheet tab and select Rename from the context menu and replace the existing name with a new one.

Sheet names must begin with either a letter or a number; other characters, including spaces, are not allowed, although spaces can be used between words. Attempting to rename a sheet with an incorrect name results in an error message.

Calc appearance

Freezing Rows and Columns

A freeze locks several top rows, or several columns from the left side of the sheet, or both. When scrolling through the sheet, any anchored rows and columns remain in the author's field of view.

In fig. 11 shows frozen rows and columns. The thickened horizontal line between rows 3 and 16, and the thickened vertical line between columns C and H, separate the anchored areas. Rows 4 through 16 and columns D through G have been scrolled up. The frozen three rows and three columns remained in place.

The anchor point can be set after one row, one column, or both, as shown in Fig. eleven.

Freezing individual rows or columns

• Click the heading below the freeze row or to the left of the freeze column.
• Run the command Window> Freeze.

A dark line appears, indicating the border of the snap.

Freeze row and column

• Select the cell immediately after the freeze row and immediately to the right of the freeze column.
• Run the command Window> Freeze.

Two lines will appear on the screen, a horizontal line above this cell and a vertical line to the left of this cell. Now, when scrolling, all lines above and to the left of these lines will remain in place.

Removing a commit

To remove a row or column freeze, run the command Window> Freeze... The checkbox next to Commit should disappear.

Split window

Another way to change the look is to split the window - also known as split screen. The screen can be split either horizontally or vertically, or both. This allows you to view up to four fragments of the sheet at any time.

What is it for? Imagine that you have a large sheet and one of its cells contains a number used in three formulas in other cells. Using a split screen, you can position a cell containing a number in one section, and each of the cells with formulas in other sections. Then you can change the number in the cell and watch how it affects the contents of the cells with the formulas.

Splitting the screen horizontally

To split the screen horizontally:

1. Place your mouse cursor in the vertical scroll bar on the right side of the screen and position the cursor over the small arrow button at the top.

1. A thick black line is visible directly above this button (Figure 13). Move the mouse cursor to this line, as a result the cursor will change its shape to a line with two arrows (Fig. 14).

1. Hold down the left mouse button, a gray line will appear across the page. Drag the cursor down, the line will follow the cursor.
2. Release the mouse button and the screen will split into two images, each with its own vertical scroll bar.

In fig. 11, the "Beta" and "A0" values ​​are at the top of the window, and the other calculation results are at the bottom. The top and bottom can be scrolled independently of each other. Therefore, you can change the Beta and A0 values, observing their influence on the calculation results in the lower half of the window.

You can also split the window vertically, which is discussed below - the results will be the same, allowing you to scroll both parts of the window independently. With vertical and horizontal division, we get four independent scrolling windows.

Splitting the screen vertically

To split the screen vertically:

1. Place your mouse cursor on the horizontal scroll bar at the bottom of the screen and position the cursor over the small arrow button on the right.

1. A thick black line is visible to the immediate right of this button (Figure 15). Move the mouse cursor over this line, as a result the cursor will change its shape to a line with two arrows.
2. Hold down the left mouse button, a gray line will appear across the page. Drag the cursor to the left, the line will follow the cursor.
3. Release the mouse button and the screen is split into two images, each with its own horizontal scroll bar.

You can also split the screen using the same procedures as for freezing rows and columns. Follow these directions, but instead of doing Window> Freeze, use Window> Split.

Entering data into a sheet

Entering numbers

Select a cell and enter a number in it using the top row of the keyboard or the numeric keypad.

To enter a negative number, enter a minus sign (-) in front of the number or enclose it in parentheses ().

By default, numbers are right justified, and negative numbers have a minus sign in front of them.

Entering text

Select a cell and enter text in it. The text is aligned to the left by default.

Entering numbers in text format

If a number is entered in the format 01481, Calc will delete the leading 0. To preserve this leading zero, when entering codes, for example, enter an apostrophe in front of the number, like: "01481. However, the data is now treated as text by Calc. Arithmetic operations will not work. The number will either be ignored or an error message will appear.

Numbers can have leading zeros and are treated as text if the cell is formatted appropriately. Right click on the cell and select Cell Format> Number... Setting Leading Zeros allows numbers with leading zeros.

Even if you declare a variable as text, it can still participate in arithmetic operations; however, the result of such operations may differ from what is expected. In some cases, Calc will perform arithmetic operations on a cell with text, whether it has characters (such as ABCD) or numbers that you formatted as text. For more information, see the Calc Guide.

Entering the date and time

Select a cell and enter the date and time in it. You can separate date items with a (/) or (-) character, or use text such as Oct 10 03. Calc recognizes a variety of date formats. Time items can be separated with a colon, for example 10:43:45.

Autocomplete

To facilitate and speed up data entry, the AutoFill mode allows - a filling marker in the form of a black square in the lower right corner of the current cell (it also works, except for the introduction of formulas, with numbers, dates, days of the week, months and mixed data).

Automated data entry:

1. 1.in the first cell of the range, enter the value of one of the list items;
2. 2. Move the mouse cursor over the fill marker so that it acquires the shape of a cross;
3. 3. Drag the filling marker, highlighting the range (if the selected range is greater than the number of items in the list, then it will be filled cyclically).

If you enter in two adjacent cells successively two numbers that make up the beginning of an arithmetic progression, for example 1 and 3, then select them and, as when copying, drag them over several cells using the fill marker, then the row will continue: 1, 3, 5, 7 etc. If you need to fill in the cells with a step of one, then it is enough to enter the first number, for example 1, and drag to the desired number using the filling marker, we get a row 1, 2, 3, etc.

Org Calc also allows you to enter non-numeric sequences. For example, if you enter January in a cell and perform the operation described above, then February, March, etc. will appear in the next cells.

Leave your comment!

The more convenient it is for us to navigate through the sheet in the spreadsheet, the less time we spend editing it, which means we have more time to analyze its contents. Of course, convenience is a very relative thing. And each person creates his own workspace based on his habits and ideas. I will talk about the basic principles to move through the sheet as quickly as possible, and you yourself decide what to adopt and what not.

How to freeze a row and a column

The first thing that comes to mind when working with large tables in LibreOffice Calc or MS Excel is pinning the table headers. Naturally, the table can go to the right and down, so it is desirable to be able to fix both rows and columns.
Select cell A2 and go to the main menu "Window" and check the box in the item "Fix". The result will be the committed first line.

The basic principle of fixing rows, both in LibreOffice Calc and in MS Excel, is always to fix the entire area (rows and columns) above and to the left of the selected cell. That is, if we need to fix only the first column, then we must put the cursor in cell B1, and if we need to fix the first row and column, then in cell B2. I'll tell you a secret, the program doesn't care how many rows and columns will be fixed. But sometimes there is a situation when several rows (columns) are allocated for the table header, and we only need one for work, what should we do in this case? Just hide all unnecessary parts of the title.
Unpinning a locked range is also easy - uncheck the Lock box in the Windows menu. Where our cursor will be at this time does not matter, the entire fixed range of both rows and columns will be released.
Sometimes we need to change the docked area, this may be due to the fact that we did not select it correctly or we now need another docked area. Obviously, this can be done in two steps: first, unpin the existing range, and second, lock the desired range. Therefore, another question may arise: how to do it faster? Correct answer: assign hotkeys. Yes, by default the keyboard shortcut for this action is not assigned, but you can do it yourself. If you are just getting started with LibreOffice, it will probably be hard for you to intuitively do this. So I'll walk you through the steps.
Go to the main menu "Service" → "Options ..." and in the window that opens, go to the "Keyboard" tab.

Highlight any item in the "Keyboard shortcuts" list and press the desired combination on the keyboard (for example, Ctrl + Shift + X). If the combination is free, then there will be an empty space opposite it. You can also just scroll through the list and see what is assigned to, and choose what you like best. After you have decided on the key combination (be sure to select it), select the View item in the "Categories" list of the "Commands" section, in the "Command" list at the very bottom there will be "Fix the window" (he himself is shocked by the name), and click the "Assign" button. The selected combination will appear in the Key field. If you did everything right, click "Ok", if not - "Cancel". A detailed explanation of the operation of this window will require a separate article, since the logic of its operation is, to put it mildly, strange, so that's all for now. To be on the safe side, I would like to clarify that this "Fix Window" command serves both to fix the range and to detach it.
We now have hotkeys for pinning and releasing the area. If you have forgotten what hotkeys you assigned to a menu item, then know that they are displayed next to this item, and you can always remind yourself.

If you decide to change the hotkeys, they will also change next to the menu item.

Split window

The situation when we have not just a large table, but it is also all connected by formulas, leads to the fact that the fixing of rows or columns may become ineffective. In this case, we often want to split the window into parts in order to see different fragments of the same sheet. Splitting a window can also be done in several ways, and there really is a choice. Which of these methods is simpler and more convenient is up to you.

The first, you guessed it, is the menu-driven method. Select the cell in the first column (for example, A9) and, having opened the "Windows" item in the main menu, put a tick in front of "Split". The window will be split in two. Both will have the same sheet, but each of them will have their own scrollbars, which means we are getting what we want.
The principles of window splitting in LibreOffice Calc are the same as for freezing a region of cells - the splitting into parts will occur at the top and left of the cell. As you understand, we can split the sheet into 2 parts vertically if we place the cursor somewhere in the first line, or horizontally if we place the cursor somewhere in the first column, or into 4 parts if we place the cursor somewhere in the middle of the sheet ... The latter option is used very rarely, but you never know what you need ...
The second way is to assign a keyboard shortcut. This is done in the same way as assigning the keys to dock the area, only in the Commands section we need to select "Split Window". I can assure you that not a single glass will be damaged. :)
There is a third way. And people who prefer to use a mouse, rather than hotkeys, will probably like it more than others (by the way, MS Excel 2013 cannot do that, so you can throw a stone at it on occasion). Provided that our sheet does not use row and / or column pinning and window splitting, there are small rectangles at the top of the right scroll bar and on the right of the bottom scroll bar.

Grab one of them and pull, right to left, and top down. You will see the result for yourself. And I'd rather tell you about the condition that I gave in the previous paragraph. It is clear that if we divide the sheet vertically, then we will not have the right rectangle, and vice versa. The trick is different, if we have fixed, for example, the first line, then there will be no upper rectangle, but the right one will remain, and if we pull on it, we will not get the page division, but the fixed first line plus the vertical range of the size where we let go line. Sounds scary, just try it and you will understand. It will be the same for columns. On the one hand, the fact that we cannot simultaneously fix the area and divide the page is sometimes saddening. On the other hand, this trick may allow not to return to the top of the page once again, but simply, if necessary, fix the perpendicular range in addition to the already existing one. Unfortunately, we will have to change the size of the docked area after releasing the mouse button either through the menu or by hot keys. But still, as an option ...

Moving with hotkeys

This is a fairly broad topic, I will only touch on the most essential points that are most often needed when working with spreadsheets in LibreOffice. In addition, I will indicate the hotkeys assigned by default, but this does not mean that you cannot change them. But it is rather difficult to give recommendations on assigning and changing hotkeys. First, it has to do with whether a person owns a blind seal. Usually, if a person owns it, then he uses two hands to press the service keys and keys of the alphanumeric row (for example, with his right hand Shift and left hand A, for the capital letter A), in this case, it will probably be more convenient to assign frequently used keys closer to the initial position of the hand (closer to the center of the keyboard). If a person does not know the touch typing method, then commonly used keys are assigned to the left hand, so that it is convenient to press several service keys at once and some additional character with one hand. Secondly, it depends on the person's habits and work style. Many people are accustomed to using both a mouse and a keyboard at the same time. In this case, it is not always convenient to take your hand off the mouse once again, and the keys are "sharpened" for the left hand (for right-handed people).
Usually the combination is used to move Ctrl + some key... If you add Shift to this combination, then the key combination will select the corresponding range. In my opinion, this scheme is convenient, and, probably, it should be adhered to in case of changes. Also, in my opinion, it makes sense to open the "Tools" → "Settings ..." window and carefully study the "Keyboard" tab, especially the "Commands" section. In LibreOffice, you can assign keys to almost anything, including applying styles and macros. You can find a lot of useful things there. Although, as I said, the translation is not always intuitive and sometimes even confusing, you may have to experiment to get to the true state of affairs. I will indicate the keyboard shortcut, the name of the command and its meaning, for those commands that I will describe.
It's no secret (well, I hope) that when we turn the mouse wheel, we can move the sheet up / down. If you press Ctrl, then we adjust the scale of the displayed document. And if instead of Crtl to push Shift, the worksheet will move left / right. The same rule applies to the touchpad as well. It's good to remember this when you are working without a mouse. If there is such an opportunity, why not take advantage of it?
The up / down / left / right arrows on your keyboard will move the cursor away from the cell accordingly.
End- Go to the end of the document. - Selects a cell in the current sheet at the intersection of this row and the last column in which there are filled cells (he is in shock).
Home- Go to the beginning of the document. - Selects a cell in the current sheet at the intersection of this row and the first column in the sheet.
Ctrl + PageUp/PageDown- Go to the previous / next sheet. - Surprisingly, this keyboard shortcut does exactly what it says, that is, it opens the previous / next sheet. But there is also a small catch. In the LibreOffice interface, the PageUp key is written as Next and the PageDown key as Prior, at least in version 4.3. If you want to change your keyboard shortcuts, pay attention to this.
PageUp/PageDown- Go to page up / down. - A page in this context is the number of lines that can fit into the program window, the line following the last visible one becomes the first visible on the next page.
Alt + PageUp/PageDown- Go to page left / right. - Works like the previous combination, but moves the screen left / right.
Ctrl + up arrow/down/to the left/to the right- Move to the top / bottom / left / right edge of the block. The arrows are pressed alternately :) A block in this context is a continuous range of filled cells. Example: let's say we have several tables on a sheet, arranged vertically and separated by a pair of blank lines, but inside the tables, all the cells are filled, and we have a selected cell at the beginning or middle of the first table. Then when you press the keyboard shortcut Ctrl + down arrow we first go to the last row of the first table, the second click to the first row of the second table, the next click to the end of the second table, and the next click will take us to the 1048576th (last) row of our sheet. Likewise, it will work in all directions.
Ctrl + Home- Go to the beginning of the file. - Selects the first cell A1 on the active sheet and transfers focus to it. Please note that it is the active sheet, on the first sheet, that is, actually at the beginning of the file, it does not transfer focus. Why "file", and, for example, not a "metal saw"? (shrug). Probably, the word “file” seemed more familiar to our localizers.
Ctrl + End- Go to the end of the file. - Selects a cell at the intersection of the last row and column of the active sheet that has a filled cell, a cell with a background or frame, and brings focus to it. Pay attention, it is the active sheet, it does not transfer focus to the last sheet. Why a file, and not, for example, a "data block"? (see above).
Ctrl + BackSpace- Go to the current cell. - This combination is needed when the selected cell is out of sight, for example, we are looking at a table somewhere far below, to the right, and we need to go back quickly. This keyboard shortcut tries to place the selected cell in the center of the window.
Ctrl + Shift + J- Full screen. - The mode can be very useful when we have a large range for a table, or when splitting a window. The only thing is that panels and other very convenient elements, such as "Navigator" and "Stylist", are not available in it, so we need to use hotkeys. But now, I think it will be easier for you. A little hint: in this mode there is only one floating panel, there is only one button on the panel, but who prevents us from configuring it in "Tools" → "Settings ..." on the "Panels" tab? You can leave it “floating” or attach it to some side, where it will not interfere with you. Little tip # 2: nobody forbids you to make this window small. I mean, it's not really full screen mode. This is a mode in which there are no toolbars, status bar and main menu. Otherwise, it is a window like a window, and in Writer too.
Another point that many people know, but for some reason forget, is the availability of all menu items via the keyboard. Take a look at the menu. There is an underlined letter in the name of each item, this letter will activate this menu item when you press the combination Alt + this letter... And although in full screen mode this trick will not work, since there is no menu, but in standard mode it sometimes helps out.

Navigator

This thing is not necessary for everyone, it really becomes convenient when working with LibreOffice Calc documents containing a large number of sheets, charts, pictures, ranges. You can call it by clicking F5.

If you press Ctrl and double-click on the area next to the icons in this window, then it will be attached to the left side of the main Calc window, and will become the "Navigator" panel. F5 will hide and show this panel. For me, the view of the panel for this window is more convenient. But these are already tastes ... Let's take a quick look at the elements of this window.
The Column and Row fields allow you to specify the address of the cell to which you want to go. After setting the value, press Enter... The cell, the address of which you specified in the fields, will be highlighted and placed, if possible, in the center of the program window.
The button to the right of the column field is named Range. Clicking on this button allows you to select a continuously filled area with cells (block) around the cell in which the cursor is located.
The Start and End arrows move the cursor to the first or last cell in the range. In this case, the last selected area is considered the range, either by means of a normal selection, or using the "Range" button described above. It doesn't matter which cell the cursor is on when the arrow is pressed. Interestingly, even if you go to another sheet, the arrows will direct you to the cells with the same address, but on this sheet. Please note that if no range has been selected since the file was opened, then clicking on both arrows leads to the selection of the first cell of the sheet. Once I had to parse the code of another programmer. Out of a couple of thousand lines, I found only one comment, literally it sounded like this: "This is a brilliant function." I have no other comments on these buttons. If you know the sacred meaning of the actions they perform, tell me. This is one of those things in LibreOffice that I haven't really figured out yet.
The "Content" button allows you to hide the list of objects, leaving only the basic navigator tools. Pointless when it is a docked pane, but frees up space when the navigator is floating.
The "Toggle" button allows you to show either all groups of elements in the list, or only the one in which the cursor is located. This is a very useful function if there are a lot of objects in a group (for example, "Notes"), and we only need to work with them.
The "Scripts" button turns on the display of the list of scripts. The topic of scripts is beyond the scope of this article, if you want to know more about them, check out the LibreOffice Help (although it's better to google for "Excel scripts"), or wait for an article on them.
The "Drag Mode" button has a drop-down submenu and is responsible for how the object will be inserted when dragging it from the list in the "Navigator". I want to note that some drag-and-drop functions do not work for different objects, but this is a separate topic.
At the bottom of the window there is a drop-down list where you can select an open document. What for? For example, you need to drag an object from one document to another. You can also quickly navigate between objects, including sheets, of different documents. The item "Active document" is intended for those who like to work with a large number of documents and occasionally get confused in them :). Therefore, it is correct to call the list of objects in the field of the navigator window “For the selected” document, and not for the current one.
The field with the lists of objects is what we started this whole conversation about the Navigator for. The object lists are divided into categories. All the categories are named quite clearly, in my opinion, but just in case, let me briefly comment on them.
Category "Sheets" - contains a list of all sheets of the selected document.
Category "Range names" - contains the names of all ranges defined using the "Service" → "Set range ..." function. More on setting ranges sometime next time.
Database Ranges Category - In LibreOffice Calc you can define a range of cells to use as a database. This range resembles a database table, in which each row corresponds to one record in the database, and each cell in a row corresponds to a database field. As with a regular database, such ranges can be sorted, grouped, searched, and calculated.
"Linked areas" - contains the names of all ranges from external data sources created using the function "Insert" → "Link to external data ..."
"Images" - all images inserted into the document are located here.
OLE Objects - These are objects inserted using Insert → Object. That is, this is where you should look for diagrams, odg drawings, pieces of text documents, and more.
Notes - This is where all your notes are. One caveat: if in other places there are names of objects that can be edited, then the name of the note is its content. Naturally, if you have the same note on different sheets, especially an unintelligible one (for example, "a"), then it will be difficult to find the one you need. Try to make your notes readable if you want to easily navigate them in the navigator.
"Graphic objects". I can only speculate on this point in theory. According to all the descriptions, and there are not so many of them, this category should contain objects from the drawing panel. But for some unknown reason, objects from this panel are not present in the navigator at all. Here, something like this.
The general guidelines for all objects are very simple: remember to give them human-readable names. While you have 1-2 objects, it is easy to find the one you need, but it becomes difficult to navigate in large and complex documents without working out the names. To give a name to an object is a couple of seconds, right-click → "Name ...", but in the navigator you will be able to find it unmistakably the first time.

Finding dependent and influencing cells

There is an opinion that it is necessary to talk about dependent and influencing cells when describing working with formulas. Yes, this really only works when there are formulas in the LibreOffice Calc document and we want to trace where we use the values ​​from and where we then send them. But when we work with large tables, this function allows us to quickly and accurately navigate the document, and possibly trace the error. And that's why I decided to mention this feature here. Here's a simple example:

Visual information is easier to perceive than textual information, isn't it? All functionality for working with influencing and dependent cells is located in the menu "Tools" → "Dependencies".

It is possible to use hotkeys. However, those hotkeys that are on by default are not very convenient. For myself, I reassigned them, and also assigned hotkeys to the items "Remove arrows to influencing cells", "Remove arrows to dependent cells" and "Remove all arrows". But these items are accessible from the keyboard and without it, you just need to press a little more buttons. For example, to show "Influencing Cells", press the keyboard shortcut Alt + t, Alt + p and Alt + d... Notice, as I said, the letters you need to combine with the key Alt are underlined in menu items.

conclusions

As you can see, this does not work as a panacea. These are just suggestions for independent conclusions. And a lot will depend not only on whether you want to speed up your work, but also on the habits already in place and the specific tasks to be solved. In my experience, just by using the skills of navigating through large documents, you can speed up your work by 5-6 times. To make it clear, what we do in a week can be done in a day. And this, by the way, is not the limit, this is just the beginning. Try to figure it out while I write you a new article, I think there will be no less information there. Enjoy your work :)

At the request of readers, I publish material on how to fix the headings of the table so that they do not scroll when viewing, but only the content of the table changes.

We have a table:

Objective: to fix the title (highlighted in blue in the screenshot) so that when the table is scrolled down, it stays in place and the content scrolls.

To complete this task, we stand in cell A2 and select Window -> Freeze

The title now remains in place when scrolling through the table.

In a similar way, you can freeze a column, or even both a column and a row. To do this, it is important to stand in the cell next to the row and column that you want to fix and mark Fix. For example, to simultaneously fix the first row and the first column, you need to become in cell B2:

To cancel the commit, you must uncheck the menu item Window -> Freeze.

Sometimes a problem arises: to fix, for example, the second column, but so that the first one is not visible. Scroll with the horizontal scroll bar until the first column B is on the screen:

We go to cell C1 and achieve the desired result. The main thing then is not to forget that there is also column A, removed in this way from the screen.

The more convenient it is for us to navigate through the sheet in the spreadsheet, the less time we spend editing it, which means we have more time to analyze its contents. Of course, convenience is a very relative thing. And each person creates his own workspace based on his habits and ideas. I will talk about the basic principles to move through the sheet as quickly as possible, and you yourself decide what to adopt and what not.

Before starting, I recommend that you read the article on how to hide ranges. Limiting the area also helps you move faster.

How to freeze a row and a column

The first thing that comes to mind when working with large tables in LibreOffice Calc or MS Excel is pinning the table headers. Naturally, the table can go to the right and down, so it is desirable to be able to fix both rows and columns.
Select cell A2 and go to the main menu "Window" and check the box in the item "Fix". The result will be the committed first line.

The basic principle of fixing rows, both in LibreOffice Calc and in MS Excel, is always to fix the entire area (rows and columns) above and to the left of the selected cell. That is, if we need to fix only the first column, then we must put the cursor in cell B1, and if we need to fix the first row and column, then in cell B2. I'll tell you a secret, the program doesn't care how many rows and columns will be fixed. But sometimes there is a situation when several rows (columns) are allocated for the table header, and we only need one for work, what should we do in this case? Just hide all unnecessary parts of the title.
Unpinning a locked range is also easy - uncheck the Lock box in the Windows menu. Where our cursor will be at this time does not matter, the entire fixed range of both rows and columns will be released.
Sometimes we need to change the docked area, this may be due to the fact that we did not select it correctly or we now need another docked area. Obviously, this can be done in two steps: first, unpin the existing range, and second, lock the desired range. Therefore, another question may arise: how to do it faster? Correct answer: assign hotkeys. Yes, by default the keyboard shortcut for this action is not assigned, but you can do it yourself. If you are just getting started with LibreOffice, it will probably be hard for you to intuitively do this. So I'll walk you through the steps.
Go to the main menu "Service" → "Options ..." and in the window that opens, go to the "Keyboard" tab.

Highlight any item in the "Keyboard shortcuts" list and press the desired combination on the keyboard (for example, Ctrl + Shift + X). If the combination is free, then there will be an empty space opposite it. You can also just scroll through the list and see what is assigned to, and choose what you like best. After you have decided on the key combination (be sure to select it), select the View item in the "Categories" list of the "Commands" section, in the "Command" list at the very bottom there will be "Fix the window" (he himself is shocked by the name), and click the "Assign" button. The selected combination will appear in the Key field. If you did everything right, click "Ok", if not - "Cancel". A detailed explanation of the operation of this window will require a separate article, since the logic of its operation is, to put it mildly, strange, so that's all for now. To be on the safe side, I would like to clarify that this "Fix Window" command serves both to fix the range and to detach it.
We now have hotkeys for pinning and releasing the area. If you have forgotten what hotkeys you assigned to a menu item, then know that they are displayed next to this item, and you can always remind yourself.

If you decide to change the hotkeys, they will also change next to the menu item.

Split window

The situation when we have not just a large table, but it is also all connected by formulas, leads to the fact that the fixing of rows or columns may become ineffective. In this case, we often want to split the window into parts in order to see different fragments of the same sheet. Splitting a window can also be done in several ways, and there really is a choice. Which of these methods is simpler and more convenient is up to you.

The first, you guessed it, is the menu-driven method. Select the cell in the first column (for example, A9) and, having opened the "Windows" item in the main menu, put a tick in front of "Split". The window will be split in two. Both will have the same sheet, but each of them will have their own scrollbars, which means we are getting what we want.
The principles of window splitting in LibreOffice Calc are the same as for freezing a region of cells - the splitting into parts will occur at the top and left of the cell. As you understand, we can split the sheet into 2 parts vertically if we place the cursor somewhere in the first line, or horizontally if we place the cursor somewhere in the first column, or into 4 parts if we place the cursor somewhere in the middle of the sheet ... The latter option is used very rarely, but you never know what you need ...
The second way is to assign a keyboard shortcut. This is done in the same way as assigning the keys to dock the area, only in the Commands section we need to select "Split Window". I can assure you that not a single glass will be damaged. :)
There is a third way. And people who prefer to use a mouse, rather than hotkeys, will probably like it more than others (by the way, MS Excel 2013 cannot do that, so you can throw a stone at it on occasion). Provided that our sheet does not use row and / or column pinning and window splitting, there are small rectangles at the top of the right scroll bar and on the right of the bottom scroll bar.

Grab one of them and pull, right to left, and top down. You will see the result for yourself. And I'd rather tell you about the condition that I gave in the previous paragraph. It is clear that if we divide the sheet vertically, then we will not have the right rectangle, and vice versa. The trick is different, if we have fixed, for example, the first line, then there will be no upper rectangle, but the right one will remain, and if we pull on it, we will not get the page division, but the fixed first line plus the vertical range of the size where we let go line. Sounds scary, just try it and you will understand. It will be the same for columns. On the one hand, the fact that we cannot simultaneously fix the area and divide the page is sometimes saddening. On the other hand, this trick may allow not to return to the top of the page once again, but simply, if necessary, fix the perpendicular range in addition to the already existing one. Unfortunately, we will have to change the size of the docked area after releasing the mouse button either through the menu or by hot keys. But still, as an option ...

Moving with hotkeys

This is a fairly broad topic, I will only touch on the most essential points that are most often needed when working with spreadsheets in LibreOffice. In addition, I will indicate the hotkeys assigned by default, but this does not mean that you cannot change them. But it is rather difficult to give recommendations on assigning and changing hotkeys. First, it has to do with whether a person owns a blind seal. Usually, if a person owns it, then he uses two hands to press the service keys and keys of the alphanumeric row (for example, with his right hand Shift and left hand A, for the capital letter A), in this case, it will probably be more convenient to assign frequently used keys closer to the initial position of the hand (closer to the center of the keyboard). If a person does not know the touch typing method, then commonly used keys are assigned to the left hand, so that it is convenient to press several service keys at once and some additional character with one hand. Secondly, it depends on the person's habits and work style. Many people are accustomed to using both a mouse and a keyboard at the same time. In this case, it is not always convenient to take your hand off the mouse once again, and the keys are "sharpened" for the left hand (for right-handed people).
Usually the combination is used to move Ctrl + some key... If you add Shift to this combination, then the key combination will select the corresponding range. In my opinion, this scheme is convenient, and, probably, it should be adhered to in case of changes. Also, in my opinion, it makes sense to open the "Tools" → "Settings ..." window and carefully study the "Keyboard" tab, especially the "Commands" section. In LibreOffice, you can assign keys to almost anything, including applying styles and macros. You can find a lot of useful things there. Although, as I said, the translation is not always intuitive and sometimes even confusing, you may have to experiment to get to the true state of affairs. I will indicate the keyboard shortcut, the name of the command and its meaning, for those commands that I will describe.
It's no secret (well, I hope) that when we turn the mouse wheel, we can move the sheet up / down. If you press Ctrl, then we adjust the scale of the displayed document. And if instead of Crtl to push Shift, the worksheet will move left / right. The same rule applies to the touchpad as well. It's good to remember this when you are working without a mouse. If there is such an opportunity, why not take advantage of it?
The up / down / left / right arrows on your keyboard will move the cursor away from the cell accordingly.
End- Go to the end of the document. - Selects a cell in the current sheet at the intersection of this row and the last column in which there are filled cells (he is in shock).
Home- Go to the beginning of the document. - Selects a cell in the current sheet at the intersection of this row and the first column in the sheet.
Ctrl + PageUp/PageDown- Go to the previous / next sheet. - Surprisingly, this keyboard shortcut does exactly what it says, that is, it opens the previous / next sheet. But there is also a small catch. In the LibreOffice interface, the PageUp key is written as Next and the PageDown key as Prior, at least in version 4.3. If you want to change your keyboard shortcuts, pay attention to this.
PageUp/PageDown- Go to page up / down. - A page in this context is the number of lines that can fit into the program window, the line following the last visible one becomes the first visible on the next page.
Alt + PageUp/PageDown- Go to page left / right. - Works like the previous combination, but moves the screen left / right.
Ctrl + up arrow/down/to the left/to the right- Move to the top / bottom / left / right edge of the block. The arrows are pressed alternately :) A block in this context is a continuous range of filled cells. Example: let's say we have several tables on a sheet, arranged vertically and separated by a pair of blank lines, but inside the tables, all the cells are filled, and we have a selected cell at the beginning or middle of the first table. Then when you press the keyboard shortcut Ctrl + down arrow we first go to the last row of the first table, the second click to the first row of the second table, the next click to the end of the second table, and the next click will take us to the 1048576th (last) row of our sheet. Likewise, it will work in all directions.
Ctrl + Home- Go to the beginning of the file. - Selects the first cell A1 on the active sheet and transfers focus to it. Please note that it is the active sheet, on the first sheet, that is, actually at the beginning of the file, it does not transfer focus. Why "file", and, for example, not a "metal saw"? (shrug). Probably, the word “file” seemed more familiar to our localizers.
Ctrl + End- Go to the end of the file. - Selects a cell at the intersection of the last row and column of the active sheet that has a filled cell, a cell with a background or frame, and brings focus to it. Pay attention, it is the active sheet, it does not transfer focus to the last sheet. Why a file, and not, for example, a "data block"? (see above).
Ctrl + BackSpace- Go to the current cell. - This combination is needed when the selected cell is out of sight, for example, we are looking at a table somewhere far below, to the right, and we need to go back quickly. This keyboard shortcut tries to place the selected cell in the center of the window.
Ctrl + Shift + J- Full screen. - The mode can be very useful when we have a large range for a table, or when splitting a window. The only thing is that panels and other very convenient elements, such as "Navigator" and "Stylist", are not available in it, so we need to use hotkeys. But now, I think it will be easier for you. A little hint: in this mode there is only one floating panel, there is only one button on the panel, but who prevents us from configuring it in "Tools" → "Settings ..." on the "Panels" tab? You can leave it “floating” or attach it to some side, where it will not interfere with you. Little tip # 2: nobody forbids you to make this window small. I mean, it's not really full screen mode. This is a mode in which there are no toolbars, status bar and main menu. Otherwise, it is a window like a window, and in Writer too.
Another point that many people know, but for some reason forget, is the availability of all menu items via the keyboard. Take a look at the menu. There is an underlined letter in the name of each item, this letter will activate this menu item when you press the combination Alt + this letter... And although in full screen mode this trick will not work, since there is no menu, but in standard mode it sometimes helps out.

Navigator

This thing is not necessary for everyone, it really becomes convenient when working with LibreOffice Calc documents containing a large number of sheets, charts, pictures, ranges. You can call it by clicking F5.

If you press Ctrl and double-click on the area next to the icons in this window, then it will be attached to the left side of the main Calc window, and will become the "Navigator" panel. F5 will hide and show this panel. For me, the view of the panel for this window is more convenient. But these are already tastes ... Let's take a quick look at the elements of this window.
The Column and Row fields allow you to specify the address of the cell to which you want to go. After setting the value, press Enter... The cell, the address of which you specified in the fields, will be highlighted and placed, if possible, in the center of the program window.
The button to the right of the column field is named Range. Clicking on this button allows you to select a continuously filled area with cells (block) around the cell in which the cursor is located.
The Start and End arrows move the cursor to the first or last cell in the range. In this case, the last selected area is considered the range, either by means of a normal selection, or using the "Range" button described above. It doesn't matter which cell the cursor is on when the arrow is pressed. Interestingly, even if you go to another sheet, the arrows will direct you to the cells with the same address, but on this sheet. Please note that if no range has been selected since the file was opened, then clicking on both arrows leads to the selection of the first cell of the sheet. Once I had to parse the code of another programmer. Out of a couple of thousand lines, I found only one comment, literally it sounded like this: "This is a brilliant function." I have no other comments on these buttons. If you know the sacred meaning of the actions they perform, tell me. This is one of those things in LibreOffice that I haven't really figured out yet.
The "Content" button allows you to hide the list of objects, leaving only the basic navigator tools. Pointless when it is a docked pane, but frees up space when the navigator is floating.
The "Toggle" button allows you to show either all groups of elements in the list, or only the one in which the cursor is located. This is a very useful function if there are a lot of objects in a group (for example, "Notes"), and we only need to work with them.
The "Scripts" button turns on the display of the list of scripts. The topic of scripts is beyond the scope of this article, if you want to know more about them, check out the LibreOffice Help (although it's better to google for "Excel scripts"), or wait for an article on them.
The "Drag Mode" button has a drop-down submenu and is responsible for how the object will be inserted when dragging it from the list in the "Navigator". I want to note that some drag-and-drop functions do not work for different objects, but this is a separate topic.
At the bottom of the window there is a drop-down list where you can select an open document. What for? For example, you need to drag an object from one document to another. You can also quickly navigate between objects, including sheets, of different documents. The item "Active document" is intended for those who like to work with a large number of documents and occasionally get confused in them :). Therefore, it is correct to call the list of objects in the field of the navigator window “For the selected” document, and not for the current one.
The field with the lists of objects is what we started this whole conversation about the Navigator for. The object lists are divided into categories. All the categories are named quite clearly, in my opinion, but just in case, let me briefly comment on them.
Category "Sheets" - contains a list of all sheets of the selected document.
Category "Range names" - contains the names of all ranges defined using the "Service" → "Set range ..." function. More on setting ranges sometime next time.
Database Ranges Category - In LibreOffice Calc you can define a range of cells to use as a database. This range resembles a database table, in which each row corresponds to one record in the database, and each cell in a row corresponds to a database field. As with a regular database, such ranges can be sorted, grouped, searched, and calculated.
"Linked areas" - contains the names of all ranges from external data sources created using the function "Insert" → "Link to external data ..."
"Images" - all images inserted into the document are located here.
OLE Objects - These are objects inserted using Insert → Object. That is, this is where you should look for diagrams, odg drawings, pieces of text documents, and more.
Notes - This is where all your notes are. One caveat: if in other places there are names of objects that can be edited, then the name of the note is its content. Naturally, if you have the same note on different sheets, especially an unintelligible one (for example, "a"), then it will be difficult to find the one you need. Try to make your notes readable if you want to easily navigate them in the navigator.
"Graphic objects". I can only speculate on this point in theory. According to all the descriptions, and there are not so many of them, this category should contain objects from the drawing panel. But for some unknown reason, objects from this panel are not present in the navigator at all. Here, something like this.
The general guidelines for all objects are very simple: remember to give them human-readable names. While you have 1-2 objects, it is easy to find the one you need, but it becomes difficult to navigate in large and complex documents without working out the names. To give a name to an object is a couple of seconds, right-click → "Name ...", but in the navigator you will be able to find it unmistakably the first time.

Finding dependent and influencing cells

There is an opinion that it is necessary to talk about dependent and influencing cells when describing working with formulas. Yes, this really only works when there are formulas in the LibreOffice Calc document and we want to trace where we use the values ​​from and where we then send them. But when we work with large tables, this function allows us to quickly and accurately navigate the document, and possibly trace the error. And that's why I decided to mention this feature here. Here's a simple example:

Visual information is easier to perceive than textual information, isn't it? All functionality for working with influencing and dependent cells is located in the menu "Tools" → "Dependencies".

It is possible to use hotkeys. However, those hotkeys that are on by default are not very convenient. For myself, I reassigned them, and also assigned hotkeys to the items "Remove arrows to influencing cells", "Remove arrows to dependent cells" and "Remove all arrows". But these items are accessible from the keyboard and without it, you just need to press a little more buttons. For example, to show "Influencing Cells", press the keyboard shortcut Alt + t, Alt + p and Alt + d... Notice, as I said, the letters you need to combine with the key Alt are underlined in menu items.

conclusions

As you can see, this does not work as a panacea. These are just suggestions for independent conclusions. And a lot will depend not only on whether you want to speed up your work, but also on the habits already in place and the specific tasks to be solved. In my experience, just by using the skills of navigating through large documents, you can speed up your work by 5-6 times. To make it clear, what we do in a week can be done in a day. And this, by the way, is not the limit, this is just the beginning. Try to figure it out while I write you a new article, I think there will be no less information there. Enjoy your work :)