View source for LibreOffice-Suite-Calc/C3/Advanced-Formatting-and-Protection/English

=Resources for recording=
[[Media:Advanced Formatting and Protection.zip |Advanced Formatting and Protection]]




{| border=1
|| '''VISUAL CUE'''
|| '''NARRATION'''

|-
|| '''Show Slide '''
Welcome to the Spoken Tutorial on '''Advanced Formatting and Protection''' in LibreOffice Calc.
|| 
Welcome to the Spoken Tutorial on '''Advanced Formatting and Protection '''in LibreOffice Calc.

|-
|| '''Show Slide '''
'''Learning Objectives'''
In this tutorial we will learn how to:
* Password protect a spreadsheet	
* Password protect a single sheet or a tab in a spreadsheet.
* Define Ranges for a database.
* Use the Subtotal option.
* Validate cells.
|| 

In this tutorial we will learn how to:
* Password protect a spreadsheet
* Password protect a single sheet or a tab in a spreadsheet.
* Define Ranges for a database.
* Use the Subtotal option.
* Validate cells.

|-
|| '''Show Slide '''
'''OS and versions'''
* Ubuntu Linux version 10.04
* LibreOffice Suite version 3.3.4

|| 
Here we are using Ubuntu Linux version 10.04 and  LibreOffice Suite version 3.3.4.

|-
|| Open “'''Personal-Finance-Tracker.ods'''”
|| Let’s open “'''Personal-Finance-Tracker.ods'''”.

|-
|| Show Slide
'''Protect the file by using a password.'''

'''this option'''
'''*  '''ensures that only people 
*  who know the password'''   '''
*  can open this file

|| First let us learn to password protect this file. 
This option ensures that only people who know the password can open this file.

|-
|| From the '''Main '''menu, click '''File '''and '''Save As'''.
the '''Save''' dialog box appears
|| From the '''Main '''menu, click '''File '''and '''Save As'''.
the '''Save '''dialog box appears

|-
|| Check the Save with password option.

then click '''Save'''
|| Next, check the '''Save with password '''box.

then click '''Save'''

|-
|| Point to the the file already exists overwrite? dialog box.

Here let us replace the file.

Click '''Yes.'''

|| As we are using the '''Save As''' option, we can either save it as a different file or replace the same file.

Here let us replace the file.

Click '''Yes.'''

|-
|| enter the password : password123


and re enter the password in confirm box also  
Click Ok
|| Then enter a password 
and re enter the password in confirm box also and click OK 

|-
|| Close the '''Personal-Finance-Tracker.ods'''
|| Then close the '''Personal-Finance-Tracker.ods'''

|-
|| Open the file '''Personal-Finance-Tracker.ods'''
|| Now, let us reopen this file and check what happens.

|-
|| Enter the password: password 111.
Click OK.
|| The Enter Password dialog box appears!
Let us enter a wrong password here.
Click OK.

|-
|| Point to the error message
|| We get an error message which says that the password is incorrect.

|-
|| Enter the password: password123.
Click OK.
|| Now type the correct password. 

The file opens.

|-
|| 
|| How do we remove the password option?
It’s simple too.

|-
|| From the '''Main '''menu, click '''File '''and '''Save As'''. Un-check Save with Password option.
|| We un-check the Save with password option.

|-
|| Point to the '''Save '''option.
|| Again as we use the '''Save '''option, we can either save it as a different file or replace the same file.

|-
|| Point to the the file already exists overwrite? dialog box.
|| Here let us replace the file.


|-
|| Click Yes.
|| Click '''Yes'''.

|-
|| Close and open the file.
|| Let us close and open this file.
You do not require a password to open the file.

|-
|| 
|| Let’s learn how to password protect the individual sheets from this file.  

|-
|| From the Menu bar >> click on “'''Tools'''” >> go to “'''Protect Document'''” >> click on “'''Sheet'''”.
|| From the Menu bar, click on “'''Tools'''”, “'''Protect Document'''” and “'''Sheet'''”.

|-
|| Point to the “'''Protect Sheet'''” dialog box.
|| The “'''Protect Sheet'''” dialog box appears.

|-
|| Un-check “'''Select Locked cells'''” and “'''Select Unlocked cells'''”.
|| To protect the sheet, first, un-check the options''' '''“'''Select Locked cells'''” and “'''Select Unlocked cells'''”.

|-
|| In the “'''Password'''” type “'''abc'''”.
Re-enter the password “'''abc'''” in the “'''Confirm'''” field.
Click '''OK'''.
|| Now, in the “'''Password'''” field, let’s enter “'''abc'''”, in the lower case, and re-enter the password in the  “'''Confirm'''” field.
Click '''OK'''.

|-
|| Select any cell and show that you cannot enter the data.
|| Now, let’s try to select and modify data in a cell.

|-
|| Move cursor along the sheet click to show that no cells can be selected.
|| We are not able to select any cell!
The sheet cannot be modified!

|-
|| 

Click on Sheet2.
|| But what about the other sheets?  

Let’s click on Sheet2.

|-
|| Click on '''A6''' and type '''“testing”'''.
|| Let’s select a cell and try to edit it

Calc allows us to edit the cells in the other sheets.

|-
|| Go back to the first sheet.
|| Let’s go back to the first sheet.

|-
|| 
|| Now, let’s un-protect the sheet.
This is simple.

|-
|| From the Menu bar >> click on “'''Tools'''” >> go to “'''Protect Document'''” >> click on “'''Sheet'''”.
|| From the Menu bar, click on “'''Tools'''”, “'''Protect Document'''” and “'''Sheet'''”.

|-
|| Point to the “'''Unprotect Sheet'''” dialog box.
|| A dialog box, that requests for the password, appears. 

|-
|| Enter “'''abc'''” as password >> click “OK”.
|| Enter “'''abc'''” in small case in it and click '''OK'''.

|-
|| Move cursor along sheet and select cells.
|| We are able to select the cells again!

|-
|| 
|| Let’s learn about “'''Ranges'''”.

|-
|| Show Slide
'''Ranges'''
* 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''' 
* Each '''cell '''in a row corresponds to a '''database field'''.
* You can sort, group, search, and perform calculations on the range as you would in any database.
|| 

* You 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''' and
* Each '''cell '''in a row corresponds to a '''database field'''.
* You can sort, group, search, and perform calculations on the range as you would in any database.

|-
|| 
|| Let’s define a database in “'''Personal-Finance-Tracker.ods'''” and sort the data.

|-
|| 
|| First, let’s select the items which we require in the database.

|-
|| 
Select all data under “'''SN'''”to Account together.
Select only upto Miscelleneous.  
Do not select '''Sub-Total '''and '''Average'''
|| Let’s select all the data under the heading “'''SN'''”to Account together. We have already learnt how to select data.

|-
|| 
|| Now, let’s name our database.

|-
|| From the Menu bar >> Click on “'''Data'''” >> click on “'''Define Range'''”
|| From the Menu bar, click “'''Data'''” and then click on “'''Define Range'''”.

|-
|| In the “'''Name'''” field, type “'''dtbs'''”.
|| In the “'''Name'''” field, type“'''dtbs'''” which is the short-form of database.

|-
|| Click “'''OK'''”.
|| Click “'''OK'''”.

|-
|| From the Menu bar >> Click on “'''Data'''” >> click on “'''Select Range'''”
|| Again, from the Menu bar, click “'''Data'''” and “'''Select Range'''”.

|-
|| 
Point to “'''dtbs'''” in the dialog box.
|| Notice, that in the “'''Select Database Range'''” dialog box that appears, the name “'''dtbs'''” is listed as a database.

|-
|| Click on “'''OK'''”
|| Click on the “'''OK'''” button.

|-
|| 
|| Now let’s sort the data in this database.


|-
|| From the Menu bar >> Click on “'''Data'''” >> click on “'''Sort'''”
|| From the Menu bar, click “'''Data'''” and “'''Sort'''”.

|-
|| Point to the '''Sort '''dialog box.

In “'''Sort by'''” field >> select “'''SN'''”
Click on “'''Descending'''” next to it.
|| In the '''Sort '''dialog box that appears, click  the “'''Sort''' '''by'''”   '''by'''”field and select “'''SN'''”. 

Next, from the right side, select “'''Descending'''”.

|-
|| Under “'''Then''' '''by'''” field click “'''Cost'''”

Click on “'''Descending'''” next to it.
|| Under the first “'''Then by'''” field, click on the drop-down, and select “'''Cost'''”.
Again, from the right side, select “'''Descending'''”.

|-
|| Under second “'''Then''' '''by'''” field click “'''Spent'''”

Click on “'''Descending'''” next to it.
|| In the second “'''Then by'''” field, click on the drop-down, select “'''Spent'''” and then, again select “'''Descending'''”.

|-
|| Click '''OK'''.
|| Click '''OK'''.

|-
|| Point to the sorted data.
|| The data is sorted under the heading “'''SN'''” and in the descending order!

|-
|| 
|| In a similar manner, we can perform other operations in the database too!

|-
|| '''UNDO '''the actions performed.
Press the CTRL+Z keys to undo the sort action.
|| Let’s press the CTRL+Z keys to undo the sort and get the original data.

|-
|| 
|| Now, let’s learn how how to use the “'''Subtotal'''” option in Calc.

|-
|| '''Show Slide'''
'''Subtotal'''
* Calculates the grand total of data under different headings 
* Using a mathematical function of our choice.
|| 
The “'''Subtotal'''” option, calculates the grand total of data under different headings, using a mathematical function of our choice.

|-
|| 
|| Let’s find the subtotal of the data under the heading “'''Cost'''”.

|-
|| Delete the entry in row number 8.
select all data under SN to ACCOUNT together
but exclude '''Average'''
|| First, let’s delete the entry in row number 8.
select all data under SN to ACCOUNT together

|-
|| From the Menu bar, click “'''Data'''” and “'''Subtotals'''”.
|| Next, from the Menu bar, click “'''Data'''” and “'''Subtotals'''”.

|-
|| Point to the '''Subtotals '''dialog box.
In “'''Group''' '''by'''” click and choose “'''SN'''”.
|| In the '''Subtotals '''dialog box that appears, from the “'''Group by'''” field, let us select “'''SN'''”. 
This groups the data by Serial Number.

|-
|| In “'''Calculate''' '''subtotals for'''” click on “'''Cost'''”.
|| Next, in the “'''Calculate''' '''subtotals for'''” field click on the “'''Cost'''” option.
This will calculate the total of all the entries under it.

|-
|| Under “'''Use function'''” choose “'''Sum'''”.
Click OK.
|| Under the “'''Use function'''” field ,let’s choose “'''Sum'''” and click '''OK'''.

|-
|| Point to “'''Grand Total'''”.
|| Notice, that the “'''Grand total'''” of the entries under the heading “'''Costs'''” is displayed on the spreadsheet.

|-
|| Point to “1”,”2”,”3” on the left hand side.
|| On the left side of the sheet there are 3 new tabs “'''1'''” ”'''2'''”and “'''3'''”

|-
|| 
|| These tabs give 3 different views of the data.

|-
|| Click on tab “'''1'''”.
|| Let’s click on tab '''1'''.
Notice, that only the grand total of the data under “'''Costs'''” is displayed.

|-
|| Click on tab “'''2'''”.
|| Click on tab “'''2'''” .
The data under “'''Costs” '''as well as the grand total is displayed.

|-
|| Click on tab “'''3'''”.
|| Now, click on tab “'''3'''”.
We get the detailed view of the sheet along with the grand total of the data under “'''Costs'''”.

|-
|| Close the file.
|| Let’s close this file.

|-
|| A dialog box with message Save or Discard changes appears.
Click '''Discard'''.
|| A dialog box with message Save or Discard changes appears.
Click '''Discard'''.

|-
|| Close this file and reopen it again.
|| Now let’s re-open the file.  

|-
|| 
|| Now, let’s learn about the “'''Validity'''” option in LibreOffice Calc.

|-
|| '''Show Slide'''
'''Validity'''
* Validates data in the spreadsheet.
* This is done by specifying the “'''Validation rules'''” for the selected cells 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.

|-
|| 
|| For example, in “'''Personal-Finance-Tracker.ods'''”, we can specify the mode of payment for the items bought using '''Validation'''.

|-
|| Delete “Date” and its contents.
|| Now, let us delete the heading “'''Date'''” and its contents.

|-
|| Type the heading “'''MOP'''” next to “'''Received'''”.
|| Let us give another heading for “'''Mode''' '''of Payment'''” as “'''M-O-P'''” next to the heading “'''Received'''”.

|-
|| 
Point to “'''M-O-P'''”

|| Below the heading “'''M-O-P'''”, the cells can be used to display the mode of payments, for the data entries, under the heading “'''Items'''”,
that is,”'''Salary'''”,”'''Electricity''' '''Bills'''” and the other components.

|-
|| Click on empty cell just below “'''MOP'''”.

Point to “'''Salary'''”.
|| Now, let us click on the empty cell just below the heading”'''M-O-P'''” 

This will have the mode of payment for the component “'''Salary'''”.

|-
|| From the Menu bar >>click on “'''Data'''” >> click on “'''Validity'''”
|| Now, from the Menu bar, click “'''Data'''” and “'''Validity'''”.


|-
|| Point to the “'''Validity'''” dialog box.
Click the “'''Criteria'''” tab.
|| The “'''Validity'''” dialog box appears.
Let’s click the “'''Criteria'''” tab.

|-
|| From the “'''Allow” '''field drop-down, click “'''List'''”.
|| From the “'''Allow” '''field drop-down, click “'''List'''”.

|-
|| Point to the “'''Entries'''” box.
|| The “'''Entries'''” box pops-up.
Let’s enter the options which will appear on validating the selected cell.

|-
|| In the “'''Entries'''” box >> type “'''In Cash'''” >> press the '''Enter '''key.
|| Let’s type the first mode of payment as  “'''In Cash'''”, and then press the “'''Enter'''” key from the keyboard.

|-
|| In the “'''Entries'''” box >> type “'''Demand Draft'''” >> press the '''Enter '''key.
|| Next, let’s type the second mode of payment as “'''Demand Draft'''”.

|-
|| Click on “'''OK'''”.
|| Click '''OK'''.

|-
|| Point to the cells.
|| The selected cells are validated!

|-
|| Press the down arrow on the cell >> point to the two options.
|| Now, press the down arrow that is displayed alongside.

Can you see the options that we entered as '''Mode of Payments''' in the “'''Entries'''” box?

|-
|| Click on “'''Format paintbrush'''” 
|| To validate the cells below, first click on the “'''Format Paintbrush'''” option on the toolbar.



|-
|| Drag and select 5 cells below it.

|| Then,  select the cells below the validated cell by pressing the left mouse button, and then dragging along the cells.
Now, release the mouse button.

|-
|| Select a cell and show that it has been validated.
|| All the selected cells are validated in the same manner.

|-
|| Click on the cell just below “'''MOP'''” >> click on down arrow
|| Now click on the cell just below the heading “'''M-O-P'''” and then click on the down arrow.


|-
|| Point that both the options are displayed.
|| Both the options for mode of payment are displayed.

|-
|| Click on  “'''In Cash'''”
|| Let’s select the “'''In Cash'''” option.

|-
|| Click on the cell below it >> click on down arrow and select “'''Demand''' '''Draft'''”.
|| In the same manner, you can select “'''Cash'''” or “'''Demand''' '''Draft'''” in each of the validated cells, according to the mode of payment made.

|-
|| '''Show Slide '''

''''''SUMMARY''''''
* Password protect a spread sheet
* Password protect a single sheet or tab in a spreadsheet file.
* Define Ranges for database.
* Use Subtotals.
* Validate cells.
|| This brings us to the end of this Spoken Tutorial on LibreOffice Calc:
To summarize, we have learnt how to:
* Password protect a spread sheet
* Password protect a single sheet or tab in a spreadsheet file. 
* How to define Ranges for database.
* How to use Subtotals.
* How to validate cells.

|-
|| Show About Slide

About the Spoken Tutorial Project

* Watch the video available at http://spoken-tutorial.org/What_is_a_Spoken_Tutorial

* It summarises the Spoken Tutorial project 

* If you do not have good bandwidth, you can download and watch it
||

* Watch the video available at the following link

* It summarises the Spoken Tutorial project

*If you do not have good bandwidth, you can download and watch it
|-
|| Show About Slide

Spoken Tutorial Workshops

The Spoken Tutorial Project Team

* Conducts workshops using spoken tutorials

*Gives certificates for those who pass an online test

* For more details, please write to contact@spoken-tutorial.org
||

The Spoken Tutorial Project Team

* Conducts workshops using spoken tutorials.

* Gives certificates for those who pass an online test

* For more details, please write to contact at spoken hyphen tutorial dot org
|-
|| Show Acknowledgement Slide

Acknowledgements 

* Spoken Tutorial Project is a part of the Talk to a Teacher project

* It is supported by the National Mission on Education through ICT, MHRD, Government of India.

* More information on this Mission is available at 

* http://spoken-tutorial.org/NMEICT-Intro
||

*Spoken Tutorial Project is a part of the Talk to a Teacher project

* It is supported by the National Mission on Education through ICT, MHRD, Government of India

*More information on this Mission is available at

* spoken hyphen tutorial dot org slash NMEICT hyphen Intro
|-
|| Show About the contributor Slide

About the Contributor

* This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd

* www.desicrew.in

* Thanks for joining
||

*This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd

* Thanks for joining
|-
|}