Calc: Import and present a data series

In this article, the friend Gaius Baltar presents us with a series of Tricks for Calc, the spreadsheet editing tool for Libre / OpenOffice.

When faced with a new program we tend to iterate through the buttons without a single guide, because we will use it when the first problem arises. Although we laugh at the previous sentence, 99% of the average user falls into this error taking for good the "user level office automation" of their curriculum.

This post is intended for those novice users who want to start using Calc, the Libreoffice spreadsheet, but are not willing to read the concise but complete 44 pages of «First steps with Calc».

Being a fairly broad topic, we will focus on how to import data from a text file and prepare the sheet for an acceptable presentation. In a next post we will stop to represent them graphically.

Preparations

The program from which I obtained the data offers me tables with the results in American scientific format, using the '.' as a decimal separator. In order not to change the regional mode of Libreoffice, I have decided to change the '.' by ',' using the command «replace» [ctrl + H] from Gedit.

This is the original file:

In which I replace the '.' by ','

Leaving the series in this way:

I have several of these files. In order not to saturate the Calc sheet, since I intend to insert multiple charts as well, I'll create a sheet for each data series.

Data import

By inertia we would go to the data menu, but in Calc it is different. To import data into a file of type .txt, .csv and similar, we will call it from the menu 'File> Open'. As we see in the screenshot, there is nothing complicated about it.

In my case, I start from a text file that has separate columns, so Calc automatically recognizes the "Fixed width" separation. If it was not always the same width, you could move the red delimiters of the lower graphic. In case we had a CSV that separates the rows with symbols, we would use the "separated by" option.

After 'OK', we will observe that it opens in a new calc file. If we now copy all the data to our own file, we can use the imported file to recover any information that we have lost if we make mistakes in our sheet.

Cell format

Now I would recommend working in two windows when you have to move data, but each one with what is most comfortable. It is not very annoying and it is quite practical if we use the zooms [CTRL + mouse wheel].

Format cells:

Once the data is copied to my file, I will adapt the data format. First I'm going to specify that the first column is numbers (from 10 to 10, up to a thousand), so I'm not interested in it being scientific numbering.

The first thing is to tell Calc that these cells are of a scientific type so that it recognizes them as such, and then I will repeat the process to specify that I want them in numeric format. All this is achieved with right click and 'Format Cells' (although we also have predefined values ​​in buttons on the toolbar).

Note that if we select several sheets at the same time (holding CTRL while pressing the corresponding tabs), all the changes we make in the active sheet will be made in the rest of the selected ones.

Let's go back to my column A. The most normal thing is that, by default, Calc interprets it to be numeric. With this format we cannot transform it (since it really is not), so we select 'Scientific'.

Once accepted, we can transform this column to the desired format. To do this I repeat the 'Format Cells' and select numeric (without decimals, which is the default option). Et voilá!

To interpret the next column I want a numeric format with two decimal places, so I repeat the steps: 'Format Cells> Scientific> OK' and 'Format Cells> Numeric'.

Obtaining this result:

With this process I already have the data ready for my interpretations. I only need one more: column C, which I have to change units. In my case I multiply it by a thousand. Just in case, I do this in a new column and keep the old one as column C.

Column D as a function of column C:

To do this, I right click on D and select 'Insert Column'. I select the first cell (D3) and press the '=' to write the function. I select the cell I want to multiply (C3), type the function: 'C3 * 1000' and press "enter".

To apply the same calculation to all cells in this column, I select D3 and, holding down on the lower right box of this cell, I expand the selection to the last data field (D1002 in my case). If you click on any cell in column D you will see that it multiplies the adjacent cell C by 1000.

Visually spruce up

After the data import is finished, I adapt some more column and make the table more visually attractive. We will help ourselves with the borders and the formats of styles, alignment and colors (the "pastel tones" always triumph, do not go overboard with the garish colors or use black, please ...). Other tools are 'Join Cells' to group common data and 'Optimal column width' to aid visualization.

Right now my table looks like this, but before we finish, let's look at an important Calc tool.

Immobilize

With this utility (located in 'View> Freeze'), we can view certain cells even when we are in another place in the spreadsheet. If I freeze my first two rows (which correspond to the names and units of the values) they will always stay at the top.

This can be done horizontally and vertically. To achieve this, we will select the lower cell and to the right of the limit to be established. In my example I have selected cell A3. On the other hand, if I wanted to also fix column A to be visible at all times (despite scrolling horizontally), I would have to select cell B3 and click on the menu 'View> Freeze'.