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

From Script | Spoken-Tutorial
Revision as of 16:23, 10 September 2020 by Nancyvarkey (Talk | contribs)

Jump to: navigation, search

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

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.

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