Nancyvarkey at 15:20, 4 July 2020

2020-07-04T15:20:40Z

Arvindpillai90: Created page with "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..."

2020-07-03T21:26:28Z

Created page with "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..."

New page

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

{| border="1"
|-
|| '''VISUAL CUE'''
| align=center| '''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.
|-
|}

@@ Line 52: / Line 52: @@
 ||
-Select cells with “'''6000'''” to “'''2000'''” under '''cost'''
+Select cells with “'''6000'''” to “'''2000'''” under '''Cost'''
-|| Click on the '''cell''' which contains the entry '''6000''' under '''cost'''.
+|| Click on the '''cell''' which contains the entry '''6000''' under '''Cost'''.
@@ Line 110: / Line 110: @@
-|| Now let’s insert a new heading named '''Days''' in our '''sheet'''.
+|| Now let’s insert a new '''heading''' named '''Days''' in our '''sheet'''.
@@ Line 122: / Line 122: @@
-|| Now click on the '''Sheet''' '''menu''' in the '''menu''' '''bar''' and then on '''Fill Cells''' submenu.
+|| Now click on the '''Sheet menu''' in the '''menu bar''' and then on '''Fill Cells''' submenu.
-Select '''Fill''' '''Series''' option from the submenu.
+Select '''Fill Series''' option from the submenu.
 |-
-|| Point to '''Fill''' '''Series'''.
+|| Point to '''Fill Series'''.
-|| The '''Fill''' '''Series '''dialog box appears.
+|| The '''Fill Series '''dialog box appears.
 |-
 || Under >> Series Type
@@ Line 136: / Line 136: @@
 |-
 || Type '''Sunday''' in the Start '''value''' field.
-|| In the '''Start''' '''value''' field, we’ll type the first day of the week, that is '''Sunday'''.
+|| In the '''Start value''' field, we’ll type the first day of the week, that is '''Sunday'''.
 |-
 || Point to '''Increment'''
@@ Line 228: / Line 228: @@
 |-
 || Point to the “'''SN'''” column.
-|| The '''cells''' get filled automatically with sequential serial numbers between 1 and 7.
+|| Observe the changes.
 |-
 || Slide: Assignment
@@ Line 348: / Line 348: @@
 Now type '''Expenses '''and press''' Enter.'''
 |-
 || Press '''Ctrl+Z '''keys
@@ Line 360: / Line 363: @@
 || '''Slide''': Summary
 || In this tutorial, we learnt to:
-* Autofill '''data''' using '''Fill''' '''tools.'''
+* 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'''.
@@ Line 367: / Line 370: @@
 || '''Slide: Assignment '''
 || As an assignment
-* Open “'''spreadsheet-practice.ods'''” sheet.
+* Open “'''spreadsheet-practice.ods'''” file.
 * Create a '''Month''' column besides '''Time.'''
-* Use the '''Fill''' '''Series''' option to auto-fill till the month of '''May'''.
+* Use the '''Fill Series''' option to auto-fill till the month of '''May'''.
 |-
@@ Line 391: / Line 394: @@
 |-
 || '''Slide''': Thank you
-| | This is '''Arvind''' from''' IIT Bombay''' signing off. Thank you for watching.
+| | This tutorial was originally contributed
 |-
 |}

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

Nancyvarkey at 15:20, 4 July 2020

Arvindpillai90: Created page with "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..."