How to secure it in an open office. Working with a Column

The more convenient it is for us to navigate through a sheet in a 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 of moving through the sheet as quickly as possible, and you decide for yourself what to adopt and what not.

How to freeze a row and column

The first thing that comes to mind when working with large tables in LibreOffice Calc or MS Excel, is to fix table headings. Naturally, the table can go to the right and down, so it is advisable to be able to fix both rows and columns.
Select cell A2 and go to the main menu “Window” and check the “Fix” checkbox. The result will be the committed first row.

The basic principle of fixing rows, whether in LibreOffice Calc or MS Excel, is that the entire area (rows and columns) above and to the left of the selected cell will always be fixed. That is, if we need to fix only the first column, then we must place 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 are recorded. But sometimes there is a situation when several rows (columns) are allocated for the table header, but we only need one for work, what should we do in this case? Just hide all unnecessary parts of the header.
Undocking a frozen range is also easy - uncheck the “Fix” checkbox in the “Windows” menu. Where our cursor will be at this time does not matter; the entire assigned range of both rows and columns will be freed.
Sometimes we need to change the pinned area, this may be because we didn't select it correctly or we now need a different pinned area. Obviously, this can be done in two steps: first, unpin the existing range, second, pin the desired range. Therefore, another question may arise: how to do this faster? Correct answer: assign hotkeys. Yes, by default there is no key combination assigned for this action, but you can do it yourself. If you've just started using LibreOffice, you might find it difficult to do this intuitively. So I'll tell you step by step.
Go to the main menu “Tools” → “Settings...” and in the window that opens, go to the “Keyboard” tab.

Select any item in the “Key combinations” 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 simply scroll through the list and see what is assigned to what, and choose what you like best. After you have decided on the key combination (be sure to select it), select “View” in the “Categories” list of the “Commands” section, in the “Command” list at the very bottom there will be “Fix window” (I’m shocked by the name), and click the “Assign” button. The selected combination will appear in the “Key” field. If you did everything correctly, click “Ok”; if not, click “Cancel”. A detailed explanation of how this window works will require a separate article, since the logic of its operation is, to put it mildly, strange, so that’s all for now. Just in case, let me clarify that this command“Fix Window” serves both for pinning a range and for unpinning it.
Now we have hotkeys for docking and releasing an area. If you forget 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, next to the menu item they will also change.

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 pinning rows or columns may become ineffective. In this case, we often want to split the window into parts so that we can see different fragments of one 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 to decide.

The first, as you may have guessed, will be the method using the menu. Select a cell in the first column (for example A9) and, opening the “Windows” item in the main menu, check the “Split” box. The window will be divided into two parts. Both will have the same sheet, but each will have its own scroll bars, which means we achieve what we want.
The principles of splitting a window in LibreOffice Calc are the same as when docking an area of ​​cells - the splitting into parts will take place above and to the left of the cell. As you can imagine, we can split the sheet into 2 parts vertically if we place the cursor somewhere in the first row, 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 last option is used very rarely, but you never know...
The second way is to assign a key combination. This is done in the same way as assigning keys to pin an area, only in the Commands section we need to select “Split window”. I can assure you that not a single piece of glass will be damaged. :)
There is a third way. And people who prefer to use the mouse rather than hotkeys will probably like it more than others (by the way, MS Excel 2013 cannot do this, so you can throw a stone at it if necessary). Provided that our worksheet is not using row and/or column docking and window splitting, there are small rectangles at the top of the right scroll bar and to the right of the bottom scroll bar.

Grab one of them and pull, the right one to the left, and the top one down. You will see the result for yourself. And I’d better tell you about the condition that I cited in the previous paragraph. It is clear that if we divide the sheet vertically, then we will not have a right rectangle, and vice versa. The trick is different, if we have fixed, for example, the first line, then there will be no top rectangle, but the right one will remain, and if we pull it, we will not get a page division, but a fixed first line plus a vertical range of the size where we release line. It 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 an area and divide the page is sometimes sad. On the other hand, this trick can allow you not to return to the top of the page once again, but simply, if necessary, fix a perpendicular range in addition to the existing one. Unfortunately, we will have to change the size of the pinned area after releasing the mouse button either through the menu or using hotkeys. But still, as an option...

Moving using hotkeys

This is a fairly broad topic; I will only touch on the most essential points that are most often necessary when working with spreadsheets in LibreOffice. In addition, I will indicate the default hotkeys, but this does not mean that you cannot change them. But it is quite difficult to give recommendations on assigning and changing hotkeys. Firstly, it has to do with whether a person has mastery of blind typing. Typically, if a person owns it, then he uses two hands to press service keys and alphanumeric keys (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 touch typing, then usually frequently used keys are assigned to the left hand, so that it is convenient to press several service keys and some additional sign with one hand. Secondly, it depends on the person’s habits and work style. Many people are used to working with 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-handers).
Usually a combination is used to move Ctrl+some key. If you add Shift to this combination, the key combination will highlight the corresponding range. This scheme, in my opinion, is convenient, and probably should be followed if changes are made. Also, in my opinion, it makes sense to open the “Tools” → “Settings...” window and carefully study the “Keyboard” tab, especially carefully 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 even sometimes 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 the 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. What if instead Crtl press Shift, the worksheet will move left/right. The same rule applies to the touchpad. This is a good thing to remember when you're working without a mouse. If there is such an opportunity, why not take advantage of it?
The up/down/left/right arrow keys on the 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 (I’m shocked).
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 here. 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 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 in a program window; the line following the last visible one becomes the first visible one on the next page.
Alt+PageUp/PageDown- Go to page left/right. - Works similar to the previous combination, but moves the screen left/right.
Ctrl+up arrow/down/left/right— Go to the top/bottom/left/right edge of the block. The arrows are pressed one by one :) 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 empty lines, but inside the tables all the cells are filled, and we have a cell selected at the beginning or middle of the first table. Then when you press the keyboard shortcut Ctrl+down arrow We will 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. This will work similarly 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 does not transfer focus to the active sheet, to the first sheet, i.e. actually to the beginning of the file. Why “file” and, for example, not “pipe 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 last lines and columns of the active sheet that have a filled cell, a cell with a background or a border, and transfers focus to it. Please note that 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 the table somewhere far below, to the right, and we need to quickly go back. This keyboard shortcut tries to center the selected cell in 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 is stopping us from setting it up 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: no one is stopping you from making this window small. I mean, it's not really full screen. This is a mode in which there are no toolbars, status bar, or main menu. Otherwise, this window is just like a window, and in Writer too.
Another point that many people know, but for some reason forget, is the accessibility of all menu items via the keyboard. Look at the menu. The name of each item has an underlined letter, 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 it sometimes helps out.

Navigator

Not everyone needs this thing; it becomes really convenient when working with LibreOffice Calc documents containing a large number of sheets, diagrams, drawings, ranges. You can call it by pressing F5.

If you press Ctrl and double-click the area next to the icons in this window, it will attach to the left side of the main Calc window and become the “Navigator” panel. F5 will hide and show this panel. For me, the panel view for this window is more convenient. But these are just tastes... Let's briefly look at the elements of this window.
The “Column” and “Row” fields allow you to specify the address of the cell you want to go to. After setting the value, click Enter. The cell whose address 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 this button allows you to select a continuously filled area with cells (block) around the cell in which the cursor is positioned.
The "Start" and "End" arrows move the cursor to the first or last cell of the range. In this case, the range is considered to be the last selected area, either through a normal selection or using the “Range” button described above. It does not 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 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 will select the first cell of the sheet. Once I had to disassemble the code of another programmer. Out of a couple of thousand lines, I found only one comment, word for word it sounded like this: “This is a brilliant feature.” I have no other comments about these buttons. If you know the sacred meaning of the actions they perform, tell me. This is one of those things in LibreOffice whose meaning has not yet been revealed to me.
The “Content” button allows you to hide the list of objects, leaving only the main navigator tools. Pointless when it is a docked panel, but frees up space when the navigator is floating.
The “Switch” 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 convenient function if there are a lot of objects in a group (for example, “Notes”), and we only need to work with them.
The “Scenarios” button enables display of a list of scenarios. The topic of scripts itself is beyond the scope of this article, if you want to learn more about them, consult the LibreOffice help (though it is better to google “Excel scripts”), or wait for the article dedicated to them.
The “Drag Mode” button has a drop-down submenu and is responsible for how an object will be inserted when dragging it from the list in the “Navigator”. I would like 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 in which you can select open document. For what? For example, you need to drag some object from one document to another. You can also quickly move between objects, including sheets, of different documents. The “Active Document” item is intended for those who like to work with a large number of documents and periodically get confused in them :). Therefore, the list of objects in the field of the navigator window is correctly called “For the selected” document, and not for the current one.
The field with lists of objects is what we started this whole conversation about the Navigator for. Lists of objects are divided into categories. All the categories are named quite clearly, in my opinion, but let me briefly comment on them, just in case.
Category "Sheets" - contains a list of all sheets of the selected document.
Category “Range names” - contains the names of all ranges defined using the “Tools” → “Set range...” function. More about setting ranges next time.
Database Ranges category - In LibreOffice Calc, you can define a range of cells to use as a database. This range is similar to a database table, in that each row corresponds to one record in the database, and each cell in the row corresponds to a database field. As in a regular database, such ranges can be sorted, grouped, searched, and performed calculations.
“Linked Areas” - contains the names of all ranges from external data sources created using the “Insert” → “Link to External Data...” function.
“Images” - here are all the images inserted into the document.
"OLE Objects" - These are objects inserted using "Insert" → "Object". That is, this is where you should look for diagrams, odg drawings, pieces text documents and other.
“Notes” - This is where all your notes are located. One caveat: if in other places there are names of objects that can be edited, then the name of the note is its contents. Naturally, if you have the same note on different sheets, especially an obscure one (for example, “a”), then it will be difficult to find what you need. Try to make notes readable if you want to easily navigate them in the navigator.
"Graphic objects". I can only talk theoretically about this point. According to all descriptions, and there are not many of them, objects from the drawing panel should be in this category. But for some unknown reason, objects from this panel are not present in the navigator at all. Here, something like this.
General recommendations for all objects are very simple: do not forget to give them human-readable names. As long as you have 1-2 objects, it’s easy to find the one you need, but in large and complex documents it becomes difficult to navigate without working out the names. Giving a name to an object takes a couple of seconds, right-click → “Name...”, but in the navigator you can accurately find it the first time.

Finding dependent and influencing cells

There is an opinion that we should talk about dependent and influencing cells when describing how to work with formulas. Yes, this only really works when there are formulas in a LibreOffice Calc document and we want to keep track of where we're using the values ​​and where we're sending them to next. But when we work with large tables, this function allows us to quickly and accurately navigate the document, and it is possible to trace the mistake made. And that is why I decided to mention this opportunity here. Here's a simple example:

Visual information is perceived easier than textual information, isn’t it? All functionality for working with influencing and dependent cells is located in the “Tools” → “Dependencies” menu.

It is possible to use hot keys. True, those hotkeys that are set 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 without it, you just need to press a little more buttons. For example, to show "Influential Cells" press the keyboard shortcut in sequence Alt+t, Alt+p And Alt+d. Please note, as I already said, the required letters to combine with the key Alt highlighted in menu items.

conclusions

As you can see, this is not a panacea. These are just suggestions for your own conclusions. And a lot will depend not only on whether you want to speed up your work, but also on the habits already instilled and the specific tasks being solved. In my experience, just through 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 this out while I write you a new article, I think there will be no less information there. Enjoy your work :)

The more convenient it is for us to navigate through a sheet in a 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 of moving through the sheet as quickly as possible, and you decide for yourself what to adopt and what not.

Before you start, I recommend reading the article on how to hide ranges. Limiting the area also helps you move faster.

How to freeze a row and column

The first thing that comes to mind when working with large tables in LibreOffice Calc or MS Excel is to assign table headings. Naturally, the table can go to the right and down, so it is advisable to be able to fix both rows and columns.
Select cell A2 and go to the main menu “Window” and check the “Fix” checkbox. The result will be the committed first row.

The basic principle of fixing rows, whether in LibreOffice Calc or MS Excel, is that the entire area (rows and columns) above and to the left of the selected cell will always be fixed. That is, if we need to fix only the first column, then we must place 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 are recorded. But sometimes there is a situation when several rows (columns) are allocated for the table header, but we only need one for work, what should we do in this case? Just hide all unnecessary parts of the header.
Undocking a frozen range is also easy - uncheck the “Fix” checkbox in the “Windows” menu. Where our cursor will be at this time does not matter; the entire assigned range of both rows and columns will be freed.
Sometimes we need to change the pinned area, this may be because we didn't select it correctly or we now need a different pinned area. Obviously, this can be done in two steps: first, unpin the existing range, second, pin the desired range. Therefore, another question may arise: how to do this faster? Correct answer: assign hotkeys. Yes, by default there is no key combination assigned for this action, but you can do it yourself. If you've just started using LibreOffice, you might find it difficult to do this intuitively. So I'll tell you step by step.
Go to the main menu “Tools” → “Settings...” and in the window that opens, go to the “Keyboard” tab.

Select any item in the “Key combinations” 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 simply scroll through the list and see what is assigned to what, and choose what you like best. After you have decided on the key combination (be sure to select it), select “View” in the “Categories” list of the “Commands” section, in the “Command” list at the very bottom there will be “Fix window” (I’m shocked by the name), and click the “Assign” button. The selected combination will appear in the “Key” field. If you did everything correctly, click “Ok”; if not, click “Cancel”. A detailed explanation of how this window works will require a separate article, since the logic of its operation is, to put it mildly, strange, so that’s all for now. Just in case, I’ll clarify that this “Fix window” command serves both for pinning a range and for unpinning it.
Now we have hotkeys for docking and releasing an area. If you forget 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, next to the menu item they will also change.

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 pinning rows or columns may become ineffective. In this case, we often want to split the window into parts so that we can see different fragments of one 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 to decide.

The first, as you may have guessed, will be the method using the menu. Select a cell in the first column (for example A9) and, opening the “Windows” item in the main menu, check the “Split” box. The window will be divided into two parts. Both will have the same sheet, but each will have its own scroll bars, which means we achieve what we want.
The principles of splitting a window in LibreOffice Calc are the same as when docking an area of ​​cells - the splitting into parts will take place above and to the left of the cell. As you can imagine, we can split the sheet into 2 parts vertically if we place the cursor somewhere in the first row, 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 last option is used very rarely, but you never know...
The second way is to assign a key combination. This is done in the same way as assigning keys to pin an area, only in the Commands section we need to select “Split window”. I can assure you that not a single piece of glass will be damaged. :)
There is a third way. And people who prefer to use the mouse rather than hotkeys will probably like it more than others (by the way, MS Excel 2013 cannot do this, so you can throw a stone at it if necessary). Provided that our worksheet is not using row and/or column docking and window splitting, there are small rectangles at the top of the right scroll bar and to the right of the bottom scroll bar.

Grab one of them and pull, the right one to the left, and the top one down. You will see the result for yourself. And I’d better tell you about the condition that I cited in the previous paragraph. It is clear that if we divide the sheet vertically, then we will not have a right rectangle, and vice versa. The trick is different, if we have fixed, for example, the first line, then there will be no top rectangle, but the right one will remain, and if we pull it, we will not get a page division, but a fixed first line plus a vertical range of the size where we release line. It 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 an area and divide the page is sometimes sad. On the other hand, this trick can allow you not to return to the top of the page once again, but simply, if necessary, fix a perpendicular range in addition to the existing one. Unfortunately, we will have to change the size of the pinned area after releasing the mouse button either through the menu or using hotkeys. But still, as an option...

Moving using hotkeys

This is a fairly broad topic; I will only touch on the most essential points that are most often necessary when working with spreadsheets in LibreOffice. In addition, I will indicate the default hotkeys, but this does not mean that you cannot change them. But it is quite difficult to give recommendations on assigning and changing hotkeys. Firstly, it has to do with whether a person has mastery of blind typing. Typically, if a person owns it, then he uses two hands to press service keys and alphanumeric keys (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 touch typing, then usually frequently used keys are assigned to the left hand, so that it is convenient to press several service keys and some additional sign with one hand. Secondly, it depends on the person’s habits and work style. Many people are used to working with 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-handers).
Usually a combination is used to move Ctrl+some key. If you add Shift to this combination, the key combination will highlight the corresponding range. This scheme, in my opinion, is convenient, and probably should be followed if changes are made. Also, in my opinion, it makes sense to open the “Tools” → “Settings...” window and carefully study the “Keyboard” tab, especially carefully 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 even sometimes 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 the 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. What if instead Crtl press Shift, the worksheet will move left/right. The same rule applies to the touchpad. This is a good thing to remember when you're working without a mouse. If there is such an opportunity, why not take advantage of it?
The up/down/left/right arrow keys on the 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 (I’m shocked).
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 here. 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 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 in a program window; the line following the last visible one becomes the first visible one on the next page.
Alt+PageUp/PageDown- Go to page left/right. - Works similar to the previous combination, but moves the screen left/right.
Ctrl+up arrow/down/left/right— Go to the top/bottom/left/right edge of the block. The arrows are pressed one by one :) A block in this context is a continuous range of filled cells. Example: let's say we have several tables on a sheet, lined up vertically and separated by a couple of empty lines, but inside the tables all the cells are filled, and we have a cell selected at the beginning or middle of the first table. Then when you press the keyboard shortcut Ctrl+down arrow We will 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. This will work similarly 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 does not transfer focus to the active sheet, to the first sheet, i.e. actually to the beginning of the file. Why “file” and, for example, not “pipe 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 a border, and transfers focus to it. Please note that 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 the table somewhere far below, to the right, and we need to quickly go back. This keyboard shortcut tries to center the selected cell in 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 is stopping us from setting it up 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: no one is stopping you from making this window small. I mean, it's not really full screen. This is a mode in which there are no toolbars, status bar, or main menu. Otherwise, this window is just like a window, and in Writer too.
Another point that many people know, but for some reason forget, is the accessibility of all menu items via the keyboard. Look at the menu. The name of each item has an underlined letter, this letter will activate this menu item when you press the combination Alt+this letter. And although this trick will not work in full-screen mode, since there is no menu, it sometimes helps out in standard mode.

Navigator

Not everyone needs this thing; it becomes really convenient when working with LibreOffice Calc documents containing a large number of sheets, diagrams, drawings, and ranges. You can call it by pressing F5.

If you press Ctrl and double-click the area next to the icons in this window, it will attach to the left side of the main Calc window and become the “Navigator” panel. F5 will hide and show this panel. For me, the panel view for this window is more convenient. But these are just tastes... Let's briefly look at the elements of this window.
The “Column” and “Row” fields allow you to specify the address of the cell you want to go to. After setting the value, click Enter. The cell whose address 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 this button allows you to select a continuously filled area with cells (block) around the cell in which the cursor is positioned.
The "Start" and "End" arrows move the cursor to the first or last cell of the range. In this case, the range is considered to be the last selected area, either through a normal selection or using the “Range” button described above. It does not 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 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 will select the first cell of the sheet. Once I had to disassemble the code of another programmer. Out of a couple of thousand lines, I found only one comment, word for word it sounded like this: “This is a brilliant feature.” I have no other comments about these buttons. If you know the sacred meaning of the actions they perform, tell me. This is one of those things in LibreOffice whose meaning has not yet been revealed to me.
The “Content” button allows you to hide the list of objects, leaving only the main navigator tools. Pointless when it is a docked panel, but frees up space when the navigator is floating.
The “Switch” 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 convenient function if there are a lot of objects in a group (for example, “Notes”), and we only need to work with them.
The “Scenarios” button enables display of a list of scenarios. The topic of scripts itself is beyond the scope of this article, if you want to learn more about them, consult the LibreOffice help (though it is better to google “Excel scripts”), or wait for the article dedicated to them.
The “Drag Mode” button has a drop-down submenu and is responsible for how an object will be inserted when dragging it from the list in the “Navigator”. I would like 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 in which you can select an open document. For what? For example, you need to drag some object from one document to another. You can also quickly move between objects, including sheets, of different documents. The “Active Document” item is intended for those who like to work with a large number of documents and periodically get confused in them :). Therefore, the list of objects in the field of the navigator window is correctly called “For the selected” document, and not for the current one.
The field with lists of objects is what we started this whole conversation about the Navigator for. Lists of objects are divided into categories. All the categories are named quite clearly, in my opinion, but let me briefly comment on them, just in case.
Category "Sheets" - contains a list of all sheets of the selected document.
Category “Range names” - contains the names of all ranges defined using the “Tools” → “Set range...” function. More about setting ranges next time.
Database Ranges category - In LibreOffice Calc, you can define a range of cells to use as a database. This range is similar to a database table, in that each row corresponds to one record in the database, and each cell in the row corresponds to a database field. As in a regular database, such ranges can be sorted, grouped, searched, and performed calculations.
“Linked Areas” - contains the names of all ranges from external data sources created using the “Insert” → “Link to External Data...” function.
“Images” - here are all the images inserted into the document.
"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 located. One caveat: if in other places there are names of objects that can be edited, then the name of the note is its contents. Naturally, if you have the same note on different sheets, especially an obscure one (for example, “a”), then it will be difficult to find what you need. Try to make notes readable if you want to easily navigate them in the navigator.
"Graphic objects". I can only talk theoretically about this point. According to all descriptions, and there are not many of them, objects from the drawing panel should be in this category. But for some unknown reason, objects from this panel are not present in the navigator at all. Here, something like this.
General recommendations for all objects are very simple: do not forget to give them human-readable names. As long as you have 1-2 objects, it’s easy to find the one you need, but in large and complex documents it becomes difficult to navigate without working out the names. Giving a name to an object takes a couple of seconds, right-click → “Name...”, but in the navigator you can accurately find it the first time.

Finding dependent and influencing cells

There is an opinion that we should talk about dependent and influencing cells when describing how to work with formulas. Yes, this only really works when there are formulas in a LibreOffice Calc document and we want to keep track of where we're using the values ​​and where we're sending them to next. But when we work with large tables, this function allows us to quickly and accurately navigate the document, and it is possible to trace the mistake made. And that is why I decided to mention this opportunity here. Here's a simple example:

Visual information is perceived easier than textual information, isn’t it? All functionality for working with influencing and dependent cells is located in the “Tools” → “Dependencies” menu.

It is possible to use hot keys. True, those hotkeys that are set 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 without it, you just need to press a few more buttons. For example, to show "Influential Cells" press the keyboard shortcut in sequence Alt+t, Alt+p And Alt+d. Please note, as I already said, the required letters to combine with the key Alt highlighted in menu items.

conclusions

As you can see, this is not a panacea. These are just suggestions for your own conclusions. And a lot will depend not only on whether you want to speed up your work, but also on the habits already instilled and the specific tasks being solved. In my experience, just through 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 this out while I write you a new article, I think there will be no less information there. Enjoy your work :)

Single cell

Left click on the cell. The result will be as shown in Fig. 5 on the left. You can verify that your selection is correct 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-adjacent cells

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

Working with Columns and Rows

Inserting Columns and Rows

Columns and rows can be inserted multiple times different ways in unlimited quantities.

Single column or row

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

When you insert 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.

You can also insert a single column or row using the mouse:

1. Select the column or row where you want to insert a new column or new row.
2. Right-click on the title.
3. Do either Insert > Columns or Insert > Rows.

Multiple columns or rows

You can insert multiple columns or rows at once rather than inserting them one at a time.

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

Removing Columns and Rows

Columns and rows can be deleted individually or as a group.

Single column or row

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

1. Select the column or row you want to delete.
2. Right-click on a column or row heading.
3. Execute from context menu Remove columns or Delete rows.

Multiple columns or rows

You can delete multiple columns or rows 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 one and drag the cursor to the required number of headings.
2. Continue as you would when deleting a single column or row as above.

Working with sheets

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

Inserting new sheets

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

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

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

Removing sheets

Sheets can be deleted individually or as a group.

Separate sheet

Right-click on 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 inconvenient when there are 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. Trying to rename a sheet with the wrong name causes an error message.

Calc's appearance

Freezing Rows and Columns

A freeze locks the top few rows or the top few columns on the left side of the sheet, or both. When scrolling within a sheet, any frozen rows and columns remain in the view of the author.

In Fig. Figure 11 shows the fixed rows and columns. Thickened horizontal line between lines 3 and 16, and also thickened vertical line between columns C and H separate the fixed areas. Rows 4 to 16 and columns D to G scroll up. The fixed three rows and three columns remained in place.

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

Freezing individual rows or columns

• Click on the header below the row you are fixing or to the left of the column you are fixing.
• Run the command Window > Commit.

A dark line will appear indicating the fixation boundary.

Fix row and column

• Select the cell located immediately after the row you are fixing and immediately to the right of the column you are fixing.
• Run the command Window > Commit.

Two lines will appear on the screen, a horizontal one above this cell and a vertical one to the left of this cell. Now, when scrolling, all the 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 > Commit. The checkbox next to Commit should disappear.

Split window

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

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

Split screen horizontally

To split the screen horizontally:

1. Place the mouse cursor on the vertical scroll bar located with right side screen, and position the cursor over the small arrow button at the top.

1. Directly above this button you can see a thick black line (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 and a gray line will appear across the page. Drag the cursor down and 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 values ​​of "Beta" and "A0" are located at the top of the window, and other calculation results are located at the bottom. The top and bottom parts can be scrolled independently of each other. Therefore, you can change the values ​​of Beta and A0, observing their effect 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. Having vertical and horizontal division, we get four independent scrolling windows.

Split 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. Directly to the right of this button is a thick black line (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 and a gray line will appear across the page. Drag the cursor to the left, causing the line to follow the cursor.
3. Release the mouse button and the screen will 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 > Commit, use Window > Split.

Entering data into a sheet

Entering numbers

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

To enter a negative number, enter a minus symbol (–) before the number or enclose it in parentheses ().

By default, numbers are right aligned, and negative numbers are preceded by a minus symbol.

Entering text

Select a cell and enter text into it. Text is left aligned by default.

Entering numbers in text format

If a number is entered in the format 01481, Calc will remove the leading 0. To preserve this leading zero, when entering codes, for example, enter an apostrophe character before 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 accordingly. Right-click the cell and select Format Cells > Number. Setting the value to Leading Zeros allows you to have numbers with leading zeros.

Even if you declare a variable as text, it may still be involved in arithmetic operations; however, the results of such transactions may not be as expected. In some cases Calc will do arithmetic operations in a cell with text, whether it has characters (such as ABCD) or numbers that you formatted as text. More detailed information See the Calc Manual.

Entering date and time

Select a cell and enter the date and time into it. You can separate date elements with a (/) or (–) symbol, or use text, such as 10 Oct 03. Calc recognizes many date formats. Time elements can be separated by a colon, for example 10:43:45.

Autofill

To make data entry easier and faster, the AutoFill mode allows you to use the fill marker in the form of a black square in the lower right corner. current cell(works, in addition to entering 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 elements;
2. 2. move the mouse cursor over the fill marker so that it takes the shape of a cross;
3. 3.drag the fill marker, selecting the range (if the selected range more quantity elements in the list, it will be filled cyclically).

If you enter two numbers in succession into two adjacent cells that make up the beginning of an arithmetic progression, for example 1 and 3, then select them and, as when copying, use the fill marker to drag them over several cells, then the series will continue: 1, 3, 5, 7 etc. If you need to fill the cells in increments of one, then just enter the first number, for example 1, and use the fill marker to drag it to the desired number, we get row 1, 2, 3, etc.

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

Leave your comment!

I think many are already familiar with such a useful tool as Excel, with which you can create a variety of tables for various purposes. We periodically have articles on our website with tips on useful tools that can be used in office suites such as: Microsoft Office or Libre Office.

This guide also falls into the category of such articles, as it will show examples of how you can fix rows and columns in Excel from Microsoft or Calc from Libre Office, for more convenient viewing of the document.

That is, we will try to make sure that, for example, the first column and the first row are fixed, but the rest of the contents of the document can easily move.

How to Freeze Columns or Rows in Microsoft Excel

So, despite the fact that Microsoft Office is a paid office suite, it still remains the most popular among the majority of users who have to deal with documentation, so I think we’ll start with Excel.

So, based on the conditions of our example, we need to freeze the first column and the first row.

In this case, with the left mouse click, select the row below and the column on the right side of the column or row that limits the docking area.

That is, if you need to fix the first column and the first row, then you should select the second cell from the row and column, which will be fixed in the future, for example, in the same way as shown below in the screenshot.

Accordingly, if the task is to fix the first two rows and columns, then select the third cell, and so on.

In general, in this way we need to show the boundaries of the area that will be frozen in Excel.

Having set the desired border, go to the control panel to the “View” tab - “ To fix areas».

Then in the drop-down menu select “ To fix areas».

That's all, after this the rows and columns you need in Excel will be fixed, and you can flip through the contents without any problems.

Note that there are three items in the area pinning menu:

• To fix areas– is responsible for fixing both columns and rows;
• Pin top line – by selecting this option you will record only the first line in your document;
• Freeze first column– is responsible for freezing only the first column.

In general, if you need to fix only one row or column, you can use the second or third point, but if you have several such rows, then you will need to choose the first option.

How to Freeze Columns and Rows in Libre Office Calc

Now regarding the example in Libre Office Calc. Here, I propose to change the conditions a little and fix the first two lines and the first column.

You can pin them in Calc like this:

As a result, when scrolling the document, the first two lines, as well as the column, will remain in place, but you can easily view all the necessary contents of this document.

By the way, in order to return everything to original state, just go through the same steps and once again select the “Fix rows and columns” option.

How to Freeze or Freeze Columns and Rows in Excel and LibreOffice Calc

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

We have a table:

Task: fix the header (highlighted in blue in the screenshot) so that when the table is scrolled down, it remains in place and the content scrolls.

To complete this task, go to cell A2 and select Window -> Freeze

Now, when scrolling the table, the header remains in place.

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

To cancel the fixation, you must uncheck the menu item Window -> Fix.

Sometimes a problem arises: to fix, for example, the second column, but so that the first one is not visible. Scrolling horizontal stripe scrolling until column B is the first on our screen:

We become cell C1 and achieve the required result. The main thing is not to forget that there is also column A, which was removed from the screen in this way.