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)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
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.

Contributors and Content Editors

PoojaMoolya