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

Title: Basic Data Manipulation

Author: Arvind Pillai

Keywords: LibreOffice,Calc,Ubuntu,Addition in Calc, Subtraction in Calc,Sorting columns in Calc,Filtering data in Calc

VISUAL CUE	NARRATION
Slide:Title	Welcome to the spoken tutorial on Basic Data Manipulation in Calc.
Slide:Learning objectives	In this tutorial, we will learn how to: Use some basic Formula in Calc Sort by columns and Filter data
Slide: System Requirements	This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
Slide: What is a formula?	A formula is an equation that uses numbers and variables to get a result. The variables are cell locations that hold the data needed for the equation.
Slide: Basic Arithmetic Operations	First, we’ll learn how to perform some basic arithmetic calculations in Calc. That is Addition, subtraction, multiplication and division.
Open Personal-Finance-Tracker.ods	Open the Personal-Finance-Tracker.ods file.
Point to Cost.	Let’s sum up the cost of all the expenses mentioned under the heading Cost.
Type SUM TOTAL in the cell B8 under Miscellaneous. Click on the cell A8 >> type 7	We will type Sum Total just under Miscellaneous. Then click on cell A8 and type the serial number as 7.
Click on C8.	Now click on cell C8 which is where we want to display the sum total of the costs.
Type =SUM(C3:C7) in the cell C8	Type is equal to SUM and within braces the range of columns to be added. That is C3 colon C7.
Press Enter. Point to the total under Cost cell C8	Now press Enter. Observe that all the amounts under Cost get added and sum total is displayed in cell C8.
Only Narration	Next, let’s learn how to do subtraction in Calc.
Point to House Rent and Electricity Bill. Point to cell C10.	Say we want to subtract the cost of the House Rent and Electricity Bill. We will display this in cell C10.
Click cell C10 Type =C3-C4.	So click on cell C10 and type the following- “is equal to C3 minus C4”.
Press Enter. Point to the cell C10. Point to cell C3 and C4	Press Enter. Observe the value in the cell C10. It shows the subtracted value of C3 and C4.
Only Narration	Similarly, one can divide and multiply data in different cells.
Click on cell C11 >> type =C5*C6 >> press Enter.	Click on cell C11 and type “is equal to C5 asterix C6” and press Enter. The answer of the multiplication is shown in cell C11.
Click on cell C12 >> type =C3/C6 >> press Enter.	Click on cell C12 and type “is equal to C3 forward slash C6 and press Enter. The answer of the division is shown in cell C12.
Press Ctrl + Z thrice	Let's undo the changes of division, multiplication and subtraction that we made. To do so, press the Ctrl+Z key combination thrice.
Only Narration	Another basic operation in a spreadsheet is to find the average of numbers. Let us see how to do that.
Type Average in B9	In cell B9, let's type the heading as Average. Here we want to display the average of the total cost.
Click on cell C9 Type =Average(C3:C7) Press Enter	Next click on cell C9. And type is equal to Average and within braces C3 colon C7. Press Enter.
Point to the average of the Cost Press CTRL + Z	We see that the average of the Cost column gets displayed in the cell. Let's undo the changes by pressing Ctrl+Z keys.
Only Narration	In the same way, we can find the average of elements in a horizontal row also.
Only Narration	We will learn about other formulae and operators later in this series. Now let’s learn how to sort data in a Calc spreadsheet.
Slide: Sorting	Sorting organises rows and columns in a spreadsheet in a meaningful order. Sorting helps us to analyse and visualize data more effectively. It makes it easier to find and retrieve an item from the entire sheet. We can sort data using upto three criteria, which are then applied one after another.
Slide: Sorting	Sorting can be applied to the entire sheet or a range of cells Data is typically sorted based on actual values. For eg: ascending, descending, alphabetic, left to right, oldest to newest, etc.
Point to Costs Select The cells C1 to C7 Point to Selection C1 to C7	We will sort the data under the heading Costs in ascending order. Now holding down the left mouse button, select the cells C1 to C7. This selects the range of cells which we want to sort.
Cursor on the Standard toolbar. Point to the 3 sort icons	Go to the Standard toolbar. Here we can see three different sorting icons.
Click Sort Ascending icon	Click on the Sort Ascending icon.
Point to Sort range dialog box Click Current Selection	The Sort range dialog box appears. Click on the Current selection button.
Highlight the selected cells	Observe that the numbers in the selected cells are now arranged in ascending order.
Click on Sort Descending icon	Click on the Sort Descending icon.
Point to Sort range dialog box >> click Current Selection	In the Sort range dialog box, click on the Current selection button.
Highlight the selected cells	Observe that the numbers in the selected cells are now arranged in descending order.
Press CTRL + Z twice	Undo these changes by pressing Ctrl+Z keys twice. The numbers are all back in their original cells.
Click Sort icon	To have greater control of sorting data, click on the Sort icon.
Click on Data Click on Sort	Alternatively, we can go to the menu bar and click on Data. Then click on Sort from the sub-menu.
Point to Sort range dialog box Click >> Current Selection	In the Sort Range dialog box, once again click on the Current selection button.
“Sort” dialog box >> Point to “Sort criteria” and “Options” tabs.	This time another Sort dialog box appears with two tabs named Sort criteria and Options.
Point to Sort criteria tab	Sort criteria tab is selected by default. Below, we can see three Sort keys.
Select “Cost” in the Sort Key 1 field	Under the drop-down in Sort key 1, select Cost.
Point on Ascending Point to Descending Click Ascending radio button	Then click on the Ascending option just next to it. To sort in the descending order, click on the Descending option. I will select the Ascending option.
Click on OK	Now click on the OK button at the bottom right corner.
Point to sorting which is done in ascending order.	We see that column Cost gets sorted in the ascending order.
Press CTRL + Z	Let us undo these changes by pressing Ctrl+Z keys.
Only Narration	Multiple columns can also be sorted in one go. To do so, first select multiple columns, and then apply the sort options. I’ll demonstrate this for you.
Select data under SN ,Items and Costs together.	Let’s say, we want to sort Serial numbers, Items as well as Cost. So first select all the 3 columns SN, Items and Cost as shown here.
Click on Data Click on Sort	Now go to the menu bar and click on Data. Then from the sub-menu, click on Sort.
Sort dialog box	Sort dialog box opens up.
Sort criteria tab >> In Sort Key 1 select Cost In Sort Key 2, select SN In Sort Key 3, select Items	Under Sort criteria tab, in Sort key 1 drop-down, select Cost. Then in Sort key 2 drop-down, select SN. And in Sort key 3 drop-down, select Items.
Click on Descending in both the options.	Click on Descending in the first two sort options. Click on Ascending in the third sort option.
Click on the OK button.	And then click on the OK button.
Point to the sorted data.	Observe the changes. We see that all the columns get sorted in the selected orders.
Press CTRL + Z	Let us undo these changes.
Slide: Filter Data	Next let’s learn how to filter data in Calc. A filter is a list of conditions that each entry has to meet in order to be displayed.
Click on the cell Items.	I will demonstrate this feature. In order to apply a filter, let's click on any cell, for example the cell named Items.
Click >> Auto Filter icon	Click on the Auto Filter icon in the Standard toolbar.
Click on Data Click on Auto Filter	Alternatively, you can go to the menu bar and click on Data. Then click on Auto Filter from the sub-menu.
Point arrow marks in the headings. Click on the down arrow on the cell named Items.	We see that a drop-down arrow appears on the heading Items. Click on this drop-down arrow.
Filter popup.	Filter pop-up box opens up.
Highlight Electricity Bill.	Now suppose we want to display only Electricity Bill data.
Uncheck the Check box at the top Check Electricity bill >> Click Ok	So first uncheck the option All. Then check the option Electricity Bill and click on the Ok button.
Point to Electricity Bill in the sheet.	Only the data related to the Electricity Bill is displayed in the sheet. The rest of the items are filtered out.
Click the down arrow on the cell heading which contains Items	In order to view all the data again, click on the dropdown arrow on the cell Items.
Check the option All >> Click Ok	Check the option All. Make sure all the headings are checked and then click on the Ok button.
Show all the data in the spreadsheet.	We are now able to view all the data which we had in the sheet.
Only narration	There are other filters in Calc which we will learn about in later tutorials of this series.
Ctrl + S	Let’s save and close the file.
Only narration	This brings us to the end of this tutorial. Let us summarize.
Slide:Summary	In this tutorial, we learnt to: Use a basic Formula in Calc Sort by columns and Filter data
Slide: Assignment	As an assignment: Open “spreadsheet-practice.ods” sheet. Sort the Salary column in Ascending order Filter the Name column and display Rahul’s data. Undo the changes.
Slide: About Spoken Tutorial Project	The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
Slide: Spoken Tutorial Workshops	The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
Slide: Forums	Please post your timed queries in this forum.
Slide: Acknowledgement	Spoken Tutorial project is funded by MHRD, Govt. of India.
Slide: Thank you	This is Arvind from IIT Bombay signing off. Thank you for watching.

Contributors and Content Editors

Arvindpillai90, Nancyvarkey