LibreOffice-Suite-Calc-6.3/C3/Advanced-Formatting-and-Protection-in-Calc/English-timed
From Script | Spoken-Tutorial
Revision as of 14:21, 29 September 2020 by PoojaMoolya (Talk | contribs)
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.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.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.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-Practice.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. |