Category: Advanced Spreadsheets

To link data between different sheets within a spreadsheet, select the cell on which you want to create the link. After this go to the input line and input ‘=’. After this, go to the sheet whose data you want to link to this sheet and select the cell which needs to be linked. Click ‘Enter’ from the keyboard. The control will return to the original sheet and the data value from the other sheet will get copied here. This is how we can create a link within a spreadsheet in Calc.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-link-data]
[the_ad id=”12356″]

To create link between spreadsheets, you need to follow similar steps as have been given above. But instead of opening a different sheet within the same spreadsheet as we had done above, here you need to open a new spreadsheet, select the cell in this sheet and then press ‘Enter’. This will link the data between spreadsheets.

Update, break a link.

To update or break a link, select the cell which contains the link. This will show the link information in the input line. We can make changes to the link info here in order to update the link.

In order to break the link, we just need to delete the link info. This will break the link.

[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″]

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″]