# LibreOffice-Suite-Calc-6.3/C2/Basic-Data-manipulation-in-Calc/English-timed

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
 TIME NARRATION 00:01 Welcome to the spoken tutorial on Basic Data Manipulation in Calc. 00:07 In this tutorial, we will learn how to: 00:11 Use some basic Formula in Calc 00:15 Sort by columns and Filter data 00:20 This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5 00:34 What is a formula? 00:36 A formula is an equation that uses numbers and variables to get a result. 00:43 The variables are cell locations that hold the data needed for the equation. 00:50 Basic Arithmetic Operations First, we’ll learn how to perform some basic arithmetic calculations in Calc. 01:00 That is Addition, subtraction, multiplication and division. 01:07 Open the Personal-Finance-Tracker.ods file. 01:12 Let’s sum up the cost of all the expenses mentioned under the heading Cost. 01:19 We will type Sum Total just under Miscellaneous. 01:25 Then click on cell A8 and type the serial number as 7. 01:33 Now click on cell C8 which is where we want to display the sum total of the costs. 01:42 Type is equal to SUM and within braces the range of columns to be added. That is C3 colon C7. 01:55 Now press Enter. 01:58 Observe that all the amounts under Cost get added and sum total is displayed in cell C8. 02:07 Next, let’s learn how to do subtraction in Calc. 02:12 Say we want to subtract the cost of the House Rent and Electricity Bill. 02:19 We will display this in cell C10. 02:23 So click on cell C10 and type the following- “is equal to C3 minus C4”. 02:33 Press Enter. Observe the value in the cell C10. 02:39 It shows the subtracted value of C3 and C4. 02:45 Similarly, one can divide and multiply data in different cells. 02:52 Click on cell C11 and type “is equal to C5 asterix C6” and press Enter. 03:04 The answer of the multiplication is shown in cell C11. 03:10 Click on cell C12 and type “is equal to C3 forward slash C6 and press Enter. 03:22 The answer of the division is shown in cell C12. 03:27 Let's undo the changes of division, multiplication and subtraction that we made. 03:35 To do so, press the Ctrl+Z key combination thrice. 03:41 Another basic operation in a spreadsheet is to find the average of numbers. Let us see how to do that. 03:50 In cell B9, let's type the heading as Average. 03:56 Here we want to display the average of the total cost. 04:01 Next click on cell C9. 04:05 And type is equal to Average and within braces C3 colon C7. Press Enter. 04:16 We see that the average of the Cost column gets displayed in the cell. 04:23 Let's undo the changes by pressing Ctrl+Z keys. 04:29 In the same way, we can find the average of elements in a horizontal row also. 04:36 We will learn about other formulae and operators later in this series. 04:43 Now let’s learn how to sort data in a Calc spreadsheet. 04:48 Sorting organises rows and columns in a spreadsheet in a meaningful order. 04:55 Sorting helps us to analyse and visualize data more effectively. 05:02 It makes it easier to find and retrieve an item from the entire sheet. 05:09 We can sort data using upto three criteria, which are then applied one after another. 05:17 Sorting can be applied to the entire sheet or a range of cells 05:23 Data is typically sorted based on actual values. For eg: ascending, descending, alphabetic, left to right, oldest to newest, etc. 05:39 We will sort the data under the heading Costs in ascending order. 05:45 Now holding down the left mouse button, select the cells C1 to C7. 05:52 This selects the range of cells which we want to sort. 05:57 Go to the Standard toolbar. Here we can see three different sorting icons. 06:04 Click on the Sort Ascending icon. 06:07 The Sort range dialog box appears. 06:11 Click on the Current selection button. 06:14 Observe that the numbers in the selected cells are now arranged in ascending order. 06:21 Click on the Sort Descending icon. 06:24 In the Sort range dialog box, click on the Current selection button. 06:30 Observe that the numbers in the selected cells are now arranged in descending order. 06:37 Undo these changes by pressing Ctrl+Z keys twice. 06:43 The numbers are all back in their original cells. 06:48 To have greater control of sorting data, click on the Sort icon. 06:54 Alternatively, we can go to the menu bar and click on Data. Then click on Sort from the sub-menu. 07:05 In the Sort Range dialog box, once again click on the Current selection button. 07:11 This time another Sort dialog box appears with two tabs named Sort criteria and Options. 07:19 Sort criteria tab is selected by default. Below, we can see three Sort keys. 07:27 Under the drop-down in Sort key 1, select Cost. 07:32 Then click on the Ascending option just next to it. 07:37 To sort in the descending order, click on the Descending option. I will select the Ascending option. 07:46 Now click on the OK button at the bottom right corner. 07:51 We see that column Cost gets sorted in the ascending order. 07:57 Let us undo these changes by pressing Ctrl+Z keys. 08:02 Multiple columns can also be sorted in one go. 08:07 To do so, first select multiple columns, and then apply the sort options. I’ll demonstrate this for you. 08:16 Let’s say, we want to sort Serial numbers, Items as well as Cost. 08:23 So first select all the 3 columns SN, Items and Cost as shown here. 08:31 Now go to the menu bar and click on Data. From the sub-menu, click on Sort. 08:40 Sort dialog box opens up. 08:43 Under Sort criteria tab, in Sort key 1 drop-down, select Cost. 08:50 Then in Sort key 2 drop-down, select SN. 08:55 And in Sort key 3 drop-down, select Items. 09:00 Click on Descending in the first two sort options. 09:05 Click on Ascending in the third sort option. 09:09 And then click on the OK button. 09:13 Observe the changes. We see that all the columns get sorted in the selected orders. 09:22 Let us undo these changes. 09:26 Next let’s learn how to filter data in Calc. 09:31 A filter is a list of conditions that each entry has to meet in order to be displayed. 09:39 I will demonstrate this feature. 09:43 In order to apply a filter, let's click on any cell, for example the cell named Items. 09:52 Click on the Auto Filter icon in the Standard toolbar. 09:57 Alternatively, you can go to the menu bar and click on Data. Then click on Auto Filter from the sub-menu. 10:08 We see that a drop-down arrow appears on the heading Items. 10:15 Click on this drop-down arrow. 10:18 Filter pop-up box opens up. 10:21 Now suppose we want to display only Electricity Bill data. 10:27 So first uncheck the option All. 10:32 Then check the option Electricity Bill and click on the Ok button. 10:39 Only the data related to the Electricity Bill is displayed in the sheet. The rest of the items are filtered out. 10:49 In order to view all the data again, click on the dropdown arrow on the cell Items. 10:57 Check the option All. 11:00 Make sure all the headings are checked and then click on the Ok button. 11:05 We are now able to view all the data which we had in the sheet. 11:12 There are other filters in Calc which we will learn about in later tutorials of this series. 11:19 Let’s save and close the file. 11:22 This brings us to the end of this tutorial. Let us summarize. 11:27 In this tutorial, we learnt to: ```Use a basic Formula in Calc, Sort by columns and Filter data ``` 11:38 As an assignment: 11:40 Open “spreadsheet-practice.ods” sheet. 11:45 Sort the Salary column in Ascending order 11:49 Filter the Name column and display Rahul’s data. 11:54 Undo the changes. 11:57 The video at the following link summarizes the Spoken Tutorial project. Please download and watch it. 12:05 The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us. 12:15 Please post your timed queries in this forum. 12:19 Spoken Tutorial project is funded by MHRD, Govt. of India. 12:25 This is Arvind from IIT Bombay signing off. Thank you for watching.