LibreOffice Calc basics

1. A spreadsheet is a software application used for computations and data processing eg LibreOffice Calc, OpenOffice Calc
2. 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
4. Basic 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,
   6. Basic column width, row height etc
   7. Freeze panes (column headings) WINDOW - FREEZE
5. 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
6. 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
7. 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
8. 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
9. 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
10. 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
11. 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)