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

From Script | Spoken-Tutorial
Jump to: navigation, search
 
(11 intermediate revisions by 3 users not shown)
Line 9: Line 9:
 
|-
 
|-
 
||00:00
 
||00:00
−
||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'''.  
  
 
|-
 
|-
 
||00:07
 
||00:07
−
||In this tutorial we will learn how to:  
+
||In this tutorial, we will learn how to: '''Password protect''' a spreadsheet  
−
Password protect a spreadsheet  
+
 
−
Password protect a single sheet or a tab in a spreadsheet.
+
Password protect a single '''sheet''' or a tab in a spreadsheet, Define '''Ranges''' for a database  
−
Define Ranges for a database.
+
 
−
Use the Subtotal option.
+
Use the '''Subtotal''' option and '''Validate''' cells.  
−
Validate cells.  
+
 
 
|-
 
|-
 
||00:25
 
||00:25
−
||Here we are using Ubuntu Linux version 10.04 and LibreOffice Suite version 3.3.4.  
+
||Here we are using:
 +
 
 +
'''Ubuntu Linux''' version '''10.04''' and '''LibreOffice Suite''' version '''3.3.4'''.  
  
 
|-
 
|-
Line 29: Line 31:
 
|-
 
|-
 
||00:40
 
||00:40
−
||First let us learn to password protect this file.  
+
||First, let us learn to '''password protect''' this file.  
 +
 
 
|-
 
|-
 
||00:44
 
||00:44
 
||This option ensures that only people who know the password can open this file.  
 
||This option ensures that only people who know the password can open this file.  
 +
 
|-
 
|-
 
||00:51
 
||00:51
−
||From the Main menu, click File and Save As.  
+
||From the '''Main menu''', click '''File''' and '''Save As'''.  
 +
 
 
|-
 
|-
 
||00:55
 
||00:55
−
||the Save dialog box appears  
+
||The '''Save''' dialog-box appears.
  
 
|-
 
|-
 
||00:58
 
||00:58
−
||Next, check the Save with password box.  
+
||Next, check the ''''Save with password'''' box.  
 +
 
 
|-
 
|-
 
||01:03
 
||01:03
−
||then click Save  
+
||Then click '''Save'''.
 +
 
 
|-
 
|-
 
||01:06
 
||01:06
−
||As we are using the Save As option, we can either save it as a different file or replace the same file.  
+
||As we are using the '''Save As''' option, we can either save it as a different file or replace the same file.  
 +
 
 
|-
 
|-
 
||01:15
 
||01:15
−
||Here let us replace the file.  
+
||Here, let us replace the file.  
  
 
|-
 
|-
 
||01:18
 
||01:18
−
||Click Yes.  
+
||Click '''Yes'''.  
  
 
|-
 
|-
 
||01:20
 
||01:20
−
||Then enter a password  
+
||Then enter a '''password'''
 +
 
 
|-
 
|-
 
||01:23
 
||01:23
−
||and re enter the password in confirm box also and click OK  
+
||and re-enter the password in '''Confirm box''' also and click '''OK'''.
  
 
|-
 
|-
 
||01:30
 
||01:30
−
||Then close the Personal-Finance-Tracker.ods
+
||Then, close the "Personal-Finance-Tracker.ods".
  
 
|-
 
|-
 
||01:36
 
||01:36
−
||Now, let us reopen this file and check what happens.  
+
||Now, let us re-open this file and check what happens.  
  
 
|-
 
|-
 
||01:41
 
||01:41
−
||The Enter Password dialog box appears!  
+
||The '''Enter Password''' dialog-box appears!  
  
 
|-
 
|-
Line 82: Line 91:
 
|-
 
|-
 
||01:48
 
||01:48
−
||Click OK.  
+
||Click '''OK'''.  
  
 
|-
 
|-
Line 90: Line 99:
 
|-
 
|-
 
||01:56
 
||01:56
−
||Now type the correct password.  
+
||Now, type the correct password.  
  
 
|-
 
|-
 
||01:59
 
||01:59
 
||The file opens.  
 
||The file opens.  
 +
 
|-
 
|-
 
||02:01
 
||02:01
Line 101: Line 111:
 
|-
 
|-
 
||02:07
 
||02:07
−
||We un-check the Save with password option.  
+
||We un-check the '''Save with password''' option.  
  
 
|-
 
|-
 
||02:10
 
||02:10
−
||Again as we use the Save option, we can either save it as a different file or replace the same file.  
+
||Again, as we use the '''Save''' option, we can either save it as a different file or replace the same file.  
  
 
|-
 
|-
 
||02:18
 
||02:18
−
||Here let us replace the file.  
+
||Here, let us replace the file.  
  
 
|-
 
|-
 
||02:21
 
||02:21
−
||Click Yes.  
+
||Click '''Yes'''.  
  
 
|-
 
|-
 
||02:23
 
||02:23
−
||Let us close and open this file.  
+
||Let us '''close''' and '''open''' this file.  
  
 
|-
 
|-
Line 125: Line 135:
 
|-
 
|-
 
||02:31
 
||02:31
−
||Let’s learn how to password protect the individual sheets from this file.  
+
||Let’s learn how to '''password protect''' the individual sheets from this file.  
  
 
|-
 
|-
 
||02:37
 
||02:37
−
||From the Menu bar, click on “Tools”, “Protect Document” and “Sheet”.  
+
||From the menu bar, click on '''Tools > Protect Document''' and '''Sheet'''.  
  
 
|-
 
|-
 
||02:44
 
||02:44
−
||The “Protect Sheet” dialog box appears.  
+
||The '''“Protect Sheet”''' dialog-box appears.  
  
 
|-
 
|-
 
||02:47
 
||02:47
−
||To protect the sheet, first, un-check the options “Select Locked cells” and “Select Unlocked cells”.  
+
||To protect the sheet, first un-check the options '''“Select Locked cells”''' and '''“Select Unlocked cells”'''.  
  
 
|-
 
|-
 
||02:56
 
||02:56
−
||Now, in the “Password” field, let’s enter “abc”, in the lower case, and re-enter the password in the “Confirm” field.  
+
||Now, in the '''“Password”''' field, let’s enter “abc”, in the lower case and re-enter the password in the '''“Confirm”''' field.  
  
 
|-
 
|-
 
||03:07
 
||03:07
−
||Click OK.  
+
||Click '''OK'''.Now, let’s try to select and modify data in a '''cell'''.  
−
 
+
−
|-
+
−
||03:08
+
−
||Now, let’s try to select and modify data in a cell.  
+
  
 
|-
 
|-
Line 165: Line 171:
 
|-
 
|-
 
||03:24
 
||03:24
−
||Let’s click on Sheet2.  
+
||Let’s click on '''Sheet2'''.  
  
 
|-
 
|-
 
||03:27
 
||03:27
−
||Let’s select a cell and try to edit it  
+
||Let’s select a cell and try to '''edit''' it.
  
 
|-
 
|-
 
||03:30
 
||03:30
−
||Calc allows us to edit the cells in the other sheets.  
+
||'''Calc''' allows us to edit the cells in the other sheets.  
  
 
|-
 
|-
Line 182: Line 188:
 
||03:38
 
||03:38
 
||Now, let’s un-protect the sheet.  
 
||Now, let’s un-protect the sheet.  
 +
 
|-
 
|-
 
||03:41
 
||03:41
 
||This is simple.  
 
||This is simple.  
 +
 
|-
 
|-
 
||03:43
 
||03:43
−
||From the Menu bar, click on “Tools”, “Protect Document” and “Sheet”.  
+
||From the Menu bar, click on '''“Tools” > “Protect Document”''' and '''“Sheet”'''.  
  
 
|-
 
|-
 
||03:49
 
||03:49
−
||A dialog box, that requests for the password, appears.  
+
||A dialog-box that requests for the password, appears.  
  
 
|-
 
|-
 
||03:53
 
||03:53
−
||Enter “abc” in small case in it and click OK.  
+
||Enter “abc” in small case in it and click '''OK'''.  
  
 
|-
 
|-
Line 203: Line 211:
 
|-
 
|-
 
||04:03
 
||04:03
−
||Let’s learn about “Ranges”.  
+
||Let’s learn about '''Ranges'''.  
  
 
|-
 
|-
 
||04:06
 
||04:06
−
||     You can define a range of cells in a spreadsheet and use it as a database.  
+
|| You can define a range of cells in a spreadsheet and use it as a database.  
  
 
|-
 
|-
 
||04:12
 
||04:12
−
||Each row in this database range corresponds to a database record and  
+
||Each row in this '''database range''' corresponds to a database '''record''' and  
 +
 
 
|-
 
|-
 
||04:17
 
||04:17
−
||Each cell in a row corresponds to a database field.  
+
||each '''cell''' in a row corresponds to a database '''field'''.  
 +
 
 
|-
 
|-
 
||04:22
 
||04:22
−
||You can sort, group, search, and perform calculations on the range as you would in any database.  
+
||You can '''sort''', group, search and perform calculations on the range as you would in any database.  
  
 
|-
 
|-
 
||04:30
 
||04:30
−
||Let’s define a database in “Personal-Finance-Tracker.ods” and sort the data.
+
||Let’s define a database in “Personal-Finance-Tracker.ods” and '''sort''' the data.
  
 
|-
 
|-
Line 229: Line 239:
 
|-
 
|-
 
||04:43
 
||04:43
−
||Let’s select all the data under the heading “SN”to Account together. We have already learnt how to select data.  
+
||Let’s select all the data under the heading '''“SN”''' to '''"Account"''' together. We have already learnt how to select data.  
  
 
|-
 
|-
Line 237: Line 247:
 
|-
 
|-
 
||04:56
 
||04:56
−
||From the Menu bar, click “Data” and then click on “Define Range”.  
+
||From the Menu bar, click '''Data''' and then click on '''Define Range'''.  
  
 
|-
 
|-
 
||05:02
 
||05:02
−
||In the “Name” field, type“dtbs” which is the short-form of database.
+
||In the '''“Name”''' field, type “dtbs” which is the short-form of database.
  
 
|-
 
|-
 
||05:08
 
||05:08
−
||Click “OK”.  
+
||Click '''OK'''.  
  
 
|-
 
|-
 
||05:10
 
||05:10
−
||Again, from the Menu bar, click “Data” and “Select Range”.  
+
||Again, from the Menu bar, click '''Data''' and '''Select Range'''.  
  
 
|-
 
|-
 
||05:15
 
||05:15
−
||Notice, that in the “Select Database Range” dialog box that appears, the name “dtbs” is listed as a database.  
+
||Notice that in the '''“Select Database Range”''' dialog-box that appears, the name “dtbs” is listed as a database.  
  
 
|-
 
|-
 
||05:24
 
||05:24
−
||Click on the “OK” button.  
+
||Click on the '''OK''' button.  
  
 
|-
 
|-
 
||05:27
 
||05:27
−
||Now let’s sort the data in this database.  
+
||Now, let’s sort the data in this database.  
  
 
|-
 
|-
 
||05:31
 
||05:31
−
||From the Menu bar, click “Data” and “Sort”.  
+
||From the Menu bar, click '''Data''' and '''Sort'''.  
  
 
|-
 
|-
 
||05:35
 
||05:35
−
||In the Sort dialog box that appears, click the “Sort by” by”field and select “SN”.  
+
||In the '''Sort''' dialog-box that appears, click the '''“Sort by”'''  field and select '''“SN”'''.  
  
 
|-
 
|-
 
||05:42
 
||05:42
−
||Next, from the right side, select “Descending”.  
+
||Next, from the right side, select '''“Descending”'''.  
  
 
|-
 
|-
 
||05:47
 
||05:47
−
||Under the first “Then by” field, click on the drop-down, and select “Cost”.  
+
||Under the first '''“Then by”''' field, click on the drop-down and select '''“Cost”'''.  
  
 
|-
 
|-
 
||05:54
 
||05:54
−
||Again, from the right side, select “Descending”.  
+
||Again, from the right side, select '''“Descending”'''.  
  
 
|-
 
|-
 
||05:58
 
||05:58
−
||In the second “Then by” field, click on the drop-down, select “Spent” and then, again select “Descending”.  
+
||In the second '''“Then by”''' field, click on the drop-down, select '''“Spent”''' and then again select '''“Descending”'''.  
  
 
|-
 
|-
 
||06:07
 
||06:07
−
||Click OK.  
+
||Click '''OK'''.  
  
 
|-
 
|-
 
||06:09
 
||06:09
−
||The data is sorted under the heading “SN” and in the descending order!
+
||The data is sorted under the heading '''“SN”''' and in the descending order!
  
 
|-
 
|-
Line 301: Line 311:
 
|-
 
|-
 
||06:21
 
||06:21
−
||Let’s press the CTRL+Z keys to undo the sort and get the original data.  
+
||Let’s press the '''CTRL+Z''' keys to '''undo''' the '''sort''' and get the original data.  
  
 
|-
 
|-
 
||06:28
 
||06:28
−
||Now, let’s learn how how to use the “Subtotal” option in Calc.  
+
||Now, let’s learn how to use the '''“Subtotal”''' option in '''Calc'''.  
  
 
|-
 
|-
Line 313: Line 323:
 
|-
 
|-
 
||06:43
 
||06:43
−
||Let’s find the subtotal of the data under the heading “Cost”.  
+
||Let’s find the subtotal of the data under the heading '''“Cost”'''.  
  
 
|-
 
|-
Line 321: Line 331:
 
|-
 
|-
 
||06:53
 
||06:53
−
||select all data under SN to ACCOUNT together  
+
||select all data under '''SN''' to '''ACCOUNT''' together.
  
 
|-
 
|-
 
||06:59
 
||06:59
−
||Next, from the Menu bar, click “Data” and “Subtotals”.  
+
||Next, from the Menu bar, click '''Data''' and '''Subtotals'''.  
  
 
|-
 
|-
 
||07:04
 
||07:04
−
||In the Subtotals dialog box that appears, from the “Group by” field, let us select “SN”.  
+
||In the '''Subtotals''' dialog-box that appears, from the '''“Group by”''' field, let us select '''“SN”'''.  
  
 
|-
 
|-
 
||07:11
 
||07:11
−
||This groups the data by Serial Number.  
+
||This groups the data by '''Serial Number'''.  
 +
 
 
|-
 
|-
 
||07:15
 
||07:15
−
||Next, in the “Calculate subtotals for” field click on the “Cost” option.  
+
||Next, in the '''“Calculate subtotals for”''' field, click on the '''“Cost”''' option.  
  
 
|-
 
|-
Line 344: Line 355:
 
|-
 
|-
 
||07:26
 
||07:26
−
||Under the “Use function” field ,let’s choose “Sum” and click OK.  
+
||Under the '''“Use function”''' field , let’s choose '''“Sum”''' and click '''OK'''.  
  
 
|-
 
|-
 
||07:33
 
||07:33
−
||Notice, that the “Grand total” of the entries under the heading “Costs” is displayed on the spreadsheet.  
+
||Notice that the “Grand total” of the entries, under the heading “Costs”, is displayed on the spreadsheet.  
  
 
|-
 
|-
 
||07:41
 
||07:41
−
||On the left side of the sheet there are 3 new tabs “1” ”2”and “3”  
+
||On the left side of the '''sheet''' there are 3 new tabs “1”, ”2”and “3”.
  
 
|-
 
|-
Line 364: Line 375:
 
|-
 
|-
 
||07:54
 
||07:54
−
||Notice, that only the grand total of the data under “Costs” is displayed.  
+
||Notice that only the grand total of the data under “Costs” is displayed.  
  
 
|-
 
|-
Line 380: Line 391:
 
|-
 
|-
 
||08:11
 
||08:11
−
||We get the detailed view of the sheet along with the grand total of the data under “Costs”.  
+
||We get the detailed view of the '''sheet''' along with the '''grand total''' of the data under “Costs”.  
  
 
|-
 
|-
Line 388: Line 399:
 
|-
 
|-
 
||08:21
 
||08:21
−
||A dialog box with message Save or Discard changes appears.  
+
||A dialog-box with the message '''Save''' or '''Discard changes''' appears.  
  
 
|-
 
|-
 
||08:26
 
||08:26
−
||Click Discard.  
+
||Click '''Discard'''.  
  
 
|-
 
|-
 
||08:28
 
||08:28
−
||Now let’s re-open the file.  
+
||Now, let’s re-open the file.  
  
 
|-
 
|-
 
||08:31
 
||08:31
−
||Now, let’s learn about the “Validity” option in LibreOffice Calc.  
+
||Now, let’s learn about the '''“Validity”''' option in LibreOffice Calc.  
  
 
|-
 
|-
 
||08:37
 
||08:37
−
|| The “Validity” option validates data in the spreadsheet.  
+
||The “Validity” option validates data in the spreadsheet.  
  
 
|-
 
|-
 
||08:41
 
||08:41
−
||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.  
  
 
|-
 
|-
 
||08:49
 
||08:49
−
||For example, in “Personal-Finance-Tracker.ods”, we can specify the mode of payment for the items bought using Validation.  
+
||For example- in “Personal-Finance-Tracker.ods”, we can specify the mode of payment for the items bought, using '''Validation'''.  
  
 
|-
 
|-
Line 420: Line 431:
 
|-
 
|-
 
||09:04
 
||09:04
−
||Let us give another heading for “Mode of Payment” as “M-O-P” next to the heading “Received”.  
+
||Let us give another heading for “Mode of Payment” as “M-O-P”, next to the heading “Received”.  
  
 
|-
 
|-
 
||09:12
 
||09:12
−
||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”,
+
||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”.
  
 
|-
 
|-
 
||09:21
 
||09:21
−
||that is,”Salary”,”Electricity Bills” and the other components.  
+
||That is, ”Salary”, ”Electricity Bills” and the other components.  
  
 
|-
 
|-
 
||09:27
 
||09:27
−
||Now, let us click on the empty cell just below the heading”M-O-P”  
+
||Now, let us click on the empty cell just below the heading ”M-O-P”.
  
 
|-
 
|-
Line 440: Line 451:
 
|-
 
|-
 
||09:38
 
||09:38
−
||Now, from the Menu bar, click “Data” and “Validity”.  
+
||Now, from the Menu bar, click '''“Data”''' and '''“Validity”'''.  
  
 
|-
 
|-
 
||09:43
 
||09:43
−
||The “Validity” dialog box appears.  
+
||The '''Validity''' dialog-box appears.  
  
 
|-
 
|-
 
||09:47
 
||09:47
−
||Let’s click the “Criteria” tab.  
+
||Let’s click the '''“Criteria”''' tab.  
  
 
|-
 
|-
 
||09:50
 
||09:50
−
||From the “Allow” field drop-down, click “List”.  
+
||From the '''“Allow”''' field drop-down, click '''“List”'''.  
  
 
|-
 
|-
 
||09:55
 
||09:55
−
||The “Entries” box pops-up.  
+
||The '''“Entries”''' box pops-up.  
  
 
|-
 
|-
Line 464: Line 475:
 
|-
 
|-
 
||10:05
 
||10:05
−
||Let’s type the first mode of payment as “In Cash”, and then press the “Enter” key from the keyboard.  
+
||Let’s type the first mode of payment as “In Cash” and then press the '''Enter''' key from the keyboard.  
−
 
+
−
 
+
  
 
|-
 
|-
Line 474: Line 483:
 
|-
 
|-
 
||10:19
 
||10:19
−
||Click OK.  
+
||Click '''OK'''.  
  
 
|-
 
|-
Line 482: Line 491:
 
|-
 
|-
 
||10:25
 
||10:25
−
||Now, press the down arrow that is displayed alongside.  
+
||Now, press the down-arrow that is displayed alongside.  
  
 
|-
 
|-
 
||10:30
 
||10:30
−
||Can you see the options that we entered as Mode of Payments in the “Entries” box?  
+
||Can you see the options that we entered as '''Mode of Payments''' in the '''Entries''' box?  
  
 
|-
 
|-
 
||10:36
 
||10:36
−
||To validate the cells below, first click on the “Format Paintbrush” option on the toolbar.  
+
||To validate the cells below, first click on the '''Format Paintbrush''' option on the toolbar.  
  
 
|-
 
|-
 
||10:43
 
||10:43
−
||Then, select the cells below the validated cell by pressing the left mouse button, and then dragging along the cells.  
+
||Then, select the cells below the validated cell by pressing the left '''mouse button''' and then dragging along the cells.  
  
 
|-
 
|-
 
||10:53
 
||10:53
−
||Now, release the mouse button.  
+
||Now, release the '''mouse button'''.  
  
 
|-
 
|-
Line 506: Line 515:
 
|-
 
|-
 
||11:09
 
||11:09
−
||Now click on the cell just below the heading “M-O-P” and then click on the down arrow.  
+
||Now, click on the '''cell''' just below the heading “M-O-P” and then click on the down-arrow.  
  
 
|-
 
|-
Line 514: Line 523:
 
|-
 
|-
 
||11:21
 
||11:21
−
||Let’s select the “In Cash” option.  
+
||Let’s select the '''In Cash''' option.  
−
 
+
−
 
+
  
 
|-
 
|-
 
||11:25
 
||11:25
−
||In the same manner, you can select “Cash” or “Demand Draft” in each of the validated cells, according to the mode of payment made.  
+
||In the same manner, you can select '''“Cash”''' or '''“Demand Draft”''' in each of the validated cells according to the mode of payment made.  
  
 
|-
 
|-
 
||11:36
 
||11:36
−
||This brings us to the end of this Spoken Tutorial on LibreOffice Calc:
+
||This brings us to the end of this Spoken Tutorial on LibreOffice Calc.
  
 
|-
 
|-
 
||11:42
 
||11:42
−
||To summarize, we have learnt how to:  
+
||To summarize, we have learnt how to: '''Password protect''' a spread sheet  
−
Password protect a spread sheet  
+
 
−
Password protect a single sheet or tab in a spreadsheet file.
+
Password protect a single sheet or tab in a spreadsheet file  
−
How to define Ranges for database.
+
 
−
How to use Subtotals.
+
How to define '''Ranges''' for database  
−
How to validate cells.  
+
 
 +
How to use '''Subtotals''' and
 +
 
 +
How to '''validate''' cells.  
  
 
|-
 
|-
 
||12:01
 
||12:01
−
||Watch the video available at the following link  
+
||Watch the video available at the following link.
  
 
|-
 
|-
 
||12:04
 
||12:04
−
||It summarises the Spoken Tutorial project  
+
||It summarizes the '''Spoken Tutorial''' project.
 +
 
 
|-
 
|-
 
||12:07
 
||12:07
−
||If you do not have good bandwidth, you can download and watch it  
+
||If you do not have good bandwidth, you can download and watch it.
  
 
|-
 
|-
 
||12:11
 
||12:11
−
||The Spoken Tutorial Project Team
+
||The Spoken Tutorial Project team:
  
 
|-
 
|-
Line 556: Line 567:
 
|-
 
|-
 
||12:17
 
||12:17
−
||Gives certificates for those who pass an online test  
+
||Gives certificates for those who pass an online test.
  
 
|-
 
|-
 
||12:20
 
||12:20
−
||For more details, please write to contact at spoken hyphen tutorial dot org  
+
||For more details, please write to:
−
 
+
contact at spoken hyphen tutorial dot org.
  
 
|-
 
|-
 
||12:27
 
||12:27
−
||Spoken Tutorial Project is a part of the Talk to a Teacher project
+
||'''Spoken Tutorial''' project is a part of the '''Talk to a Teacher''' project.
  
 
|-
 
|-
 
||12:31
 
||12:31
−
||It is supported by the National Mission on Education through ICT, MHRD, Government of India
+
||It is supported by the National Mission on Education through ICT, MHRD, Government of India.
  
 
|-
 
|-
 
||12:39
 
||12:39
−
||More information on this Mission is available at  
+
||More information on this mission is available at:
  
 
|-
 
|-
 
||12:42
 
||12:42
−
||spoken hyphen tutorial dot org slash NMEICT hyphen Intro  
+
||'''spoken hyphen tutorial dot org slash NMEICT hyphen Intro'''.
  
 
|-
 
|-
 
||12:50
 
||12:50
−
||This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd
+
||This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd.
−
Thanks for joining
+
Thanks for joining.

Latest revision as of 14:24, 23 March 2017

Resources for recording Advanced Formatting and Protection


Time Narration
00:00 Welcome to the Spoken Tutorial on Advanced Formatting and Protection in LibreOffice Calc.
00:07 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 and Validate cells.

00:25 Here we are using:

Ubuntu Linux version 10.04 and LibreOffice Suite version 3.3.4.

00:35 Let’s open “Personal-Finance-Tracker.ods”.
00:40 First, let us learn to password protect this file.
00:44 This option ensures that only people who know the password can open this file.
00:51 From the Main menu, click File and Save As.
00:55 The Save dialog-box appears.
00:58 Next, check the 'Save with password' box.
01:03 Then click Save.
01:06 As we are using the Save As option, we can either save it as a different file or replace the same file.
01:15 Here, let us replace the file.
01:18 Click Yes.
01:20 Then enter a password
01:23 and re-enter the password in Confirm box also and click OK.
01:30 Then, close the "Personal-Finance-Tracker.ods".
01:36 Now, let us re-open this file and check what happens.
01:41 The Enter Password dialog-box appears!
01:45 Let us enter a wrong password here.
01:48 Click OK.
01:50 We get an error message which says that the password is incorrect.
01:56 Now, type the correct password.
01:59 The file opens.
02:01 How do we remove the password option? It’s simple too.
02:07 We un-check the Save with password option.
02:10 Again, as we use the Save option, we can either save it as a different file or replace the same file.
02:18 Here, let us replace the file.
02:21 Click Yes.
02:23 Let us close and open this file.
02:27 You do not require a password to open the file.
02:31 Let’s learn how to password protect the individual sheets from this file.
02:37 From the menu bar, click on Tools > Protect Document and Sheet.
02:44 The “Protect Sheet” dialog-box appears.
02:47 To protect the sheet, first un-check the options “Select Locked cells” and “Select Unlocked cells”.
02:56 Now, in the “Password” field, let’s enter “abc”, in the lower case and re-enter the password in the “Confirm” field.
03:07 Click OK.Now, let’s try to select and modify data in a cell.
03:15 We are not able to select any cell!
03:18 The sheet cannot be modified!
03:22 But what about the other sheets?
03:24 Let’s click on Sheet2.
03:27 Let’s select a cell and try to edit it.
03:30 Calc allows us to edit the cells in the other sheets.
03:35 Let’s go back to the first sheet.
03:38 Now, let’s un-protect the sheet.
03:41 This is simple.
03:43 From the Menu bar, click on “Tools” > “Protect Document” and “Sheet”.
03:49 A dialog-box that requests for the password, appears.
03:53 Enter “abc” in small case in it and click OK.
03:59 We are able to select the cells again!
04:03 Let’s learn about Ranges.
04:06 You can define a range of cells in a spreadsheet and use it as a database.
04:12 Each row in this database range corresponds to a database record and
04:17 each cell in a row corresponds to a database field.
04:22 You can sort, group, search and perform calculations on the range as you would in any database.
04:30 Let’s define a database in “Personal-Finance-Tracker.ods” and sort the data.
04:38 First, let’s select the items which we require in the database.
04:43 Let’s select all the data under the heading “SN” to "Account" together. We have already learnt how to select data.
04:53 Now, let’s name our database.
04:56 From the Menu bar, click Data and then click on Define Range.
05:02 In the “Name” field, type “dtbs” which is the short-form of database.
05:08 Click OK.
05:10 Again, from the Menu bar, click Data and Select Range.
05:15 Notice that in the “Select Database Range” dialog-box that appears, the name “dtbs” is listed as a database.
05:24 Click on the OK button.
05:27 Now, let’s sort the data in this database.
05:31 From the Menu bar, click Data and Sort.
05:35 In the Sort dialog-box that appears, click the “Sort by” field and select “SN”.
05:42 Next, from the right side, select “Descending”.
05:47 Under the first “Then by” field, click on the drop-down and select “Cost”.
05:54 Again, from the right side, select “Descending”.
05:58 In the second “Then by” field, click on the drop-down, select “Spent” and then again select “Descending”.
06:07 Click OK.
06:09 The data is sorted under the heading “SN” and in the descending order!
06:15 In a similar manner, we can perform other operations in the database too!
06:21 Let’s press the CTRL+Z keys to undo the sort and get the original data.
06:28 Now, let’s learn how to use the “Subtotal” option in Calc.
06:34 The “Subtotal” option, calculates the grand total of data under different headings, using a mathematical function of our choice.
06:43 Let’s find the subtotal of the data under the heading “Cost”.
06:49 First, let’s delete the entry in row number 8.
06:53 select all data under SN to ACCOUNT together.
06:59 Next, from the Menu bar, click Data and Subtotals.
07:04 In the Subtotals dialog-box that appears, from the “Group by” field, let us select “SN”.
07:11 This groups the data by Serial Number.
07:15 Next, in the “Calculate subtotals for” field, click on the “Cost” option.
07:21 This will calculate the total of all the entries under it.
07:26 Under the “Use function” field , let’s choose “Sum” and click OK.
07:33 Notice that the “Grand total” of the entries, under the heading “Costs”, is displayed on the spreadsheet.
07:41 On the left side of the sheet there are 3 new tabs “1”, ”2”and “3”.
07:47 These tabs give 3 different views of the data.
07:52 Let’s click on tab 1.
07:54 Notice that only the grand total of the data under “Costs” is displayed.
08:00 Click on tab “2” .
08:02 The data under “Costs” as well as the grand total is displayed.
08:08 Now, click on tab “3”.
08:11 We get the detailed view of the sheet along with the grand total of the data under “Costs”.
08:18 Let’s close this file.
08:21 A dialog-box with the message Save or Discard changes appears.
08:26 Click Discard.
08:28 Now, let’s re-open the file.
08:31 Now, let’s learn about the “Validity” option in LibreOffice Calc.
08:37 The “Validity” option validates data in the spreadsheet.
08:41 This is done by specifying the “Validation rules” for the selected cells in the spreadsheet.
08:49 For example- in “Personal-Finance-Tracker.ods”, we can specify the mode of payment for the items bought, using Validation.
08:59 Now, let us delete the heading “Date” and its contents.
09:04 Let us give another heading for “Mode of Payment” as “M-O-P”, next to the heading “Received”.
09:12 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”.
09:21 That is, ”Salary”, ”Electricity Bills” and the other components.
09:27 Now, let us click on the empty cell just below the heading ”M-O-P”.
09:33 This will have the mode of payment for the component “Salary”.
09:38 Now, from the Menu bar, click “Data” and “Validity”.
09:43 The Validity dialog-box appears.
09:47 Let’s click the “Criteria” tab.
09:50 From the “Allow” field drop-down, click “List”.
09:55 The “Entries” box pops-up.
09:58 Let’s enter the options which will appear on validating the selected cell.
10:05 Let’s type the first mode of payment as “In Cash” and then press the Enter key from the keyboard.
10:13 Next, let’s type the second mode of payment as “Demand Draft”.
10:19 Click OK.
10:21 The selected cells are validated!
10:25 Now, press the down-arrow that is displayed alongside.
10:30 Can you see the options that we entered as Mode of Payments in the Entries box?
10:36 To validate the cells below, first click on the Format Paintbrush option on the toolbar.
10:43 Then, select the cells below the validated cell by pressing the left mouse button and then dragging along the cells.
10:53 Now, release the mouse button.
10:57 All the selected cells are validated in the same manner.
11:09 Now, click on the cell just below the heading “M-O-P” and then click on the down-arrow.
11:17 Both the options for mode of payment are displayed.
11:21 Let’s select the In Cash option.
11:25 In the same manner, you can select “Cash” or “Demand Draft” in each of the validated cells according to the mode of payment made.
11:36 This brings us to the end of this Spoken Tutorial on LibreOffice Calc.
11:42 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 and

How to validate cells.

12:01 Watch the video available at the following link.
12:04 It summarizes the Spoken Tutorial project.
12:07 If you do not have good bandwidth, you can download and watch it.
12:11 The Spoken Tutorial Project team:
12:13 Conducts workshops using spoken tutorials.
12:17 Gives certificates for those who pass an online test.
12:20 For more details, please write to:

contact at spoken hyphen tutorial dot org.

12:27 Spoken Tutorial project is a part of the Talk to a Teacher project.
12:31 It is supported by the National Mission on Education through ICT, MHRD, Government of India.
12:39 More information on this mission is available at:
12:42 spoken hyphen tutorial dot org slash NMEICT hyphen Intro.
12:50 This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd.

Thanks for joining.

Contributors and Content Editors

PoojaMoolya, Pratik kamble, Ranjana, Sandhya.np14, Sneha

Retrieved from "https://script.spoken-tutorial.org/index.php?title=LibreOffice-Suite-Calc/C3/Advanced-Formatting-and-Protection/English-timed&oldid=33665"