Category: Advanced Spreadsheets

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

Create named scenarios.

A scenario is a tool in Calc which helps in testing ‘what-if’ questions. Anything which depends on a particular condition can be defined using a scenario. Say you need to calculate some amount in different currencies; you can have different scenarios for different currencies and can use them as per the requirement.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-scenarios]
[the_ad id=”12356″]
To create a scenario in Calc, select the cells which contain the values which are going to change between different scenarios. Like we have in the below given example.

After selecting the cells, click on ‘Tools’ from the main menu bar. From the resulting drop-down, click on ‘Scenarios’. This will open the ‘Create Scenario’ dialog.

It is preferred to not use the default name and use something which is meaningful for the users. Having a name for the scenarios is compulsory. An optional comment can also be entered. Settings contain some simple options which can be selected as per the requirements. The important ones are the ‘Prevent Changes’ and ‘Copy Back’ checkboxes. ‘Copy Back’ copies any changes made to the values of the scenario cells back into the active scenario. ‘Prevent Changes’ prevents changes to a scenario enabled as ‘Copy Back’.

After entering all the details, click on ‘OK’, and the scenario gets created.

We can create multiple scenarios for the same cells and have different cell values for each scenario. Then we just need to select the appropriate scenario from the drop-down and the values for that scenario will get auto populated in the cells.

Show, edit, and delete scenarios.

Click on ‘View’ button on the main menu bar. From the resulting drop-down, click on ‘Navigator’. This opens ‘Navigator’ dialog.

Click on the ‘scenarios’ icon in the navigator and this displays all the scenarios on the page. Double click on any scenario to make that scenario active.

To edit a scenario, right-click on any scenario name. This gives two options, delete and properties. Click on delete to delete a scenario and click on properties to edit it. Properties will open the same ‘Create Scenario’ dialog box where the appropriate changes can be made.
[the_ad id=”12397″]

Create named scenarios.

A scenario is a tool in Calc which helps in testing ‘what-if’ questions. Anything which depends on a particular condition can be defined using a scenario. Say you need to calculate some amount in different currencies; you can have different scenarios for different currencies and can use them as per the requirement.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-scenarios]
[the_ad id=”12356″]
To create a scenario in Calc, select the cells which contain the values which are going to change between different scenarios. Like we have in the below given example.

After selecting the cells, click on ‘Tools’ from the main menu bar. From the resulting drop-down, click on ‘Scenarios’. This will open the ‘Create Scenario’ dialog.

It is preferred to not use the default name and use something which is meaningful for the users. Having a name for the scenarios is compulsory. An optional comment can also be entered. Settings contain some simple options which can be selected as per the requirements. The important ones are the ‘Prevent Changes’ and ‘Copy Back’ checkboxes. ‘Copy Back’ copies any changes made to the values of the scenario cells back into the active scenario. ‘Prevent Changes’ prevents changes to a scenario enabled as ‘Copy Back’.

After entering all the details, click on ‘OK’, and the scenario gets created.

We can create multiple scenarios for the same cells and have different cell values for each scenario. Then we just need to select the appropriate scenario from the drop-down and the values for that scenario will get auto populated in the cells.

Show, edit, and delete scenarios.

Click on ‘View’ button on the main menu bar. From the resulting drop-down, click on ‘Navigator’. This opens ‘Navigator’ dialog.

Click on the ‘scenarios’ icon in the navigator and this displays all the scenarios on the page. Double click on any scenario to make that scenario active.

To edit a scenario, right-click on any scenario name. This gives two options, delete and properties. Click on delete to delete a scenario and click on properties to edit it. Properties will open the same ‘Create Scenario’ dialog box where the appropriate changes can be made.
[the_ad id=”12397″]

Use automatic sub-totaling features.

Automatic sub-totaling features help in displaying the sub-totals of particular sections of a table. To use automatic sub-totaling features, first of all make sure that you have automatic filters enabled for all the columns for which sub-totaling needs to be done. After this, select any one cell from the entire range.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-sub-totaling]
[the_ad id=”12356″]

Now click on ‘Data’ from the main menu bar and from the resulting drop-down, click on ‘Subtotals’. This will open the ‘Subtotals’ dialog box.

In this dialog box, we have some tabs which give the option for creating three groups and in each of these tabs we have some options. In the ‘Group by’ drop down list, we can select the field by which we want to group the data, and then we can select the fields whose subtotal needs to be calculated. We can choose from a list of functions for calculating the subtotals. After making all the changes, click on ‘OK’. The subtotal calculation and grouping for the data will get done.

Expand, collapse outline detail levels.

To expand or collapse the outline detail levels, you can simply use the ‘+’ and ‘-’ signs produced at the leftmost side of the data table upon the generation of subtotals. Upon clicking the ‘-’ sign, the detail level will collapse and on clicking the ‘+’ sign the detail level will expand back.

[the_ad id=”12397″]

Use automatic sub-totaling features.

Automatic sub-totaling features help in displaying the sub-totals of particular sections of a table. To use automatic sub-totaling features, first of all make sure that you have automatic filters enabled for all the columns for which sub-totaling needs to be done. After this, select any one cell from the entire range.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-sub-totaling]
[the_ad id=”12356″]

Now click on ‘Data’ from the main menu bar and from the resulting drop-down, click on ‘Subtotals’. This will open the ‘Subtotals’ dialog box.

In this dialog box, we have some tabs which give the option for creating three groups and in each of these tabs we have some options. In the ‘Group by’ drop down list, we can select the field by which we want to group the data, and then we can select the fields whose subtotal needs to be calculated. We can choose from a list of functions for calculating the subtotals. After making all the changes, click on ‘OK’. The subtotal calculation and grouping for the data will get done.

Expand, collapse outline detail levels.

To expand or collapse the outline detail levels, you can simply use the ‘+’ and ‘-’ signs produced at the leftmost side of the data table upon the generation of subtotals. Upon clicking the ‘-’ sign, the detail level will collapse and on clicking the ‘+’ sign the detail level will expand back.

[the_ad id=”12397″]

Apply advanced filter options to a list.

Advanced filter options help in filtering the data more precisely than it is possible with the default methods available in Calc. To apply advanced filter options to a list, select the list which needs to be filtered. After selecting the list, click on ‘Data’ from the main menu bar and from the resulting drop-down click on ‘Filter’. From the sub-menu which is generated, click on ‘Advanced Filter’. This will open the ‘Advanced Filter’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-filter]
[the_ad id=”12356″]

In this dialog box, we can select the filter criteria in a similar way to what we had done for the standard filter. The only difference is that in this case we are not restricted by the operators or conditions which we can use. Simply write the filter criteria in any cell or cell range in the sheet and then select that cell or cell range using the ‘Select’ icon placed in front of ‘Read Filter Criteria From’ option.

After selecting the filter criteria and making all other changes, click on ‘OK’. The list will get filtered as per the specified criteria.
[the_ad id=”12397″]

Automatically filter a list in place.

To automatically filter a list, select the complete list which needs to be filtered. After selecting the list, click on ‘Data’ from the main menu bar and from the resulting drop-down click on ‘Filter’. From the sub-menu which is generated, click on ‘AutoFilter’.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-filter-a-list]
[the_ad id=”12356″]

This will create a drop-down symbol with the list label.

Click on this symbol and from the resulting drop-down menu; we can select the different auto filtering options like ‘Top 10’, ‘Empty’ or ‘Not Empty’. We can also click on ‘Standard Filter’. This will open the ‘Standard Filter’ dialog box.

In this dialog, we have the options to add some filter criteria using the field names and values and some conditional and logical operators. After adding all the filters which are required, click on ‘OK’. This will filter the list in place.
[the_ad id=”12397″]

Automatically filter a list in place.

To automatically filter a list, select the complete list which needs to be filtered. After selecting the list, click on ‘Data’ from the main menu bar and from the resulting drop-down click on ‘Filter’. From the sub-menu which is generated, click on ‘AutoFilter’.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-filter-a-list]
[the_ad id=”12356″]

This will create a drop-down symbol with the list label.

Click on this symbol and from the resulting drop-down menu; we can select the different auto filtering options like ‘Top 10’, ‘Empty’ or ‘Not Empty’. We can also click on ‘Standard Filter’. This will open the ‘Standard Filter’ dialog box.

In this dialog, we have the options to add some filter criteria using the field names and values and some conditional and logical operators. After adding all the filters which are required, click on ‘OK’. This will filter the list in place.
[the_ad id=”12397″]

Create a customized list and perform a custom sort.

In some cases, when we might need to sort the data in a way other than the ones available by default in Calc, we can create our own custom list format. In the ‘Sort’ dialog box, under the ‘Options’ tab, we have the ‘Custom sort order’ checkbox. Here, any of the custom sort lists can be chosen using which the sorting can be done according to some user defined rules. These rules and custom lists can be created using the ‘Options’ settings of LibreOffice.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-list-and-sort]
[the_ad id=”12356″]
Click on ‘Tools’ button on the main menu bar. From the resulting drop-down, click on ‘Options’. This opens the ‘Options’ dialog box. Go to ‘Sort Lists’ under ‘LibreOffice Calc’.

Click on ‘New’ button to create a new custom list and save this list by clicking on ‘OK’. Once the custom list is created, it can be used to sort data using the ‘Sort’ dialog box.
[the_ad id=”12397″]

Create a customized list and perform a custom sort.

In some cases, when we might need to sort the data in a way other than the ones available by default in Calc, we can create our own custom list format. In the ‘Sort’ dialog box, under the ‘Options’ tab, we have the ‘Custom sort order’ checkbox. Here, any of the custom sort lists can be chosen using which the sorting can be done according to some user defined rules. These rules and custom lists can be created using the ‘Options’ settings of LibreOffice.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-list-and-sort]
[the_ad id=”12356″]
Click on ‘Tools’ button on the main menu bar. From the resulting drop-down, click on ‘Options’. This opens the ‘Options’ dialog box. Go to ‘Sort Lists’ under ‘LibreOffice Calc’.

Click on ‘New’ button to create a new custom list and save this list by clicking on ‘OK’. Once the custom list is created, it can be used to sort data using the ‘Sort’ dialog box.
[the_ad id=”12397″]