From Script | Spoken-Tutorial
Revision as of 17:52, 13 July 2020 by PoojaMoolya (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
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.

Contributors and Content Editors