View source for 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



{| border="1"
|-
|| '''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 '''<nowiki>=SUM(C3:C7) </nowiki>'''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 '''<nowiki>=C3-C4.</nowiki>'''
|| 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 '''<nowiki>=C5*C6</nowiki>''' >> 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''' <nowiki>=C3/C6 </nowiki>'''>> 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 '''<nowiki>=Average(C3:C7)</nowiki>'''




Press '''Enter'''
|| Next click on '''cell C9.'''


And type i'''s 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.

|-
|}