One of the key techniques in using a spreadsheet is to master the art of fills, which lets you fill a column or a row with data without having to type in every cell individually. And this technique requires that there be a predictable pattern to the contents of each cell as you fill them. But you can do a lot with this technique, and we will want to use this when we do our first model, which will be a simple savings model.

But first we need to build the tools in our tool kit, and fills are a big one. To begin with, you can fill either rows or columns, though columns are more frequently filled using this technique. Still, it is good to know you can do either. The simplest fill begins with a cell that has some kind of contents. For example, let’s say that cell B1 contains the word “Rain”. If you click on the cell, you will see it highlighted with a thick black border:

 

 

 

 

 

But if you look closely, there is also a small back square on the lower-right corner of the cell, and this is the Fill Handle. If you move your cursor of this handle, it will turn into a “plus” sign. At this point you can click-and-drag to fill cells in the column or row. In this case, because the contents are text, it will simply repeat the text:

 

 

 

 

 

 

 

Of course, you can also click and drag to fill a row:

 

 

 

So you may be looking at this and thinking it is no big deal. How often would anyone want to fill a bunch of cells with a single word? But this is only the beginning. What if we use a number? I entered the number “1” in cell B1, then did the same click and drag to fill the column:

 

 

 

 

 

 

 

Now, when I am clicking and dragging cell B1 down to fill the column, it is incrementing each cell by 1 to give me successive numbers. Also, there is a little back square that travels with my cursor and shows me the latest number I am inserting. This is handy if you wanted to know where to stop. And as before, I can also fill a row using the same technique.

But what if you don’t want to start with 1? What if you wanted to start with 42? No problem, you just enter 42, and when you fill the column you will get the numbers 43, 44, 45, and so on.

Ah, but you have more sophisticated needs. You want to increment by some other amount. You want to go up by two each time. Well, you can do that, but you need to give Calc enough information to figure it out. Remember that we still do not have the telepathy interface. So the way you do this is to type the first two numbers into successive cells. Then click on the first cell to select it, then hold down the Shift key while you click the second cell. This should now show the two cells selected:

 

 

 

 

 

 

Here we can see that the first cell has the heavy black border, but the fill handle is now on the second cell, and both cells are highlighted. Now you can click-and-drag, the numbers will go up by two each time.

And if you combine numbers and words, Calc will increment the numbers but keep the words constant. For example, if you enter “Year 1” in the first cell and then fill the rest of the column or row, you will get “Year 2”, “Year 3”, etc. This is very useful if you want to increment the numbers, but sometimes you don’t, and you can get frustrated. Well, in this case you need to find a way to tell Calc to not do this. And you can do this easily by holding down the control key as you fill the row or column. Enter Year 1, then hold down the control key while you fill, and you get “Year 1”, “Year 1”, “Year 1”, etc.

Lists

The next thing you can try is to enter “January” in the first cell, then fill your row or column. And you will discover that Calc will continue the fill with “February”, “March”, “April”, etc. And the same thing with “Monday”. That will get you “Tuesday, “Wednesday”, etc. These are examples of lists, and Calc comes with a number of these built-in. Where can you find these lists? They can be found by going to the Tools menu–>Options–>LibreOffice Calc–> Sort Lists, and when you select this a window will open:

 

 

 

 

 

 

From this you can see that you have several built-in options. You can use full names (January, February), abbreviations (Jan, Feb), and you have a couple of other lists which represent Jewish names for days and months. But suppose you wanted to add your own list? No problem, just click “New” and start typing it in. Or if you already have this list in a range of cells on a spreadsheet, you can copy the list in by giving it the range of cells to read. As an example of where this might be handy, imagine a company that has 10 sales regions and frequently wants to report results from all ten on a spreadsheet. Create the list once, then you can fill a row or column any time you need it.

Fill with Cell Addresses

This is something you will do a lot in Calc, as it is a very powerful part of building a model. Suppose you enter the cell address “=A3” in a cell. That tells Calc to copy the contents of cell A3 and place them in this cell. BTW, if you left out the equals sign, all you would get is the text string “A3”, and if you filled a row or column from here you would get “A4”, “A5”, etc. But if you did this properly with an equals sign, you would get different results. If you filled a column beginning with “=A3”, you would get “=A4”, “=A5”, etc. Each cell would pull the contents of another cell from column A. But if you begin with “=A3” and fill a row, something very different happens. You would then get “=B3”, “=C3”, “=D3” and so on. Calc knows that the Letter at the beginning of the cell address denotes the column, and that if you are filling a row you must want to increment the column designator.

This also work with more complicated formulas. Suppose you begin with the formula “=A3+B2”. If you fill the column you would then get “=A4+B3”, “=A5+B4”, and so on. But if you fill a row you would get “=B3+C2”, “=C3+D2”, etc. And no matter how complicated you make it, you can fill a row or column and it will always increment the number when filling a column, and increment the letter when filling a row.

That is great when you want the row number or column letter to increment. But what if you want to keep one or both of them constant? Then you need to place a dollar sign in front of the one you want to hold constant. Suppose you wanted to keep the address of cell A3 constant while letting the other cell address increment? If you enter the formula “=A$3+B2”, you can fill the column, and each cell will have the formula “=A$3+B3”, “=A$3+B4”, etc. But if you tried to fill the row, you would get “=B$3+C2”. C$3+D2″, and so on. What happened is that you set the row address of 3 as a constant, but not the column address. You can set the column address as a constant by putting the dollar sign in front of the column letter like “=$A3+B2”. Now if you fill the column the number will go up, but when you fill the row the column stays on A. And if you combine them to say you want to use cell A3 no matter what, you would enter “=$A$3+B2”. Now no matter you fill a row or a column, the first term in the formula will not change.

There is a place for each of these ways of locking down addresses, so get used to using all of them.

Filling Multiple Rows and Columns

You are not limited to only filling one row or column at a time. You can fill multiple rows or columns so long as they are contiguous. For example, I want to fill the columns A, B, and C, based on what I put in the first row. Then I would click on cell A1, hold down my Shift key, and click on cell C1. I would see all three cells highlighed, cell A1 would have the heavy black border, and cell C1 would have the fill handle. If I click and drag on the fill handle I can fill all three columns  at once. And it I want to have any or all columns increment by some amount other than 1, I can fill in the first two rows, click on cell A1, then hold down the Shift key, click on cell C2, and now I have highlighted a rectangular block of six cells. I can now click-and-drag to fill all three columns incrementing any way I want.

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