1,089
edits
Changes
From Open Educational Resources
no edit summary
==== Overview of features ====
==== Overview of features ====
Spreadsheet application is used for recording data, processing data, analysing data, creating text and graphical outputs. The application has many statistical, arithmetical, text processing functions which makes it a very powerful desktop tool. Data can be sorted, filtered and processed into outputs, including multi-variate tables.
Spreadsheet application is used for recording data, processing data, analyzing data, creating text and graphical outputs. The application has many statistical, arithmetical, text processing functions which makes it a very powerful desktop tool. Data can be sorted, filtered and processed into outputs, including multi-variate tables.
====Installation====
====Installation====
##Open terminal by clicking (Ctrl+Alt+T),
##Open terminal by clicking (Ctrl+Alt+T),
##Once Window page is open, in front of dollar($) symbol just type below command.
##Once Window page is open, in front of dollar($) symbol just type below command.
##sudo apt-get install libreOffice
##sudo apt-get install libreoffice
===== For Windows =====
===== For Windows =====
# It will start downloading "'''LibreOffice"''' into your computer (by default browser will save the files in "'''Downloads'''" folder in your system)
# It will start downloading "'''LibreOffice"''' into your computer (by default browser will save the files in "'''Downloads'''" folder in your system)
Click Yes if it ask your permission to allow this program make change to the computer,The Installation Wizard Welcome dialog box opens advising that the installation process is about to be started. Click “Next >”
Click "Yes" if it ask your permission to allow this program make change to the computer,The Installation Wizard Welcome dialog box opens advising that the installation process is about to be started. Click “Next”
<gallery mode="packed" heights="300px">
<gallery mode="packed" heights="300px">
</gallery>
</gallery>
Another dialog box opens, inviting you to choose whether to open Microsoft Office documents with LibreOffice. By default, this feature is not enabled. If you want LibreOffice to open Microsoft Office files (documents, spreadsheets and presentations), put a check mark in all four checkboxes.Another dialog box opens, asking you whether:
Another dialog box opens, inviting you to choose whether to open Microsoft Office documents with LibreOffice. By default, this feature is not enabled. If you want LibreOffice to open Microsoft Office files (documents, spreadsheets and presentations), put a check mark in all four check boxes. Another dialog box opens, asking you whether:
* A shortcut to open LibreOffice should be placed on your desktop. The default option is to create a shortcut.
* A shortcut to open LibreOffice should be placed on your desktop. The default option is to create a shortcut.
File:LO Calc 1 viewing a spreadsheet.png|Basic spreadsheet interface
File:LO Calc 1 viewing a spreadsheet.png|Basic spreadsheet interface
</gallery>
</gallery>
* LibreOffice Calc can be opened from "Applications --> Office --> LibreOffice Calc". This opens a ‘work book’. A work book can have many ‘sheets’. When you open the LO Calc application, it will show a window like this. The spreadsheet consists of rows and columns. Each column-row intersection is a cell; this is the place you will enter data in a spreadsheet. You can click the cursor on any cell and type in the information you want to enter. <br> Select Cell A1 and type “Name of city”, Hit enter. Next select cell B1 and type “Average annual rainfall (cms). Hit enter, and select cells of Column A to enter names of cities and Cells of Column B to enter annual average rainfall. <br> You can click on the A1 and B1 cells and click on the BOLD function icon (or simply type CTRL+B) to make the headings '''bold.'''
* LibreOffice Calc can be opened from "Applications --> Office --> LibreOffice Calc". This opens a ‘work book’. A work book can have many ‘sheets’. When you open the LO Calc application, it will show a window like this. The spreadsheet consists of rows and columns. Each column-row intersection is a cell; this is the place you will enter data in a spreadsheet. You can click the cursor on any cell and type in the information you want to enter. <br> Select Cell A1 and type “Name of city”, Hit enter. Next select cell B1 and type “Average annual rainfall (cms). Hit enter, and select cells of Column A to enter names of cities and Cells of Column B to enter annual average rainfall. <br> You can click on the A1 and B1 cells and click on the BOLD function icon (or simply type CTRL+B) to make the headings bold.
Like in the case of text document, you can use the File menu to save your spreadsheet. The file will be saved with a .ods extension. ODS is the short form of Open Document Spreadsheet.
Like in the case of text document, you can use the File menu to save your spreadsheet. The file will be saved with a .ods extension. ODS is the short form of Open Document Spreadsheet.
{{clear}}
{{clear}}
File:Cal step 2.png|Navigation from various sheets
File:Cal step 2.png|Navigation from various sheets
</gallery>
</gallery>
* You can move across cells using the arrow keys. You can also quickly go to the ends of the sheet using CTRL Keys, such as CTRL-Home (go to Cell A1), CTRL-End (bottom rightmost part of filled cells / entered data), CTRL – Up Arrow (next cell in same column, before an empty cell) etc. <br> It is useful to become comfortable using keyboard to move across the spreadsheet.<br> Columns and rows can be inserted or deleted or hidden in a spreadsheet. You can right click anywhere on the spreadsheet and insert/ delete rows and columns. You can also go to Sheet menu and insert rows/ columns. <br>
* You can move across cells using the arrow keys. You can also quickly go to the ends of the sheet using CTRL Keys, such as CTRL-Home (go to Cell A1), CTRL-End (bottom rightmost part of filled cells / entered data), CTRL – Up Arrow (next cell in same column, before an empty cell) etc. <br>It is useful to become comfortable using keyboard to move across the spreadsheet.<br>Columns and rows can be inserted or deleted or hidden in a spreadsheet. You can right click anywhere on the spreadsheet and insert/ delete rows and columns. You can also go to Sheet menu and insert rows/ columns. <br>
==== Inserting a new sheet ====
==== Inserting a new sheet ====
* '''Alignment of Text inside the cell :''' Since Calc gives much importance for cells, the text alignment inside the cell also needs to be taken care of. Such alignments can be Top, center and bottom. Wrap text option allows the text which gone out of the cell to be aligned within the cell boundary.
* '''Alignment of Text inside the cell :''' Since Calc gives much importance for cells, the text alignment inside the cell also needs to be taken care of. Such alignments can be Top, center and bottom. Wrap text option allows the text which gone out of the cell to be aligned within the cell boundary.
There are two useful options associated with cells known as Merge cell and Split cell.
There are two useful options associated with cells known as Merge cell and Split cell.
* '''Merge cell''' combines two or more cells together to make it as one. For example, If you want the main heading to be given in a particular column which is having two or more sub menus or subsections you can use this feature. To merge cells, select two or more cells and right click on anyone of the selected cells and click on merge cell.
* '''Merge cell''' combines two or more cells together to make it as one. For example, If you want the main heading to be given in a particular column which is having two or more sub menus or subsections you can use this feature.
To merge cells, select two or more cells and right click on anyone of the selected cells and click on "merge cell".
* '''Split cell''' is needed when you accidentally merged the cells which are no longer required and you want them to be the separate cells. To Split a merged cell, right click on the merged cell and click split cell.
* '''Split cell''' is needed when you accidentally merged the cells which are no longer required and you want them to be the separate cells.
To Split a merged cell, right click on the merged cell and click "split cell".
==== Providing headings to the data ====
==== Providing headings to the data ====
</gallery>
</gallery>
In a case, If you enter data for more than 50 cities, you will not be able to read the column headings. To be able to see the column (and row) headings, you should move your cursor to the cell above which (and to the left of which) you want to be able to see your headings and click on View --> Freeze Cells --> Freeze Rows and Columns.
In a case, If you enter data for more than 50 cities, you will not be able to read the column headings. To be able to see the column (and row) headings, you should move your cursor to the cell above which (and to the left of which) you want to be able to see your headings and click on "View --> Freeze Cells --> Freeze Rows and Columns".
{{clear}}
{{clear}}
{{Clear}}
{{Clear}}
<br>
<br>When the cursor is on this cell, the formula will be seen in the ‘formula’ bar on top of the sheet, below the menu.<br>All arithmetic operations, statistical operations are possible with spreadsheet. <br>You can ‘copy paste’ a formula from one cell to other cells in the same column, here ‘copy paste’ by default will copy paste the formula and not the content. This 'copy' and 'paste' of formula is not useful, when we want to 'fix' one value in our formula. For instance, if we are computing 'Percentage of total' in the example of Average annual rainfall (cms), then we will input in column C2 "=B2*100/B16". If we copy this cell C2 to C3, Calc will change the formula to "=B3*100/B17", since it will increment both numerator and denominator cells. However we want to fix the denominator to 'B16'. To 'fix' the reference, you should insert '$' before the cell reference. So you should give formula C2 "=B2*100/B$16" since we want to fix the value in the 16th row. When you copy paste the formula to C3, it will copy as "=B3*100/B$16", which is what you want.
When the cursor is on this cell, the formula will be seen in the ‘formula’ bar on top of the sheet, below the menu.<br>
All arithmetic operations, statistical operations are possible with spreadsheet. <br>
You can ‘copy paste’ a formula from one cell to other cells in the same column, here ‘copy paste’ by default will copy paste the formula and not the content. This 'copy' and 'paste' of formula is not useful, when we want to 'fix' one value in our formula. For instance, if we are computing 'Percentage of total' in the example of Average annual rainfall (cms), then we will input in column C2 "=B2*100/B16". If we copy this cell C2 to C3, Calc will change the formula to "=B3*100/B17", since it will increment both numerator and denominator cells. However we want to fix the denominator to 'B16'. To 'fix' the reference, you should insert '$' before the cell reference. So you should give formula C2 "=B2*100/B$16" since we want to fix the value in the 16th row. When you copy paste the formula to C3, it will copy as "=B3*100/B$16", which is what you want.
{{clear}}
{{clear}}
</gallery>
</gallery>
You can sort the data in anyway you want. You could sort it on the descending order of the rainfall (Column B) to see the data by the cities with the heaviest rainfall at the top. You can sort the data by cities (Column B).
You can sort the data in anyway you want. You could sort it on the descending order of the rainfall (Column B) to see the data by the cities with the heaviest rainfall at the top. You can sort the data by cities (Column B).
{{Note}} You should select the entire sheet ("Edit --> Select All", or simply CTRL-A) or keep the cursor on a single cell, else you may sort only the data selected which will be incorrect.
{{clear}}
{{clear}}
File:LO_Calc_8_Graph_of_rainfall_with_data.png|Inserting charts
File:LO_Calc_8_Graph_of_rainfall_with_data.png|Inserting charts
</gallery>
</gallery>
#Selecting data - Select the data (columns A and B) and select Insert → chart option. You will get the chart wizard. You can select Bar chart to get a Bar chart of the rainfall. You should experiment with different graphical formats to learn
#Select the data (columns A and B) and select "Insert --> chart" option. You will get the chart wizard. You can select Bar chart to get a Bar chart of the rainfall. You should experiment with different graphical formats to learn.
#Inserting charts - You can prepare charts and show it alongside the data
#Inserting charts - You can prepare charts and show it alongside the data
</gallery>
</gallery>
# Position the cursor within a range of cells containing values, row and column headings.
# Position the cursor within a range of cells containing values, row and column headings.
# Choose Insert --> Pivot Table --> Create. The Select Source dialog appears. Choose Current selection and confirm with OK. The table headings are shown as
# Choose "Insert --> Pivot Table --> Create". The Select Source dialog appears. Choose Current selection and confirm with OK. The table headings are shown as
# The table headings are shown as buttons in the Pivot Table dialog. Drag these buttons as required and drop them into the layout areas "Page Fields", "Column Fields", "Row Fields" and "Data Fields". <br>Drag the desired buttons into one of the four areas.<br><br>
# The table headings are shown as buttons in the Pivot Table dialog. Drag these buttons as required and drop them into the layout areas "Page Fields", "Column Fields", "Row Fields" and "Data Fields". <br>Drag the desired buttons into one of the four areas.<br><br>
<gallery mode="packed" heights="250">
<gallery mode="packed" heights="250">
File:3. LO Calc Data Pivot 3. Current Selection.png|Selection wizard
File:3. LO Calc Data Pivot 3. Current Selection.png|Selection wizard
File:4. LO Calc Data Pivot 4. Pivot form.png|Pivot Table wizard </gallery>
File:4. LO Calc Data Pivot 4. Pivot form.png|Pivot Table wizard </gallery>
Drag a button to the '''Page Fields''' area to create a button and a listbox on top of the generated pivot table. The listbox can be used to filter the pivot table by the contents of the selected item. You can use drag-and-drop within the generated pivot table to use another page field as a filter. <br>
Drag a button to the "Page Fields" area to create a button and a listbox on top of the generated pivot table. The listbox can be used to filter the pivot table by the contents of the selected item. You can use drag-and-drop within the generated pivot table to use another page field as a filter. <br>
<gallery mode="packed" heights="200">
<gallery mode="packed" heights="200">
File:Insert data, row and column fields.png|Insert data, row and column fields
File:Insert data, row and column fields.png|Insert data, row and column fields
{{Clear}}
{{Clear}}
If the button is dropped in the '''Data Fields''' area it will be given a caption that also shows the formula that will be used to calculate the data. <br>
If the button is dropped in the "Data Fields" area it will be given a caption that also shows the formula that will be used to calculate the data. <br>
* By double-clicking on one of the fields in the '''Data Fields''' area you can call up the '''Data Field''' dialog.
* By double-clicking on one of the fields in the "Data Fields" area you can call up the '''"'''DataField" dialog.
* Use the '''Data Field''' dialog to select the calculations to be used for the data. To make a multiple selection, press the Ctrl key while clicking the desired calculation.
* Use the "Data Field" dialog to select the calculations to be used for the data. To make a multiple selection, press the Ctrl key while clicking the desired calculation.
{{Clear}}
{{Clear}}
</gallery>
</gallery>
{{clear}}
{{clear}}
Before printing any document we must make sure about the overall preview of the page. So get that we can click on File--> Print Preview. This gives the user the broader idea of how the printed material look like.
Before printing any document we must make sure about the overall preview of the page. So get that we can click on "File--> Print Preview". This gives the user the broader idea of how the printed material look like.
<gallery mode="packed" heights="250">
<gallery mode="packed" heights="250">
File:Margins in Print Preview.png|Click on Margin
File:Margins in Print Preview.png|Click on Margin
</gallery>
</gallery>
* Click on margin icon for the flexible movement of margins in the spreadsheet.
* Click on margin icon for the flexible movement of margins in the spreadsheet.
* Click on Format Page option to get various options such as '''Page settings''' to get the orientation of the page, '''Page borders''' to choose the style of the border, Inserting Header, Footer and other settings can be done.
* Click on "Format Page" option to get various options such as "Page settings" to get the orientation of the page, Page borders to choose the style of the border, Inserting Header, Footer and other settings can be done.
Before printing the document go through these tips:
Before printing the document go through these tips:
# Use the ‘text wrap’ feature to wrap all the text input in a cell so that it won’t overflow to the next cell.
# Use the "text wrap" feature to wrap all the text input in a cell so that it won’t overflow to the next cell.
# Increase or reduce the column width so that all columns you want to print are included.
# Increase or reduce the column width so that all columns you want to print are included.
# Delete (or hide) columns if you don’t want them in the printout.
# Delete (or hide) columns if you don’t want them in the printout.
# Use Format → Page option to insert header / footer information, such as file name, page number etc. <br>Use print preview feature to keep checking if the formatting is satisfactory <br>{{clear}}
# Use "Format --> Page" option to insert header / footer information, such as file name, page number etc. <br>Use "Print preview" feature to keep checking if the formatting is satisfactory <br>{{clear}}
==== Saving the files and formats ====
==== Saving the files and formats ====
