Difference between revisions of "LibreOffice-Suite-Calc-6.3/C2/Basic-Data-manipulation-in-Calc/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "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...")
 
 
Line 95: Line 95:
 
|-
 
|-
 
|| Press '''Enter'''.
 
|| Press '''Enter'''.
−
 
  
 
Point to the '''cell C10'''.
 
Point to the '''cell C10'''.
Line 136: Line 135:
 
|| Only Narration
 
|| Only Narration
 
|| Another basic operation in a '''spreadsheet''' is to find the average of numbers.
 
|| Another basic operation in a '''spreadsheet''' is to find the average of numbers.
−
 
  
 
Let us see how to do that.
 
Let us see how to do that.
Line 150: Line 148:
  
 
Type '''<nowiki>=Average(C3:C7)</nowiki>'''
 
Type '''<nowiki>=Average(C3:C7)</nowiki>'''
−
 
  
  
Line 158: Line 155:
  
  
−
And type i'''s equal to Average''' '''and within braces C3 colon C7'''.
+
And type i'''s equal to Average''' and within braces '''C3 colon C7'''.
  
  
Line 164: Line 161:
 
|-
 
|-
 
|| Point to the average of the '''Cost'''  
 
|| Point to the average of the '''Cost'''  
−
 
−
 
  
  
Line 200: Line 195:
  
  
−
 
+
Select the cells '''C1''' to '''C7'''
−
 
+
−
Select The cells '''C1''' to '''C7'''
+
−
 
+
−
 
+
  
  
Line 291: Line 282:
  
 
Point to '''Descending'''
 
Point to '''Descending'''
−
 
−
 
  
  
Line 335: Line 324:
 
|-
 
|-
 
|| Click on '''Data'''
 
|| Click on '''Data'''
 +
  
  
 
Click on '''Sort'''
 
Click on '''Sort'''
−
|| Now go to the '''menu''' '''bar''' and click on '''Data'''.
+
|| Now go to the '''menu bar''' and click on '''Data'''.
  
  
Line 346: Line 336:
 
|| '''Sort '''dialog box opens up.
 
|| '''Sort '''dialog box opens up.
 
|-
 
|-
−
|| '''Sort''' '''criteria''' tab >> In '''Sort''' '''Key''' '''1''' select '''Cost'''
+
|| '''Sort criteria''' tab >> In '''Sort Key 1''' select '''Cost'''
  
  
−
In '''Sort''' '''Key''' '''2,''' select '''SN'''  
+
In '''Sort Key 2,''' select '''SN'''  
  
  
−
In '''Sort''' '''Key''' '''3,''' select '''Items'''
+
In '''Sort Key 3,''' select '''Items'''
−
|| Under '''Sort criteria''' tab, in '''Sort key 1 '''drop-down''',''' select '''Cost.'''
+
|| Under '''Sort criteria''' tab, in '''Sort key 1 '''drop-down, select '''Cost.'''
  
  
Line 361: Line 351:
 
And in '''Sort key 3''' drop-down, select '''Items.'''
 
And in '''Sort key 3''' drop-down, select '''Items.'''
 
|-
 
|-
−
|| Click on '''Descending''' in both the options.
+
|| Click on '''Descending''' in first 2
 +
 
 +
Click on '''Ascending''' in the third
 
|| Click on '''Descending''' in the first two '''sort''' options.
 
|| Click on '''Descending''' in the first two '''sort''' options.
  
Line 385: Line 377:
  
 
|-
 
|-
−
|| Click on the cell '''Items'''.
+
||  
 +
 
 +
 
 +
Click on the cell '''Items'''.
 
|| I will demonstrate this feature.
 
|| I will demonstrate this feature.
  
Line 421: Line 416:
 
|-
 
|-
 
|| Uncheck the '''Check''' box at the top
 
|| Uncheck the '''Check''' box at the top
−
 
  
 
Check '''Electricity''' '''bill >> '''Click '''Ok'''
 
Check '''Electricity''' '''bill >> '''Click '''Ok'''
Line 457: Line 451:
 
|-
 
|-
 
|| Only narration
 
|| Only narration
−
|| This brings us to the end of this tutorial. Let us summarize.
+
|| This brings us to the end of this tutorial.  
 +
 
 +
Let us summarize.
 
|-
 
|-
 
|| '''Slide:'''Summary
 
|| '''Slide:'''Summary

Latest revision as of 19:04, 4 July 2020

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 first 2

Click on Ascending in the third

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

Retrieved from "https://script.spoken-tutorial.org/index.php?title=LibreOffice-Suite-Calc-6.3/C2/Basic-Data-manipulation-in-Calc/English&oldid=52832"