Welcome to the spoken tutorial on Basic Data Manipulation in Calc.
In this tutorial, we will learn how to:
Use some basic Formula in Calc
Sort by columns and Filter data
This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
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.
Basic Arithmetic Operations First, we’ll learn how to perform some basic arithmetic calculations in Calc.
That is Addition, subtraction, multiplication and division.
Open the Personal-Finance-Tracker.ods file.
Let’s sum up the cost of all the expenses mentioned under the heading Cost.
We will type Sum Total just under Miscellaneous.
Then click on cell A8 and type the serial number as 7.
Now click on cell C8 which is where we want to display the sum total of the costs.
Type is equal to SUM and within braces the range of columns to be added. That is C3 colon C7.
Now press Enter.
Observe that all the amounts under Cost get added and sum total is displayed in cell C8.
Next, let’s learn how to do subtraction in Calc.
Say we want to subtract the cost of the House Rent and Electricity Bill.
We will display this in cell C10.
So click on cell C10 and type the following- “is equal to C3 minus C4”.
Press Enter. Observe the value in the cell C10.
It shows the subtracted value of C3 and C4.
Similarly, one can divide and multiply data in different cells.
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 and type “is equal to C3 forward slash C6 and press Enter.
The answer of the division is shown in cell C12.
Let's undo the changes of division, multiplication and subtraction that we made.
To do so, press the Ctrl+Z key combination thrice.
Another basic operation in a spreadsheet is to find the average of numbers. Let us see how to do that.
In cell B9, let's type the heading as Average.
Here we want to display the average of the total cost.
Next click on cell C9.
And type is equal to Average and within braces C3 colon C7. Press Enter.
We see that the average of the Cost column gets displayed in the cell.
Let's undo the changes by pressing Ctrl+Z keys.
In the same way, we can find the average of elements in a horizontal row also.
We will learn about other formulae and operators later in this series.
Now let’s learn how to sort data in a Calc spreadsheet.
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.
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.
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.
Go to the Standard toolbar. Here we can see three different sorting icons.
Click on the Sort Ascending icon.
The Sort range dialog box appears.
Click on the Current selection button.
Observe that the numbers in the selected cells are now arranged in ascending order.
Click on the Sort Descending icon.
In the Sort range dialog box, click on the Current selection button.
Observe that the numbers in the selected cells are now arranged in descending order.
Undo these changes by pressing Ctrl+Z keys twice.
The numbers are all back in their original cells.
To have greater control of sorting data, click on the Sort icon.
Alternatively, we can go to the menu bar and click on Data. Then click on Sort from the sub-menu.
In the Sort Range dialog box, once again click on the Current selection button.
This time another Sort dialog box appears with two tabs named Sort criteria and Options.
Sort criteria tab is selected by default. Below, we can see three Sort keys.
Under the drop-down in Sort key 1, select Cost.
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.
Now click on the OK button at the bottom right corner.
We see that column Cost gets sorted in the ascending order.
Let us undo these changes by pressing Ctrl+Z keys.
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.
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.
Now go to the menu bar and click on Data. From the sub-menu, click on Sort.
Sort dialog box opens up.
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 the first two sort options.
Click on Ascending in the third sort option.
And then click on the OK button.
Observe the changes. We see that all the columns get sorted in the selected orders.
Let us undo these changes.
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.
I will demonstrate this feature.
In order to apply a filter, let's click on any cell, for example the cell named Items.
Click on the Auto Filter icon in the Standard toolbar.
Alternatively, you can go to the menu bar and click on Data. Then click on Auto Filter from the sub-menu.
We see that a drop-down arrow appears on the heading Items.
Click on this drop-down arrow.
Filter pop-up box opens up.
Now suppose we want to display only Electricity Bill data.
So first uncheck the option All.
Then check the option Electricity Bill and click on the Ok button.
Only the data related to the Electricity Bill is displayed in the sheet. The rest of the items are filtered out.
In order to view all the data again, click on the dropdown arrow on the cell Items.
Check the option All.
Make sure all the headings are checked and then click on the Ok button.
We are now able to view all the data which we had in the sheet.
There are other filters in Calc which we will learn about in later tutorials of this series.
Let’s save and close the file.
This brings us to the end of this tutorial. Let us summarize.
In this tutorial, we learnt to: Use a basic Formula in Calc, Sort by columns and Filter data
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.
The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
Please post your timed queries in this forum.
Spoken Tutorial project is funded by MHRD, Govt. of India.
This is Arvind from IIT Bombay signing off. Thank you for watching.