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.

Contributors and Content Editors

PoojaMoolya