LibreOffice-Calc-on-BOSS-Linux/C2/Basic-Data-Manipulation/English-timed

From Script | Spoken-Tutorial
Jump to: navigation, search
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.

Contributors and Content Editors

PoojaMoolya, Sandhya.np14