Category: Advanced Spreadsheets

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

Changing the chart type for any chart is helpful when we want to showcase our data in a different format. Sometimes, we might create a particular chart for a set of data but then later figure out that some other chart type would have been a better way of depicting this data. In such a case, we do not need to create another chart from scratch and can simply change the chart type for the already present chart.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-change-the-chart-type-for-a-defined-data]
[the_ad id=”12356″]

In order to change the chart type of a chart which has been created for a defined data series, first of all we need to select the chart by double-clicking on it. The chart will become surrounded by a grey border upon selection. After selecting the chart, click on ‘Format’ button on the main menu bar. From the resulting drop-down, click on ‘Chart Type’. After clicking on the ‘Chart Type’ button, the ‘Chart Type’ dialog box appears.

You can also right-click anywhere on the chart area, and then from the resulting menu click on ‘Chart Type’. This will also open the ‘Chart Type’ dialog box.

This lists the different chart types available. Any chart type can be selected from this list. After selection of the required chart type, just click on ‘OK’ and the chart type will get changed and a new chart will be created having the updated chart type.

[the_ad id=”12397″]

To add a secondary axis to a chart, first of all select the chart. After this, right-click anywhere on the chart area, and from the resulting menu click on ‘Insert/Delete Axes’. This will open the ‘Axes’ dialog box.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-add-a-secondary-axis-to-a-chart]
[the_ad id=”12356″]

In this dialog box, we have two checkboxes under ‘Secondary Axes’. Check any one or both of these boxes as per the requirement and then click on ‘OK’. New secondary axis will get added to the chart as per the selection.

[the_ad id=”12397″]

Create a combined column and line chart.

A combined column and line chart is a combination of two chart types. It is useful for combining two distinct but related data series. To create a combined column and line chart in Calc, first of all we need to select some data which we are going to represent with the help of this chart. After selecting the data, click on the ‘Insert’ button from the main menu bar and from the resulting drop-down click on ‘Chart’. This will open the chart wizard.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-creating-charts]
[the_ad id=”12356″]
Here you can select ‘Column and Line’ from ‘Choose a Chart Type’. In ‘Number of lines’, you can select the number of lines required to be there in the chart. After selecting everything, click on ‘Next’.

This will open the ‘Data Range’ tab. Any changes, if required can be done here. Similarly on the click of ‘Next’, we’ll have the ‘Data Series’ and ‘Chart Elements’ tabs. Changes can be done in these tabs, but keeping the default values will also work fine. Finally, click on ‘Finish’.
The combined column and line chart will get created.

[the_ad id=”12397″]

Use a 3-D reference within a sum function.

Till now we have seen how to use the cell ranges from the same sheet as the arguments for the functions. But we can also use cell ranges from other sheets as arguments for the function. This is known as 3-D referencing.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-3-d-and-mixed-reference]
[the_ad id=”12356″]
To use 3-D referencing, use the same ‘Select’ icon which is always used and when the ‘Select’ icon is in use and in the minimized state, you can go to any of the open sheets and select the cell ranges from there. Then, upon maximizing the wizard, these ranges will be taken as the arguments for the function. This is how 3-D referencing works. This has also been shown in the illustration given below.

Use mixed references in formulas.

Using mixed references in formulas means to use the different kinds of references which we have learned till now in the same formula. A single formula can use all or some of the different kinds of references for getting the arguments and is not restricted to using only one particular kind. So if a function has, say, 4 arguments. The first can be entered manually, the second one can be a nested function, the third one can be a 3-D reference and the fourth one can be a cell range on the same sheet. So we can use as many types of references for the same formula as we want. This is what is meant by using mixed references for a formula.
[the_ad id=”12397″]

Create a two-level nested function.

A two-level nested function means that we use another function as an argument for a function. We’ll take this with the help of an example. Say we are using a ‘SUM’ function. We can input numbers as the arguments manually or we can input cells or data ranges by using the ‘Select’ icon.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-nested-functions]
[the_ad id=”12356″]
Additionally, we can also input another function as the argument by clicking on the ‘Function’ icon. This will give the option for selecting another function. Input another function here, which in this case is another ‘SUM’ function and even for this function we can have another function as the argument. So we can have a function inside a function and we can do this up to as many levels as we want. This is how nested functions work in Calc. A two-level nested function has been shown in the below given illustration.

Under the structure tab of the ‘Function Wizard’, we can see the levels of nesting very clearly. This shows how one function comes under another function and helps us in understating the nesting in a better way. This has been shown in the below given illustration.

[the_ad id=”12397″]

Related Lesson

In this lesson, we explored how to use database functions like dsum, dmin, dmax, dcount, and daverage. We started by explaining the DSUM function which adds all the cells of a data range that match the search criteria.
Advanced Spreadsheets – Database Functions

Create a two-level nested function.

A two-level nested function means that we use another function as an argument for a function. We’ll take this with the help of an example. Say we are using a ‘SUM’ function. We can input numbers as the arguments manually or we can input cells or data ranges by using the ‘Select’ icon.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-nested-functions]
[the_ad id=”12356″]
Additionally, we can also input another function as the argument by clicking on the ‘Function’ icon. This will give the option for selecting another function. Input another function here, which in this case is another ‘SUM’ function and even for this function we can have another function as the argument. So we can have a function inside a function and we can do this up to as many levels as we want. This is how nested functions work in Calc. A two-level nested function has been shown in the below given illustration.

Under the structure tab of the ‘Function Wizard’, we can see the levels of nesting very clearly. This shows how one function comes under another function and helps us in understating the nesting in a better way. This has been shown in the below given illustration.

[the_ad id=”12397″]

Related Lesson

In this lesson, we explored how to use database functions like dsum, dmin, dmax, dcount, and daverage. We started by explaining the DSUM function which adds all the cells of a data range that match the search criteria.
Advanced Spreadsheets – Database Functions

Use database functions: dsum, dmin, dmax, dcount, daverage.

In the same ‘Function Wizard’, under the ‘Database’ category, we have some database functions. One of these functions is ‘DSUM’. This function adds all the cells of a data range where the contents match the search criteria. This function takes 3 arguments, ‘Database’, ‘Database field’, and ‘Search criteria’. ‘Database’ gives the range of cells containing the data. ‘Database field’ gives the database column which needs to be used for the search criteria. ‘Search criteria’ defines the cell range containing the search criteria.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-database-functions]
[the_ad id=”12356″]

Let us take an example; let the ‘Database’ contains some fields as have been shown below.

Fruit	Weight	Cost	Profit
Apple	5	6	4
Pear	4	8	3
Pineapple	8	2	1
Mango	2	9	3

Also, let the ‘Search criteria’ be defined as given below.

Fruit	Weight	Cost	Profit
=Apple
=Pear

So, when these inputs are used, and the ‘Database field’ is set as ‘Profit, the result will come out to be ‘7’. This is because the entire database is evaluated and it is found that only two rows satisfy the criteria, row 2 and row 3. So after this the sum of the values of ‘Profit’ column is taken for these two rows, which comes out to be 4+3 = 7. This is how ‘DSUM’ works.

We also have the ‘DMIN’ and ‘DMAX’ functions in the same category. ‘DMIN’ returns the minimum of all the cells of a data range where the contents correspond to the search criteria. ‘DMAX’ does the same thing but it returns the maximum value. Both these functions use the same three arguments which have been described above.

Let us take an example, using the same data; we will get the result of ‘DMIN’ to be ‘3’ and for ‘DMAX’ to be ‘4’.
‘DCOUNT’ counts the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.

‘DAVERAGE’ returns the average value of all the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.

[the_ad id=”12397″]

Use database functions: dsum, dmin, dmax, dcount, daverage.

In the same ‘Function Wizard’, under the ‘Database’ category, we have some database functions. One of these functions is ‘DSUM’. This function adds all the cells of a data range where the contents match the search criteria. This function takes 3 arguments, ‘Database’, ‘Database field’, and ‘Search criteria’. ‘Database’ gives the range of cells containing the data. ‘Database field’ gives the database column which needs to be used for the search criteria. ‘Search criteria’ defines the cell range containing the search criteria.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-database-functions]
[the_ad id=”12356″]

Let us take an example; let the ‘Database’ contains some fields as have been shown below.

Fruit	Weight	Cost	Profit
Apple	5	6	4
Pear	4	8	3
Pineapple	8	2	1
Mango	2	9	3

Also, let the ‘Search criteria’ be defined as given below.

Fruit	Weight	Cost	Profit
=Apple
=Pear

So, when these inputs are used, and the ‘Database field’ is set as ‘Profit, the result will come out to be ‘7’. This is because the entire database is evaluated and it is found that only two rows satisfy the criteria, row 2 and row 3. So after this the sum of the values of ‘Profit’ column is taken for these two rows, which comes out to be 4+3 = 7. This is how ‘DSUM’ works.

We also have the ‘DMIN’ and ‘DMAX’ functions in the same category. ‘DMIN’ returns the minimum of all the cells of a data range where the contents correspond to the search criteria. ‘DMAX’ does the same thing but it returns the maximum value. Both these functions use the same three arguments which have been described above.

Let us take an example, using the same data; we will get the result of ‘DMIN’ to be ‘3’ and for ‘DMAX’ to be ‘4’.
‘DCOUNT’ counts the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.

‘DAVERAGE’ returns the average value of all the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.

[the_ad id=”12397″]

Use lookup functions: vlookup, hlookup.

In the same ‘Function Wizard’, under the ‘Spreadsheet’ category, we have the ‘VLOOKUP’ and ‘HLOOKUP’ functions. ‘VLOOKUP’ is vertical lookup and ‘HLOOKUP’ is the horizontal lookup. ‘VLOOKUP’ does the vertical search and references to the indicated cells. ‘HLOOKUP’ does the horizontal search and reference to the cells located below. Both these functions take four similar arguments, ‘search_criteria’, ‘array’, ‘Index’ and ‘sorted’. These give the value to be found in the first row or column, the array or the range for the reference, the row or column index in the array and the order in which the range needs to be sorted respectively. These arguments can be entered either manually or by using the ‘Select’ or ‘Function’ icons.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-lookup-function]
[the_ad id=”12356″]
Let us take an example for the ‘VLOOKUP’ function. Let the ‘search_criteria’ be ‘600’. This is the value which will be found by the function in the first column. Then we will select an ‘array’. The only important thing is that the ‘search_criteria’ value must be present in the first column of the ‘array’. ‘Index’ gives the index of the column in which the result value will be found. Let this be ‘4’ here. We will keep the value of ‘sorted’ as ‘False’. This means we are using no sorting mechanism. So here, the ‘VLOOKUP’ function will check for ‘600’ in the first column of the range and then will return the value stored in column whose index is equal to ‘Index’ from the same row as the one which contains ‘600’. So this function simply looks up ‘600’ in column 1, and returns the value from column 4 that’s in the same row (which contains 600 in column 1). This is how ‘VLOOKUP’ works.

The ‘HLOOKUP’ function works pretty much in the same manner. The only thing is that the ‘search_criteria’ is looked up for in the first row and the value is returned from the row given by ‘Index’ in the same column.
[the_ad id=”12397″]