LibreOfficeSuiteCalc6.3/C2/BasicDatamanipulationinCalc/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:

Slide: System Requirements  This tutorial is recorded using

Slide: What is a formula?  A formula is an equation that uses numbers and variables to get a result.

Slide: Basic Arithmetic Operations  First, we’ll learn how to perform some basic arithmetic calculations in Calc.

Open PersonalFinanceTracker.ods  Open the PersonalFinanceTracker.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.

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.

Now press Enter.

Only Narration  Next, let’s learn how to do subtraction in Calc. 
Point to House Rent and Electricity Bill.

Say we want to subtract the cost of the House Rent and Electricity Bill.

Click cell C10

So click on cell C10 and type the following
“is equal to C3 minus C4”. 
Press Enter.
Point to cell C3 and C4 
Press Enter.
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.

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.

Press Ctrl + Z thrice 
Let's undo the changes of division, multiplication and subtraction that we made.

Only Narration  Another basic operation in a spreadsheet is to find the average of numbers.

Type Average in B9  In cell B9, let's type the heading as Average.

Click on cell C9

Next click on cell C9.

Point to the average of the Cost

We see that the average of the Cost column gets displayed in the cell.

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.

Slide: Sorting 

Slide: Sorting 

Point to Costs

We will sort the data under the heading Costs in ascending order.

Cursor on the Standard toolbar.

Go to the Standard toolbar.

Click Sort Ascending icon  Click on the Sort Ascending icon. 
Point to Sort range dialog box

The Sort range dialog box appears.

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.

Click Sort icon  To have greater control of sorting data, click on the Sort icon. 
Click on Data

Alternatively, we can go to the menu bar and click on Data.

Point to Sort range dialog box

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.

Select “Cost” in the Sort Key 1 field  Under the dropdown in Sort key 1, select Cost. 
Point on Ascending

Then click on the Ascending option just next to it.

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.

Select data under SN ,Items and Costs together. 
Let’s say, we want to sort Serial numbers, Items as well as Cost.

Click on Data

Now go to the menu bar and click on Data.

Sort dialog box  Sort dialog box opens up. 
Sort criteria tab >> In Sort Key 1 select Cost

Under Sort criteria tab, in Sort key 1 dropdown, select Cost.

Click on Descending in both the options.  Click on Descending in the first two sort options.

Click on the OK button.  And then click on the OK button. 
Point to the sorted data.  Observe the changes.

Press CTRL + Z  Let us undo these changes. 
Slide: Filter Data  Next let’s learn how to filter data in Calc.

Click on the cell Items.  I will demonstrate this feature.

Click >> Auto Filter icon  Click on the Auto Filter icon in the Standard toolbar. 
Click on Data

Alternatively, you can go to the menu bar and click on Data.

Point arrow marks in the headings.
Click on the down arrow on the cell named Items. 
We see that a dropdown arrow appears on the heading Items.

Filter popup.  Filter popup box opens up. 
Highlight Electricity Bill.

Now suppose we want to display only Electricity Bill data. 
Uncheck the Check box at the top

So first uncheck the option All.

Point to Electricity Bill in the sheet.

Only the data related to the Electricity Bill is displayed in the sheet.

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.

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:

Slide: Assignment  As an assignment:

Slide: About Spoken Tutorial Project 

Slide: Spoken Tutorial Workshops 

Slide: Forums 

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. 