Learn LibreOffice Calc

From Open Educational Resources
Jump to navigation Jump to search

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.

Educational application and relevance

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. In this manual, we will:-

  1. Maintain rainfall information for a period for different regions, using a spreadsheet
  2. Analysing data, through computations
  3. Creating reports based on the information analysed (rainfall pattern for different regions)
  4. Generating graphs for pictorial representation of data (quantum of rainfall over different periods in different regions)

Version

"Fresh" version:
5.3.0 (1 February 2017)
"Still" version:
5.2.5 (26 January 2017)

(LibreOffice Calc is also available on the Windows and Macintosh operating systems)

Configuration

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

Working with the application

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’.

LO Calc 1 viewing a spreadsheet.png Cal step 2.png
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

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

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.

Columns and rows can be inserted or deleted or hidden in a spreadsheet.

Sheets can be added or deleted from a work book

LO Calc 6 Formatting numbers.png LO Calc 2 Freeze rows and columns.png
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 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
LO Calc 3 Processing the data Summing up rainfall information.png LO Calc 4 Sorting data.png
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.

When the cursor is on this cell, the formula will be seen in the ‘formula’ bar on top of the sheet, below the menu. 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.

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.

LO Calc 7 Inserting a chart.png LO Calc 8 Graph of rainfall with data.png
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. Information in text and graphic forms
LO Calc 9 Adding Page Header before printing.png 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.

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)

File formats for creation

Saving the file

Like in most applications, a file can be saved using the FILE – SAVE AS 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

Export and publishing files

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

Installation

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

Ideas for resource creation

You can create information for analysing in any subject. Students can collect data on different topics such as

  1. information about family members
  2. houses in the area
  3. crops / vegetation in the area
  4. goods sold and prices in local shop
  5. programs of the local government and budgets etc.

the collected information can be analysed and published in tabular cum graph formats.

References

  1. Wikipedia
  2. User manual for LO Calc is available on https://help.libreoffice.org/scalc/.uno:HelpIndex?Language=en-US&System=UNIX&Version=5.2
  3. Community support is available on https://www.libreoffice.org/get-help/community-support
  4. 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