Category: Advanced Spreadsheets

[mlw_quizmaster quiz=7]

[the_ad id=’13545′]

Add, remove password protection for a spreadsheet: to open, to modify.

To add password protection for a spreadsheet, click on ‘File’ from the main menu bar and from the resulting drop-down, click on ‘Save As’. This will open the ‘Save As’ dialog box. In this dialog box, we have a ‘Save with password’ checkbox. Check this checkbox in order to enable password protection for the spreadsheet. After this click on ‘Save’.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-security]
[the_ad id=”12356″]

This will open the ‘Set Password’ dialog box. In this dialog, enter the password and then click on ‘OK’. This will add the password for opening the file.

If you want to add the password for modification of file as well, then maximize the ‘Options’ area in this dialog box and check the ‘Open file read-only’ checkbox. After this, enter the password for file editing and click on ‘OK’. This will always open the file as a read only file and will ask the users for this password in case they need to make any modifications to the file.

Protect, unprotect cells, worksheet with a password.

To protect a worksheet with a password, click on ‘Tools’ from the main menu bar. From the resulting drop-down, click on ‘Protect Document’ and from the sub-menu, click on ‘Sheet’. This opens the ‘Protect Sheet’ dialog box.

Enter the appropriate password here and click on ‘OK’. This will make the worksheet password protected. We also have two checkboxes in this dialog; these can be checked according to the requirements.

Hide, unhide formulas.

To hide or unhide formulas in the worksheet, 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’. 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 hide or unhide formulas.

[the_ad id=”12397″]

When the spreadsheet in which the changes have been made is sent to someone else, then they can accept or reject the changes and keep only the ones which are required. To accept or reject changes, simply click on ‘Edit’ from the main menu bar. From the drop-down, click on ‘Track Changes’, and from the sub-menu, click on ‘Manage Changes’. This will open the ‘Manage Changes’ dialog box.

[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-accept-reject-changes-in-a-worksheet]
[the_ad id=”12356″]

All the changes which have been made to the document are listed in this dialog under the ‘List’ tab. Any change can be selected and then can be accepted or rejected by clicking on the ‘Accept’ or ‘Reject’ button. ‘Accept All’ and ‘Reject All’ buttons accept or reject all the changes respectively.
Under the ‘Filter’ tab are given various options which help in filtering out the required changes. These can be selected as per the requirements.

Compare and merge spreadsheets.

Sometimes, due to human error, it is possible that a user forgets to switch on change tracking while editing a document. This will make the process of tracking changes very difficult. But in Calc we have a tool to compare two different documents. We just need to have the original one and the one in which the changes have been made.

Just open the document which has been edited. Click on ‘Edit’ from the main menu bar and from the drop-down click on ‘Compare Document’. This will open an ‘Insert’ dialog. Browse and select the original file with which you want to compare the edited file, and click on ‘Open’.

On click of ‘Open’, all the data which has been edited is highlighted in the document and the ‘Manage Changes’ dialog is opened. The data which is present in the edited document, but was not there in the original, is termed as inserted. The data which was there in the original document but is absent from the edited one is termed as deleted, and any data which has been changed is termed as changed. After this, the changes can be accepted or rejected in the same way as has been explained earlier.

To merge two documents, open the original spreadsheet and click on ‘Edit’ from the main menu bar. From the resulting drop-down, click on ‘Track Changes’, and from the sub-menu, click on ‘Merge Document’. This will open an ‘Insert’ dialog. Browse and select the other document which you want to merge, and click on ‘Open’.

On click of ‘Open’, the data differences between the documents are highlighted and the ‘Manage Changes’ dialog is opened. After this, the changes can be accepted or rejected in the same way as has been explained earlier. If more than two files need to be merged, simply repeat the above steps again.

[the_ad id=”12397″]

Tracking and Reviewing

Turn on, off track changes. Track changes in a worksheet using a specified display view.

Whenever we make changes to any document and send that document to someone else, then it might be possible that some of those changes might not be required by the other person. In such a case, it would be very helpful if we could highlight the changes which we make and the other person could choose among these changes for the ones he wants to come and the ones he does not want.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-collaborative-editing]
[the_ad id=”12356″]

Calc offers this functionality. Simply open the spreadsheet to which you want to make the changes.

Now click on ‘Edit’ from the main menu bar. From the resulting drop down, click on ‘Track Changes’, and from the sub menu, click on ‘Record Changes’. Now make the changes to your spreadsheet. Any changes which you make will be highlighted by a colored cell.

To turn-off change tracking, simply click on ‘Record Changes’ again.

This way we can track all the changes which are done to a spreadsheet. To make the tracking more useful for the users, comments can also be added for the changes. To add a comment, select the cell which has been changed. Then click on ‘Edit’ from the main menu bar, from the drop-down click on ‘Track Changes’, then from the sub-menu click on ‘Comment on Change’. This will open the comment dialog. Add the comment which needs to be added and click on ‘OK’.

This will add the comment to the cell, which is shown when the mouse is hovered over that cell.

[the_ad id=”12397″]

Tracking and Reviewing

Turn on, off track changes. Track changes in a worksheet using a specified display view.

Whenever we make changes to any document and send that document to someone else, then it might be possible that some of those changes might not be required by the other person. In such a case, it would be very helpful if we could highlight the changes which we make and the other person could choose among these changes for the ones he wants to come and the ones he does not want.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-collaborative-editing]
[the_ad id=”12356″]

Calc offers this functionality. Simply open the spreadsheet to which you want to make the changes.

Now click on ‘Edit’ from the main menu bar. From the resulting drop down, click on ‘Track Changes’, and from the sub menu, click on ‘Record Changes’. Now make the changes to your spreadsheet. Any changes which you make will be highlighted by a colored cell.

To turn-off change tracking, simply click on ‘Record Changes’ again.

This way we can track all the changes which are done to a spreadsheet. To make the tracking more useful for the users, comments can also be added for the changes. To add a comment, select the cell which has been changed. Then click on ‘Edit’ from the main menu bar, from the drop-down click on ‘Track Changes’, then from the sub-menu click on ‘Comment on Change’. This will open the comment dialog. Add the comment which needs to be added and click on ‘OK’.

This will add the comment to the cell, which is shown when the mouse is hovered over that cell.

[the_ad id=”12397″]

To run a macro, click on ‘Tools’ from the main menu bar. From the resulting drop-down menu, click on ‘Macros’, and from the sub-menu which gets displayed, click on ‘Run Macro’. This will open the ‘Macro Selector’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-run-a-macro]
[the_ad id=”12356″]

In this dialog box, in the ‘Library’ field, we have the list of all the libraries. Select the appropriate library and also the required module from that library. This will display the name of the macros present in that module in the ‘Macro Name’ field. Select the appropriate macro name and then click on ‘Run’. This will run the selected macro.

Assign a macro to a custom button on a toolbar.

To assign a macro to a custom button on a toolbar, click on ‘View’ from the main menu bar. From the resulting drop-down menu, click on ‘Toolbars’, and from the resulting sub-menu, click on ‘Form Controls’. This will open the ‘Form Controls’ dialog box. In this dialog, first of all click on the ‘Design Mode On/Off’ button. This will enable all the other buttons.

After this, click on ‘Push Button’. The mouse pointer will change to a ‘+’ sign. Drag anywhere on the sheet to create a ‘Push Button’.

After this, right click on the newly created button and from the resulting menu, click on ‘Control’. This will open the ‘Properties’ dialog box.

In this dialog, under the ‘Events’ tab, we have different actions. Click on the ‘…’ button in front of the action to which you want to assign the macro. This will open the ‘Assign Action’ dialog box.

In this dialog box, under the ‘Assign’ field, click on the ‘Macro’ button. This will open the ‘Macro Selector’ dialog box.

In this dialog box, select the appropriate macro which needs to be assigned to this button. After selecting, click on ‘OK’. This will bring back the ‘Assign Action’ dialog box. Here also click on ‘OK’. This will bring back the ‘Properties’ dialog. Close this dialog and the selected macro will get assigned to the newly created button.
[the_ad id=”12397″]

To run a macro, click on ‘Tools’ from the main menu bar. From the resulting drop-down menu, click on ‘Macros’, and from the sub-menu which gets displayed, click on ‘Run Macro’. This will open the ‘Macro Selector’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-run-a-macro]
[the_ad id=”12356″]

In this dialog box, in the ‘Library’ field, we have the list of all the libraries. Select the appropriate library and also the required module from that library. This will display the name of the macros present in that module in the ‘Macro Name’ field. Select the appropriate macro name and then click on ‘Run’. This will run the selected macro.

Assign a macro to a custom button on a toolbar.

To assign a macro to a custom button on a toolbar, click on ‘View’ from the main menu bar. From the resulting drop-down menu, click on ‘Toolbars’, and from the resulting sub-menu, click on ‘Form Controls’. This will open the ‘Form Controls’ dialog box. In this dialog, first of all click on the ‘Design Mode On/Off’ button. This will enable all the other buttons.

After this, click on ‘Push Button’. The mouse pointer will change to a ‘+’ sign. Drag anywhere on the sheet to create a ‘Push Button’.

After this, right click on the newly created button and from the resulting menu, click on ‘Control’. This will open the ‘Properties’ dialog box.

In this dialog, under the ‘Events’ tab, we have different actions. Click on the ‘…’ button in front of the action to which you want to assign the macro. This will open the ‘Assign Action’ dialog box.

In this dialog box, under the ‘Assign’ field, click on the ‘Macro’ button. This will open the ‘Macro Selector’ dialog box.

In this dialog box, select the appropriate macro which needs to be assigned to this button. After selecting, click on ‘OK’. This will bring back the ‘Assign Action’ dialog box. Here also click on ‘OK’. This will bring back the ‘Properties’ dialog. Close this dialog and the selected macro will get assigned to the newly created button.
[the_ad id=”12397″]

Record a simple macro like: change page setup, apply a custom number format, apply auto formats to a cell range, and insert fields in worksheet header, footer.

A macro is a saved sequence of commands or keystrokes that are stored for later use. The LibreOffice macro language is very flexible, allowing automation of both simple and complex tasks. Macros are especially useful to repeat a task the same way over and over again.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-automation]
[the_ad id=”12356″]

To learn how to record a simple macro, we need to take a simple example. Here we are going to create a macro for applying a custom number format to a cell.

To record a macro, first of all we have to make sure that macro recording has been enabled for the sheet. This can be checked by clicking on ‘Tool’ from the main menu bar. From the resulting drop-down, click on ‘Options’. This opens the ‘Options’ dialog box. In this dialog box, under ‘LibreOffice’, we have an option ‘Advanced’. Here we have a checkbox called ‘Enable macro recording (limited)’. This checkbox must be checked in order to be able to record macros in Calc.

After the macro recording has been enabled, select the cell to which the custom number format needs to be applied. Now click on ‘Tools’ from the main menu bar. From the resulting drop-down, click on ‘Macros’ and from the resulting sub-menu, click on ‘Record Macro’. This will open the ‘Record Macro’ dialog box.

In this dialog box, we have a ‘Stop Recording’ button. Once this dialog is open, start working on applying the custom number format to the selected cell. Once you are finished, click on ‘Stop Recording’. This will open the ‘LibreOffice Basic Macros’ dialog box.

In this dialog box, select the location where you want to save the macro. Here we are going to select the name of the document, which is ‘Untitled1.ods’. Under this name we have the ‘Standard’ library. In this library create a new module by clicking on the ‘New Module’ button. After creating the module, click on the module name and enter a new name for the macro under ‘Macro Name’. After entering the name, click on ‘Save’. This will save the macro.
[the_ad id=”12397″]

There are some file types which are which are actually spreadsheet files, but in text format where cell contents are separated by a character. This character can be anything, say a comma or a colon. The extensions for such files are normally .csv or .txt and data from such files can be imported to Calc.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-import-delimited-data]
[the_ad id=”12356″]
To import data from a text file, click on ‘File’ from the main menu bar. From the resulting drop-down, click on ‘Open’. This opens the ‘Open’ dialog box. Browse to the .csv or .txt file which needs to be imported, select that file and click on ‘Open’. This opens the ‘Text Import’ dialog box.

A variety of options can be set using the ‘Text Import’ dialog. The ‘From Row:’ under ‘Import’ is the row number from where the import must begin. The different rows and their row numbers can be viewed under ‘Fields’. ‘Separator Options’ can be used to select the characters or the different rules which are going to be used to separate the data in the different columns in a text file.

After selecting all the values, click on ‘OK’ and the text file will get imported into Calc and will get opened as a new spreadsheet.

[the_ad id=”12397″]

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