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

TIME	NARRATION
00:01	Welcome to the spoken tutorial on Advanced Formatting and Password Protection in Calc.
00:08	In this tutorial, we will learn how to:
00:12	Protect a spreadsheet with a password
00:16	Protect a single sheet with a password
00:20	Define Ranges for a database
00:24	Use the Subtotal option and Validate cells.
00:30	This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
00:44	Let us open our Personal Finance Tracker dot ods file.
00:50	This file has been provided to you in the Code files link on this tutorial page.
00:57	Please download and extract the file. Make its copy and use it for practising.
01:06	First let us learn to protect this spreadsheet with a password.
01:11	This option ensures that only people who know the password can open this spreadsheet.
01:18	Click on the drop-down besides the Save icon and select Save As option.
01:25	Save As dialog box opens up.
01:29	Check the Save with password checkbox at the bottom left corner. And then click the Save button at the top right corner.
01:41	In the Save As option, we can either save it as a different file or replace the same file.
01:49	For this demonstration, we will replace the file. Click Yes.
01:56	Set Password dialog box opens up.
02:00	Here in the Enter password to open field, type a password, say spoken123.
02:10	In the Confirm password text field, retype the same password that is spoken123.
02:19	Then click on the OK button in the dialog box.
02:24	Close the file.
02:27	Now, we’ll try to reopen this file and check what happens.
02:33	Immediately the Enter Password dialog box appears.
02:38	Let us type the password as 111 here and click on the OK button.
02:46	We get an error message. It says The password is incorrect. The file cannot be opened.
02:55	Click on the OK button.
02:58	This time in the Enter password field, type the correct password as spoken123. Click on the OK button.
03:09	The Personal Finance Tracker dot ods file opens.
03:14	It is possible for us to remove the password option at a later time, as well.
03:21	To do that, click on the drop-down besides the Save icon and then on the Save As option.
03:29	Now uncheck the Save with password option in the Save As dialog box.
03:36	Then click the Save button at the top right corner.
03:41	Let’s replace the same file by clicking on the Yes button.
03:46	Once again, let us close and open this file.
03:52	Notice that, this time we do not require a password to open the file.
03:58	Next, let’s learn how to password protect individual sheets in the spreadsheet.
04:05	Go to the Tools menu in the menu bar and click on the Protect sheet option.
04:12	Alternatively, right click on the Sheet 1 tab at the bottom and select Protect Sheet option.
04:20	Either ways, the Protect Sheet dialog box opens.
04:25	To protect the sheet, uncheck the following options by clicking on the checkbox besides it:
04:32	Select protected cells and Select unprotected cells.
04:38	Now, in the Password field, let’s type spoken123 as our password.
04:46	In the Confirm field, retype the same password.
04:51	Then click on the OK button in the dialog box.
04:55	We can see a lock symbol in the Sheet 1 tab.
04:59	This indicates that our sheet is now password-protected.
05:04	To verify, let’s try to add some data in any cell on this sheet.
05:11	Notice that we are unable to type anything in any cell.
05:17	Also, we see a warning message. Click Ok to exit this box.
05:26	We are also unable to select or move the image in this sheet elsewhere.
05:32	But what about the other sheets? Let’s click on Sheet 2.
05:39	Here we’ll select cell E3 and type the word testing inside the cell.
05:47	Calc allows us to edit the cells in other sheets.
05:42	Let’s undo these changes.
05:55	Now go back to Sheet 1.
05:58	Let’s unprotect the sheet now.
06:01	Right-click on Sheet 1 tab and select Protect sheet option.
06:07	The Unprotect Sheet dialog box opens up and prompts us for the password.
06:15	Type spoken123 in the password optional text field.
06:21	Click on the Ok button in the dialog box
06:25	Notice the lock symbol in Sheet 1 tab has disappeared now.
06:31	This indicates that Sheet 1 is no longer password
06:36	We are able to edit the cells again and move the images as well.
06:45	Next, let’s learn about Ranges.
06:49	We can define a range of cells in a spreadsheet and use it as a database.
06:56	Each row in this database range corresponds to a database record
07:02	While each cell in a row corresponds to a database field.
07:07	We can sort, group, search and perform calculations on the range as we would do in any database.
07:17	First, let’s define a database in our Personal Finance Tracker dot ods and then sort the data.
07:26	So, let’s select the items which we require in the database.
07:32	We’ll select all the data from column SN to Miscellaneous.
07:38	We’ll Ignore the Sum Total row for now.
07:42	Let’s name our database.
07:45	Go to the Data menu in the menu bar and select the Define Range option.
07:51	Define Database Range dialog box opens up.
07:55	In the Name field, type dtbs which is the short-form of the database.
08:01	Click on the OK button in the dialog box.
08:05	Click anywhere else in the spreadsheet to deselect the cells.
08:10	Again go to the Data menu in the menu bar and click on the Select Range option.
08:18	Select Database Range dialog box appears.
08:23	Notice that in the Ranges section, the name dtbs is listed as a database.
08:30	Click on dtbs from the list and then click on the OK button in the dialog box.
08:37	Now we can sort the data within this database, however we want.
08:43	Sorting data was already explained in an earlier tutorial in this series.
08:50	So, we’ll skip that demonstration here.
08:54	Next, let’s learn how to use the Subtotal option in Calc.
09:00	The Subtotals option calculates the sub total of data under different columns.
09:07	For this, we can use any mathematical function of our choice.
09:13	Let’s find the subtotals of the data in the Cost column.
09:18	First, let’s delete the entry in row number 8 which is SUM TOTAL.
09:24	Then select all the data from cell A1 to F7 as shown.
09:31	Go to the Data menu in the menu bar and click on the Subtotals option.
09:38	The Subtotals dialog box appears.
09:42	By default, the 1st Group tab is selected at the top.
09:47	Click the Group by field drop-down and choose SN, if not already selected.
09:54	This groups the data by SN which is the Serial Number.
09:59	Next, in the Calculate subtotals for field, check on the Cost checkbox.
10:06	This will calculate the total of all the entries under it.
10:11	Under the Use function field, choose Sum, if not already selected. And click on the OK button at the bottom.
10:21	Notice that the Grand Sum of the entries under the column Costs is displayed on the spreadsheet.
10:29	We also see subtotals after every row and see the changes in the SN column.
10:36	Click on cell C14 and look at the formula bar.
10:41	We see the syntax of the formula, Calc has used for the SUBTOTAL.
10:47	It says equal to SUBTOTAL and within brackets number 9 and the cell range.
10:55	The same is seen highlighted in the spreadsheet as well.
11:00	Refer to this table for numbers that correspond to functions within the SUBTOTAL formula.
11:08	9 stands for the function SUM which is what we had selected in the SUBTOTAL dialog box.
11:16	Other function numbers are mentioned in this table, for your quick reference.
11:22	Use the corresponding numbers in the left column, when you wish to use another function.
11:29	At the top left below the Name Box, we can see 3 tiny new tabs 1, 2 and 3.
11:37	These tabs give 3 different views of the data.
11:42	Let’s click on tab 1. Notice that only the Grand Sum of the data under Costs along with the last subtotal.
11:51	Click on tab 2.
11:55	The data under Costs as well as the Grand Sum is displayed.
12:01	Now, click on tab 3.
12:04	We get the detailed view of all the data along with the Grand Sum of the data under Costs.
12:11	Let’s close this file without saving the changes.
12:16	And let’s re-open the file.
12:21	We’ll now learn about the Validity option in Calc.
12:26	The Validity option validates data in the spreadsheet.
12:31	This is done by specifying the Validation rules for the selected cells in the spreadsheet.
12:38	For example, we can specify the mode of payment for the items bought using Validation.
12:45	Let’s add a new column Mode of Payment as MOP next to the column Received.
12:53	Below the heading MOP, the cells can be used to display the mode of payments.
12:59	The MOP for the Items in Column B can be shown here.
13:04	That is, Salary, House Rent, Electricity Bill and the other components.
13:11	Now, let us click on the empty cell just below the heading MOP.
13:17	This will have the mode of payment for the item Salary.
13:22	Go to the Data menu in the menu bar and select Validity option.
13:28	The Validity dialog box pops up.
13:32	Let’s click the Criteria tab at the top if not already selected.
13:37	From the Allow field drop-down, select List.
13:42	The Entries box gets displayed.
13:46	Let’s enter the options which will appear on validating the selected cell.
13:51	We’ll type the first mode of payment as In Cash and press Enter.
13:57	Next, we’ll type the following entries as shown.
14:03	Click on the OK button at the bottom of the dialog box.
14:08	Notice the down arrow displayed alongside cell F2, which means the cell is validated.
14:16	Now, click the down arrow.
14:19	We see the options that we entered as Mode of Payments in the Entries box here.
14:26	Click on the down arrow again to collapse the list.
14:30	To validate the cells below, click on the Clone Formatting icon in the Standard toolbar.
14:37	Then, click on the cell F3 and press the left mouse button.
14:43	Drag along the cursor till cell F7 and release the mouse button.
14:49	All selected cells are validated in one go.
14:54	Now click on the cell just below the heading MOP and then click on the down arrow alongside.
15:02	For mode of payment let’s select Online.
15:06	In the same manner, you can select the options in each of the validated cells.
15:12	Select according to the mode of payment made for each Items as shown.
15:19	Likewise we can format and validate other columns in the spreadsheet.
15:25	Let’s save all these changes and close the file.
15:30	This brings us to the end of this tutorial, let us summarize.
15:36	In this tutorial, we learnt to: Protect a spreadsheet with a password
15:43	Protect a single sheet with a password
15:47	Define Ranges for a database, Use the Subtotal option and Validate cells.
15:57	As an assignment Open Spreadsheet hyphen Practice dot ods file
16:03	Use the password protect option to protect the sheet named Department Sheet
16:10	Use the Subtotals option and find the Grand Sum of Salary column
16:16	Close the file without saving the changes.
16:20	The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
16:28	The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
16:39	Please post your timed queries in this forum.
16:44	Spoken Tutorial project is funded by MHRD, Govt. of India.
16:51	This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011
16:59	This is Arvind along with the Spoken Tutorial team from IIT Bombay signing off. Thank you for watching.

Contributors and Content Editors

PoojaMoolya