home-Software-How to attach a table header in an open office. Convenient navigation in LibreOffice Calc

How to attach a table header in an open office. Convenient navigation in 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. Scroll with a horizontal scroll bar 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.

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;
  • Freeze top row– 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

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 Content window, in the Select group, select the desired deletion option:

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 open table window, 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. In the additional menu, select Width.

4. In the Column Width box, 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 a range of 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. B context menu select Insert Columns.

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

How to freeze a column on the screen Freezing columns on the screen allows you to work with long rows of data that do 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 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 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 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 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. 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 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 couple of empty lines, but inside the tables all the cells are filled, and we have a cell at the beginning or middle of the first table selected. 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 place the selected cell in the center of the window.
Ctrl+Shift+J- Full screen. — The mode can be very useful when we have a large range for a table, or when splitting a window. The only thing is that panels and other very convenient elements, such as “Navigator” and “Stylist”, are not available in it, so we need to use hotkeys. But now I think it will be easier for you. A little hint: in this mode there is only one floating panel, there is only one button on the panel, but who 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, 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 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 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.
“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 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 :)

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. Scroll with a horizontal scroll bar 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.

Related publications: