Author: user

Insert, edit, and remove a hyperlink.

Hyperlinks are used to jump to a different location from within a spread sheet and this location can be in the same file, in different files and also some websites.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-hyperlinks]
[the_ad id=”12356″]
To insert a hyperlink, click on ‘Insert’ from the main menu bar, and from the resulting drop-down menu, click on ‘Hyperlink’. This will open the ‘Hyperlink’ dialog box.

In this dialog box, we have four options in the left hand side of the dialog. These are ‘Internet’, ‘Mail & News’, ‘Document’ and ‘New Document’. Under ‘Internet’, we can create a hyperlink to jump to a website. The website URL can be entered in the ‘Target’ field.

Under ‘Mail & News’, we can create a hyperlink for sending an email. The email ID and the subject for the email can be set here under the ‘Recipient’ and ‘Subject’ fields.
Under ‘Document’, we can create a hyperlink for jumping an already existing document. The path for this document can be browsed to by using the ‘Path’ field.

Under ‘New Document’, we can create a hyperlink for creating and opening a new document. The file name and file type can be specified under the ‘File’ and ‘File Type’ fields.
There are a number of other options as well which can be set as per the requirements. This is how we can insert a hyperlink in a Calc spread sheet.

To edit a hyperlink, right click on the cell containing the hyperlink. After this double-click on the same cell to select the hyperlink. Now click on ‘Insert’ from the main menu bar, and from the resulting drop-down, click on ‘Hyperlink’. The ‘Hyperlink’ dialog box will get opened with the selected hyperlink already open in the dialog. You can make changes to the hyperlink here and then click on ‘Apply’. This will save the changes. After this, click on ‘OK’. The changes will get reflected in the sheet.

To remove a hyperlink, select the hyperlink and then click on ‘Delete’ button from the keyboard. This will delete the hyperlink.
[the_ad id=”12397″]

Insert, edit, and remove a hyperlink.

Hyperlinks are used to jump to a different location from within a spread sheet and this location can be in the same file, in different files and also some websites.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-hyperlinks]
[the_ad id=”12356″]
To insert a hyperlink, click on ‘Insert’ from the main menu bar, and from the resulting drop-down menu, click on ‘Hyperlink’. This will open the ‘Hyperlink’ dialog box.

In this dialog box, we have four options in the left hand side of the dialog. These are ‘Internet’, ‘Mail & News’, ‘Document’ and ‘New Document’. Under ‘Internet’, we can create a hyperlink to jump to a website. The website URL can be entered in the ‘Target’ field.

Under ‘Mail & News’, we can create a hyperlink for sending an email. The email ID and the subject for the email can be set here under the ‘Recipient’ and ‘Subject’ fields.
Under ‘Document’, we can create a hyperlink for jumping an already existing document. The path for this document can be browsed to by using the ‘Path’ field.

Under ‘New Document’, we can create a hyperlink for creating and opening a new document. The file name and file type can be specified under the ‘File’ and ‘File Type’ fields.
There are a number of other options as well which can be set as per the requirements. This is how we can insert a hyperlink in a Calc spread sheet.

To edit a hyperlink, right click on the cell containing the hyperlink. After this double-click on the same cell to select the hyperlink. Now click on ‘Insert’ from the main menu bar, and from the resulting drop-down, click on ‘Hyperlink’. The ‘Hyperlink’ dialog box will get opened with the selected hyperlink already open in the dialog. You can make changes to the hyperlink here and then click on ‘Apply’. This will save the changes. After this, click on ‘OK’. The changes will get reflected in the sheet.

To remove a hyperlink, select the hyperlink and then click on ‘Delete’ button from the keyboard. This will delete the hyperlink.
[the_ad id=”12397″]

Create a spreadsheet based on an existing template.

To create a spreadsheet based on an already existing template, click on ‘File’ from main menu bar. From the resulting drop-down, click on ‘New’, and from the sub-menu, click on ‘Templates’. This will open the ‘Template Manager’ wizard.

[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-templates]
[the_ad id=”12356″]

Under the ‘Spreadsheets’ tab, click on ‘My Templates’ or any of the other folders in case you have stored your templates in the other folders. This will open the templates which you have stored in that particular folder.

Here we get the ‘Personal Information’ template. This is just an example and the name will vary. Simple double-click on the template and a new spreadsheet based on this template will get created.

Modify a template.

To modify a template, follow the same steps as above till we get to the template which needs to be edited. Click on the template to select it. This will make a row of icons visible above the template.

Among the icons, click on ‘Edit’ and this will open the spreadsheet template in Calc. Make all the required changes which need to be made and finally click on ‘Save’ from the ‘File’ button of the main menu bar to save your changes.
[the_ad id=”12397″]

Create a spreadsheet based on an existing template.

To create a spreadsheet based on an already existing template, click on ‘File’ from main menu bar. From the resulting drop-down, click on ‘New’, and from the sub-menu, click on ‘Templates’. This will open the ‘Template Manager’ wizard.

[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-templates]
[the_ad id=”12356″]

Under the ‘Spreadsheets’ tab, click on ‘My Templates’ or any of the other folders in case you have stored your templates in the other folders. This will open the templates which you have stored in that particular folder.

Here we get the ‘Personal Information’ template. This is just an example and the name will vary. Simple double-click on the template and a new spreadsheet based on this template will get created.

Modify a template.

To modify a template, follow the same steps as above till we get to the template which needs to be edited. Click on the template to select it. This will make a row of icons visible above the template.

Among the icons, click on ‘Edit’ and this will open the spreadsheet template in Calc. Make all the required changes which need to be made and finally click on ‘Save’ from the ‘File’ button of the main menu bar to save your changes.
[the_ad id=”12397″]

Use paste special options: add, subtract, multiply, divide.

Paste special options help in making some quick calculations and formatting changes by a special copy paste function. We need to have some data to use the special paste function.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-paste-special]
[the_ad id=”12356″]
From the available data, simply right-click on any cell and copy that cell to the clip board. Now, say we have another cell which contains some data and we need to subtract these two values, the data in the cell which we have copied from the data in this cell. We will simple right-click on this cell and from the menu will click on ‘Paste Special’.

Here, we have copied 250 to the clipboard and have right-clicked on 650. On the click of ‘Paste Special’, the ‘Paste Special’ dialog box opens.

In this dialog, we can select from a variety of available options. Here we want to subtract, so we will select subtract and then click on ‘OK’. The other options can be selected appropriately. On click of ‘OK’, the ‘650’ cell changes to ‘400’ as 250 has been subtracted from 650. The other three operations, addition, multiplication and division, can be carried out in similar manner.

Use paste special options: values /numbers, transpose.

In the ‘Paste Special’ dialog, we have three icons at the top, these icons are for ‘data only’, ‘data and formatting’, and ‘transpose’ functions from left to right.

If we click on data only, then only the data from the selected cell is used for paste operation. If the data and formatting icon is selected, then the formatting of the selected cell is also pasted. If transpose icon is selected, then the transpose operation for a row or a column or both takes place. This means that the rows of the selected range become columns and the columns become rows. This has been shown below.

The same can be achieved by selecting the ‘Transpose’ checkbox under ‘Options’ and then clicking on ‘OK’.

[the_ad id=”12397″]

Name cell ranges, delete names for cell ranges.

To name a cell range, click on ‘Insert’ from the main menu bar. From the resulting menu, click on ‘Names’ and from the sub-menu which gets generated, click on ‘Define’. This will open the ‘Define Name’ dialog box.

[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-naming-cells]
[the_ad id=”12356″]

In this dialog box, under ‘Name’, give the name of the range and from the ‘Select’ icon given in front of ‘Range’, select the cell range which you want to be included in this range. After this, click on ‘Add’. This will add the named cell range to the spreadsheet.

To delete the cell range, again click on ‘Insert’ from the main menu bar. From the resulting menu, click on ‘Names’ and from the resulting sub-menu, click on ‘Manage’. This will open the ‘Manage Names’ dialog box. This dialog displays a list of all the named cell ranges in the sheet. Simply select the name which you want to delete and click on ‘Delete’.

After this, click on ‘OK’. The named cell range will get deleted.

Use named cell ranges in a function.

We can use a named cell range in a function. To do that, just put the name of the cell range in the argument of a function in ‘Function Wizard’, instead of selecting the cells.

As an example, here we have created a named cell range called ‘Roll’, which contains the roll numbers as have been highlighted in the illustration below. Then while using the ‘SUM’ function, instead of selecting the cells, we have just put the name of the range as the argument. This is how we use named cell ranges in functions.

[the_ad id=”12397″]

Insert, edit, delete, show, and hide comments/notes.

To insert comments is a worksheet, select the cell to which you need to insert the comment and right-click on this cell. This will open a menu from which you need to click, ‘Insert Comment’. This will give you a small text box attached to the cell as has been shown in the below given illustration.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-comments-notes]
[the_ad id=”12356″]

Write your comment in this text box and click anywhere else on the sheet. The text box will disappear but the comment will get saved and a small red square will get displayed on the top right corner of the cell. Whenever you place the mouse pointer on this red square, the comment will get displayed.

To show the comment, right-click on the cell and from the resulting menu, click on ‘Show Comment’.

If you need to edit the comment, simply double-click inside the text box. This will enable you to make the changes to the text.

To hide the comment, right-click on the cell and from the resulting menu, click on ‘Hide Comment’.

To delete the comment, right-click on the cell and from the resulting menu, click on ‘Delete Comment’.

[the_ad id=”12397″]

Show all formulas in a worksheet, rather than the resulting values.

To show formulas in the worksheet instead of the resulting values, click on ‘Tools’ from the main menu bar. From the resulting drop-down, click on ‘Options’. This opens the ‘Options’ wizard. In the ‘Options’ wizard, under ‘LibreOffice Calc’, click on ‘View’.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-show-formulas]
[the_ad id=”12356″]
Then under ‘Display’, we have a checkbox called ‘Formulas’. If this checkbox is checked, then the formulas will be shown in the worksheet instead of the results, but if the checkbox is unchecked, then the results of the formulas will be shown. This is how we can display formulas in a worksheet in place of resulting values.

[the_ad id=”12397″]

Trace precedent, dependent cells. Identify cells with missing dependents.

Trace precedent gives us the cells on which the value of the current selected cell depends. To trace the precedent of a cell, select the cell and then click on ‘Tools’ from the main menu bar. From the resulting drop-down menu, click on ‘Detective’ and from the resulting sub-menu, click on ‘Trace Precedents’. This will show the cells on which the value of the selected cell depends. This has been shown in the illustration below.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-auditing]
[the_ad id=”12356″]
Here, the value of the selected cell depends on the values of the two other highlighted cells, and this has been displayed with the help of trace precedent functionality.

Trace dependents gives us the cells which depend on the current selected cell. To trace the dependents of a cell, select the cell and then click on ‘Tools’ from the main menu bar. From the resulting drop-down menu, click on ‘Detective’ and from the resulting sub-menu, click on ‘Trace Dependents’. This will show the cells whose value depends on the current cell. This has been shown in the illustration below.

Here, the values of the selected cells depend on the value of the cell ‘B4’, and this has been displayed with the help of trace dependent functionality.

[the_ad id=”12397″]

Set and edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time.

To set validation criteria for data entry in a cell range, click on ‘Data’ from the main menu bar, and from the resulting menu, click on ‘Validity’. This will open the ‘Validity’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-validating]
[the_ad id=”12356″]

In this dialog box, under the ‘Criteria’ tab, we have certain options to set validations for the input data. From ‘Allow’ drop-down list, we can choose the type of data that we want to allow in the particular cell or cell range. This can be whole number, decimal, date or time. We can also set some particular set of values for these data types. After making all the changes, click on ‘OK’. The validation will get applied to the cell range.

To set validations for a list, in the same dialog box, select ‘List’ from the ‘Allow’ drop-down. After this, you can set the different options from the available functions and you can even set the allowed entries for the list under ‘Entries’. After making the changes, click on ‘OK’. The validation will be set for the list.

Enter input message and error alert.

The input message is the message which gets displayed alongside the cell when that cell is selected. This message can be used to give the users of the spreadsheet some hint about the kind of validations which have been set for the cell and the kind of data which this cell can take.

To enter an input message, in the same ‘Validity’ dialog box, under the ‘Input Help’ tab, we have the ‘Contents’ field. In this tab, simply check the ‘Show input help when cell is selected’ checkbox and enter the title and text for the input message. After this, click on ‘OK’. The input message will be set for the cell.

An error alert is the message which is displayed when some invalid value is input to the cell. To set the error alert, in the same ‘Validity’ dialog box, under the ‘Error Alert’ dialog box, check the given checkbox and then enter the message details in the fields provided. After this, click on ‘OK’. This will set the error message for the cell.

[the_ad id=”12397″]