LibreOffice Calc basics

Introduction

1. A spreadsheet is a software application used for computations and data processing eg LibreOffice Calc, OpenOffice Calc. It is a very powerful and versatile software which is a 'number editor' like LibreOffice Writer is a 'text editor'. In addition spreadsheet can also do many text related functions also
2. A spreadsheet is made of rows and columns which are referred to by numbers and alphabets
3. A cell is a combination of a row and a column which is referred to by alphabet+number eg A1, N100. You can visualise a spreadsheet as a table, with rows and columnns. Data can be entered into a spreadsheet in this tabular manner, with each record entered in one row and each element entered in one column for each row/record. Thus student register can be entered in a spreadsheet, with one student record per row and all data elements relating to the student, entered in different columns, such as name, class, mother tongue, sex, religion etc

Basic features

1. Edit features
   1. Insert row/column (INSERT- ROW, INSERT -COLUMN)
   2. Delete row/column (EDIT- DELETE CELLS-DELETE ROW/COLUMN)
   3. Hide row/column (FORMAT -ROW/COLUMN -HIDE)
   4. Show row/column (FORMAT -ROW/COLUMN -SHOW)
   5. Others - multiple sheets (One_cluster, one_block, Mix), workbook, naming book and sheet,

Format features

1. 1. Basic column width, row height etc
   2. Freeze panes (column headings) WINDOW - FREEZE
2. Important commands – SORT
   1. Sorting – is used to sort the data by a particular column or set of columns (DATA-SORT)
   2. If column headings are not seen, use TAB <Options> and tick <Range contains column labels>
   3. Use Edit- select All (or control A) to select sheet before DATA-SORT
   4. e.g. sort DISE data by DISTRICTNAME-BLOCKNAME-CLUSTERCD or RURAL-GIRLSTOILET or sort School Admission Register data by Sex, Mother tongue
3. FILTER
   1. Used to filter some rows based on selection
   2. Use Edit- select All (or control A) to select sheet before DATA-SORT
   3. Do DATA-FILTER-AUTO FILTER to create a filter (you can see Arrow sign on each column)
   4. Click on the arrow on any column to select filter
   5. Filter School Admission Register data on APL/BPL
4. FORMULA
   1. Addition, (total boys, total girls in HPS =AE2+AG2+AI2+AK2)
   2. Copy formula across rows by double clicking bottom right part when you get + sign
   3. Sum of TOTENR for the cluster (use sigmal sign, or formula =sum(AK2:AK12)
   4. Division ( Teacher pupil ration = Total children / Teachers posted 'P' =AL2/Jj2)
   5. Use formula for other cases – subtraction, multiplication, division, percentages
   6. Use formula to connect across work sheets and across work books
   7. If then conditional formula (calculate student grades from marks, download student grade computation.ods.
   8. Can use statistical functions – average, product, Frequency, Countif etc
5. Other features
   1. List of values (validation for data entry)
   2. Algebra – plotting linear equations in single and two variables
   3. Creating graphs from tabular data
   4. Copy paste from ODS to ODT (table)
   5. Paste special – formatted text, transpose cells to convert rows to columns and vice versa
6. Formatting spreadsheets
   1. Print rage definition for spreadsheets to repeat row headings in printouts
   2. Text alignment (word wrap) to control column width
   3. Header and Footer information
7. Data Pilot
   1. Open the spreadsheet Yadgir Primary School DISE 2010 Information for Decision Support.ods
   2. Select the data – through Control Home (to go to top), then Control Shift Down Arrow and Control Shift Right Arrow
   3. Data - Data Pilot - Start - Current Selection
   4. You will see the Data Pilot Form. It has four boxes -
   5. Move BLKNAME to Row Fields
   6. Move SCHME_DESC to Column Fields
   7. Move SCHNAME to Data field
   8. Double click on SCHNAME in the Data field and select Function COUNT (can also do this through Options button)
   9. Move LIB_RARY to Page Fields
   10. Click on OK. The Data Pilot is the most advanced feature in spreadsheet software application. It can be used to analyze data in multiple ways
8. Practice using spreadsheet
   1. Use the spreadsheet to create data pilot
   2. Insert a column “Total Boys” and use formula to add the number of girls class 1,2,3,4,5,6,7,8. Hide the columns containing number of boys so that the ##Total girls is seen after the columns with number of girls
   3. Calculate the number (count) of schools which are in rural areas and which do not have girls toilet facility
   4. Number of schools with private management
   5. Number of children (sum of girls and boys)