LibreOffice-Suite-Calc/C3/Advanced-Formatting-and-Protection/English

Resources for recording

Advanced Formatting and Protection

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

Contributors and Content Editors

Chandrika