Convenient navigation in LibreOffice Calc

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 highlighted using Ctrl keys+ mouse:

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

Do 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.

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

Select the column or row where you want to insert new column or a new line.
Click on the title right click mice.
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.

Highlight the required number of columns or rows and, while holding down left button mouse on the first of them, move the cursor to the required number of headings.
Continue as you would when 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:

Select the column or row you want to delete.
Right-click on a column or row heading.
Execute from the 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.

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.
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 the 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 large number 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

Fixation blocks several top lines or several 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, as well as 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:

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

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).

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.
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:

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

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.
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.
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 top row keyboard or 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 have a minus symbol in front of them.

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.

Autocomplete

The AutoFill mode makes it easier and faster to enter data - a fill marker in the form of a black square in the lower right corner of the current cell (works, in addition to entering formulas, with numbers, dates, days of the week, months and mixed data).

Automated data entry:

1.in the first cell of the range, enter the value of one of the list elements;
2. move the mouse cursor over the fill marker so that it takes the shape of a cross;
3.drag the fill marker, selecting the range (if the selected range is greater than the number of 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!

A column is a vertical row of cells from row 1 to row 65,536.

How to select a column

First way

Second way

2. In the Delete Contents window, in the Select group, select required parameter removal:

Delete all - for complete removal string content;

Text - to delete only text (formats, formulas, numbers and dates are not deleted);

Numbers - to delete only numbers (formats and formulas are not deleted);

Date and time - to delete only dates and time values (formats, text, numbers and formulas are not deleted);

Formulas - for deleting formulas only (text, numbers, formats, dates and times are not deleted);

Notes - to delete only notes on row cells (all other elements are not deleted);

Formats - to remove only the format attributes applied to the row cells (the contents of the cells are not removed);

Objects - to delete only objects (cell contents are not deleted).

3. Close the window with the OK button.

Working with a Column A column is a vertical row of cells from row 1 to row 65,536.

How to select a column

First way

In the window open table Click on the name of the desired column once with the left mouse button with the arrow cursor.

Second way

In the open table window, select any cell of the desired column and use the key combination Shift+Ctrl+Space (spacebar).

How to set an exact column width

4. In the Column Width window, set the desired value using the Width slider.

The maximum column width is 100 cm.

5. Close the window with the OK button.

How to set the default column width

By default, the column width is considered to be 2.27 cm.

If, after changing the column width, you need to return to this width, activate the appropriate setting.

1. In the open table window, select the desired column or range of columns.

2. Open the Format menu and in the list of commands, move the cursor to Column.

3. B additional menu select Width.

4. In the Column Width window, activate the Default option.

5. Close the window with the OK button.

How to hide a column

1. In the open table window, select the desired column or range of columns.

2. Open the Format menu and in the list of commands, move the cursor to Column.

3. In the additional menu, select Hide.

Hidden columns will no longer appear along with their names and the information they contain. This will not break connections with hidden cells in formulas and functions.

How to show range hidden columns

This method is suitable if the range of hidden columns is known.

1. In an open table window, select a range of columns that includes hidden columns.

2. Open the Format menu and in the list of commands, move the cursor to Column.

3. In the menu that opens, select Show.

How to show all hidden columns

This method is suitable if the range of hidden columns is unknown or if you want to display all hidden columns.

1. In the open table window, select the entire table field.

2. Open the Format menu and in the list of commands, move the cursor to Column.

3. In the additional menu, select Show. How to insert a column

First way

1. In the open table window, select the desired column, to the left of which you need to add a new one.

2. Open the Insert menu.

3. In the list of commands, select Column.

Second way

1. In the open table window, right-click on the column to the left of which you want to add a new one.

2. From the context menu, select Insert Columns.

To insert multiple columns at the same time, you must first select required quantity columns.

How to freeze a column on the screen Freezing columns on the screen allows you to work with long lines data that does not fit in the viewable area of the spreadsheet.

1. In the open table window, select the column to the right of the one that will be fixed, for example, if you need to fix column 10 on the sheet, select column 11.

2. Open the Window menu and select Freeze. -All columns to the left of the selected one will be frozen with a non-scrollable area. How to disable freezing of columns In an open table window, expand the Window menu and disable the Freeze item. How to delete a column

First way

1. In the open table window, select the desired column.

2. Open the Edit menu.

3. In the list of commands, select Delete Cells.

Second way

1. In the open table window, right-click on the desired column.

2. From the context menu, select Remove Columns.

To delete multiple columns at once, you must first select the required number of columns.

How to quickly delete the contents of a column

In an open table window, select the desired column or range of columns and press Backspace.

How to selectively clear columns

1. In the open table window, select the desired column or range of columns and press the Delete key.

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 working space based on your 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, this 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 opposite it will be empty place. 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”. Detailed explanation 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. 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 can become ineffective. In this case, we often want to split the window into parts so that we can see different fragments 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 menu method. 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 . Last chance It 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 top rectangle it won’t, 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 the 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 the 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 rather broad topic; I will touch only 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. Usually, if a person owns it, he uses two hands to press service keys and alphanumeric keys (for example, with your right hand Shift and left hand A, For 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 we add to this Shift combination, then 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 things. 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 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 (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 that is on last page she doesn't shift focus. 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 very convenient function, if there are a lot of objects in a group (for example, “Notes”), and we need to work only 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, different documents. The “Active Document” item is intended for those who like to work with big amount 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.
“Related Areas” - contains the names of all ranges from external sources data 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 different sheets the same note, 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, just click right key→ “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, only due to navigation skills large documents You can speed up 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'm writing to you. 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.

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 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. 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 can 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 menu method. 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 the 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 things. 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 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 (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

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 :)