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

From Script | Spoken-Tutorial
Jump to: navigation, search

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 Let's 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 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: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 Let's 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 up to 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 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: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 Let's 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 Let's Undo the changes.
05:49 Now, let's 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, let's 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 summarizes 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 spoken hyphen tutorial dot org slash NMEICT hyphen Intro.
08:16 This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd.

Thanks for joining.

Contributors and Content Editors

Minal, PoojaMoolya, Pratik kamble, Sandhya.np14