LibreOffice-Suite-Calc-6.3/C2/Basic-Data-manipulation-in-Calc/English-timed
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. |