LibreOffice-Suite-Calc-6.3/C2/Working-with-Data-in-Calc/English

Title: Working with Data

Author: Arvind PillaiKeywords: LibreOffice,Calc,Ubuntu,Using Fill Tools in Calc,Using Selection lists in Calc,Sharing contents between sheets in Calc,Removing data in Calc,Replacing data in Calc,Changing part of data in Calc

VISUAL CUE	NARRATION
Slide: Title Slide	Welcome to the spoken tutorial on Working with data in Calc.
Slide: Learning objectives	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.
Slide: System Requirements	This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
Slide: Automated Input of Data	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.
Slide: Automated Input of Data	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 Personal-Finance-Tracker.ods	Open the Personal-Finance-Tracker.ods file.
Point to Cost Column	In our file, let's copy the data under the heading Cost to the adjacent cells.
Select cells with “6000” to “2000” under cost	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.
Select the cells adjacent to the right.	Also select the cells adjacent to the right of the selected cells along with it.
	In these adjacent cells we will copy the data.
Release the left mouse after selecting the cells.	Now release the left mouse button.
Click on Sheet >> click on Fill Cells Click on Fill Right.	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.
Click >> Column Icon Click >> Fill Right Option	Alternatively, you can click on the dropdown of the Column icon in the Standard toolbar. And then click Fill Right option
Point to the copied data Undo the changes.	We see that the data under the heading Cost gets copied into the adjacent cells. Let us undo the changes.
Slide: Fil Tool	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.
Insert new row besides Cost and type heading as Days	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 seven consecutive cells below the heading Days	Select the first seven cells below the heading Days.
Click on Sheet>>Fill Cells>>Fill Series.	Now click on the Sheet menu in the menu bar and then on Fill Cells submenu. Select Fill Series option from the submenu.
Point to Fill Series.	The Fill Series dialog box appears.
Under >> Series Type Click on AutoFill.	Now under Series Type, click on the AutoFill option.
Type Sunday in the Start value field.	In the Start value field, we’ll type the first day of the week, that is Sunday.
Point to Increment	The Increment field is already set as 1.
Click on OK	Now click on the OK button.
Point to the Days column.	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.
Only Narration	Let me demonstrate another method for auto-filling sequential data.
Type Sunday in cell D11 >> press Enter.	Type Sunday in another cell and press Enter. This will shift the focus to the next cell in the column.
Click on cell D11 Sunday Point to the black box on the bottom right of cell D11.	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 Drag it downwards till you see Saturday Release the mouse button.	Click on this box with the mouse. Drag downwards till you see Saturday in the display box on the right. Release the mouse button.
Point to the cells	The cells get autofilled with the weekdays automatically. This method works for all data that are sequential.
Press Ctrl + Z	Let's undo these changes.
Only Narration Delete the serial number 1 to 7 below SN.	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.
Click on Sheet >> Fill Cells >> Fill Series	Now click on the Sheet in the menu bar. Then on the Fill Cells and Fill Series option.
Point to Fill Series dialog box >> Click on Linear.	In the Fill Series dialog box, under Series Type click on the Linear option.
Type 1 in Start value.	In the Start value field, we will type the first serial number, that is 1.
Type 7 in End value.	In the End value field, we will type the last value to be entered as 7.
Type 1 in Increment value. Click on OK	Now we’ll set the Increment value as 1, if it is not already 1. Then click on the OK button.
Point to the “SN” column.	The cells get filled automatically with sequential serial numbers between 1 and 7.
Slide: Assignment	As an assignment, repeat the above steps with an increment value of 5. Observe the changes and then undo them.
Only Narration:	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.
Slide: Share content between sheets	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.
Point to Sheet 1 where our data is entered. Point to the Sheet 2 and Sheet 3 tabs at the bottom.	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.
Click on Edit >> Select	To do that, click on the Edit menu in the menu bar and then click on Select.
Click on Select Sheets.	Now click on Select sheets.
Point to Select Sheets dialog box.	The Select Sheets dialog box appears.
Press and hold Shift key and select Sheet 1, Sheet 2, Sheet 3 Click on OK.	Using the Shift key, select the options Sheet 1, Sheet 2, and Sheet 3. And then click on the OK button.
Point to Sheet 1	This takes us back to Sheet 1. Now let's type something in Sheet 1.
Type in a cell F12.	For example, in cell F12 we’ll type “This will be displayed on multiple sheets”.
Click on Sheet 2 and Sheet 3 tab below.	Now click on the Sheet 2 and the Sheet 3 tab one after the other.
Point to Cell F12 in Sheet 2 and Sheet 3	We see that in each of the sheets, cell F12 displays the exact same data.
Undo the changes.	Let us undo these changes.
Only Narration:	Next we will learn about different ways in which we can delete and edit data in cells.
Click on cell A2 which contains 1.	To delete data without removing any of the existing cell formatting, first select the cell.
Point to the Input line. Click on the Input Line	The data of the cell is displayed in the Input line field. Click on the Input line once.
Press backspace on the Input line. Point to the Input line.	Now press the Backspace key on the keyboard. The data gets deleted.
Press Ctrl+Z keys	Let us undo these changes.
Click on cell A2 which contains 1 and type 23. Click any other cell on the sheet >> Point to cell A2	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.
Press Ctrl+Z keys	Let us undo these changes.
Only narration	We can change part of the data in a cell also, without removing all the contents.
Double click on cell B3	To do so, just double-click on the cell.
Double click on word Rent >> Type Expenses >> press Enter Point to cell B3	Then select the word Rent by double-clicking on it. Now type Expenses and press Enter.
Press Ctrl+Z keys	Let us undo these changes.
Press Ctrl + S >> close the file	Lastly, save and close the file.
Only narration	This brings us to the end of this tutorial. Let us summarize.
Slide: Summary	In this tutorial, we learnt to: Autofill data using Fill tools. Share the same content on all sheets of the same file. Remove data, replace data, change part of the data in a cell.
Slide: Assignment	As an assignment Open “spreadsheet-practice.ods” sheet. Create a Month column besides Time. Use the Fill Series option to auto-fill till the month of May.
Slide: About Spoken Tutorial Project	The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
Slide: Spoken Tutorial Workshops	The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
Slide: Forums	Please post your timed queries in this forum.
Slide: Acknowledgement	Spoken Tutorial project are funded by MHRD, Govt. of India.
Slide: Thank you	This is Arvind from IIT Bombay signing off. Thank you for watching.

Contributors and Content Editors

Arvindpillai90, Nancyvarkey