Welcome to the spoken tutorial on Working with data in Calc.
In this tutorial, we will learn to: Autofill data using Fill tools
Share the same content on all sheets of the same spreadsheet.
Remove data, replace data, change part of the data in a cell.
This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
Entering data into a spreadsheet can be very labor-intensive.
So 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 the mouse.
However Calc also has other tools for automating input, especially for repetitive data.
One such tool is the Fill tool.
This can input data into multiple sheets of the same spreadsheet in one go.
Open the Personal-Finance-Tracker.ods file.
In our file, let's copy the data under the heading Cost to the adjacent cells.
Click on the cell which contains the entry 6000 under Cost.
Hold the left mouse button, drag it till the end of the cell which contains the cost entry 2000.
Also select the cells adjacent to the right of the selected cells along with it.
In these adjacent cells we will copy the data.
Now release the left mouse button.
Next, click on the Sheet menu in the menu bar and go to the Fill Cells submenu.
Click on the Fill Right option from the submenu.
Alternatively, you can click on the dropdown of the Column icon in the Standard toolbar. And then click Fill Right option
We see that the data under the heading Cost gets copied into the adjacent cells. Let us undo the changes.
A more complex use of the Fill tool is used for filling some series as data in the sheets
Calc provides default lists for the full and abbreviated Days of the week and The months of the year.
It also enables the users to create their 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 Sheet menu in the menu bar and then on Fill Cells submenu.
Select Fill Series option from the submenu.
The Fill Series dialog box appears.
Now under Series Type, click on the AutoFill option.
In the Start value field, we’ll type the first day of the week, that is Sunday.
The Increment field is already set as 1.
Now click on the OK button.
We see that all the days are automatically displayed in the cells.
We can enter only weekdays and months by this method as these are pre-defined in Calc.
Let me demonstrate another method for auto-filling sequential data.
Type Sunday in another 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.
We see a small black box at the bottom-right-hand corner of the cell.
Click on this box with the mouse.
Drag downwards till you see Saturday in the display box on the right. Release the mouse button.
The cells get autofilled with the weekdays automatically.
This method works for all data that are sequential.
Let's undo these changes.
Next, we’ll create an autofill series of numbers by entering the start, end and increment values.
We will first delete the serial numbers already entered in the cells A2 to A8.
Now click on the Sheet in the menu bar. Then on the Fill Cells and Fill Series option.
In the Fill Series dialog box, under Series Type click on the Linear option.
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 7.
Now we’ll set the Increment value as 1, if it is not already 1.
Then click on the OK button.
Observe the changes.
As an assignment, repeat the above steps with an increment value of 5.
Observe the changes and then undo them.
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.
In Calc the same data can be entered in the same cell location on multiple sheets.
This means, there’s no need to enter the same data on each sheet separately.
One can enter them in all the sheets at once.
Let’s learn how to do this.
In the Personal-Finance-Tracker.ods file, our entire data is on Sheet 1.
Now we want Sheet 2 and Sheet 3 to show the same data as in Sheet 1.
To do that, click on the Edit menu in the menu bar and then click on Select.
Now click on Select sheets.
The Select Sheets dialog box appears.
Using the Shift key, 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 type something in Sheet 1.
For example, in cell 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 the sheets, cell F12 displays the exact same data.
Let us undo these changes.
Next we will learn about different ways in which we can delete and edit data in cells.
To delete data without removing any of the existing cell formatting, first select the cell.
The data of the cell is displayed in the Input line field.
Click on the Input line once.
Now press the Backspace key on the keyboard.
The data gets deleted.
Let us undo these changes.
To replace data in a cell, select the cell and type over the old data.
The new data will retain the cell’s original formatting.
Let us undo these changes.
We can change part of the data in a cell also, without removing all the contents.
To do so, just double-click on the cell.
Then select the word Rent by double-clicking on it. Now type Expenses and press Enter.
Observe the changes.
Let us undo these changes.
Lastly, save and close the file.
This brings us to the end of this tutorial. Let us summarize.
In this tutorial, we learnt to:
Autofill data using Fill tools.
Share the same content on all sheets of the same file.
And Remove data, replace data, change part of the data in a cell.
As an assignment Open “spreadsheet-practice.ods” file.
Create a Month column besides Time.
Use the Fill Series option to auto-fill till the month of May.
The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
Please post your timed queries in this forum.
Spoken Tutorial project are funded by MHRD, Govt. of India.
This tutorial was originally contributed by DesiCrew Solutions Private Limited in 2011. This is Arvind along with the Spoken Tutorial team from IIT Bombay signing off. Thank you for watching.