Welcome to the spoken tutorial on Advanced Formatting and Password Protection in Calc.
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.
This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
Let us open our Personal-Finance-Tracker.ods file.
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.
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 on the drop-down besides the Save icon and select Save As option.
Save As dialog box opens up.
Check the Save with password checkbox at the bottom left corner. And then click the Save button at the top right corner.
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.
Set Password dialog box opens up.
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.
Now, we’ll try to reopen this file and check what happens.
Immediately the Enter Password dialog box appears.
Let us type the password as 111 here and click on the OK button.
We get an error message. It says The password is incorrect. The file cannot be opened.
Click on the OK button.
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.
It is possible for us to remove the password option at a later time, as well.
To do that, click on the drop-down besides the Save icon and then on the Save As option.
Now uncheck the Save with password option in the Save As dialog box.
Then click the Save button at the top right corner.
Let’s replace the same file by clicking on the Yes button.
Once again, let us close and open this file.
Notice that, this time we do not require a password to open the file.
Next, let’s learn how to password-protect individual sheets in the spreadsheet.
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.
Either ways, the Protect Sheet dialog box opens.
To protect the sheet, uncheck the following options by clicking on the checkbox besides it:
Select protected cells and Select unprotected cells.
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.
We can see a lock symbol in the Sheet 1 tab.
This indicates that our sheet is now password-protected.
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.
Also, we see a warning message. Click Ok to exit this box.
We are also unable to select or move the image in this sheet elsewhere.
But what about the other sheets? Let’s click on Sheet 2.
Here we’ll select cell E3 and type the word testing inside the cell.
Calc allows us to edit the cells in other sheets.
Let’s undo these changes.
Now go back to Sheet 1.
Let’s unprotect the sheet now.
Right-click on Sheet 1 tab and select Protect sheet option.
The Unprotect Sheet dialog box opens up and prompts us for the password.
Type spoken123 in the password optional text field.
Click on the Ok button in the dialog box
Notice the lock symbol in Sheet 1 tab has disappeared now.
This indicates that Sheet 1 is no longer password
We are able to edit the cells again and move the images as well.
Next, let’s learn about 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.
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.
We’ll select all the data from column SN to Miscellaneous.
We’ll Ignore the Sum Total row for now.
Let’s name our database.
Go to the Data menu in the menu bar and select the Define Range option.
Define Database Range dialog box opens up.
In the Name field, type dtbs which is the short-form of the database.
Click on the OK button in the dialog box.
Click anywhere else in the spreadsheet to deselect the cells.
Again go to the Data menu in the menu bar and click on the Select Range option.
Select Database Range dialog box appears.
Notice that in the Ranges section, the name dtbs is listed as a database.
Click on dtbs from the list and then click on the OK button in the dialog box.
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.
Next, let’s learn how to use the Subtotal option in Calc.
The Subtotals option calculates the sub total of data under different columns.
For this, we can use any mathematical function of our choice.
Let’s find the subtotals of the data in the Cost column.
First, let’s delete the entry in row number 8 which is SUM TOTAL.
Then select all the data from cell A1 to F7 as shown.
Go to the Data menu in the menu bar and click on the Subtotals option.
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.
Next, in the Calculate subtotals for field, check on the Cost checkbox.
This will calculate the total of all the entries under it.
Under the Use function field, choose Sum, if not already selected. And click on the OK button at the bottom.
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 and look at the formula bar.
We see the syntax of the formula, Calc has used for the SUBTOTAL.
It says equal to SUBTOTAL and within brackets number 9 and the cell range.
The same is seen highlighted in the spreadsheet as well.
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.
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.
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.
The data under Costs as well as the Grand Sum is displayed.
Now, click on tab 3.
We get the detailed view of all the data along with the Grand Sum of the data under Costs.
Let’s close this file without saving the changes.
And let’s re-open the file.
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.
For example, we can specify the mode of payment for the items bought using Validation.
Let’s add a new column Mode of Payment as MOP next to the column Received.
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.
Now, let us click on the empty cell just below the heading MOP.
This will have the mode of payment for the item Salary.
Go to the Data menu in the menu bar and select Validity option.
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, select List.
The Entries box gets displayed.
Let’s enter the options which will appear on validating the selected cell.
We’ll type the first mode of payment as In Cash and press Enter.
Next, we’ll type the following entries as shown.
Click on the OK button at the bottom of the dialog box.
Notice the down arrow displayed alongside cell F2, which means the cell is validated.
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.
To validate the cells below, click on the Clone Formatting icon in the Standard toolbar.
Then, click on the cell F3 and press the left mouse button.
Drag along the cursor till cell F7 and release the mouse button.
All selected cells are validated in one go.
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.
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.
Likewise we can format and validate other columns in the spreadsheet.
Let’s save all these changes and close the file.
This brings us to the end of this tutorial, let us summarize.
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.
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.
The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
Please post your timed queries in this forum.
Spoken Tutorial project is funded by MHRD, Govt. of India.
This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011
This is Arvind along with the Spoken Tutorial team from IIT Bombay signing off. Thank you for watching.