# LibreOffice Calc basics

From Karnataka Open Educational Resources

# Introduction

- 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
- A spreadsheet is made of rows and columns which are referred to by numbers and alphabets
- 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

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

# Format features

- Basic column width, row height etc
- Freeze panes (column headings) WINDOW - FREEZE

- Important commands – SORT
- Sorting – is used to sort the data by a particular column or set of columns (DATA-SORT)
- If column headings are not seen, use TAB <Options> and tick <Range contains column labels>
- Use Edit- select All (or control A) to select sheet before DATA-SORT
- e.g. sort DISE data by DISTRICTNAME-BLOCKNAME-CLUSTERCD or RURAL-GIRLSTOILET or sort School Admission Register data by Sex, Mother tongue

- FILTER
- Used to filter some rows based on selection
- Use Edit- select All (or control A) to select sheet before DATA-SORT
- Do DATA-FILTER-AUTO FILTER to create a filter (you can see Arrow sign on each column)
- Click on the arrow on any column to select filter
- Filter School Admission Register data on APL/BPL

- FORMULA
- Addition, (total boys, total girls in HPS =AE2+AG2+AI2+AK2)
- Copy formula across rows by double clicking bottom right part when you get + sign
- Sum of TOTENR for the cluster (use sigmal sign, or formula =sum(AK2:AK12)
- Division ( Teacher pupil ration = Total children / Teachers posted 'P' =AL2/Jj2)
- Use formula for other cases – subtraction, multiplication, division, percentages
- Use formula to connect across work sheets and across work books
- If then conditional formula (calculate student grades from marks, download student grade computation.ods.
- Can use statistical functions – average, product, Frequency, Countif etc

- Other features
- List of values (validation for data entry)
- Algebra – plotting linear equations in single and two variables
- Creating graphs from tabular data
- Copy paste from ODS to ODT (table)
- Paste special – formatted text, transpose cells to convert rows to columns and vice versa

- Formatting spreadsheets
- Print rage definition for spreadsheets to repeat row headings in printouts
- Text alignment (word wrap) to control column width
- Header and Footer information

- Data Pilot
- Open the spreadsheet Yadgir Primary School DISE 2010 Information for Decision Support.ods
- Select the data – through Control Home (to go to top), then Control Shift Down Arrow and Control Shift Right Arrow
- Data - Data Pilot - Start - Current Selection
- You will see the Data Pilot Form. It has four boxes -
- Move BLKNAME to Row Fields
- Move SCHME_DESC to Column Fields
- Move SCHNAME to Data field
- Double click on SCHNAME in the Data field and select Function COUNT (can also do this through Options button)
- Move LIB_RARY to Page Fields
- 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

- Practice using spreadsheet
- Use the spreadsheet to create data pilot
- 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
- Calculate the number (count) of schools which are in rural areas and which do not have girls toilet facility
- Number of schools with private management
- Number of children (sum of girls and boys)