PART – B (IT 402) Class – X
Unit – 2 (ELECTRONIC SPREADSHEET - ADVANCED) Session 1 : Data Using Scenarios and Goal Seek
Spreadsheet application refers to the application which is composed of rows and columns and where rows and columns will be intersected is known as cell.
We will use Electronic spread sheet application i.e. OpenOffice calc which is similar to MS Excel.
Example of some spreadsheet applications: - OpenOffice calc , MS Excel , LibreOffice calc etc.
Consolidating data: -
The word consolidate refers to “combine” it means Data consolidation refers to the gathering of Data from various worksheets into master or single worksheet . Master worksheet is that worksheet where we will displayed consolidated data.
Or In other words we can say data consolidate functions allow you to collect data from several worksheets and display in one worksheet.
Consolidate option comes from Data Menu like:
Data -> Consolidate
Steps to consolidate the Data in OpenOffice Calc: - 1. Open OpenOffice Calc
2. Create two or three worksheet with data that we want to consolidate 3. (choose consolidate option under Data Menu) Data -> consolidate 4. Consolidate dialog will be opened (As given below)
5. Select the range the of data from various worksheets by clicking on shrink icon.
6. After getting the range of data from worksheets ,add this range of data in consolidated range by clicking on add button.
Shrink icon
7. Repeat this step for same number of time until you will not get data from all sheets.
8. Select any type of function that you want to apply on your consolidate data like sum , max , min , average function etc.
9. select on More option and check the Row labels , column labels and Link to source data option.
10. Click Ok the consolidated Data will be displayed.
If you are using the same range of cell data every time then click on : Data -> define range
Subtotal : -
Subtotal function / option is used to create the sub total of values in OpenOffice calc and while creating subtotal we will group the data of same types in different groups and then we will calculate subtotal of grouped values and grand total also and while subtotalling the value we can apply various function on data e.g. sum , max , min etc.
The selected range of data on which we are going to apply the subtotal function also known as array of data.
When we will apply subtotal on worksheet data then make sure data must column labels.
Subtotal option comes under Data Menu i.e. Data -> subtotal
Note: -Column labels means Name of Columns.
Steps to use the subtotal function: -
1. Insert the required data in OpenOffice calc with column labels.
2. Select the range of cells as given below (Company Data sales of cars in different month Zone wise).
3. Choose the option: Data -> subtotals as given below: -
4. Subtotal dialog box will be opened as given below: -
5. Choose the column whose value you want to group by using Group by Box and then select the another columns where you want to apply subtotal as given below: -
Company column for Graouping
Subtotals of Jan and Feb month to know the sale group wise.
Note : -You can only calculate the subtotal for Jan Month depends on user requirements.
Used function for subtotaling , Note : - these possible functions can be used in
subtotal operation.
6. Use function that you want to use for subtotal.
7. Click Ok.
Note :- if we change the value of selected range of cells then subtotal will automatically recalculated.
Scenario: -
Scenarios are the tools to test ‘what if’ questions. Scenarios are used to display the various options in drop down list or navigator form, from where we can select one option and as per selected option we will see the different set of values to be
appeared.
Each scenario is given a different name and can be edited separately.
If we print the spreadsheet then values of active scenario is printed.
Scenario option comes under Tools menu i.e. Tools -> Scenario.
Steps to create Scenario in OpenOffice calc: -
a. Open OpenOffice calc.
b. Enter name and values for different scenario and select them , to select multiple value hold down ctrl key.
c. Choose Tools -> Scenario option.
d. Scenario dialog box will be opened.
e. Enter name of scenario and optionally you use other options.
f. Click Ok.
g. Scenario will be created.
In a similar way we can create several scenarios in OpenOffice calc.
Goal seek: -
Goal seek option is used to get or produce some target value in OpenOffice calc automatically that we want to achieve by changing one of value of the existing cell then we will use Goal seek of OpenOffice calc.
The data on which we want to apply Goal seek must contain formula.
Goal seek option comes from Tools Menu i.e. Tools -> Goal seek Normally Goal seek deals with one input variable.
Under Goal seek you will get three option: -
1. Formula cell (The cell where we have put formula) 2. Target value (The value we want to get or achieve )
3. Variable cell (The cell whose value we want to change) Steps to use Goal Seek option: -
1. Open OpenOffice calc
2. Enter data with formula that you want to enter.
3. Goto Tools -> Goal seek
4. Goal Seek dialog box value will be opened.
5. Select formula cell , enter target value and select cell whose value you want to change i.e. variable cell.
6. Click Ok.
7. Desired result will automatically produced.
Solver: -
Solver option is the extended or elaborate form of Goal Seek as in goal seek we are only able to change the value of one cell but in solver we can change the values of multiple cells to get the desired result.
Actually in solver we are going to set the rules according which we can adjust or change the values of given cells and these set of rules are actually the limitation on values.
Solver option comes under Tools menu i.e. Tools -> solver
Solver option normally deals with multiple unknown variables.
Steps to create the solver in open Office calc: -
1. Open OpenOffice calc
2. Enter the data into that you want to enter
3. Select target cell that is the optimum value for whole data 4. Then enter the value into value of field
5. Select the range of cells whose you want to change 6. Set the rules for your data
7. Click on solve button, solver will display the result in front of you.
8. Click on keep result option and Ok
Note : - whats if analysis Tools = Scenario , Goal seek , Solver.
Session 2: Link Data and spreadsheet
How to rename the worksheets in OpenOffice calc: -
When we create any worksheet in OpenOffice calc then by default its sheet named as sheet1 , sheet2 and so on and sheet names display at the bottom of spreadsheet as shown bellow: -
Note: - By default OpenOffice calc open with three worksheets, named as sheet1 , sheet2 , sheet3.
If we want to rename the worksheets in OpenOffice calc then we have three ways to do the same task: -
a. Double click on existing worksheet names and rename it.
b. Right click on worksheet name and select the option Rename sheet and rename it.
c. Go to and select : Format -> sheet -> rename option to rename the name of worksheet.
How to insert the new worksheet in OpenOffice calc: -
We can insert the new worksheets in OpenOffice calc by using following ways: -
1. From insert menu i.e. Insert -> sheet option
2. Right click on sheet tab and select the option insert sheet.
3. By Click on empty space at the end of last sheet in sheet tab.
Note : - we can also insert the multiple sheets at once.
Cell reference: -
Cell reference simply refers to the address of a cell or range of cells used on a worksheet that we want to used in calculation, It means cell reference can be the reference or address of one cell or multiple cells (range of cells).
See Example of cell references from given sheet: -
In this Example we have summed up the marks of all subjects using sum() function and reference of cells are from D8 to D13.
All sheets at the bottom of spreadsheet The part of the calc
where worksheet displayed known as
“sheet tab”.
Option 3.
Empty space at the end of last sheet in sheet tab.
Note: - Reference of any cell simply means cell address.
Referencing other sheets: -
Referencing other sheets means to take the reference of cells and data from other worksheets.
We can take the reference of cells from other sheets in two ways as given below:-
1. Creating the cell reference with mouse 2. Creating the cell reference with keyboard
1. Creating the cell reference with mouse: - In this method we will take the reference of cell or cells from other sheets using mouse.
Steps : -
a. Open Open office calc.
b. Open your main sheet, in which you are working c. Type = equal sign in any cell or in formula bar
d. Now go to other sheet and click on cell whose reference or value you want to take.
e. Press enter or click the green tick in the formula bar f. The cell reference will be taken.
2. Creating the cell reference with keyboard: - In this method we will take the reference of cell or cells from other sheets by using keyboard.
Steps: -
a. Open OpenOffice calc
b. Open your main sheet , In which you are working c. The cell reference will be as per given format : -
=’sheetname’ . cell
e.g. = ‘sheet3’ . A2 , It means we are taking value of Cell A2 of sheet3.
Note: - Sheetname should be quoted in single quotes like ‘sheetname’
Working with hyperlinks: -
Hyperlink in calc are used to link the different documents with spreadsheet or jump to other sheets within spreadsheet or even if we want to open any website or webpage then we are using the concept of hyperlink.
The hyperlink option comes under insert menu i.e. insert -> hyperlink
Note: - hyperlink dialog will be opened from hyperlink icon of standard toolbar.
Types of hyperlinks: - 1. Relative hyperlinks 2. Absolute hyperlinks
1. Relative hyperlink: -The relative hyperlink is normally created when spreadsheet and target linked file at same location. E.g. if we have two spreadsheet linked to each other and are stored in same folder.
If we move the entire folder to a new location E.g. D Drive to E Drive , will not break the relative hyperlink.
2. Absolute hyperlink: - The absolute hyperlink is created when two files are not in same location and absolute hyperlink will be broken when target link file is moved or deleted.
Note : - In absolute hyperlink we always mention the full or complete path or address of target file.
Linking to External Data: -
Linking to external data means to link the spreadsheet with some outside external data E.g. insert table data from HTML documents , external Excel spreadsheet etc.
Link to external data option comes from insert menu i.e.
Insert -> link to External Data.
Steps : -
a. Open openoffice calc.
b. Choose insert->link to External Data c. External Data dialog box will be opened.
d. Enter URL (Address) of source worksheet
e. Select the table from given table/ranges option
f. Click OK and Data will be displayed on your calc spreadsheet.
Linking to Registered Data Sources: -
We can also link the registered data sources to the spreadsheet , Data sources means the databases or Address List already created.
Steps: -
1. Open OpenOffice calc
2. Choose: Tools->Options->OpenOffice Base->Databases 3. Click on New Button, to register a New data Source 4. Select a Database file and give a name to it
5. Click Ok.
Sharing Worksheet Data: -
Sharing worksheet data means to share the worksheet among multiple users at once because sometimes It is necessary to have multiple people working on one file at a time so It is very good idea to share the worksheet among them.
using various spreadsheet software we can share the worksheet with other users , where other users can access it simultaneously.
Sharing of spreadsheet using OpenOffice calc: -
We can share the spreadsheet in OpenOffice calc to with multiple users for sharing or collaboration purpose, where all can access sheet at once.
Steps: -
a. Open OpenOffice calc
b. Choose Tools -> Share Document
c. Activate “Share this spreadsheet with other users” option d. Click Ok.
How to un shared the Document: -
If we want to stop sharing of document or if we want to disable the sharing mode of file then again we will go to Tools->Share Document Option and uncheck the option “Share this spreadsheet with other users”.
Opening a shared spreadsheet: -
When we open any spreadsheet in shared mode then shared is shown with file name and some of features are not available in this mode.
Saving a shared spreadsheet: -
when we save a shared worksheet, one of the following situation may occur: -
1. If the worksheet was not modified and saved by another user since you opened and worked on it then worksheet will be saved properly.
2. If the worksheet was modified and saved by another user since you opened it, one of the following events will occur: -
2.1. If the changes do not conflict as per changes saved by another user then worksheet will be saved properly and changed made by another user will be shown by red color.
2.2. If the changes conflict then worksheets will be shown in resolve
conflicts mode, when you are resolving the conflicts, no other user can save the shared worksheet.
2.3. If another user is resolving conflicts then you cannot save it and retry saving later.
Record changes: -
Open office calc has a feature to track which data has changed , when the changes was made , who made changes and which cell value had been changed. The
changed cell value will be shown by red color border.
Steps to active the record changes feature in OpenOffice calc: - 1. Open Office calc with worksheet data
2. Select: Edit -> changes ->Record option from menu bar.
Now after activate the Record changes feature , if we will make any changes in spreadsheet it will be shown by red color border.
Viewing changes: -
If we want to see or view changes made in file in more elaborate form then goto:-
Edit -> changes -> show option and we can see the changes by applies various kind of filter like changes of particular date , changes after or before of particular date etc.
Adding comments to changes: -
By default the comment for change cell will be displayed as : cell B4 changed from 9 to 14.
If we want to add a user comment to the changed value then we can do this by using following steps : -
a. Select Changed cell that means the cell whose value we have changed.
b. Choose Edit -> Changes -> Comments c. The Comment dialog box will be opened.
d. Type your own comments that you want to add and click OK.
After change the comments , now place the mouse pointer on changed cell , again you will see the changes in comments message.
Accepting or Rejecting Changes: -
When you receive any worksheet with changes done by other user then Record changes feature will help you to accept or reject the changes done in worksheet.
Steps to accept or reject the changes : - 1. Open edited worksheet
2. Go to : Edit -> changes -> Accept or Reject option 3. Accept or reject dialog box will be opened
4. Now you can decide you want to accept the changes or reject the changes.
Merge Worksheets: -
Sometimes multiple users are returning the worksheets after doing some changing so instead of review or check one worksheet at a time we can merge all
worksheets together.
Steps to merge the worksheets in openoffice calc: - 1. open original worksheet
2. Select : Edit –> changes ->Merge Docuements
3. select a file that you want to merge with original worksheet.
4. after merging the worksheet , either you can Accept changes or Reject changes.
Compare Documents: -
Sometimes if the reviewers or user forget to record the changes done in worksheet but it’s not a issue in open office calc , we can know the changes done in worksheet by comparing the original documents with edited document sent by someone.
Steps to compare the documents in OpenOffice calc: -
1. Open edited worksheet that you want to compare with original worksheet.
2. Select : edit -> compare document
3. Select the original worksheet and identify the changes done in worksheet.
Macro in Worksheet: -
Macro is a very powerful feature in OpenOffice calc, which is used to record the user activities as a set of commands and whenever user need to do the same task again then user can run the macro and same activity will be done.
So that’s the reason the macro is sometime also known as “saved sequence of commands” that user need to perform repeatedly.
Steps to Record the macro or create macro in calc: - 1. Open open office calc
2. Use : Tools ->Macros -> Record
3. The macro recording will be started with stop recording button.
4. Perform the operation or action that you want to perform.
5. After doing all actions click on stop recording button
6. Give the name to macro and save it, Macro name is user defined.
Note: - while saving macro , we can save it under new library and new module also
Using Macro As a Function: -
We can create the macro in OpenOffice calc and use it as a function, we can create the macro in OpenOffice calc with arguments and without arguments as well.
Format to create the Macro as Function in Calc without arguments: -
Function Function_name() Function_name() = value End Function
Example 1 : -
To create the macro named as schoolname() , which will display the School Name
“Army Public School kandrori” while calling or use it.
Function schoolname()
schoolname()=”Army Public School kandrori”
End Function Example 2: -
To create the macro named as myname() , which will display the Name of Student as “Tarun Kumar” while calling or use it.
Function myname()
myname()=”Tarun Kumar”
End Function
How to call or use macro as a function:-
The macro will be called as function so will be started with = sign.
=function_name()
E.g. =myname() , will display the message Tarun Kumar on Screen
Format to create the Macro as Function in Calc with arguments: -
Function Function_name(argument 1 , argument 2 , ………,argument n) Function Code here
End Function
Example 1 (To create the macro function to add two values): -
Function addtwovalue(x,y) addtwovalue = x+y
End Function
When we will use this Function as given below: -
=addtwovalue(4;6) , will display the Output as 10.
Example 2 (To create the macro function to subtract two values): -
Function subtwovalue(x,y) subtwovalue = x-y
End Function
When we will use this Function as given below: -
=subtwovalue(7;6) , will display the Output as 1
x,y under addtwovalue() function known as arguments, Note : - arguments are user defined , you can mention
any arguments like a , b, c etc.
