6. Write the steps to consolidate data of two sheets in Libre Office Calc.

AIM:
To write the steps to consolidate data of two sheets in Libre Office Calc.
PROCEDURE:
STEP 1: Open a new file in Open Office Calc and write the following data

STEP 2: Open another file in Libre Office Calc and write the following Data

STEP 3: Open the third sheet and click on Data → Consolidate. The following dialog box appear.
STEP 4: Click to select Source data range of first sheet and then click on Add button
STEP 5: After adding Source range from both the sheets, the dialog box will appear like shown below
STEP 6: Click on the green button and select the Cell where you want the consolidated data.
STEP 7: After adding both the range and selecting the cell where we want the result, click on OK button.
STEP 8: After clicking OK button, we will get the consolidated data.
RESULT:
The data from the two sheets has been consolidated, and the output is shown.
STEP 3. STEP 4.

STEP 5. STEP 6.

STEP 7. STEP 8.
7. The table below shows the points scored by different schools in a competition:

Name School Name Points Scored Age

Shibbu Oakridge 4 13
Taimur Green Valley 7 12
Kaali Sunshine High 6 13
Wasim Joseph School 50 13
Rahul JJRIS 2 14

Using the SUBTOTAL feature in LibreOffice Calc, find out the total number of points scored by Joseph
School. Provide the steps to calculate the subtotal and submit the final result.
AIM:
To calculate the total number of points scored by "Joseph School" using the SUBTOTAL feature in
LibreOffice Calc.
PROCEDURE:
STEP 1: Open a Calc spreadsheet.
STEP 2: Tabulate the information as given in the question.
STEP 3:With the active cell inside some data in the spreadsheet, click on Data> Autofilter
STEP 4: Select the location for the subtotal to be displayed by clicking in the chosen cell.
STEP 5: Select Insert>Function from the menu bar.
STEP 6: Select SUBTOTAL from the function list in the Function Wizard and click on Next>at the bottom of the
dialog box.
STEP 7: Enter the required information into the two input boxes. The range is selected from the filtered data,and
the function is selected from the list of available possible functions.
STEP 8: Click on the OK button to return the summed values of points
STEP 9: Choose the School as Joseph School, from the autofilter.
STEP 10: The sum value change.

RESULT:
Thus, the total number of points scored by Joseph School is calculated using the subtotal, and the
output is as shown.
 8. Create a macro function in LibreOffice Calc that calculates the area of a circle by passing the radius as an
argument.
AIM:
To design and implement a macro function in LibreOffice Calc that accurately calculates the area of a
circle, taking the radius as an input argument, and returns the result for efficient and precise circle area
computations.
PROCEDURE:
STEP 1: Click on Tools menu and then select Macros command.
STEP 2: Click on Organize Macros > Libre office Base. An Libre office base Macros dialog box appears.
STEP 3: Click on a Macro name i.e.,Main
STEP 4: Click on Edit button.
STEP 5: My Macros Code window appears.
STEP 6: Type the following codes in an empty space to create Area() function.
STEP 7: Make sure sure not to click within any sub and end sub lines or function and end function lines.
STEP 8: Click Save icon on the toolbar or press Ctrl+S keys and then close the window.
STEP 9: To use newly created function by passing a value is Area(<value>).
STEP 10: Type the function in the cell, the macro call the function.
STEP 11: The calculated area of a circle is displayed as output.
9. Learning Outcome Analyze data using scenarios and goal seek
Objective: Applying Data Consolidation in LO Calc
Task: 1. Create a new file in spreadsheet software and enter data for 10 salesmen.
2. Add two worksheets in the file and modify the data for all 3 quarters.
3. Rename all worksheets like sheet1 - 2018, sheet2 - 2019 and sheet3 - 2020.
4. Now add one more worksheet at the end and rename as consolidated sheet.
5. Now type the serial no and names as displayed, use sum function to add data for 2018, 2019 and 2020 in
respective cells using consolidation.

1. Create a new spreadsheet in LO Calc.

2. Enter the data for Sheet 1 and rename sheet as 2018. To rename the worksheet right click on sheet tab then type
the desired name. Press enter key to accept the name.

3. Follow the same instructions for next two worksheets.

4. Add one more worksheet to display the consolidation result and rename as Consolidated.
5. Select the cell ranges where results needs to appear.
6. Click on Data ⇢ Consolidate option. A consolidate dialog box will appear.
7. Select your desired function (Sum here) and add references to the cells by selecting ranges.
8. Move the cursor on 2018 work sheet and select the range. Click on Add button

9. Repeat the same for 2019 and 2020 worksheet respectively.

10. Click on OK button.
10. Create and Customize Table of Contents
Objective: Create and Customize Table of Contents
Task: Prepare a document with different headings and apply a table of contents to it.
PROCEDURE:
STEP: 1. Type the contents.
STEP 2. Apply appropriate level of headings from the Styles and Formatting window.
STEP 3. Click on Insert → Indexes and Tables → Indexes and Tables.
STEP 4. Select and apply the styles for the Table of Contents.
STEP 5. Click on OK.
11. Share and review a spreadsheet
Objective: Setting up a spreadsheet for sharing.
Task: Create a new file in spreadsheet software and enable sharing
1. Create a new worksheet, enter data as you wish.
2. Save your worksheet with your desired name.
3. Enable sharing for your worksheet.
PROCEDURE:
1. Create a new worksheet and enter data.
2. Click on File → Save and Use proper name for the worksheet.
3. Click on Tools → Share Document.

4. The Share Document dialog box will appear. Click on Share this spreadsheet with other users.

5. Click OK button.
12. Create and edit tables using wizard
Objective: Steps to create a table using table wizard
Task: Create a table using wizard
Create any table in LO base using table wizard.
Solution
1. Click on Start → All Programs → Libre Office 4.1.7 → Libre Office Base.
2. LO Base window will open with Database Wizard. Select or create the database to work upon it. I have
selected existing database db1. Click on Finish button

3. Now click on Tables button from Left pane and chLOse Use Wizard to Create Table… option from the tasks
window.

4. The Table Wizard will open. Follow wizard steps to create a table.

5. In first step of wizard select the Category either Business or Personal, Table from list of sample tables, and
fields from available fields. Click on Finish button
6. Click on Next and select field types and all if you want to change it. Click on Next.

7. Set a primary key for your table in this step. I have selected EmplyeeID as Primary key.

8. If you wish to change the table name then type new name for the table and click on Insert Data immediately,
and click on Finish.

9. Insert data.
13. Queries
1. Add Columns
1. Add column Total → alter table "marksheet" add column "Total" tinyint
2. Add column Percentage → alter table "marksheet" add column "Percentage" decimal(5,2)
3. Add column grade → alter table "marksheet" add column "Grade" char(2)

2. Insert records
1. insert into "marksheet" values (101, 'Sagar', 105, 27, 25, 28, 80 , 88 , 'B1')
3. Steps to perform a query using design view
1. Click Queries → Create Query in Design View….
2. The query design window will open.
3. Select the marksheet table and click on Add button.
4. Now select fields given in the question like name, rollno, marks,Sub101, Sub102, Sub103, Total and
Percentage.
5. Save the query

4. Query Wizard Steps

1. Click on Queries → Use Wizard to Create Query…
2. Query Wizard opens.
3. Select the table marksheet and Select the fields given the question
4. Click on finish.
Output

5. Display maximum marks and minimum marks for the fields given in the question, using design view.
1. Click Queries → Create Query in Design View….
2. The query design window will open.
3. Select the marksheet table and click on Add button.
4. Now select the field Sub101 and Sub102.
5. ChLOse the function maximum under Sub101 and Minimum under Sub102.

6. Save the query and check the result

14. Display the rollno, name and percentage whose percentage are more than 70 using design view
1. Click Queries → Create Query in Design View….
2. The query design window will open.
3. Select the marksheet table and click on Add button.
4. Select columns given in the question i.e. Rollno, Name and Percentage.
5. Type >70 in the front of criteria under the percentage field
6. Save the query and check the result.

Output
15.Create Forms and Reports using wizard
Objective: Creating form using wizard
Task: Create table a form using wizard by selecting all the fields for the table – Marksheet
Solution
Steps to create a form using wizard
1. Click on Forms → Use Wizard to Create Form…
2. A Form Wizard appears

3. Select the table from Tables or Queries then add all the fields.
4. Click on Next button. Ignore step 3 and step 4.
5. Select the first option i.e. Columnar – Labels Left then click on the next button

6. Now set data entry step will be there. Ignore this step and click on Next. ChLOse the styles for the form
interface and click on next.

7. Type new name for the form and click on work with the form. Click on Finish