|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.
|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.|