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

Resources for recording

Basic Data Manipulation

Time	Narration
00:00	Welcome to the Spoken tutorial on LibreOffice Calc – Basics of Data Manipulation.
00:07	In this tutorial, we will learn about:
00:09	* Introduction to basics of Formula
00:12	* Sorting by Columns
00:15	* Basics of Filtering Data.
00:17	Here we are using Ubuntu Linux version 10.04 as our operating system and LibreOffice Suite version 3.3.4.
00:27	Let us begin the tutorial by learning about the basic formulas used in LibreOffice Calc.
00:35	Formulas are equations that use numbers and variables to get a result.
00:41	In a spreadsheet, the variables are cell locations that hold the data needed for the equation to be completed.
00:47	The most basic arithmetic operations which are performed are addition, subtraction, multiplication and division.
00:56	Let us open our “Personal-Finance-Tracker.ods” file first.
01:02	In our “personal finance tracker.ods” file, let us see how to add the cost of all the expenses mentioned under the heading, “Cost”.
01:13	We shall give one more heading as “SUM TOTAL” just under “Miscellaneous”.
01:19	And we shall click on the cell A8 and give the serial number as “7”.
01:25	Now let's click on cell number “C8” where we want to display the total of the costs.
01:32	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”.
01:44	Now press Enter on the keyboard.
01:47	You see that all the items under “Cost” get added.
01:51	Now let's learn how to subtract in Calc.
01:55	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.
02:06	Now in this cell, type “is equal to” and within braces the respective cell references, that is, “C3 minus C4”.
02:17	Press the “Enter” key on the keyboard.
02:20	We see that the cost in the two cell references get subtracted and the result is displayed in cell number A9.
02:29	Lets undo the changes.
02:32	Similarly, one can divide and multiply data in different cells.
02:37	Another basic operation in a spreadsheet is finding the “Average” of numbers.
02:43	Let us see how it is implemented.
02:45	Lets give the heading as “Average” just below the “SUM TOTAL” cell.
02:50	Here we want to display the average of the total cost.
02:55	So lets click on the “C9” cell.
02:58	Now we type “is equal to” Average and Cost within braces.
03:04	Press the “Enter” key on the keyboard.
03:07	You see that the average of the “Cost” column gets displayed in the cell.
03:11	Lets undo the changes.
03:15	Similarly, you can find average of elements in a horizontal row.
03:20	We will learn more about formulae and operators in advanced level tutorials.
03:25	Let us now learn how to “Sort” data in a Calc spreadsheet.
03:30	Sorting arranges the visible cells on the sheet in any desired manner.
03:35	In Calc, you can sort the data using upto three criteria, which are then applied one after another.
03:43	They are handy when you are searching for a particular item and become even more powerful after you have filtered data.
03:51	Lets say, we want to sort the data under the heading “Costs” in the ascending order.
03:57	So first, we highlight the cells to be sorted by clicking on the cell “Cost”.
04:03	Now holding down the left mouse button, drag it along the column till the end of the cell which says “2000”.
04:12	This selects the column which we want to sort.
04:15	Now click on the “Data” option in the menu bar and then click on “Sort”.
04:21	Next Select “Current Selection”
04:24	You see that a dialog box appears with tabs as “Sort criteria” and “Options”.
04:31	In the “Sort criteria” tab, select “Cost” in the “Sort by” field.
04:37	In order to sort the “Cost” in ascending order, click on the “Ascending” option just next to it.
04:44	Now click on the “OK” button.
04:47	You see that the column gets sorted in the ascending order.
04:51	Similarly, to sort in the descending order, click on “Descending” and then click on the “OK” button.
04:59	Let us Undo the changes
05:02	Multiple columns can be sorted by first selecting all the columns and then applying the sort options.
05:09	Lets say, we want to sort the serial numbers as well as the cost.
05:14	So first select these columns as we did earlier.
05:18	Now click on the “Data” option in the menu bar and then click on “Sort”.
05:24	In the dialog box which appears, first select “Cost” in the “Sort by” field.
05:30	Then select “SN” in the “Then by” field.
05:35	Click on “ Descending” in both the options near them and then click on the “OK” button.
05:43	You see that both the headings get sorted in the descending order.
05:47	Lets Undo the changes
05:49	Now lets learn how to filter data in LibreOffice Calc.
05:53	A filter is a list of conditions that each entry has to meet in order to be displayed.
06:00	In order to apply a filter in the spreadsheet, lets click on the cell named “Item”.
06:07	Now click on the “Data” option in the menu bar and then click on “Filter”.
06:12	Click on the “AutoFilter” option in the pop up menu.
06:16	You see that an arrow mark appears on the headings.
06:20	Click on the down arrow on the cell named “Item”.
06:24	Now suppose if you want to display only the data related to “Electricity Bill”.
06:29	So click on the “Electricity Bill” option.
06:34	You see that only the data related to “Electricity Bill” is displayed in the sheet.
06:40	The rest of the options are filtered out.
06:43	In order to view all the data, again click on the downward arrow on the cell named ”Item” and click on “All”.
06:52	We see that we are now able to view all the data which we had originally written.
06:59	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.
07:11	This brings us to the end of the Spoken Tutorial on LibreOffice Calc:
07:15	To summarize, we learned about:
07:18	Introduction to basics of Formula
07:21	Sorting by Columns
07:23	Basics of Filtering Data
07:26	*Watch the video available at the following link
07:30	*It summarises the Spoken Tutorial project
07:33	*If you do not have good bandwidth, you can download and watch it
07:37	The Spoken Tutorial Project Team
07:40	*Conducts workshops using spoken tutorials
07:43	*Gives certificates for those who pass an online test
07:47	*For more details, please write to contact@spoken-tutorial.org
07:53	*Spoken Tutorial Project is a part of the Talk to a Teacher project
07:58	*It is supported by the National Mission on Education through ICT, MHRD, Government of India
08:06	*More information on this Mission is available at
08:08	*http:spoken-tutorial.org slash NMEICT hyphen Intro
08:16	This tutorial has been contributed by ...............................(Name of the translator and narrator) And this is -----------------------(name of the recorder) from --------------------------(name of the place)signing off. Thanks for watching. Thanks for joining

Contributors and Content Editors

Minal, PoojaMoolya, Pratik kamble, Sandhya.np14