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

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. 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 reopen 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.
03:08	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” 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 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 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. How to validate cells.
12:01	Watch the video available at the following link
12:04	It summarises 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