LibreOffice-Calc-on-BOSS-Linux/C2/Basic-Data-Manipulation/English-timed
From Script | Spoken-Tutorial
| 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 GNU/Linux 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:33 | Formulas are equations that use numbers and variables to get a result. |
| 00:39 | 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:01 | 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:18 | And we shall click on the cell A8 and give the serial number as “7”. |
| 01:24 | Now, let's click on cell number “C8” where we want to display the total of the costs. |
| 01:31 | 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:43 | Now press “Enter” on the keyboard. |
| 01:46 | You see that all the items under “Cost” get added. |
| 01:50 | Now let's learn how to subtract in Calc. |
| 01:54 | 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:16 | Press the Enter key on the keyboard. |
| 02:19 | We see that the cost in the two cell references get subtracted and the result is displayed in cell number A9. |
| 02:28 | Let's undo the changes. |
| 02:31 | Similarly, one can divide and multiply data in different cells. |
| 02:36 | Another basic operation in a spreadsheet is finding the average of numbers. |
| 02:42 | Let us see how it is implemented. |
| 02:45 | Let's 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, let's click on the “C9” cell. |
| 02:58 | Now, we type: “is equal to” Average and Cost within braces. |
| 03:03 | 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 | Let's undo the changes. |
| 03:14 | Similarly, you can find average of elements in a horizontal row. |
| 03:19 | We will learn more about formulae and operators in advanced level tutorials. |
| 03:24 | Let us now learn how to sort data in a Calc spreadsheet. |
| 03:29 | Sorting arranges the visible cells on the sheet in any desired manner. |
| 03:34 | In Calc, you can sort the data using up to three criteria which are then applied one after another. |
| 03:42 | They are handy when you are searching for a particular item and become even more powerful after you have filtered data. |
| 03:51 | Let's 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:11 | 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:23 | 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:36 | In order to sort the “Cost” in ascending order, click on the “Ascending” option just next to it. |
| 04:43 | Now click on the OK button. |
| 04:46 | You see that the column gets sorted in the ascending order. |
| 04:50 | Similarly, to sort in the descending order, click on Descending and then click on the OK button. |
| 04:58 | Let us Undo the changes. |
| 05:01 | Multiple columns can be sorted by first selecting all the columns and then applying the sort options. |
| 05:09 | Let's say, we want to sort the serial numbers as well as the cost. |
| 05:13 | So, first select these columns as we did earlier. |
| 05:17 | Now, click on the Data option in the menu bar and then click on Sort. |
| 05:23 | In the dialog-box which appears, first select Cost in the Sort byfield. |
| 05:29 | Then select “SN” in the Then by field. |
| 05:34 | Click on Descending in both the options near them and then click on the OK button. |
| 05:40 | You see that both the headings get sorted in the descending order. |
| 05:45 | Let's Undo the changes. |
| 05:48 | Now, let's learn how to filter data in LibreOffice Calc. |
| 05:52 | A filter is a list of conditions that each entry has to meet in order to be displayed. |
| 05:57 | In order to apply a filter in the spreadsheet, let's click on the cell named “Item”. |
| 06:05 | 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:15 | You see that an arrow mark appears on the headings. |
| 06:19 | 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:32 | You see that only the data related to “Electricity Bill” is displayed in the sheet. |
| 06:39 | 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:51 | We see that we are now able to view all the data which we had originally written. |
| 06:58 | 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:10 | 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:29 | It summarizes the Spoken Tutorial project. |
| 07:32 | If you do not have good bandwidth, you can download and watch it. |
| 07:36 | The Spoken Tutorial Project team: |
| 07:40 | Conducts workshops using spoken tutorials. |
| 07:42 | Gives certificates for those who pass an online test. |
| 07:46 | For more details, please write to: contact@spoken-tutorial.org |
| 07:52 | Spoken Tutorial Project is a part of the Talk to a Teacher project. |
| 07:57 | It is supported by the National Mission on Education through ICT, MHRD, Government of India. |
| 08:05 | 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 DesiCrew Solutions Pvt. Ltd. Thanks for joining. |
