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

Title: Advanced Formatting and Password Protection

Author: Arvind Pillai

Novice reviewer: Nikita Misal & Pooja Moolya

Domain reviewer: Praveen S and nancy varkeyKeywords: LibreOffice,Calc,Ubuntu,Password protect Calc file,Password protect a single sheet in Calc,Define ranges for database in Calc,Subtotal option in Calc,Validate cells in Calc

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

Contributors and Content Editors

Arvindpillai90, Nancyvarkey, Pravin1389