1,940
edits
Changes
Jump to navigation
Jump to search
mLine 1:
Line 1: − =Introduction= − Earlier accountants used large piece of paper for business finances, which they called “spreadsheet”, to enter information like costs, payments, taxes, income etc. to get a complete financial overview. 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 understood as an array of cells. − ==ICT Competency== − Spreadsheet can be used to create analyses of data, and publish the analyses through tables and graphs. − + − + − In this manual, we will:- − #Maintain rainfall information for a period for different regions, using a spreadsheet − #Analysing data, through computations − #Creating reports based on the information analysed (rainfall pattern for different regions) − #Generating graphs for pictorial representation of data (quantum of rainfall over different periods in different regions) − ==Version== − "Fresh" version:<br> − 5.3.0 (1 February 2017)<br> − "Still" version:<br> − 5.2.5 (26 January 2017)<br> − (LibreOffice Calc is also available on the Windows and Macintosh operating systems)+ − − − This tool has no specific configuration requirements. It is available as a part of Ubuntu custom distribution. − − ==Overview of Features== − Spreadsheet application is used for recording data, processing data, analysing data, creating text and graphical outputs. The application has a 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. − ==Other similar applications== − Other spreadsheet software applications include Microsoft Excel, OpenOffice Calc, Google spreadsheet etc. − ==Development and community help== − Developer(s) -The Document Foundation<br> − [https://www.libreoffice.org/get-help/community-support/ Community help] − − − ==Functionalities== − − Overview of the LibreOffice Calc Tool.LO Calc is part of the Ubuntu distribution. This can be opened from Applications → Office → LibreOffice Calc. This opens a ‘work book’. A work book can have many ‘sheets’. + + − + − |style="width: 50%;|[[File:cal step 2.png|450px]]+ + − + − 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''' − − Columns and rows can be inserted or deleted or hidden in a spreadsheet. <br> − − Sheets can be added or deleted from a work book − + − + − + − |style="width: 50%;|Step 4-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 + − + − |style="width: 50%;|[[File:LO_Calc_4_Sorting_data.png|450px]] + − + − + − All arithmetic operations, statistical operations are possible with spreadsheet. + − + − |style="width: 50%;|Step 6-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).+ − remember that 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.+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + − |-+ − |style="width: 50%;|[[File:LO_Calc_7_Inserting_a_chart.png|450px]]+ − |style="width: 50%;|[[File:LO_Calc_8_Graph_of_rainfall_with_data.png|450px]]+ − |-+ − |style="width: 50%;|Step 7 -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. − |style="width: 50%;|Information in text and graphic forms − |-+ − |style="width: 50%;|[[File:LO_Calc_9_Adding_Page_Header_before_printing.png|450px]] + − |style="width: 50%;|Step 8-Formating a sheet for printing is not an easy task. To make it simpler, some tips are:+ − *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 − *Delete (or hide) columns if you dont 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 formating is satisfactory − Advanced feature – you can repeat the column headings across pages printed (hint – define print area) − |} − + + + + + + − + − 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 exported to a PDF format. This is useful when you only need to print the file and do not want any changes to it. + − ==Advanced features==+ + + + + + + + + − + − {| class="wikitable"+ − + − ! Method of installation !! Steps+ − + − | From Ubuntu software Centre || Search Libreoffice+ − |-+ − | From Terminal || sudo apt-get install libreoffice+ − |-+ − | From the web || Go to libreoffice.org and download files to install − |- − | Web based registration ||Not Applicable − |} − =The application on mobiles and tablets= − You can view and edit spreadsheets using the LibreOffice app on Android for mobiles and tablets − + − You can create information for analysing in any subject. Students can collect data on different topics such as+ + + − Information about family members+ − Houses in the area+ − Crops / vegetation in the area − Goods sold and prices in local shop − Programs of the local government and budgets etc. − + + + − + − + − + − +
→Inserting formulae for computations
{{Template:Book-sidebar}}
{{Template:Book-sidebar}}
==Educational application and relevance==
=== Introduction ===
The power of the digital spreadsheet is that many types of data processing can be done on the information entered. Algebraic functions are possible for the mathematics teacher, while statistical analyses can be done in any subject.
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.
==== 1.1 Basic information ====
==Configuration==
=Working with the application=
{| class="wikitable"
{| class="wikitable"
|ICT Competency
|LibreOffice Calc is a free and open source application for creating generic resources. Spreadsheet is used for handling numeric data, analysing and publishing through tables and graphs.
|-
|-
|style="width: 50%;|[[File:LO Calc 1 viewing a spreadsheet.png|450px]]
|Educational application and relevance
|Data analysis is an important mathematical competency. Spreadsheets can be very effective for introducing data analysis and statistics for students. It can also be used to explore patterns as an introduction to algebraic thinking. The power of the digital spreadsheet is that many types of data processing can be done on the information entered.
|-
|-
|style="width: 50%;|Step 1 -When we open the LO Calc application, it will show the window like this. The spreadsheet consists of rows and columns. Each intersection is a cell
|Version
|Version: 5.1.6.2<br>(LibreOffice Calc is also available on the Windows and Macintosh operating systems)
|style="width: 50%;|Step 2 -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 (cell A1), CTRL-End (bottom right most part of 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.<br>
|-
|-
|style="width: 50%;|[[File:LO Calc 6 Formatting numbers.png|450px]]
|Configuration
|style="width: 50%;|[[File:LO Calc 2 Freeze rows and columns.png|450px]]
|No specific configuration requirements
|-
|-
|style="width: 50%;|Step 3 -Most of the formating options available in LibreOffice Writer are available in Calc also. Making the text bold, italicised, increasing/reducing font size, font color etc are all available. Data can also be formated based on its nature – numeric information can be assigned “,” separators
|Other similar applications
|Other spreadsheet software applications include [https://products.office.com/en-us/excel Microsoft Excel], [https://www.openoffice.org/download/ OpenOffice Calc] (not being developed any more), [https://www.google.co.in/sheets/about/ Google spreadsheet] etc.
|-
|-
|style="width: 50%;|[[File:LO Calc 3 Processing the data Summing up rainfall information.png|450px]]
|The application on mobiles and tablets
|You can view and edit spreadsheets using the LibreOffice app on Android for mobiles and tablets
|-
|-
|style="width: 50%;|Step 5 -You can do almost any kind of computation or processing with a spreadsheet. 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 “∑” and hit enter and the application will insert the same formula.
|Development and community help
When the cursor is on this cell, the formula will be seen in the ‘formula’ bar on top of the sheet, below the menu.
|Developer(s) -The Document Foundation
[https://www.libreoffice.org/get-help/community-support/ Community help]
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.
|}
==== 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.
====Installation====
#The application is part of the Ubuntu custom distribution.
#In case you do not find it on your computer, you can install by typing <code>LibreOffice</code> on top search bar in Software Centre.
#If you would like to install through the terminal follow these steps below:
##Open terminal by clicking Applications->System Tools->Terminal or through Keyboard shortcut <code>Ctrl+Alt+T</code>
##In the terminal window, type below command and press enter to start the installation by providing your machine password:
##<code>sudo apt-get install LibreOffice</code>
===Working with the application===
====Opening a spreadsheet====
[[File:LO Calc 1 viewing a spreadsheet.png|500px|left]]
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.
{{clear}}
==== Navigating a spreadsheet ====
[[File:Cal step 2.png|500px|left]]
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>
Sheets can be added or deleted from a work book using the "+" symbol seen next to the sheet name. Sheets can be named and renamed by right clicking on the name of the sheet visible in the bottom panel.
{{clear}}
==== Formatting a spreadsheet ====
[[File:LO Calc 6 Formatting numbers.png|500px|left]]
Most of the formatting options available in LibreOffice Writer are available in Calc also. Making the text bold, italicised, increasing/reducing font size, font color etc are all available. Data can also be formatted based on what it represents - date, time, currency, etc. The numeric information display format in terms of "000" separators or number of decimal places can be configured.
{{clear}}
==== Providing headings to the data ====
[[File:LO Calc 2 Freeze rows and columns.png|500px|left]]
We saw earlier how to enter headings for columns. However, 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}}
==File formats for creation==
==== Inserting formulae for computations====
[[File:LO Calc 3 Processing the data Summing up rainfall information.png|500px|left]]
You can do almost any kind of computation or processing with a spreadsheet. 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 “∑” and hit enter and the application will insert the same formula.<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.
==Saving the file==
{{clear}}
==== Sorting the data ====
==Export and publishing files==
[[File:LO_Calc_4_Sorting_data.png|500px|left]]
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).
remember that 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}}
==== Preparing charts and graphs====
<gallery mode="packed" heights="250px" caption="Preparing charts and graphs">
File:LO_Calc_7_Inserting_a_chart.png|Selecting data for inserting charts
File:LO_Calc_8_Graph_of_rainfall_with_data.png|Inserting charts
</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
#Inserting charts - You can prepare charts and show it alongside the data
=Installation=
==== Printing a spreadsheet ====
[[File:LO_Calc_9_Adding_Page_Header_before_printing.png|500px|left]]
|-
Formatting a sheet for printing is not an easy task. To make it simpler, some tips are:
# 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.
# 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}}
=Ideas for resource creation=
==== 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.
# The files will be saved as ".ods". You can save a spreadsheet as a Microsoft Excel format also (FILE -> SAVE AS)
# 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.
==== 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. 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. 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 repeat the column or row headings automatically across printed pages (hint – define print area). You can refer to the [https://wiki.documentfoundation.org/images/4/47/CG41-CalcGuideLO.pdf Calc user manual] for learning these advanced features
the collected information can be analysed and published in tabular cum graph formats.
===Ideas for resource creation===
# Calc can be used for analysing data sets generated by your students as part of projects. Large secondary data sets can also be analysed. Some of these project ideas include information about family members, houses in the area, crops / vegetation in the area, goods sold and prices in local shop and programs of the local government and budgets etc. The collected information can be analysed and published in tabular cum graph formats.
# Spreadsheets are also useful for helping solving numeric puzzles by setting up formulas and extrapolating.
=References=
===References===
#[https://en.wikipedia.org/wiki/LibreOffice_Calc Wikipedia]
#[https://en.wikipedia.org/wiki/LibreOffice_Calc Wikipedia]
#User manual for LO Calc is available on https://help.libreoffice.org/scalc/.uno:HelpIndex?Language=en-US&System=UNIX&Version=5.2
#[https://help.libreoffice.org/scalc/.uno:HelpIndex?Language=en-US&System=UNIX&Version=5.2 User manual for LO Calc]
#Community support is available on https://www.libreoffice.org/get-help/community-support
#[https://www.libreoffice.org/get-help/community-support Community support]
#Videos to learn LO Calc are available in English, Telugu and other Indian languages on the spoken-tutorials site, created by the NMEICT program of MHRD. Telugu vidoes for LO Calc are on http://spoken-tutorial.org/tutorial-search/?search_foss=LibreOffice+Suite+Calc&search_language=Telugu
#Videos to learn LO Calc are available in English, Telugu and other Indian languages on the [http://spoken-tutorial.org/tutorial-search/?search_foss=LibreOffice+Suite+Calc&search_language=Telugu Spoken-tutorials] site, created by the NMEICT program of MHRD.
[[Category:Explore an application]]
[[Category:Explore an application]]