LibreOffice-Suite-Calc/C2/Basic-Data-Manipulation/English

Resources for recording

Basic Data Manipulation

VISUAL CUE	NARRATION
Show Slide Number 1 Title Slide Spoken tutorial on LibreOffice Calc	Welcome to the Spoken tutorial on LibreOffice Calc – Basics of Data Manipulation.
Show Slide Number 2 Learning objectives Introduction to basics of Formula Sorting by Columns Basics of Filtering Data	In this tutorial we will learn about: Introduction to basics of Formula Sorting by Columns Basics of Filtering Data
Show Slide Number 3	Here we are using Ubuntu Linux version 10.04 as our operating system and LibreOffice Suite version 3.3.4
Show Slide Number 4	Let us begin the tutorial by learning about the basic formulas used in LibreOffice Calc. Formulas are equations that use numbers and variables to get a result. In a spreadsheet, the variables are cell locations that hold the data needed for the equation to be completed.
Show Slide Number 5	The most basic arithmetic operations which are performed are addition, subtraction, multiplication and division.
Open “Personal-Finance-Tracker.ods”	Let us open our “Personal-Finance-Tracker.ods” file first.
Point to “Cost”.	In our “personal finance tracker.ods” file, let us see how to add the cost of all the expenses mentioned under the heading, “Cost”.
Type “SUM TOTAL” under “Miscellaneous”. Click on cell referenced as “A8”. Type “7”	We shall give one more heading as “SUM TOTAL” just under “Miscellaneous”. And we shall click on the cell A8 and give the serial number as “7”.
Click on “C8”.	Now lets click on cell number “C8” where we want to display the total of the costs.
Type “=SUM(C3:C7)” in the cell “C8” Press “Enter”. Point to the total under “Cost”.	In order to add all the costs, we’ll type“is equal to SUM” and within braces the range of columns to be added,that is,”C3 colon C7”. Now press “Enter” on the keyboard. You see that all the items under “Cost” gets added.
	Now lets learn how to subtract in Calc.
Point to “House Rent” and “Electricity Bill”. Click on cell referenced as “A9”.	If we want to subtract the cost of “House Rent” and “ Electricity Bill”, and display it in the cell referenced as A9, simply click on the A9 cell first.
Type “=(C3-C4)”. Press “Enter”.	Now in this cell, type “is equal to” and within braces the respective cell references, that is, “C3 minus C4”. Press the “Enter” key on the keyboard.
Point to the result in cell number A9. undo the changes.	We see that the cost in the two cell references get subtracted and the result is displayed in cell number A9. Lets undo the changes.
	Similarly, one can divide and multiply data in different cells.
	Another basic operation in a spreadsheet is finding the “Average” of numbers. Let us see how it is implemented.
Type the heading “Average” just below the heading “SUM TOTAL”.	Lets give the heading as “Average” just below the “SUM TOTAL” cell. Here we want to display the average of the total cost.
Click on cell “C9” Type “=Average(Cost)”	So lets click on the “C9” cell. Now we type “is equal to” Average and Cost within braces.
Press “Enter” Point to the average of the “Cost” undo the changes.	Press the “Enter” key on the keyboard. You see that the average of the “Cost” column gets displayed in the cell. Lets undo the changes.
	Similarly, you can find average of elements in a horizontal row.
	We will learn more about formulae and operators in advanced level tutorials. Let us now learn how to “Sort” data in a Calc spreadsheet.
Show Slide Number 6	Sorting arranges the visible cells on the sheet in any desired manner. In Calc, you can sort the data using upto three criteria, which are then applied one after another. They are handy when you are searching for a particular item and become even more powerful after you have filtered data.
Point to “Costs” Click on cell “Cost”	Lets say, we want to sort the data under the heading “Costs” in the ascending order. So first, we highlight the cells to be sorted by clicking on the cell “Cost”.
Hold down the left mouse button, drag it to the cell which has “2000” typed in it.	Now holding down the left mouse button, drag it along the column till the end of the cell which says “2000”.
	This selects the column which we want to sort.
Click on “Data” >> Click on “Sort” >> “Current Selection”	Now click on the “Data” option in the menu bar and then click on “Sort”. Next Select “Current Selection”
Point to “Sort Criteria” and “Options”.	You see that a dialog box appears with tabs as “Sort criteria” and “Options”.
In the “Sort criteria” tab, select “Cost” in the “Sort by” field. Click on “Ascending”	In the “Sort criteria” tab, select “Cost” in the “Sort by” field. In order to sort the “Cost” in ascending order, click on the “Ascending” option just next to it.
Click on “OK”	Now click on the “OK” button.
Show that the sorting is done in ascending order.	You see that the column gets sorted in the ascending order.
Undo the changes	Similarly, to sort in the descending order, click on “Descending” and then click on the “OK” button. Let us Undo the changes
	Multiple columns can be sorted by first selecting all the columns and then applying the sort options.
Select data under “SN” and “Costs” together.	Lets say, we want to sort the serial numbers as well as the cost. So first select these columns as we did earlier.
Click on “Data”->click on “Sort”	Now click on the “Data” option in the menu bar and then click on “Sort”.
select “Cost” in “Sort by”, then select “SN” in the “Then by” field.	In the dialog box which appears, first select “Cost” in the “Sort by” field. Then select “SN” in the “Then by” field.
Click on “Descending” in both the options->click on the “OK” button.	Click on “ Descending” in both the options near them and then click on the “OK” button.
Show that the data gets sorted in descending order. Undo the changes	You see that both the headings get sorted in the descending order. Lets Undo the changes
Show Slide Number 7	Now lets learn how to filter data in LibreOffice Calc. A filter is a list of conditions that each entry has to meet in order to be displayed.
Click on the cell named “Item”. Click on “Data”->click on “Filter”.	In order to apply a filter in the spreadsheet, lets click on the cell named “Item”. Now click on the “Data” option in the menu bar and then click on “Filter”.
Click on “AutoFilter”.	Click on the “AutoFilter” option in the pop up menu.
Click on the arrow marks in the headings. Click on down arrow on the cell named “Item”.	You see that an arrow mark appears on the headings. Click on the down arrow on the cell named “Item”.
Click on “Electricity Bill” in the menu bar.	Now suppose if you want to display only the data related to “Electricity Bill”. So click on the “Electricity Bill” option.
Point to “Electricity Bill” in the sheet.	You see that only the data related to “Electricity Bill” is displayed in the sheet. The rest of the options are filtered out.
Click in down arrow on the cell heading which contains “Item”->click on “All”.	In order to view all the data, again click on the downward arrow on the cell named ”Item” and click on “All”.
Show all the data in the spreadsheet.	We see that we are now able to view all the data which we had originally written.
	Apart from “AutoFilter”, there are two more filter options namely “Standard Filter” and “Advanced Filter” which we will learn about in the later stages of this series.
SUMMARY Introduction to basics of Formula Sorting by Columns Basics of Filtering Data	This brings us to the end of the Spoken Tutorial on LibreOffice Calc: To summarize, we learned about: Introduction to basics of Formula Sorting by Columns Basics of Filtering Data
Show About Slide About the Spoken Tutorial Project Watch the video available at http://spoken-tutorial.org/What_is_a_Spoken_Tutorial It summarises the Spoken Tutorial project If you do not have good bandwidth, you can download and watch it	Watch the video available at the following link It summarises the Spoken Tutorial project If you do not have good bandwidth, you can download and watch it
Show About Slide Spoken Tutorial Workshops 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@spoken-tutorial.org	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
Show Acknowledgement Slide Acknowledgements 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 http://spoken-tutorial.org/NMEICT-Intro	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
Show About the contributor Slide About the contributor This tutorial has been contributed by ...............................(Name of the translator and narrator) Thanks for joining	This tutorial has been contributed by ...............................(Name of the translator and narrator) Thanks for joining

Contributors and Content Editors

Chandrika