Difference between revisions of "LibreOffice-Suite-Calc/C3/Advanced-Formatting-and-Protection/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) |
|||
(13 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. |
|- | |- | ||
||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'''. |
+ | |||
|- | |- | ||
||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 | + | ||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 | + | ||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 | + | ||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 | + | ||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 | + | ||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'''. |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
|- | |- | ||
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” | + | ||From the Menu bar, click on '''“Tools” > “Protect Document”''' and '''“Sheet”'''. |
|- | |- | ||
||03:49 | ||03:49 | ||
− | ||A dialog box | + | ||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 | + | ||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. |
|- | |- | ||
||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'''. |
+ | |||
|- | |- | ||
||04:22 | ||04:22 | ||
− | ||You can sort, group, search | + | ||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 | + | ||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 | + | ||From the Menu bar, click '''Data''' and then click on '''Define Range'''. |
|- | |- | ||
||05:02 | ||05:02 | ||
− | ||In the “Name” field, | + | ||In the '''“Name”''' field, type “dtbs” which is the short-form of database. |
|- | |- | ||
||05:08 | ||05:08 | ||
− | ||Click | + | ||Click '''OK'''. |
|- | |- | ||
||05:10 | ||05:10 | ||
− | ||Again, from the Menu bar, click | + | ||Again, from the Menu bar, click '''Data''' and '''Select Range'''. |
|- | |- | ||
||05:15 | ||05:15 | ||
− | ||Notice | + | ||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 | + | ||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 | + | ||From the Menu bar, click '''Data''' and '''Sort'''. |
|- | |- | ||
||05:35 | ||05:35 | ||
− | ||In the Sort dialog box that appears, click the “Sort by” | + | ||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 | + | ||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 | + | ||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 302: | 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 | + | ||Now, let’s learn how to use the '''“Subtotal”''' option in '''Calc'''. |
|- | |- | ||
Line 314: | 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 322: | 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 | + | ||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 346: | 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 | + | ||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 366: | Line 375: | ||
|- | |- | ||
||07:54 | ||07:54 | ||
− | ||Notice | + | ||Notice that only the grand total of the data under “Costs” is displayed. |
|- | |- | ||
Line 375: | Line 384: | ||
||08:02 | ||08:02 | ||
||The data under “Costs” as well as the grand total is displayed. | ||The data under “Costs” as well as the grand total is displayed. | ||
− | |||
|- | |- | ||
Line 383: | 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 391: | 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 | + | ||For example- in “Personal-Finance-Tracker.ods”, we can specify the mode of payment for the items bought, using '''Validation'''. |
|- | |- | ||
Line 423: | 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 | + | ||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. |
|- | |- | ||
||09:27 | ||09:27 | ||
− | ||Now, let us click on the empty cell just below the | + | ||Now, let us click on the empty cell just below the heading ”M-O-P”. |
|- | |- | ||
Line 444: | 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 | + | ||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 468: | Line 475: | ||
|- | |- | ||
||10:05 | ||10:05 | ||
− | ||Let’s type the first mode of payment as “In Cash” | + | ||Let’s type the first mode of payment as “In Cash” and then press the '''Enter''' key from the keyboard. |
− | + | ||
− | + | ||
|- | |- | ||
Line 478: | Line 483: | ||
|- | |- | ||
||10:19 | ||10:19 | ||
− | ||Click OK. | + | ||Click '''OK'''. |
|- | |- | ||
Line 486: | 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 | + | ||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 | + | ||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 | + | ||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 510: | 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 518: | Line 523: | ||
|- | |- | ||
||11:21 | ||11:21 | ||
− | ||Let’s select the | + | ||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 | + | ||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 | + | ||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 | + | ||The Spoken Tutorial Project team: |
|- | |- | ||
Line 560: | 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 | + | ||'''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 | + | ||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. |