Category: Advanced Spreadsheets

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

Automatically, manually group data in a pivot table/data pilot and rename groups.

Grouping data is a simple process which can be used to create groups of data and then analyzing these groups instead of analyzing the individual values. This simply means that instead of focusing on the individual values, we analyze these values as a part of a larger group. This helps enormously in simplifying the data analysis process.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-group-data]
[the_ad id=”12356″]
To group numerical data in a pivot table, select any one cell from the entire list of cells which need be grouped. After selecting the required cell, click on ‘Data’ from the main menu bar. From the resulting menu, click on ‘Group and Outline’ and from the resulting sub-menu, click on ‘Group’. This will open the ‘Grouping’ dialog box.

In this dialog box we have three fields, namely ‘Start’, ‘End’ and ‘Group by’. To group data automatically, select the ‘Automatically’ radio button. To group data manually, click on the ‘Manually’ radio button. If ‘Manually’ is selected, enter the start and end values and in ‘Group by’ enter the number of items per group. After making all the changes, click on ‘OK’. This will create the required groups.

To group textual data in a pivot table, go to the column from which you want to group the data and select the values which you need to be grouped together. After selecting all the required values, click on ‘Data’ from the main menu bar. From the resulting menu, click on ‘Group and Outline’ and from the resulting sub-menu, click on ‘Group’.

This will create a new group as has been shown below.

This group can be renamed simply by renaming the label of the group which has been created, which in this case is ‘Group1’.
[the_ad id=”12397″]

Automatically, manually group data in a pivot table/data pilot and rename groups.

Grouping data is a simple process which can be used to create groups of data and then analyzing these groups instead of analyzing the individual values. This simply means that instead of focusing on the individual values, we analyze these values as a part of a larger group. This helps enormously in simplifying the data analysis process.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-group-data]
[the_ad id=”12356″]
To group numerical data in a pivot table, select any one cell from the entire list of cells which need be grouped. After selecting the required cell, click on ‘Data’ from the main menu bar. From the resulting menu, click on ‘Group and Outline’ and from the resulting sub-menu, click on ‘Group’. This will open the ‘Grouping’ dialog box.

In this dialog box we have three fields, namely ‘Start’, ‘End’ and ‘Group by’. To group data automatically, select the ‘Automatically’ radio button. To group data manually, click on the ‘Manually’ radio button. If ‘Manually’ is selected, enter the start and end values and in ‘Group by’ enter the number of items per group. After making all the changes, click on ‘OK’. This will create the required groups.

To group textual data in a pivot table, go to the column from which you want to group the data and select the values which you need to be grouped together. After selecting all the required values, click on ‘Data’ from the main menu bar. From the resulting menu, click on ‘Group and Outline’ and from the resulting sub-menu, click on ‘Group’.

This will create a new group as has been shown below.

This group can be renamed simply by renaming the label of the group which has been created, which in this case is ‘Group1’.
[the_ad id=”12397″]

Filter, sort data in a pivot table/data pilot.

Filtering data means applying some conditions to the data so that only those data fields get displayed which satisfy these conditions. To filter data in a pivot table, simply right-click anywhere on the table and from the resulting menu, click on ‘Filter’. This will open the ‘Filter’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-filter-and-sort-data]
[the_ad id=”12356″]

In this dialog box, we can define 3 filters which can be used to filter the data which can be present inside the pivot table. Simple arithmetic and conditional operators can be used to define these conditions. After defining all the filters, just click on ‘OK’. This will apply the defined filters to the pivot table.

To sort data in a pivot table, we can use the drop-down menus given with each of the column labels.

Every column label in a pivot table contains a drop-down symbol, upon click of which the sort menu is displayed. From this menu the different types of sort, ‘Ascending’, ‘Descending’, or ‘Custom’ can be chosen. ‘Custom’ will give a secondary list of all the custom sort styles, from which the required style can be chosen.
[the_ad id=”12397″]

Filter, sort data in a pivot table/data pilot.

Filtering data means applying some conditions to the data so that only those data fields get displayed which satisfy these conditions. To filter data in a pivot table, simply right-click anywhere on the table and from the resulting menu, click on ‘Filter’. This will open the ‘Filter’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-filter-and-sort-data]
[the_ad id=”12356″]

In this dialog box, we can define 3 filters which can be used to filter the data which can be present inside the pivot table. Simple arithmetic and conditional operators can be used to define these conditions. After defining all the filters, just click on ‘OK’. This will apply the defined filters to the pivot table.

To sort data in a pivot table, we can use the drop-down menus given with each of the column labels.

Every column label in a pivot table contains a drop-down symbol, upon click of which the sort menu is displayed. From this menu the different types of sort, ‘Ascending’, ‘Descending’, or ‘Custom’ can be chosen. ‘Custom’ will give a secondary list of all the custom sort styles, from which the required style can be chosen.
[the_ad id=”12397″]

Create and modify a pivot table/data pilot.

A pivot table is a very effective and efficient tool for comparing, combining, and analyzing large amounts of data in a simple manner. To use pivot table, the first thing which is required is some raw data. The data should be in the form of a database table consisting of rows and columns. Data can be contained in a Calc spreadsheet or can be an external file or database.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-using-pivot-tables]
[the_ad id=”12356″]

When data is contained in a Calc spreadsheet in the form of a table, then the most important point is to format your data in such a way so that it can be identified as a list. You just need to select any cell within the list and the tool will automatically pick up your list based on some rules. Any empty row or column, or the upper or left border is considered as the boundary of the list. This is why it is very important to never have empty rows or columns within a list. At the same time, individual lists should be marked by a boundary of empty rows or columns.

Another point is that only one cell must be selected within the list. If more than one cell is selected, then only the particular selected cells are considered to be the whole list. The last point is that all the Calc lists must always follow the normal linear structure. All data must be entered into the same column in order to be analyzed by a pivot table.

Below is a sample data table which we are going to use for this illustration.

We just need to select one cell within the list. After selecting the cell, click on ‘Data’ button from the main menu bar. From the resulting drop-down, select ‘Pivot Table’ and click on ‘Create’.

From the ‘Select Source’ dialog box which appears, check ‘Current Selection’ and click on ‘OK’. This will open the ‘Pivot Table Layout’ dialog box.

In this dialog box, we have four white areas which showcase the layout of the final result. These areas are ‘Page Fields:’, ‘Column Fields:’, ‘Row Fields:’, and ‘Data Fields:’. We also have an area called ‘Available Fields:’ which contains the names of all the data fields in our source table. We can simply drag and drop these available fields into the different areas to get the required layout for our pivot table. Similarly, any field can also be dragged back to ‘Available Fields:’ in order to remove it from the other white areas.

Here we have put the ‘course’ and ‘gender’ fields into the ‘Row Fields’ area and ‘sum’ field into the ‘Data Fields’ area. Any field we put into the ‘Data Fields’ is marked as a sum, this means that the sum of the ‘marks’ for different groupings are going to be shown in the final result. The Row Fields become the rows in the final table and Column Fields become the columns. Page Fields have the same functionality as the Column or Row Fields. The ‘Options’ and ‘Source and Destination’ fields provide some options to select the source of the data or the destination for the table, or some filtering options, and these can be chosen as per the requirements.

Finally, after setting all the values, click on ‘OK’. The pivot table will get generated in a new sheet or as per the destination settings.

Modify the data source and refresh the pivot table/data pilot.

If the data in the source table is modified, then it is possible to modify the pivot table to reflect these changes. After making the changes to the source table and saving them; simply go to the pivot table, right-click on the table and from the resulting shortcut menu, click on ‘Refresh’. This will update the pivot table for any changes which have been made to the source data.

But the pivot table can only be refreshed if the changes have been made to the contents and not the data labels. If the labels have been modified, then a new pivot table will have to be generated.

[the_ad id=”12397″]

Format columns, bars, plot area, chart area to display an image.

In Calc charts, we cannot use custom images by importing them into the charts from our local machine. Though Calc does provide the option for using the different types of ‘Fill’ options for the columns, bars, plot area or chart area like ‘Color’, ‘Hatching’, ‘Gradient’, and ‘Bitmap’. These options can be used to format the different components of a chart by displaying different styles and images, but custom images cannot be used in Calc.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-format-chart-to-display-an-image]
[the_ad id=”12356″]

To use the ‘Fill’ option for columns or bars, double-click on the required column or bar. This will open a dialog box, where under the ‘Area’ tab you will find the ‘Fill’ drop-down list. From this list you can choose the required fill type.

To use the ‘Fill’ option for plot area or chart area, double-click anywhere on the plot area or chart area of your chart and this will open a similar dialog box. In the same way as given above, in this dialog box under the ‘Area’ tab you will find the ‘Fill’ drop-down list. From this list you can choose the required fill type.
[the_ad id=”12397″]

Change scale of value axis: minimum, maximum number to display, major interval.

In Calc, the values displayed with the axes of a chart are normally the default ones which are selected by the system as per the data being used. In some cases, we might need to change these values, or require different intervals between two successive values. In all such cases we need to change the scale of value axis.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-change-scale-of-chart-value]
[the_ad id=”12356″]
To change the scale of value axis, first of all select the chart. After this, click on ‘Format’ from the main menu bar, and from the resulting menu, click on ‘Axis’. This will display a sub-menu. Click on ‘X Axis’ or ‘Y Axis’ from this sub-menu as per the requirement. This will open the ‘X Axis’ or ‘Y Axis’ dialog box.

In this dialog box, under the ‘Scale’ tab, we have some options like ‘Minimum’, ‘Maximum’ and ‘Major interval’. Uncheck the ‘Automatic’ checkboxes in front of these options and then you can add the minimum and maximum numbers to be displayed, or the major interval between the axis values. After making all the changes, click on ‘OK’. The scale of value axis will get changed accordingly.
[the_ad id=”12397″]

To re-position the chart title or legends in a Calc chart, first of all select the chart. After this, click once on the chart title or the legends box, the chart title or the legend box will get selected. Now without releasing the mouse button drag the title or the legends to the position where you want to place them. Release the mouse button once the appropriate location is reached and then release the mouse button. The chart title or the legend will get re-positioned.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-re-position-chart-title]
[the_ad id=”12356″]
To re-position the data labels, right-click on the label which you want to re-position and from the resulting menu, click on ‘Format Data Labels’. This will open the ‘Data Labels’ dialog box.

In this dialog box, under the ‘Data Labels’ tab, we have a ‘Placement’ drop down list. The appropriate position for the data label can be selected from this list. After selecting the position, click on ‘OK’. This will re-position the data label.
[the_ad id=”12397″]

In the same way as we can change the chart type for a chart, in case some new data field is added for a chart, we can simply update this in the already existing chart and we do not need to create another one from the beginning.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-add-and-delete-a-data-series-in-a-chart]
[the_ad id=”12356″]
To add or delete a data series in a chart, first of all select the chart. After this, right-click anywhere on the chart area, and from the resulting menu click on ‘Data Ranges’. This will open the ‘Data Ranges’ dialog box.

In this dialog box, under the ‘Data Series’ tab, we can add or delete a data series by using the ‘Add’ or ‘Remove’ buttons respectively. When a new data series is added, the name and range for that series can be selected from the spreadsheet using the select icons provided in the dialog box. After making all the changes, upon clicking ‘OK’, the new data series will get reflected in the chart.

[the_ad id=”12397″]