LibreOffice Calc, like all spreadsheets, contains a large number of cells in various rows, columns, and sheets, and navigating that can get a little tricky. As we saw previously, each cell has an address, which is marked by the column (letters) and the row (numbers), always in that order. But in fact the address can be larger because we never discussed sheets.

By default, when you create a new Calc spreadsheet you will have three sheets in it, which you see as tabs along the bottom of the screen. They will be called Sheet 1, Sheet 2, and Sheet 3 at this point. But these defaults can be changed by going to Tools–>Options–>LibreOffice Calc–>Defaults. On this screen you can decide how many sheets you want to have on a new document. While the default as it comes is three (similar to Microsoft Excel) you can change it. On my copy of Calc I changed it to 1, because most of the time I never need more than one sheet for my work. I can also change the default naming of new sheets here. Instead of each sheet being “Sheet 1″, Sheet 2”, etc. I could make it something else, like “Tab 1”, “Tab 2”. and so on. I never bother with this though, because I will always name my sheets for what they are doing in a given spreadsheet (e.g. look at what I did when I created the simple model for “What-If” analysis.) And if I need to add a sheet, I can just go to Insert–>Sheet to bring up a window to specify where the sheet should go, what it should be named, or even insert a sheet from a file . A CSV file would be a very good choice here, such as if you wanted to bring in data from a database or another spreadsheet for use in the current spreadsheet.

You can leave your sheet named as Sheet 1 and so on, but it is often better to rename each sheet with something more descriptive like I did in my model example. And the thing we want to mention here is that the sheet name is implicitly part of the cell address, and can be explicitly addressed. If you only have one sheet in your spreadsheet, you needn’t worry about this, but if you had several sheets you might want to use data from all them in combination, and then it really matters. So begin by renaming your sheets with descriptive names. Place your cursor over the tab where it now says “Sheet 1”, and select Rename Sheet…. A window opens that lets you type in a new name. A common use for something like this is financial data where each month is on its own sheet, so rename this sheet to “January” and click OK. You should now see the tab renamed. Repeat on Sheet 2, only call it February. Now all we need to do is put some data in there.

For this purpose I am going to introduce a couple of functions that produce random numbers. The first of these is RAND. You can find this by clicking on the Function icon, which is just to the left of the Sum icon. Any mathematician would recognize this script F with a small x as the symbol “F of x”, which is the general form of a function. When you click on it a window opens that lets you select a function. We will get into this in more detail later, but for now just select the Functions tab, then for category All and scroll down to RAND. Click on it and on the right you will see a description that says “Returns a random number between 0 and 1”. Click Next and you should see the function copied to the Formula box below. Since I like my number to be slightly larger, right after the “=RAND()” I will type “*100”. Then click OK, and you should have a random number in the cell of your spreadsheet, so just click-and-drag to fill ten or so cells. You now have some random data. On the next sheet we will do something slightly different. As before, go to the Function Wizard, but this time select the RANDBETWEEN function. The description for this one says “Returns a random integer between the numbers you specify”. When you select that and click Next to put it in the Formula box, you will see two blank fields above to enter the bottom and top number of the range. I selected 1 and 100 as my bottom and top numbers, then clicked OK. As before, click-and-drag to fill some cells. From this you can see the differences between these two functions. The RAND function has decimal places, up to 10, and even though we multiplied by 100 it is possible that one or more of the numbers created is below 1. The RANDBETWEEN function has no decimal places, and thus no numbers below 1. Every number is an integer.

Back to the story. With data on two sheets I can do calculations using these numbers. I will create a third sheet, and name it “March Projected”. To do this simple calculation let’s assume we can average the numbers from January and February to get an estimate for March. So go to cell A1 on the March Projected tab and click the equals sign on the formula bar. This tells Calc to expect a calculation. Then go to the January tab, and click cell A1 there. If you look at the Formula Bar, you will now see it say “=January.A1”. That indicates that even though you are on the March Projected tab, you will be grabbing a value from the January tab. Next, we need to an addition, so type in a + sign, then go the February tab and click on the A1 cell there. Now your formula reads “=January.A1+February.A1”. We are close, but having added these together we need to divide by two. The simplest way to do this is to edit the Formula in the Formula Bar by adding parentheses around the addition, and then putting a divide by 2 at the end. When you do so your formula should read “=(January.A1+February.A1)/2”. That is it, so click the green Accept icon next to the formula. Your numbers will be different from mine if you used random numbers, but if you check you should indeed see the average. And if you then click-and-drag down the column, you will see that cells increment exactly as you would expect.

So, the cell address has the name “Sheet.ColumnRow”, but if no sheet is specifically named it is assumed to be the sheet you are on. And you can jump to a cell on a different sheet using the Name box at the left of the formula bar. Just type in the cell address using the full name, hit Enter, and you will jump to that cell on that sheet.

Adding, Deleting, and Hiding Columns and Rows

Within a sheet there are times you need to do some editing of the structure by Adding, Deleting and Hiding rows and columns. This is not hard. To add a row or column, just go to Insert. In the menu that comes down, you can see the option for Rows or Columns. This is done using certain defaults based on where you are now. If you are in a cell, Calc will use that cell address as the starting point for adding. If you add a Row, the new blank row will push down the stack and be inserted above the cell. If you add a column, the new column will be inserted to the left of the cell, and the columns will shift to the right.

Deleting is slightly different. The way Calc handles this is by deleting cells, and if you are in one cell and click Delete the question will be whether to move up the cells beneath the one you deleted, move to the left the cells to the right, or delete the entire row or column. To do this go to Edit, then Delete Cells. For rows and columns that I delete, I will often click on the row number or the column letter, which will highlight the entire row or column. If I then click the Delete Cells option it does not need to ask me what I intend, it just deletes what I highlighted.

Hiding is another option that is useful for a few reasons. First, it can clean up printing if you hide a row or column that does not need to be in the printed output. Perhaps this is because it represented an intermediate step in the calculation, or contains data that should not be printed for other reasons, such as privacy. But hiding a column or row is easy. Just go to the Format menu, choose either Row or Column as appropriate, and the submenu will contain Hide as an option. When you do this, the row or column will disappear from view, but it is still in the spreadsheet. And if you look at the Row numbers or Column letters, you will see that the hidden row or column has its label missing from the sequence. So if you see columns that go “A,B,C,D,F,G”, you know right away that the E column was hidden because that letter is missing. If you then want to bring back the column (or row) select all of the columns or rows in the range that includes the hidden columns or rows (e.g. in the above example select columns D and F), then go to Format–>Column–>Show to bring it back again.

Freezing and Splitting Rows and Columns

This is another area of useful technique. Sometimes you want a row or rows at the top to remain fixed in place as you scroll down, or it could be a column or columns to be fixed as you scroll to the right. To freeze rows at the top, go to the row below the row(s) you want frozen, then go to Window–>Freeze, and those rows will be frozen. Now you can scroll up or down, and the frozen rows will always stay in place. For freezing columns, it is the same thing. Pick the column just to the right of the columns you want to freeze, then go to Window–>Freeze. You can even set both columns and rows in one pass by selecting the cell just to the right and under where you want the freeze, and then going to Window–>Freeze. To remove the freeze on any rows or columns, go to Window–>Freeze and click on it to remove the check mark.

Splitting is a slightly different. This divides the sheet into several independently scrollable sections, so you can make around within each section without affecting other sections. You can divide into either two or four sections, depending on whether you split along a vertical line, a horizontal line, or both. Just select a cell as you did above for freezing, but this time go to Window–>Split. You will now see a thick separator between the sections, and each section will have its own scroll bars. But note that if you divide into four sections, there are still some limitations. Any scroll bar will affect both sections to which it is attached, so if you select a scroll bar on the right and move it, both of the sections on that part of the spreadsheet will move together. As with Freeze, you can remove this by going to Window–>Split and selecting to remove the check mark.

Shortcut Keys for Navigation

This comes from the LibreOffice Help site, which you may want to bookmark for reference.

Shortcut Keys	Effect
Ctrl+Home	Moves the cursor to the first cell in the sheet (A1).
Ctrl+End	Moves the cursor to the last cell on the sheet that contains data.
Home	Moves the cursor to the first cell of the current row.
End	Moves the cursor to the last cell of the current row.
Shift+Home	Selects cells from the current cell to the first cell of the current row.
Shift+End	Selects cells from the current cell to the last cell of the current row.
Shift+Page Up	Selects cells from the current cell up to one page in the current column or extends the existing selection one page up.
Shift+Page Down	Selects cells from the current cell down to one page in the current column or extends the existing selection one page down.
Ctrl+Left Arrow	Moves the cursor to the left edge of the current data range. If the column to the left of the cell that contains the cursor is empty, the cursor moves to the next column to the left that contains data.
Ctrl+Right Arrow	Moves the cursor to the right edge of the current data range. If the column to the right of the cell that contains the cursor is empty, the cursor moves to the next column to the right that contains data.
Ctrl+Up Arrow	Moves the cursor to the top edge of the current data range. If the row above the cell that contains the cursor is empty, the cursor moves up to the next row that contains data.
Ctrl+Down Arrow	Moves the cursor to the bottom edge of the current data range. If the row below the cell that contains the cursor is empty, the cursor moves down to the next row that contains data.
Ctrl+Shift+Arrow	Selects all cells containing data from the current cell to the end of the continuous range of data cells, in the direction of the arrow pressed. If used to select rows and columns together, a rectangular cell range is selected.
Ctrl+Page Up	Moves one sheet to the left.In the page preview: Moves to the previous print page.
Ctrl+Page Down	Moves one sheet to the right.In the page preview: Moves to the next print page.
Alt+Page Up	Moves one screen to the left.
Alt+Page Down	Moves one screen page to the right.
Shift+Ctrl+Page Up	Adds the previous sheet to the current selection of sheets. If all the sheets in a spreadsheet are selected, this shortcut key combination only selects the previous sheet. Makes the previous sheet the current sheet.
Shift+Ctrl+Page Down	Adds the next sheet to the current selection of sheets. If all the sheets in a spreadsheet are selected, this shortcut key combination only selects the next sheet. Makes the next sheet the current sheet.
Ctrl+ *	where (*) is the multiplication sign on the numeric key padSelects the data range that contains the cursor. A range is a contiguous cell range that contains data and is bounded by empty row and columns.
Ctrl+ /	where (/) is the division sign on the numeric key padSelects the matrix formula range that contains the cursor.
Ctrl+Plus key	Insert cells (as in menu Insert – Cells)
Ctrl+Minus key	Delete cells (as in menu Edit – Delete Cells)
Enter (in a selected range)	Moves the cursor down one cell in a selected range. To specify the direction that the cursor moves, choose Tools – Options – LibreOffice Calc – General.
Ctrl+ ` (see note below this table)	Displays or hides the formulas instead of the values in all cells.

Listen to the audio version of this post on Hacker Public Radio!