How to fix the top line in an open office. Working with a column

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

How to highlight a column

The first way

Second way

2. In the Delete Content window, in the Select group, select the desired deletion option:

Delete everything - for complete removal the content of the line;

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 times (formats, text, numbers and formulas are not deleted);

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

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

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

Objects - for deleting only objects (the contents of cells 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 highlight a column

The first way

In the open table window, click on the name of the required column once with the left mouse button with the arrow cursor.

Second way

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

How to set the exact width of a column

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

The maximum column width is 100 cm.

5. Close the window with the OK button.

How to set the default column width

The default column width is 2.27 cm.

If, after changing the width of the columns, you need to return to this width, activate the corresponding setting.

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

2. Open the Format menu and point to Column in the command list.

3. In the additional menu, select Width.

4. In the Column width box, activate the Default item.

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 point to Column in the command list.

3. In the additional menu, select Hide.

Hidden columns will no longer be displayed along with their names and the information they contain. This will not break links 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 the open table window, select the column range that includes the hidden columns.

2. Open the Format menu and point to Column in the command list.

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 you want to display all hidden columns.

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

2. Open the Format menu and point to Column in the command list.

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

The first way

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

2. Open the Insert menu.

3. In the command list, 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.In context menu select Insert Columns.

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

Freezing Columns to Screen Freezing Columns to 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 freeze column 10 on the sheet, select column 11.

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

The first way

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

2. Open the Edit menu.

3. In the command list, select Delete Cells.

Second way

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

2. In the context menu, select the Delete Columns item.

To delete several columns at the same time, 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 the Backspace key.

How to selectively clear columns

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

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

We have a table:

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

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

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

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

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

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

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

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

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

How to freeze a row and column

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

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

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

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

Split window

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

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

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

Moving with hotkeys

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

Navigator

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

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

Finding dependent and influencing cells

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

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

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

conclusions

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

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

We have a table:

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

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

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

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

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

We go to cell C1 and achieve the desired result. The main thing then is not to forget that there is also column A, removed 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 different purposes. We periodically publish articles on our site with tips on useful tools that can be used in office suites such as Microsoft Office or Libre Office.

This guide also belongs to 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 a more convenient viewing of the document.

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

How to freeze columns or rows in Microsoft Excel

So, despite the fact that Microsoft Office is paid office suite, it still remains the most popular among most 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 clicks, select the row below and the column on the right side of the column or row that delimits 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 in the screenshot below.

Accordingly, if the task is to fix the first two rows and columns, then we select the third cell, well, etc.

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

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

After that, in the drop-down menu, select the item " To fix areas».

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

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

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

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

How to freeze columns and rows in Libre Office Calc

Now for 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 as follows:

As a result, when scrolling through the document, the first two lines, like 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 initial state, you just need to go through the same steps and select the "Fix rows and pillars" option again.

How to freeze or freeze columns and rows in Excel and LibreOffice Calc