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
|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:
|Slide: System Requirements|| This tutorial is recorded using
|Open Personal-Finance-Tracker.ods||Let us open our Personal-Finance-Tracker.ods file.|
|Slide: Code files||
|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
| Next, 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.
| 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.
|Point to the dialog box.||Set Password dialog box opens up.|
| Type the password : spoken123
Retype the password as spoken123 in Confirm box also
| 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
|| 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
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.
| 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:
| In the Password type spoken123.
Re-enter the password spoken123 in the Confirm field.
| 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.|
|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.|
|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
| 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.
|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
|| 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:
|Slide: Assignment|| As an assignment
|Slide: About Spoken Tutorial Project||
|Slide: Spoken Tutorial Workshops||
| 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.