Line 3: |
Line 3: |
| [https://teacher-network.in/OER/hi/index.php/%E0%A4%B2%E0%A4%BF%E0%A4%AC%E0%A4%B0%E0%A5%87%E0%A4%91%E0%A4%AB%E0%A4%BF%E0%A4%B8_%E0%A4%95%E0%A5%88%E0%A4%B2%E0%A5%8D%E0%A4%95_%E0%A4%B8%E0%A5%80%E0%A4%96%E0%A4%BF%E0%A4%8F हिन्दी में देखिये] | | [https://teacher-network.in/OER/hi/index.php/%E0%A4%B2%E0%A4%BF%E0%A4%AC%E0%A4%B0%E0%A5%87%E0%A4%91%E0%A4%AB%E0%A4%BF%E0%A4%B8_%E0%A4%95%E0%A5%88%E0%A4%B2%E0%A5%8D%E0%A4%95_%E0%A4%B8%E0%A5%80%E0%A4%96%E0%A4%BF%E0%A4%8F हिन्दी में देखिये] |
| {{Template:Book-sidebar}} | | {{Template:Book-sidebar}} |
− | === Introduction ===
| + | == Introduction == |
− | Earlier accountants used large pieces of paper to record business finances, to enter information like costs, payments, taxes, income etc. so that they got a complete financial overview. These large paper pieces were called “spreadsheets”. The digital spreadsheet is similar, with columns and rows. A cell is a combination or intersection of a row and a column, in which information both text and numeric can be input. A sheet can be simply understood as an array of cells. Altogether Spreadsheets are useful for capturing and analyzing data.
| + | The spreadsheet is a sheet with a collection of columns and rows. It contains number of cells. A cell is a combination or intersection of a row and a column, in which information both text and numeric can be input. A sheet can be simply understood as an array of cells. Altogether Spreadsheets are useful for capturing and analyzing data. |
| | | |
− | ==== Basic information ====
| + | === Basic information === |
| {| class="wikitable" | | {| class="wikitable" |
| |ICT Competency | | |ICT Competency |
Line 26: |
Line 26: |
| |The application on mobiles and tablets | | |The application on mobiles and tablets |
| |[https://play.google.com/store/apps/details?id=com.onlyoffice.documents&hl=en_IN&gl=US OnlyOffice for Android] | | |[https://play.google.com/store/apps/details?id=com.onlyoffice.documents&hl=en_IN&gl=US OnlyOffice for Android] |
| + | [https://play.google.com/store/apps/details?id=com.collabora.libreoffice Collabora Office for Android] and [https://www.libreoffice.org/download/android-and-ios/ for iOS] |
| |- | | |- |
| |Development and community help | | |Development and community help |
Line 32: |
Line 33: |
| |} | | |} |
| | | |
− | ==== Overview of features ====
| + | === Overview of features === |
| Spreadsheet application is used for recording, processing and analyzing data. It is also used for creating text and graphical outputs. The application has many arithmetical, statistical, text processing functions which make 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, processing and analyzing data. It is also used for creating text and graphical outputs. The application has many arithmetical, statistical, text processing functions which make it a very powerful desktop tool. Data can be sorted, filtered and processed into outputs, including multi-variate tables. |
| | | |
− | ====Installation==== | + | ==== Installation ==== |
− | =====For Ubuntu=====
| + | Please click on the below link to learn about LibreOffice Installation & Configuration |
− | #The application is part of the Ubuntu custom distribution.
| |
− | #In case you do not find it on your computer, you can install by choosing “LibreOffice” in Ubuntu Software Centre
| |
− | #If you would like to install through the terminal follow these steps below:
| |
− | ##Open terminal by clicking (Ctrl+Alt+T),
| |
− | ##Once Window page is open, in front of dollar($) symbol just type below command.
| |
− | ##sudo apt-get install libreoffice
| |
| | | |
− | ===== For Windows =====
| + | [https://teacher-network.in/OER/index.php/LibreOffice_Installation_and_Configuration Click here] |
− | # Download "''LibreOffice windows installer''" from below
| |
− | ##[https://www.libreoffice.org/download/libreoffice-fresh/?type=win-x86&lang=pick Click here for the 32-bit version]
| |
− | ## [https://www.libreoffice.org/download/libreoffice-fresh/?type=win-x86_64&lang=pick Click here for the 64-bit version]
| |
− | # If you are unsure which Windows version you have, press Win+Pause to open your system properties window. The LibreOffice Main Installer download page can be selected with a built-in help file for your language:
| |
− | # Double-click on download "''LibreOffice_..._Win_x64.msi''" file to begin installation
| |
− | # 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 asks 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">
| |
− | File:Step1 LO Installation.png|Downloading LibreOffice
| |
− | File:Step2 LO Installation.png|Installation wizard
| |
− | </gallery>
| |
− |
| |
− | Another dialog box opens, giving you a choice whether you want a default installation, or whether you want to choose special locations and components. If you want a default installation, click “Next”. <gallery mode="packed" heights="400px">
| |
− | File:Step3 LO Installation.png|Setup type
| |
− | File:Step6 LO Installation.png|File type selection
| |
− | </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 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.
| |
− | * To load LibreOffice during system start-up.
| |
− |
| |
− | <gallery mode="packed" heights="400px">
| |
− | File:Step7 LO Installation.png|Ready to install the program
| |
− | File:Step9 LO Installation.png|Finish
| |
− | </gallery>
| |
− |
| |
− |
| |
− |
| |
− | If you are not able to install LibreOffice in your windows machine''''',''''' [https://www.libreoffice.org/get-help/install-howto/windows/ follow this official website installation instruction] or [https://youtu.be/ne-8PMLCj_k Click here] to see the LibreOffice Installation- English Tutorial
| |
− |
| |
− | =====For MAC OS=====
| |
− | #Click [https://www.fosshub.com/LibreOffice.html this link] and click on "''LibreOffice MAC OS"'' for MAC laptops.
| |
− | #It will start downloading "''LibreOffice_..._MacOS_x86-64.dmg"'' into your computer (by default browser will save the files in "''Downloads"'' folder in your system
| |
− | #Double-click on download "''LibreOffice_..._MacOS_x86-64.dmg"'' file to begin installation
| |
− | #An installation window appears: drag and drop the LibreOffice icon onto the Applications icon in the same window. You may be required to enter your administrator password.
| |
− | #The files are copied over and a progress bar shows when the installation is complete.
| |
− | If you are not able to install LibreOffice in your MAC machine, [https://www.libreoffice.org/get-help/install-howto/macos/ follow this official website installation instructions]
| |
| | | |
| ===Working with the application=== | | ===Working with the application=== |
Line 94: |
Line 50: |
| 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 the window like as mentioned in the image above (Basic spreadsheet interface). 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. |
| + | * 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. |
| + | * 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}} |
Line 102: |
Line 62: |
| 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" Key, 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" Key, 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. |
| + | * It is useful to become comfortable using keyboard to move across the spreadsheet. |
| + | * 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 ==== |
Line 145: |
Line 107: |
| You can do almost any kind of computation or processing with a spreadsheet. | | You can do almost any kind of computation or processing with a spreadsheet. |
| | | |
− | For example, here we will calculate the total rainfall for the cities. You can go to the cell below last row with data in Column B, which has the rainfall information and simply type in "=Sum(B2:B15)" assuming the data is in rows 2 through 15 of column B. Any computation or formula must begin with the “=” sign. Or you can simply use the shorthand icon on the menu bar “∑” where you will get various options like SUM, AVERAGE, MIN, MAX. Choose SUM here and hit "Enter" and the application will insert the same formula. | + | For example, here you can calculate the total rainfall for the cities. You can go to the cell below last row with data in Column B, which has the rainfall information and simply type in "''=Sum(B2:B15)''", assuming the data is in rows 2 through 15 of column B. Any computation or formula must begin with an "=" sign. Or you can simply use the shorthand icon on the menu bar “∑” where you will get various options like SUM, AVERAGE, MIN, MAX. Choose "SUM" here and hit "Enter" and the application will insert the same formula. |
| | | |
| {{Clear}} | | {{Clear}} |
| | | |
− | <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. |
| + | * 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 you want to 'fix' one value in our formula. |
| + | * For instance, if you are computing 'Percentage of total' in the example of Average annual rainfall (cms) then, You can input in column ''C2 "=B2*100/B16"''. If you 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 consider you want to fix the denominator to '''B16''<nowiki/>'. To 'fix' the reference, you should insert '$' before the cell reference. So you should give formula ''C2 "=B2*100/B$16''" since you 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}} |
Line 196: |
Line 161: |
| | | |
| 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 '''"'''DataField" dialog. | + | * By double-clicking on one of the fields in the "Data Fields" area you can call up the '''"'''Data Field" 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}} |
| | | |
− | ==== Printing a spreadsheet ==== | + | ==== Print Preview ==== |
− | <gallery mode="packed" heights="320">
| + | Before printing any document you must make sure about the overall preview of the page. So get that you can click on "File--> Print Preview". This gives user the broader idea of how the printed material looks like. |
− | File:LO_Calc_9_Adding_Page_Header_before_printing.png|Adding Page Header before printing
| + | [[File:LO Calc Margin.png|thumb|Setting the margin ]] |
− | </gallery>
| |
− | {{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. | |
− | <gallery mode="packed" heights="250">
| |
− | File:Margins in Print Preview.png|Click on Margin | |
− | File:Margin Movement.png|The Margin can be moved by dragging
| |
− | </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. |
| + | In the image dotted lines represent the margins. They can be adjusted according to the printing needs. |
| + | |
| 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> |
| + | |
| + | ==== Adding Page Header or Footer ==== |
| + | <gallery mode="packed" heights="300"> |
| + | File:LO Calc Header Footer.png|Header/Footer option in Print Preview |
| + | File:LO Calc Header Footer2.png|Adding Header/Footer |
| + | </gallery>Header or Footers make the document a neat presentation. Text added to the top of the margin is a Header. Text added to the bottom of the margin is a Footer. Header can include the name of the Book, Chapter name etc, whereas Footer may indicate the page number. In the "Print Preview" option, there will be a "Header and Footer" option as shown in the above image (Header/Footer option in Print Preview). Once clicked it gives you three areas where you can insert the Header, that is Left area, Center area and Right area. You can add the text at any area to apply it to all the pages. The same is applicable for Foote |
| + | |
| + | {{clear}} |
| | | |
| ==== Saving the files and formats ==== | | ==== Saving the files and formats ==== |
− | # Like in most applications, a file can be saved using the File –-> Save command, or by the shortcut key Ctrl+S. Always give a meaningful file name, reading which you should get an idea of the file contents. We can name this file “''Rainfall information for cities in South India, October 2016.ods''”. Often adding the month-year information when the file was created can be useful later. | + | # Like in most applications, a file can be saved using the File –-> Save command, or by the shortcut key Ctrl+S. Always give a meaningful file name, reading which you should get an idea of the file contents. This file has been named “''Rainfall information for cities in South India, October 2016.ods''”. Often adding the month-year information when the file was created can be useful later. |
| # The files will be saved as ".ods". You can save a spreadsheet as a Microsoft Excel format also ("File --> Save As"). | | # The files will be saved as ".ods". You can save a spreadsheet as a Microsoft Excel format also ("File --> Save As"). |
| # Main difference between "Save" and "Save As" is that Save Saves the document whereas Save As saves the document with another name. | | # Main difference between "Save" and "Save As" is that Save Saves the document whereas Save As saves the document with another name. |
− | # The files can be exported to a PDF format. This is useful when you only need to print the file and do not want any changes to it.
| + | The file you worked with can be exported to a PDF format. This is useful when you only need to print the file and do not want any changes to it. All the settings need to be done in the LibreOffice Calc and can be converted to PDF format. |
| + | <gallery mode="packed" heights=300> |
| + | File:LO Calc Export to PDF.png|Exporting the file to PDF |
| + | </gallery> |
| + | To do this, "File --> Export as PDF". A window appears which lets you to change the default settings. You can do the required changes and click "Export". In the next window it asks you to give the path where to export the file. |
| | | |
| ==== Advanced features ==== | | ==== Advanced features ==== |
| * Calc is a very sophisticated software application and can even be used as a database, by cross referring data across sheets and books. | | * Calc is a very sophisticated software application and can even be used as a database, by cross referring data across sheets and books. |
| * You can also generate multi-variate tables using the 'Pivot' feature. | | * You can also generate multi-variate tables using the 'Pivot' feature. |
− | * The 'functions' in Calc are many and can meet statistical, mathematical functions which can be useful for exploring topics in Mathematics and Statistics. | + | * The 'functions' in Calc are many and can meet statistical, logical, textual, mathematical functions which can be useful for exploring topics in Mathematics and Statistics. |
− | * You can use Calc to store contact information, which can be used along with the Thunderbird email client, to generate customised mass email messages (this functionality is called 'mail merge'). | + | * You can use Calc to store contact information, which can be used along with the Thunderbird email client, to generate customized mass email messages (this functionality is called 'mail merge'). |
| + | * User defined formulae can be created and added to make the application more relatable. |
| * You can repeat the column or row headings automatically across printed pages (hint – define print area). You can refer the references section for learning these advanced features. | | * You can repeat the column or row headings automatically across printed pages (hint – define print area). You can refer the references section for learning these advanced features. |
| + | * Vlookup is an interesting feature with which you can query for a particular information using a simple syntax. |
| | | |
| ===Ideas for resource creation=== | | ===Ideas for resource creation=== |