Welcome to the Spoken tutorial on LibreOffice Calc – Working with data.
In this tutorial, we will learn about:
Speed up using Fill tools and Selection lists.
Sharing content between sheets.
Removing data, replacing data and changing part of a data.
Here we are using:Ubuntu Linux version 10.04 andLibreOffice Suite version 3.3.4.
Entering data into a spreadsheet can be very labor-intensive but Calc provides several tools for making it considerably easier.
The most basic ability is to drag and drop the contents of one cell to another with a mouse.
But Calc also includes several other tools for automating input, especially of repetitive material.
These tools are namely- Fill tool, Selection lists.
These can input information into multiple sheets of the same document.
We will learn about each one of them one by one.
Let us open our “Personal-Finance-Tracker.ods” file.
The Fill tool is a useful method for duplicating the contents in the sheet.
In our “Personal-Finance-Tracker.ods” file, let's say- we want to copy the data under the heading Cost to the adjacent cells.
Then, first select all the data which needs to be copied by clicking on the cell which contains the entry “6000”.
Now, holding down the left mouse button, drag the mouse till the end of the cell which contains the cost entry “2000”.
Also, select the cells where we want to copy the data.
Now, release the left mouse button.
Click on the Edit option in the menu bar and then click on the Fill option.
In the pop-up menu, click on the Right option.
You see that the data under the heading “Cost” gets copied to the adjacent cells.
Let us undo the changes.
A more complex use of the Fill tool is to use it for filling some series as data in sheets.
Calc provides default lists for the full and abbreviated days of the week and the months of the year.
It also enables the user to create his own lists.
Now, let's insert a new heading named “Days” in our sheet.
Under this, we will display the seven days of the week automatically.
Select the first seven cells below the heading “Days”.
Now, click on the Edit option in the menu bar and go to the Fill option.
Click on the Series option in the menu.
You see that a dialog-box appears with the heading as “Fill Series”.
Now, under the heading- “Series type”, click on the “AutoFill” option.
In the “Start value” field, we type our first day of the week, that is “Sunday”.
The increment is already set as “1”.Now, click on the OK button.
You see that the days are automatically entered into the cells.
You can enter only weekdays, month or year by the same method as they are pre-defined in Calc.
Another method for auto-filling of sequential data is as follows-
Type “Sunday” in a cell and press Enter. This will shift the focus to the next cell in the column.
Go back to the cell where “Sunday” was typed. You will now see a small black box at the bottom-right-hand corner of the cell.
Click on this box with the mouse.
Drag it downwards till you see Saturday in the display box on the right.
Release the mouse button.
The cells get filled with the weekdays automatically.
This trick works for all data that are sequential. Let us undo the changes.
You can also create a one-time fill series for numbers by entering the start, end and the increment values.
To demonstrate this, we will first delete the serial numbers already entered in the cells “A1” to “ A7”.
After deleting the numbers, select the cells referenced as “A2” to “A7” again.
Now, click on the Edit in the menu bar and then on Fill and Series option.
A dialog-box appears in front of us.Now, click on the Linear option, under the heading “Series type”, if it is not selected by default.
In the “Start value” field, we will type the first serial number- that is “1”.
In the “End value” field, we will type the last value to be entered as “6”.
Now, we set the “Increment” value as “1” and finally click on the OK button.
We see that the cells automatically get filled with the sequential serial numbers.
In all these cases, the Fill tool creates only a momentary connection between the cells. Once they are filled, the cells have no further connection with one another.
Apart from Fill tools, there is one more speed up tool called “Selection lists” which is limited to using only text.
We will discuss it in the later tutorials of this series.
After learning about the “Fill tools” and “Selection lists”, we will now learn how to share content between sheets.
Calc enables a user to enter the same information in the same cell on multiple sheets.
This means, instead of entering the same list on each sheet individually, you can enter it in all the sheets at once.
In our “Personal-Finance-Tracker.ods” file, our entire data is on Sheet 1.
Now, we want “Sheet 2” as well as “Sheet 3” to show the same data as in Sheet 1.
So, we click on the Edit option in the menu bar and then click on the “Sheet” option.
Now, click on Select.
Now, in the dialog-box which appears, using shift key we select the options Sheet 1, “Sheet 2” and Sheet 3.
And then click on the OK button.
This takes us back to Sheet 1.
Now, let's enter some data in “Sheet 1”.
For example- in the cell referenced as “F12”, we’ll type “This will be displayed on multiple sheets”.
Now, click on the “Sheet 2” and the “Sheet 3” tab one after the other.
We see that in each of these sheets, the cell referenced as “F12” contains the same data.
Let us undo the changes.
Next, we will learn about different ways in which we can delete and edit data in the cells.
To delete data without removing any of the formatting of the cell, just select a cell.
You see that the data of the cell is displayed in the “Input line” field.
Now, press the Backspace button on the keyboard.
You see that the data gets deleted.
Let us undo the changes.
To replace the data in a cell, simply select the cell and type over the old data.
The new data will retain the original formatting.Let us undo the changes.
In order to change a part of the data in a cell, without removing all of the contents, just double-click on the cell.
Now, by navigating the cursor, you can edit the cell as per your requirement.
Let us undo the changes.
This brings us to the end of the Spoken Tutorial on LibreOffice Calc:
To summarize, we learned about:
Speed up using Fill tools and Selection lists.
Sharing content between sheets.
Removing data, replacing data, changing a part of the data.
Watch the video available at the following link.
It summarizes the Spoken Tutorial project.
If you do not have good bandwidth, you can download and watch it.
The Spoken Tutorial project team:
Conducts workshops using spoken tutorials.
Gives certificates for those who pass an online test.
For more details, please write to:contact at spoken hyphen tutorial dot org.
Spoken Tutorial project is a part of the Talk to a Teacher project.
It is supported by the National Mission on Education through ICT, MHRD, Government of India.
More information on this mission is available at:
spoken hyphen tutorial dot org slash NMEICT hyphen Intro.
This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd.Thanks for joining.