Author: user

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

Sort data by multiple columns at the same time.

Sorting data means arranging data as per some pre-defined structures. To sort any data in Calc, the first thing which needs to be done is to select the data which needs to be sorted. After selecting the data, click on ‘Data’ from the main menu bar. From the resulting drop-down, click on ‘Sort’. This opens the ‘Sort’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-sorting-data]
[the_ad id=”12356″]

Under the ‘Sort Criteria’ tab, different columns can be selected, according to which the sorting needs to be done. The order of sorting, whether ascending or descending, can also be chosen.

Under the ‘Options’ tab, some other options can be selected. If the labels of the columns are included in the data which is being selected, always check the ‘Range contains column labels’ checkbox.

After providing all the input, select on ‘OK’ and the sorting will be done.
[the_ad id=”12397″]