Difference between revisions of "LibreOffice-Suite-Calc-6.3/C2/Working-with-Data-in-Calc/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(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...")
 
 
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  
 
|| Under >> Series Type  
Line 136: Line 136:
 
|-
 
|-
 
|| Type '''Sunday''' in the Start '''value''' field.
 
|| 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'''
 
|| Point to '''Increment'''
Line 228: Line 228:
 
|-
 
|-
 
|| Point to the “'''SN'''” column.
 
|| Point to the “'''SN'''” column.
−
|| The '''cells''' get filled automatically with sequential serial numbers between 1 and 7.
+
|| Observe the changes.
 
|-
 
|-
 
|| Slide: Assignment
 
|| Slide: Assignment
Line 348: Line 348:
  
 
Now type '''Expenses '''and press''' Enter.'''
 
Now type '''Expenses '''and press''' Enter.'''
 +
|-
 +
||
 +
||Observe the changes.
 
|-
 
|-
 
|| Press '''Ctrl+Z '''keys
 
|| Press '''Ctrl+Z '''keys
Line 360: Line 363:
 
|| '''Slide''': Summary
 
|| '''Slide''': Summary
 
|| In this tutorial, we learnt to:
 
|| 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.
 
* Share the same content on all '''sheets '''of the same file.
 
* Remove '''data''', replace '''data''', change part of the '''data '''in a '''cell'''.
 
* Remove '''data''', replace '''data''', change part of the '''data '''in a '''cell'''.
Line 367: Line 370:
 
|| '''Slide: Assignment '''
 
|| '''Slide: Assignment '''
 
|| As an assignment
 
|| As an assignment
−
* Open “'''spreadsheet-practice.ods'''” sheet.  
+
* Open “'''spreadsheet-practice.ods'''” file.  
 
* Create a '''Month''' column besides '''Time.'''
 
* 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
 
|| '''Slide''': Thank you
−
| | This is '''Arvind''' from''' IIT Bombay''' signing off. Thank you for watching.
+
| | 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.
 
|-
 
|-
 
|}
 
|}

Latest revision as of 20:50, 4 July 2020

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. Observe the changes.
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.

Observe the changes.
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” file.
  • 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 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.

Contributors and Content Editors

Arvindpillai90, Nancyvarkey

Retrieved from "https://script.spoken-tutorial.org/index.php?title=LibreOffice-Suite-Calc-6.3/C2/Working-with-Data-in-Calc/English&oldid=52833"