Difference between revisions of "LibreOffice-Suite-Calc-6.3/C3/Advanced-Formatting-and-Protection-in-Calc/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "Title: Advanced Formatting and Password Protection Author: Arvind Pillai Novice reviewer: Nikita Misal & Pooja Moolya Domain reviewer: Praveen S and nancy varkeyKeywords: L...")
 
Line 11: Line 11:
 
{|border="1"
 
{|border="1"
 
|-
 
|-
|| '''VISUAL''' '''CUE'''
+
|| '''VISUAL CUE'''
 
|| '''NARRATION'''
 
|| '''NARRATION'''
 
|-
 
|-
Line 47: Line 47:
 
This option ensures that only people who know the '''password''' can open this '''spreadsheet'''.
 
This option ensures that only people who know the '''password''' can open this '''spreadsheet'''.
 
|-
 
|-
|| Click >> '''Save''' icon >> '''Save''' '''As'''
+
|| Click >> '''Save''' icon >> '''Save As'''
|| Click on the drop-down besides the '''Save''' icon and select '''Save''' '''As''' option.
+
|| Click on the drop-down besides the '''Save''' icon and select '''Save As''' option.
 
|-
 
|-
 
|| Point to dialog box.
 
|| Point to dialog box.
 
|| '''Save As''' dialog box opens up.
 
|| '''Save As''' dialog box opens up.
 
|-
 
|-
|| Check the '''Save''' '''with''' '''password''' option.
+
|| Check the '''Save with password''' option.
  
 
Click '''Save''' button
 
Click '''Save''' button
Line 63: Line 63:
  
 
Click '''Yes'''.
 
Click '''Yes'''.
|| In the '''Save''' '''As''' option, we can either save it as a different file or replace the same file.
+
|| In the '''Save As''' option, we can either save it as a different file or replace the same file.
  
 
For this demonstration, we will replace the file.
 
For this demonstration, we will replace the file.
Line 70: Line 70:
 
|-
 
|-
 
|| Point to the dialog box.
 
|| Point to the dialog box.
|| '''Set''' '''Password''' dialog box opens up.
+
|| '''Set Password''' dialog box opens up.
 
|-
 
|-
 
|| Type the password : '''spoken123'''
 
|| Type the password : '''spoken123'''
Line 111: Line 111:
  
 
Point to '''spreadsheet'''
 
Point to '''spreadsheet'''
|| This time in the '''Enter''' '''password''' field, type the correct '''password''' as '''spoken123.'''  
+
|| This time in the '''Enter password''' field, type the correct '''password''' as '''spoken123.'''  
  
 
Click on the '''OK''' button.
 
Click on the '''OK''' button.
Line 120: Line 120:
 
|| It is possible for us to remove the '''password''' option at a later time, as well.
 
|| It is possible for us to remove the '''password''' option at a later time, as well.
 
|-
 
|-
|| Click dropdown of '''Save''' icon >> '''Save''' '''As''' option
+
|| Click dropdown of '''Save''' icon >> '''Save As''' option
|| To do that, click on the drop-down besides the '''Save''' icon and then on the '''Save''' '''As''' option.
+
|| To do that, click on the drop-down besides the '''Save''' icon and then on the '''Save As''' option.
 
|-
 
|-
 
|| Un-check '''Save with Password '''option.
 
|| Un-check '''Save with Password '''option.
  
 
Click >> '''Save'''
 
Click >> '''Save'''
|| Now uncheck the '''Save''' '''with''' '''password''' option in the '''Save''' '''As''' dialog box.
+
|| Now uncheck the '''Save with password''' option in the '''Save As''' dialog box.
  
 
Then click the '''Save''' button at the top right corner.
 
Then click the '''Save''' button at the top right corner.
Line 143: Line 143:
 
|| Next, let’s learn how to '''password'''-protect individual '''sheets''' in the '''spreadsheet'''.
 
|| Next, let’s learn how to '''password'''-protect individual '''sheets''' in the '''spreadsheet'''.
 
|-
 
|-
|| Click on '''Tools''' from the menu bar >> Point on '''Protect''' '''Sheet'''
+
|| Click on '''Tools''' from the menu bar >> Point on '''Protect Sheet'''
  
Right click on '''Sheet''' '''1''' tab >> Select '''Protect''' '''Sheet'''
+
Right click on '''Sheet 1''' tab >> Select '''Protect Sheet'''
 
|| Go to the '''Tools''' menu in the '''menu bar''' and click on the '''Protect sheet''' option.
 
|| Go to the '''Tools''' menu in the '''menu bar''' and click on the '''Protect sheet''' option.
  
Alternatively, right click on the '''Sheet''' '''1''' tab at the bottom and select '''Protect''' '''Sheet '''option.
+
Alternatively, right click on the '''Sheet 1''' tab at the bottom and select '''Protect Sheet '''option.
 
|-
 
|-
|| Point to the '''Protect''' '''Sheet''' dialog box.
+
|| Point to the '''Protect Sheet''' dialog box.
|| Either ways, the '''Protect''' '''Sheet''' dialog box opens.
+
|| Either ways, the '''Protect Sheet''' dialog box opens.
 
|-
 
|-
 
||  
 
||  
  
Un-check '''Select Protected cells''' and '''Select''' '''Unprotected''' '''cells'''.
+
Un-check '''Select Protected cells''' and '''Select Unprotected cells'''.
 
|| To protect the '''sheet''', uncheck the following options by clicking on the '''checkbox''' besides it:
 
|| To protect the '''sheet''', uncheck the following options by clicking on the '''checkbox''' besides it:
* '''Select protected cells '''and''' '''
+
* '''Select protected cells '''and  
 
* '''Select unprotected cells.'''
 
* '''Select unprotected cells.'''
  
Line 172: Line 172:
 
Then click on the '''OK''' button in the dialog box.
 
Then click on the '''OK''' button in the dialog box.
 
|-
 
|-
|| Point to '''Sheet''' '''1''' '''lock''' '''Symbol'''.
+
|| Point to '''Sheet 1 lock Symbol'''.
 
|| We can see a '''lock''' symbol in the '''Sheet 1''' tab.
 
|| We can see a '''lock''' symbol in the '''Sheet 1''' tab.
  
Line 197: Line 197:
 
|| Click on '''E3''' and type '''testing'''.
 
|| Click on '''E3''' and type '''testing'''.
  
Point to '''cell''' '''B11'''
+
Point to '''cell B11'''
 
|| Here we’ll select '''cell E3 '''and type the word '''testing''' inside the '''cell.'''
 
|| Here we’ll select '''cell E3 '''and type the word '''testing''' inside the '''cell.'''
  
Line 211: Line 211:
 
|| Let’s unprotect the '''sheet '''now.
 
|| Let’s unprotect the '''sheet '''now.
 
|-
 
|-
|| Right click on '''Sheet''' '''1''' tab >> Select '''Protect''' '''Sheet'''
+
|| Right click on '''Sheet 1''' tab >> Select '''Protect Sheet'''
|| Right-click on '''Sheet''' '''1''' tab and select '''Protect''' '''sheet''' option.
+
|| Right-click on '''Sheet 1''' tab and select '''Protect sheet''' option.
 
|-
 
|-
|| Point to the '''Unprotect''' '''Sheet''' dialog box.
+
|| Point to the '''Unprotect Sheet''' dialog box.
  
 
Enter '''spoken123''' as '''password'''
 
Enter '''spoken123''' as '''password'''
|| The''' Unprotect''' '''Sheet''' dialog box opens up and prompts us for the '''password'''.
+
|| The''' Unprotect Sheet''' dialog box opens up and prompts us for the '''password'''.
  
 
Type '''spoken123''' in the '''Password (optional) '''text field.
 
Type '''spoken123''' in the '''Password (optional) '''text field.
Line 255: Line 255:
 
|| We’ll select all the '''data''' from column '''SN''' to '''Miscellaneous.'''
 
|| We’ll select all the '''data''' from column '''SN''' to '''Miscellaneous.'''
  
We’ll Ignore the '''Sum''' '''Total''' row for now.
+
We’ll Ignore the '''Sum Total''' row for now.
 
|-
 
|-
 
|| Narration Only
 
|| Narration Only
 
|| Let’s name our '''database'''.
 
|| Let’s name our '''database'''.
 
|-
 
|-
|| Click on '''Data''' menu >> click on '''Define''' '''Range'''
+
|| Click on '''Data''' menu >> click on '''Define Range'''
|| Go to the '''Data''' '''menu''' in the '''menu bar''' and select the '''Define''' '''Range''' option.
+
|| Go to the '''Data menu''' in the '''menu bar''' and select the '''Define Range''' option.
 
|-
 
|-
|| Point to '''Define''' '''Database Range '''dialog box.
+
|| Point to '''Define Database Range '''dialog box.
|| '''Define''' '''Database Range''' dialog box opens up.  
+
|| '''Define Database Range''' dialog box opens up.  
 
|-
 
|-
 
|| In the '''Name''' field, type '''dtbs'''.
 
|| In the '''Name''' field, type '''dtbs'''.
Line 275: Line 275:
 
|| Click anywhere else in the '''spreadsheet''' to deselect the '''cells'''.
 
|| Click anywhere else in the '''spreadsheet''' to deselect the '''cells'''.
 
|-
 
|-
|| From the Menu bar >> Click on '''Data''' >> click on '''Select''' '''Range'''
+
|| From the Menu bar >> Click on '''Data''' >> click on '''Select Range'''
|| Again go to the '''Data''' '''menu''' in the '''menu bar''' and click on the '''Select''' '''Range''' option.
+
|| Again go to the '''Data menu''' in the '''menu bar''' and click on the '''Select Range''' option.
 
|-
 
|-
 
|| Point to''' Select Database Range''' dialog box
 
|| Point to''' Select Database Range''' dialog box
Line 307: Line 307:
 
|| Let’s find the '''subtotals''' of the '''data''' in the '''Cost '''column.
 
|| Let’s find the '''subtotals''' of the '''data''' in the '''Cost '''column.
 
|-
 
|-
|| Delete the entry in row''' '''number''' 8'''.
+
|| Delete the entry in row number''' 8'''.
|| First, let’s delete the entry in '''row number 8''' which is '''SUM''' '''TOTAL'''.
+
|| First, let’s delete the entry in '''row number 8''' which is '''SUM TOTAL'''.
 
|-
 
|-
 
|| Select all '''A1''' to '''F7''' together.
 
|| Select all '''A1''' to '''F7''' together.
Line 314: Line 314:
 
|-
 
|-
 
|| Click the '''Data''' menu and '''Subtotals'''.
 
|| Click the '''Data''' menu and '''Subtotals'''.
|| Go to the '''Data''' '''menu''' in the '''menu bar''' and click on the '''Subtotals''' option.
+
|| Go to the '''Data menu''' in the '''menu bar''' and click on the '''Subtotals''' option.
 
|-
 
|-
 
|| Point to the '''Subtotals''' dialog box and the 1st''' Group''' tab.
 
|| Point to the '''Subtotals''' dialog box and the 1st''' Group''' tab.
  
In the '''Group''' '''by '''>> Click '''SN'''.
+
In the '''Group by '''>> Click '''SN'''.
 
|| The '''Subtotals''' dialog box appears.
 
|| The '''Subtotals''' dialog box appears.
  
 
By default, the '''1st Group''' tab is selected at the top.
 
By default, the '''1st Group''' tab is selected at the top.
  
Click the '''Group''' '''by''' field drop-down and choose '''SN, '''if not already selected.
+
Click the '''Group by''' field drop-down and choose '''SN, '''if not already selected.
  
This groups the '''data''' by '''SN''' which is the '''Serial''' '''Number'''.
+
This groups the '''data''' by '''SN''' which is the '''Serial Number'''.
 
|-
 
|-
 
|| In '''Calculate subtotals for''' click on Cost.
 
|| In '''Calculate subtotals for''' click on Cost.
Line 332: Line 332:
 
This will calculate the total of all the entries under it.
 
This will calculate the total of all the entries under it.
 
|-
 
|-
|| Under '''Use''' '''function''' choose '''Sum'''
+
|| Under '''Use function''' choose '''Sum'''
  
 
Click '''OK'''.
 
Click '''OK'''.
Line 339: Line 339:
 
And click on the '''OK''' button at the bottom.
 
And click on the '''OK''' button at the bottom.
 
|-
 
|-
|| Point to '''Grand''' '''Sum'''.
+
|| Point to '''Grand Sum'''.
  
 
Point to '''Subtotals''' and '''SN''' column
 
Point to '''Subtotals''' and '''SN''' column
|| Notice that the '''Grand''' '''Sum''' of the entries under the column '''Costs''' is displayed on the '''spreadsheet'''.
+
|| Notice that the '''Grand Sum''' of the entries under the column '''Costs''' is displayed on the '''spreadsheet'''.
  
 
We also see '''subtotals''' after every row and see the changes in the '''SN''' column.
 
We also see '''subtotals''' after every row and see the changes in the '''SN''' column.
Line 357: Line 357:
 
|| The same is seen highlighted in the '''spreadsheet''' as well.
 
|| The same is seen highlighted in the '''spreadsheet''' as well.
 
|-
 
|-
|| Slide: '''Subtotal''' '''Function'''
+
|| Slide: '''Subtotal Function'''
 
|| Refer to this table for numbers that correspond to '''functions''' within the '''SUBTOTAL formula'''.  
 
|| Refer to this table for numbers that correspond to '''functions''' within the '''SUBTOTAL formula'''.  
  
Line 367: Line 367:
 
|-
 
|-
 
|| Point to '''1,2,3''' on the left hand side.
 
|| Point to '''1,2,3''' on the left hand side.
|| At the top left below the '''Name''' '''Box,''' we can see 3 tiny new tabs '''1, 2 '''and''' 3.'''
+
|| At the top left below the '''Name Box,''' we can see 3 tiny new tabs '''1, 2 '''and''' 3.'''
  
 
These tabs give 3 different views of the '''data'''.
 
These tabs give 3 different views of the '''data'''.
Line 373: Line 373:
 
|| Click on '''tab 1.'''
 
|| Click on '''tab 1.'''
  
Point to''' Grand sum'''
+
Point to''' Grand Sum'''
 
|| Let’s click on '''tab 1'''.
 
|| Let’s click on '''tab 1'''.
  
Notice that only the '''grand''' '''sum''' of the '''data''' under '''Costs''' along with the last '''subtotal'''.
+
Notice that only the '''Grand Sum''' of the '''data''' under '''Costs''' along with the last '''subtotal'''.
 
|-
 
|-
 
|| Click on '''tab 2.'''
 
|| Click on '''tab 2.'''
  
Point to''' Costs and Grand sum'''
+
Point to''' Costs and Grand Sum'''
 
|| Click on '''tab 2.'''
 
|| Click on '''tab 2.'''
  
The '''data''' under '''Costs''' as well as the '''Grand sum''' is displayed.
+
The '''data''' under '''Costs''' as well as the '''Grand Sum''' is displayed.
 
|-
 
|-
 
|| Click on '''tab 3.'''
 
|| Click on '''tab 3.'''
Line 390: Line 390:
 
|| Now, click on '''tab 3.'''
 
|| Now, click on '''tab 3.'''
  
We get the detailed view of all the '''data''' along with the '''Grand''' '''sum''' of the '''data''' under '''Costs'''.
+
We get the detailed view of all the '''data''' along with the '''Grand Sum''' of the '''data''' under '''Costs'''.
 
|-
 
|-
 
|| Close the '''file'''.
 
|| Close the '''file'''.
Line 402: Line 402:
  
 
* The '''Validity''' option validates '''data''' in the '''spreadsheet'''.
 
* The '''Validity''' option validates '''data''' in the '''spreadsheet'''.
* This is done by specifying the '''Validation''' '''rules''' for the selected '''cells''' in the '''spreadsheet'''.
+
* This is done by specifying the '''Validation rules''' for the selected '''cells''' in the '''spreadsheet'''.
  
 
|-
 
|-
Line 418: Line 418:
 
|| Below the heading '''MOP''', the '''cells''' can be used to display the mode of payments.
 
|| Below the heading '''MOP''', the '''cells''' can be used to display the mode of payments.
  
The '''MOP''' for the '''Items''' in '''Column''' '''B''' can be shown here.
+
The '''MOP''' for the '''Items''' in '''Column B''' can be shown here.
  
 
That is, '''Salary, House Rent, Electricity Bill''' and the other components.
 
That is, '''Salary, House Rent, Electricity Bill''' and the other components.
Line 430: Line 430:
 
|-
 
|-
 
|| From the Menu bar >>click on '''Data''' >> click on '''Validity'''
 
|| From the Menu bar >>click on '''Data''' >> click on '''Validity'''
|| Go to the '''Data''' '''menu''' in the '''menu bar''' and select '''Validity''' option.
+
|| Go to the '''Data menu''' in the '''menu bar''' and select '''Validity''' option.
 
|-
 
|-
 
|| Point to the '''Validity''' dialog box.
 
|| Point to the '''Validity''' dialog box.
Line 447: Line 447:
 
Let’s enter the options which will appear on validating the selected '''cell'''.
 
Let’s enter the options which will appear on validating the selected '''cell'''.
 
|-
 
|-
|| In the '''Entries''' box >> type '''In''' '''Cash''' [Enter]
+
|| In the '''Entries''' box >> type '''In Cash''' [Enter]
|| We’ll type''' '''the first mode of payment as '''In''' '''Cash''' and press '''Enter.'''
+
|| We’ll type the first mode of payment as '''In Cash''' and press '''Enter.'''
 
|-
 
|-
 
|| In the '''Entries''' box >>  
 
|| In the '''Entries''' box >>  
Line 461: Line 461:
 
|-
 
|-
 
|| Point to the cells.
 
|| Point to the cells.
|| Notice the down arrow displayed alongside '''cell''' '''F2''', which means the '''cell''' is validated.
+
|| Notice the down arrow displayed alongside '''cell F2''', which means the '''cell''' is validated.
 
|-
 
|-
 
|| Click the down arrow on the '''cell'''  
 
|| Click the down arrow on the '''cell'''  
Line 470: Line 470:
 
|| Now, click the down arrow.
 
|| Now, click the down arrow.
  
We see the options that we entered as '''Mode''' '''of''' '''Payments''' in the '''Entries''' box here.
+
We see the options that we entered as '''Mode of Payments''' in the '''Entries''' box here.
  
 
Click on the down arrow again to collapse the list.
 
Click on the down arrow again to collapse the list.
 
|-
 
|-
|| Click on '''Clone''' '''Formatting''' icon
+
|| Click on '''Clone Formatting''' icon
|| To validate the '''cells''' below, click on the '''Clone''' '''Formatting''' icon in the '''Standard''' '''toolbar'''.
+
|| To validate the '''cells''' below, click on the '''Clone Formatting''' icon in the '''Standard toolbar'''.
 
|-
 
|-
 
|| Drag and select 5 '''cells F3 to F7 '''below it.
 
|| Drag and select 5 '''cells F3 to F7 '''below it.
|| Then, click on the '''cell''' '''F3''' and press the left mouse button.
+
|| Then, click on the '''cell F3''' and press the left mouse button.
  
 
Drag along the '''cursor''' till '''cell F7''' and release the '''mouse''' button.
 
Drag along the '''cursor''' till '''cell F7''' and release the '''mouse''' button.
Line 530: Line 530:
 
|| As an assignment
 
|| As an assignment
 
* Open '''Spreadsheet-Practice.ods '''file
 
* Open '''Spreadsheet-Practice.ods '''file
* Use the '''password''' '''protect''' option to protect the '''sheet''' named '''Department Sheet'''
+
* Use the '''password protect''' option to protect the '''sheet''' named '''Department Sheet'''
* Use the '''Subtotals''' option and find the '''grand''' '''sum''' of '''salary''' column
+
* Use the '''Subtotals''' option and find the '''Grand Sum''' of '''Salary''' column
 
* Close the file without saving the changes.  
 
* Close the file without saving the changes.  
  

Revision as of 16:23, 10 September 2020

Title: Advanced Formatting and Password Protection

Author: Arvind Pillai

Novice reviewer: Nikita Misal & Pooja Moolya

Domain reviewer: Praveen S and nancy varkeyKeywords: LibreOffice,Calc,Ubuntu,Password protect Calc file,Password protect a single sheet in Calc,Define ranges for database in Calc,Subtotal option in Calc,Validate cells in Calc


VISUAL CUE NARRATION
Slide: Tittle Welcome to the spoken tutorial on Advanced Formatting and Password Protection in Calc.
Slide: Learning objectives In this tutorial, we will learn how to:
  • Protect a spreadsheet with a password
  • Protect a single sheet with a password
  • Define Ranges for a database
  • Use the Subtotal option and
  • Validate cells.
Slide: System Requirements This tutorial is recorded using
  • Ubuntu Linux OS version 18.04 and
  • LibreOffice Suite version 6.3.5
Open Personal-Finance-Tracker.ods Let us open our Personal-Finance-Tracker.ods file.
Slide: Code files
  • This file has been provided to you in the Code files link on this tutorial page.
  • Please download and extract the file.
  • Make its copy and use it for practising.
Narration Only First let us learn to protect this spreadsheet with a password.

This option ensures that only people who know the password can open this spreadsheet.

Click >> Save icon >> Save As Click on the drop-down besides the Save icon and select Save As option.
Point to dialog box. Save As dialog box opens up.
Check the Save with password option.

Click Save button

Next, check the Save with password checkbox at the bottom left corner.

And then click the Save button at the top right corner.

Point to the file already exists overwrite? dialog box.

Click Yes.

In the Save As option, we can either save it as a different file or replace the same file.

For this demonstration, we will replace the file.

Click Yes.

Point to the dialog box. Set Password dialog box opens up.
Type the password : spoken123

Retype the password as spoken123 in Confirm box also

Click Ok

Here in the Enter password to open field, type a password, say spoken123.

In the Confirm password text field, retype the same password that is spoken123.

Then click on the OK button in the dialog box.

Close the file Close the file.
Open the file Personal-Finance-Tracker.ods Now, we’ll try to reopen this file and check what happens.
Point to Enter Password dialog box

Enter the password: 111 >> Click OK.

Immediately the Enter Password dialog box appears.

Let us type the password as 111 here and click on the OK button.

Point to the error message


Click OK.

We get an error message.

It says The password is incorrect.The file cannot be opened.

Click on the OK button.

Enter the password: 123

Click OK.

Point to spreadsheet

This time in the Enter password field, type the correct password as spoken123.

Click on the OK button.

The Personal-Finance-Tracker.ods file opens.

Narration only It is possible for us to remove the password option at a later time, as well.
Click dropdown of Save icon >> Save As option To do that, click on the drop-down besides the Save icon and then on the Save As option.
Un-check Save with Password option.

Click >> Save

Now uncheck the Save with password option in the Save As dialog box.

Then click the Save button at the top right corner.

Click >> Yes button Let’s replace the same file by clicking on the Yes button.
Close and open the file.

Narration Only

Once again, let us close and open this file.

Notice that, this time we do not require a password to open the file.

Narration Only Next, let’s learn how to password-protect individual sheets in the spreadsheet.
Click on Tools from the menu bar >> Point on Protect Sheet

Right click on Sheet 1 tab >> Select Protect Sheet

Go to the Tools menu in the menu bar and click on the Protect sheet option.

Alternatively, right click on the Sheet 1 tab at the bottom and select Protect Sheet option.

Point to the Protect Sheet dialog box. Either ways, the Protect Sheet dialog box opens.

Un-check Select Protected cells and Select Unprotected cells.

To protect the sheet, uncheck the following options by clicking on the checkbox besides it:
  • Select protected cells and
  • Select unprotected cells.
In the Password type spoken123.

Re-enter the password spoken123 in the Confirm field.

Click OK.

Now, in the Password field, let’s type spoken123 as our password.

In the Confirm field, retype the same password.

Then click on the OK button in the dialog box.

Point to Sheet 1 lock Symbol. We can see a lock symbol in the Sheet 1 tab.

This indicates that our sheet is now password-protected.

Click any cell and type to show that you cannot enter the data. To verify, let’s try to add some data in any cell on this sheet.

Notice that we are unable to type anything in any cell.

Warning message >> Click Ok to exit this box. Also, we see a warning message. Click Ok to exit this box.
Click any image and try to move. We are also unable to select or move the image in this sheet elsewhere.
Narration Only

Click on Sheet 2.

But what about the other sheets?

Let’s click on Sheet 2.

Click on E3 and type testing.

Point to cell B11

Here we’ll select cell E3 and type the word testing inside the cell.

Calc allows us to edit the cells in other sheets.

Press Ctrl + Z Let’s undo these changes.
Click on Sheet 1 tab Now go back to Sheet 1.
Narration Only Let’s unprotect the sheet now.
Right click on Sheet 1 tab >> Select Protect Sheet Right-click on Sheet 1 tab and select Protect sheet option.
Point to the Unprotect Sheet dialog box.

Enter spoken123 as password

The Unprotect Sheet dialog box opens up and prompts us for the password.

Type spoken123 in the Password (optional) text field.

Click OK button. Click on the OK button in the dialog box.
Point to Sheet 1 tab Notice the lock symbol in Sheet 1 tab has disappeared now.

This indicates that Sheet 1 is no longer password-protected.

Type testing in D10 and

move image 1 to G2

We are able to edit the cells again and move the images as well.
Narration Only Next, let’s learn about Ranges.
Slide: Ranges
  • We can define a range of cells in a spreadsheet and use it as a database.
  • Each row in this database range corresponds to a database record
  • While each cell in a row corresponds to a database field.
  • We can sort, group, search and perform calculations on the range as we would do in any database.
Narration Only: First, let’s define a database in our Personal-Finance-Tracker.ods and then sort the data.

So, let’s select the items which we require in the database.

Select all data under SN to Miscellaneous together.

Ignore the Sum Total row

We’ll select all the data from column SN to Miscellaneous.

We’ll Ignore the Sum Total row for now.

Narration Only Let’s name our database.
Click on Data menu >> click on Define Range Go to the Data menu in the menu bar and select the Define Range option.
Point to Define Database Range dialog box. Define Database Range dialog box opens up.
In the Name field, type dtbs. In the Name field, type dtbs which is the short-form of the database.
Click OK. Click on the OK button in the dialog box.
Click anywhere else in the spreadsheet Click anywhere else in the spreadsheet to deselect the cells.
From the Menu bar >> Click on Data >> click on Select Range Again go to the Data menu in the menu bar and click on the Select Range option.
Point to Select Database Range dialog box

Point to dtbs in the dialog box.

Select Database Range dialog box appears.

Notice that in the Ranges section, the name dtbs is listed as a database.

Click on dtbs and Click on OK Click on dtbs from the list and then click on the OK button in the dialog box.
Only Narration Now we can sort the data within this database, however we want.

Sorting data was already explained in an earlier tutorial in this series.

So, we’ll skip that demonstration here.

Narration Only Next, let’s learn how to use the Subtotal option in Calc.
Slide: Subtotals
  • The Subtotals option calculates the sub total of data under different columns.
  • For this, we can use any mathematical function of our choice.
Point to the cost Column Let’s find the subtotals of the data in the Cost column.
Delete the entry in row number 8. First, let’s delete the entry in row number 8 which is SUM TOTAL.
Select all A1 to F7 together. Then select all the data from cell A1 to F7 as shown.
Click the Data menu and Subtotals. Go to the Data menu in the menu bar and click on the Subtotals option.
Point to the Subtotals dialog box and the 1st Group tab.

In the Group by >> Click SN.

The Subtotals dialog box appears.

By default, the 1st Group tab is selected at the top.

Click the Group by field drop-down and choose SN, if not already selected.

This groups the data by SN which is the Serial Number.

In Calculate subtotals for click on Cost. Next, in the Calculate subtotals for field, check on the Cost checkbox.

This will calculate the total of all the entries under it.

Under Use function choose Sum

Click OK.

Under the Use function field, choose Sum, if not already selected.

And click on the OK button at the bottom.

Point to Grand Sum.

Point to Subtotals and SN column

Notice that the Grand Sum of the entries under the column Costs is displayed on the spreadsheet.

We also see subtotals after every row and see the changes in the SN column.

Click on cell C14 >> click on the formula bar. Click on cell C14 and look at the formula bar.

We see the syntax of the formula, Calc has used for the SUBTOTAL.

Point to the syntax It says equal to SUBTOTAL and within brackets number 9 and the cell range.
Point to the blue highlight box in the sheet The same is seen highlighted in the spreadsheet as well.
Slide: Subtotal Function Refer to this table for numbers that correspond to functions within the SUBTOTAL formula.

9 stands for the function SUM which is what we had selected in the SUBTOTAL dialog box.

Other function numbers are mentioned in this table, for your quick reference.

Use the corresponding numbers in the left column, when you wish to use another function.

Point to 1,2,3 on the left hand side. At the top left below the Name Box, we can see 3 tiny new tabs 1, 2 and 3.

These tabs give 3 different views of the data.

Click on tab 1.

Point to Grand Sum

Let’s click on tab 1.

Notice that only the Grand Sum of the data under Costs along with the last subtotal.

Click on tab 2.

Point to Costs and Grand Sum

Click on tab 2.

The data under Costs as well as the Grand Sum is displayed.

Click on tab 3.

Point to Sheet

Now, click on tab 3.

We get the detailed view of all the data along with the Grand Sum of the data under Costs.

Close the file. Let’s close this file without saving the changes.
Reopen the file again. And let’s re-open the file.
Slide:Validity We’ll now learn about the Validity option in Calc.
  • The Validity option validates data in the spreadsheet.
  • This is done by specifying the Validation rules for the selected cells in the spreadsheet.
Narration Only For example, we can specify the mode of payment for the items bought using Validation.
Type the heading MOP next to Received. Let’s add a new column Mode of Payment as MOP next to the column Received.
Point to M-O-P

Point to Items Column

Point to Items Component

Below the heading MOP, the cells can be used to display the mode of payments.

The MOP for the Items in Column B can be shown here.

That is, Salary, House Rent, Electricity Bill and the other components.

Click on the cell F2 just below MOP.

Point to Salary.

Now, let us click on the empty cell just below the heading MOP.

This will have the mode of payment for the item Salary.

From the Menu bar >>click on Data >> click on Validity Go to the Data menu in the menu bar and select Validity option.
Point to the Validity dialog box.

Click the Criteria tab.

The Validity dialog box pops up.

Let’s click the Criteria tab at the top if not already selected.

From the Allow field drop-down, click List. From the Allow field drop-down, select List.
Point to the Entries box. The Entries box gets displayed.

Let’s enter the options which will appear on validating the selected cell.

In the Entries box >> type In Cash [Enter] We’ll type the first mode of payment as In Cash and press Enter.
In the Entries box >>

type Cheque [Enter]

type Online [Enter]

Next, we’ll type the following entries as shown.
Click on OK. Click on the OK button at the bottom of the dialog box.
Point to the cells. Notice the down arrow displayed alongside cell F2, which means the cell is validated.
Click the down arrow on the cell

Point to the three options.

Click the down arrow on the cell

Now, click the down arrow.

We see the options that we entered as Mode of Payments in the Entries box here.

Click on the down arrow again to collapse the list.

Click on Clone Formatting icon To validate the cells below, click on the Clone Formatting icon in the Standard toolbar.
Drag and select 5 cells F3 to F7 below it. Then, click on the cell F3 and press the left mouse button.

Drag along the cursor till cell F7 and release the mouse button.

Select a cell and show that it has been validated. All selected cells are validated in one go.
Click on the cell just below MOP >> click on down arrow


Click on Online

Now click on the cell just below the heading MOP and then click on the down arrow alongside.

For mode of payment let’s select Online.

Narration Only

House rent >> Cheque

Electricity Bill >> Online

Phone BIll >> Online

Laundry >> In Cash

Miscellaneous >> In Cash

In the same manner, you can select the options in each of the validated cells.

Select according to the mode of payment made for each Items as shown.

Narration Only Likewise we can format and validate other columns in the spreadsheet.
Ctrl + S Let’s save all these changes and close the file.
Narration Only: This brings us to the end of this tutorial, let us summarize.
Slide: Summary In this tutorial, we learnt to:
  • Protect a spreadsheet with a password
  • Protect a single sheet with a password
  • Define Ranges for a database
  • Use the Subtotal option and
  • Validate cells.
Slide: Assignment As an assignment
  • Open Spreadsheet-Practice.ods file
  • Use the password protect option to protect the sheet named Department Sheet
  • Use the Subtotals option and find the Grand Sum of Salary column
  • Close the file without saving 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

Acknowledgement to DesiCrew

This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011

This is Arvind along with Spoken Tutorial team from IIT Bombay signing off.

Thank you for watching.

Contributors and Content Editors

Arvindpillai90, Nancyvarkey, Pravin1389