hpr-knowledge-base/hpr_transcripts/hpr1535.txt

Episode: 1535
Title: HPR1535: 31 - LibreOffice Calc - Sheet Editing and Navigation
Source: https://hub.hackerpublicradio.org/ccdn.php?filename=/eps/hpr1535/hpr1535.mp3
Transcribed: 2025-10-18 04:46:38

---


Hello, this is Ahuka. Welcome to Hacker Public Radio and another exciting episode and we
are continuing our look at Libra Office Calc. We've just been through an exploration of doing
what if analysis and creating models. I hope you found that interesting. And now we're going
to deepen our understanding by looking at how we edit and navigate things on spreadsheets. Now
Libra Office 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, denoted with letters, and the row denoted with numbers,
always in that order. But in fact, the address can be larger because we didn't get to talking about
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 one, sheet two,
and sheet three at this point. But these defaults can be changed by going to the tools menu,
to options, to Libra Office Calc, to defaults. On this screen, you can decide how many sheets you
want to have on a new document. While the default, as it comes as three, similar to Microsoft Excel,
you can change it. On my copy of Calc, I changed it to one 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 one, sheet two, etc. I could make it something else. I could say
tab one, tab two, 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. For example, take a look at what I did when I
created the simple model for what if analysis. If you go back and take a look at that, you see that
I gave each sheet a meaningful name. If I need to add a sheet, we showed in the what if analysis
in the savings model that you can create copies of existing sheets by holding down the control key
and clicking and dragging. If you just want to insert a blank sheet, go to the insert menu,
sheet, and then that brings up a window that specifies where the sheet should go, what it should
be named, and even insert a sheet from a file. For instance, 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. Now, you can leave your sheet name to sheet one and so on, but as I did
previously, I think it's better to rename the sheet with something more descriptive.
And the thing that 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 didn't worry about this. But if you had several sheets, you might want to use data from 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 one 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 two, only call it February.
Now all we need to do is put some data in there. Now for this purpose, I'm 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 spall 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'll get into this in more
detail later, but for now just select the functions tab and then for category all and scroll down
to UC Rand, R-A-N-D. 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 function, which says equals R-A-N-D, open parent, close parent, I will type
star 100. That is take whatever that random number is between 0 and 1 and multiply it by 100.
Then click OK and you should have a random number in the cell of your spreadsheet.
So just click and drag to fill 10 or so cells, you now have some random data.
Now let's go to the second sheet, the February one.
And I'm going to use a slightly different function here. I'm going to use the Rand between.
So as before, we'll go to the function wizard. This time select the Rand between function.
The description for this one says, returns a random integer between the numbers you specify.
When you click that and click next to put it in the formula box, you will see two blank
fields above the formula box to enter the bottom and top number of the range.
So let's select one as the bottom and 100 as the top and click 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 multiply it by 100,
it is possible that one or more of the numbers that we created is below 1.
The Rand between function has no decimal places and thus no numbers below 1 every numbers
and integer. Now back to our story. I now have data on two sheets and 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 equal sign on the formula bar. Remember, the equal sign tells Calc to expect
to do 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 says equals January dot A1. That indicates that even though
you're on the March projected tab, you will be grabbing a value from the January tab.
Next, we need to do an addition so type in a plus sign then go to the February tab and click
on the A1 cell there. Now your formula reads equals January dot A1 plus February dot A1.
We're close, but having added these together we need to divide by two. The simplest way to do that
is to edit the formula in the formula bar by adding parentheses around the addition
then putting a divide by two at the end. When you do so, your formula should read equals January dot
A1 plus February dot A1 with parentheses around it equals open parent January dot A1 plus February dot
A1 close parent. Then slash two is going to put in the division. That is it, so click the green
except icon next to the formula. Your numbers will be different from mine if you use random numbers,
but if you check, you should indeed see the average. If you then click and drag down the column,
you'll see that the cells increment exactly as you would expect. In cell A2, you would see
equals January dot A2 plus February dot A2 added together and divided by two and then in A3,
you'll see all threes and so on. The cell address has the name sheet dot column row.
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. So you can try that, open up and get onto the January tab and then type in march dot A3,
hit enter and by golly you will jump to that cell on the march tab.
Now 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 all 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 would be whether to move up the
cells beneath the one you deleted or move to the left the cells that are to the right of the cell
you deleted. Or should you delete the entire row or column? If you are just in a cell and you click
delete, that's all ambiguous. So to do this, you go to edit, then delete cells,
and you can answer the questions there. But I often find it simpler if I'm going to delete
rows or columns to make that plain by clicking on the row number or column letter, which will
highlight the entire row or column. So if I click on the number three, everything on row three will
be highlighted. Or if I click on the column B, everything in column B will be highlighted.
So if I've highlighted a row or a column, and then I click delete cells, it doesn't ask because
it's very clear what I wanted. It deletes what I highlighted.
Now hiding is another option that's 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.
For whatever the reason hiding a row or column 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 look up top and you see columns that go A,
B, C, D, F, G, well 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 ones. For instance, in the above example, if you select columns D and F,
that's the range, then go to format column show to bring it back again and similarly with rows.
Now another technique, freezing and splitting, okay? 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. That's called freezing and you might do that because those are labels that you
want to, particularly in a large spreadsheet, you know, as you scroll down, you don't want to lose
your labels and it's like, okay, what was that fifth column again? And you have to scroll up to see
what the label was and then scroll back to take a look at your data, it's a pain in the neck, freezing gets
around that. So you can freeze rows at the top by going to the row below the ones you want frozen.
So let's say the top two rows, rows one and two, have all of the column headers that you want to
keep in place, then select row three because that's the one right below the ones you want to freeze.
And then go to window, the window menu, select freeze and those rows will be frozen.
Now you can scroll up and down, you could have hundreds of lines in the spreadsheet but as you scroll
up and down those top two rows will always be at the top. Now freezing columns, same kind of thing,
you pick the column just to the right of the columns you want to freeze and then go to window
freeze. You can even set columns and rows in one pass by selecting the cell just to the right
and just under where you want to freeze and then going to window freeze and it would freeze columns
and rows both. Now to remove that, just go to window freeze and now remove the check mark
at gets rid of the freeze. Now splitting is slightly different. This divides the sheet into several
independently scrollable sections so you can you know jump 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 or horizontal line or both. Just select the cell
as you did above for freezing but this time go to the window menu and select 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's attached. So if you select the scroll
bar on the right and move it both of those sections on that part of the spreadsheet will move
together. Now as with freeze you can remove this by going to window split and selecting to remove
the check mark. Now finally there's a whole bunch of shortcut keys. I have a link in the notes
to the LibroFace help site. You can go and take a look at this. I'm just going to move,
mention a few of them here. Control and home together always moves to cell A1. So if you're on a
very large sheet and you want to get back to the beginning control plus home. The opposite one
is control plus end and that moves to the last cell on the sheet that contains data.
The home key moves the cursor to the first cell of the current row and the end key to the last cell
of the current row. If you do control left arrow moves to the left edge of the current data range.
Control right arrow moves to the right end of the current data range.
If you do control page up moves one sheet to the left. Control page down moves one sheet to the right.
And now here's a distinction between control page up and alt page up. Alt page up moves one screen
to the left and alt page down moves one screen to the right. The distinction here is between a
screen page and a printed page and they're not necessarily the same. Now there's a lot more of
these but me reading through all of them is going to be deadly dull and this has been sufficiently
long episode and full of meat already. So I'm just going to say if you really want to get in all
these keyboard shortcuts check out the link that I put in the show notes to the Libra Office
help site and you know book market and come back to it later. And so this is Ahuka signing off
and reminding you as always please support free software. Bye bye.
You have been listening to Hacker Public Radio or Hacker Public Radio does our
We are a community podcast network that releases shows every weekday and Monday through Friday.
Today's show like all our shows was contributed by a HPR listener like yourself. If you ever
consider recording a podcast then visit our website to find out how easy it really is.
Hacker Public Radio was founded by the digital dog pound and the economical computer cloud.
HPR is funded by the binary revolution at binref.com. All binref projects are crowd
sponsored by linear pages. From shared hosting to custom private clouds go to
lunarpages.com for all your hosting needs. Unless otherwise stasis today's show is released
under a creative commons, attribution, share a line, free dose of license.