LibreOffice Calc: Cells

All spreadsheets have the same basic structure, a table of rows and columns. Columns are headed up A, B, C, and so on. After Z, the next column is AA, then AB, AC, AD, and so on. The maximum number of columns is 1024. Rows are numbered 1,2,3 and so on, and the maximum number of rows is 1024*1024, or 1,048,576. At this time I am not aware of any plans to increase these numbers, though that could change if competitive pressures make it necessary.

Where a row and column intersect, there is a cell, which is given the address of the column followed by the row, e.g. A1, but never 1A. This is very useful since you can use the contents of a cell in a calculation by simply using the cell address. For example, to add the value of cell B4 to the value in cell C3 and store it, you would write “=B4+C3” in the cell where you want to store the sum. Learning to use cell addresses is extremely important, so get in the habit of doing this at every opportunity.

Contents vs. Results

When you understand cell addresses, you will start to see that an interesting distinction arises between the contents of a cell and the result of a calculation. As an example, let us say that I have the number 4 in cell A3, and the number 5 in cell B3. I want multiply these two numbers and store the results in cell C3. I would go to cell C3 and enter “=A3*B3”, and I would see the number 20. That is good, but is the number 20 actually the contents of the cell C3? No! In fact, the contents of the cell C3 are “=A3*B3”. And if you tried to copy this cell to different sheet, thinking you were copying the number 20, you would quickly discover the problem. In the new sheet, the cell C3 would attempt to multiply whatever was in cells A3 and B3 of that sheet. If both cells were empty you would get an answer of zero, if they both had numbers you would get whatever those numbers multiplied out to, and if one of those cells had a word instead of a number you would get an error that reads “#VALUE!”, which is the general error message whenever you do a calculation that makes no sense. If you try to divide by zero, for instance, you would get this error.

Cell Formats

The most common contents for cells is numbers of course, but you can have other contents as well. In a financial application, you might want to present data by month or by quarter, and this means you would need to have cells that contain words. And when you get into numbers, there are numbers and there are numbers. So let’s take a look at the options available. Go to Format–>Cells, and you will get this window:

 

 

 

 

 

 

 

First, look at the options in the first tab, Numbers. They are:

• All – This combines the results of all the other options, making for a long list to scroll through. I rarely use this since it is much faster to go directly to the category I am interested in.
• User-defined – This is probably empty right now since you probably have not defined anything. But it is good to know it is there if you need it.
• Number – This is for numbers in general, let’s you set the decimal places and whether you want to use a thousands separator, and if you want negative numbers to be red.
• Percent – This formats the cell as a percent.
• Currency – This lets you express your numbers as currency units and let’s you add the currency designation automatically. Mine is in US Dollars, of course, but if you had set your LibreOffice localization for a different country you would probably see those currency units by default. In any case, the Format drop-down gives you all of the options if you need to make a manual change, such as if you are in one country but need to do financial analysis in a currency that is different.
• Date – This gives you the date formats that are standard for your country. Mine is the US standard of Month/Day/Year, but if you are in another country you might see a different standard, such as the much more sensible Day/Month/Year. You can manually change this though in the bottom field “Format Code”
• Time – Similarly, you can set the display of Time values, and use the “Format Code” field at the bottom to tweak the display if needed.
• Scientific – This lets you use Scientific Notation, e.g. numbers expressed as a base and an exponent.
• Fraction – This lets you display numbers as fractions instead of decimals. Note that this only affects the display. The actual calculations are done using decimals. Normally, if you enter a fraction it is converted to the decimal equivalent immediately and displayed that way
• Boolean Value – This lets you get results of TRUE or FALSE in your cell. This is used with functions that let you do logical tests on your data.
• Text – This lets you format the cell as text instead of as a number.

What is a Number?

You may think this is pretty obvious, but it turns out to be more subtle than most people realize. You see, numbers can be used as labels as well as the use in calculations, and we encounter this every day. If you wanted to call me on the phone you would use my telephone number to do this. In the United States our social pension program, called Social Security, identifies each person by a Social Security number that is nine digits long. You may have a number for automobile registration plate, you may have an employee number at your job, and so on. In using spreadsheets, and in using databases, it is very important that you get in the habit of treating these kinds of numbers as text. One of the things that people frequently do is move data in and out of databases via spreadsheets, and this trips up people a lot. Just because you know what the number is doing when you look at it does not mean that the software knows. We still have not perfected the telepathy interface that lets the computer read your mind to figure these things out.

Other Format options

There are other things you can do in formatting your cells that may be of use, so let’s look at the other tabs of this window.

• Font – This is just the standard Font chooser, and is the same as what you already know in Writer. The idea of integrated suites is to do something once and reuse it everywhere.<g>
• Font Effects – Again, this is pretty much the same as in Writer. They were something to use rarely in Writer, and the same applies here. Less is more with these things.
• Alignment – This is where you set the horizontal and vertical alignment of your cells. It also lets you change the way text flows, and has a check box to wrap text within a cell. If the only thing you ever did in a spreadsheet was calculate with numbers, you might not even need most of these features. But the fact is that we use spreadsheets for other purposes, like keeping lists. As a project manager I create spreadsheets to manage issues or keep track of enhancement requests, for instance. And that is where this tab suddenly becomes very important.
• Borders – In general, most people set this for the whole spreadsheet rather than on a cell-by-cell basis, but you can set a format for a cell (or range of cells) separate from what you do for the entire spreadsheet. For example, you might want a thick black line under the last cell in a column of numbers, then put the sum in another cell underneath.
• Background – This lets you use color backgrounds for a cell or a range of cells, and can be very useful for improving the usability of a spreadsheet. Giving the column descriptions a colored background makes them stand out. This is best if you have a color printer, since otherwise when you print it gets converted to gray scale and that is not as useful. Of course, if your spreadsheets will only be viewed electronically that is not a problem.
• Cell Protection – This requires that you have protection turned on the spreadsheet as a whole, but it does let you lock down a cell or range of cells so that no one else can change or edit those cells. This is common in corporate environments where a spreadsheet might get widely distributed (e.g. to submit expense reports) but you don’t want people changing the structure.

You can apply a format to a cell by selecting the cell, then opening the Format Cell properties window and applying the format you want. You can also apply a format to a group of cells by highlighting all of them, then opening the Format Cell properties window and making your selections.

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