SOFTWARE FOR BUSINESS AND

RESEARCH

Module - 5
 LIBREOFFICE CALC

 Spreadsheet
 Features
 user interface
 Formatting text
 Formatting worksheet
 Adding and renaming worksheets
 Entering formulae
 Previewing and printing
 LIBREOFFICE C ALC

 Calc is the spreadsheet component of LibreOffice.

 It is a spreadsheet application that user can use to calculate, analyse and manage data.

 Spreadsheets consist of a number of individual sheets, each sheet containing cells arranged in rows and
columns.

 A particular cell is identified by its row number and column letter.

 In Calc, each sheet can have a maximum of 1,048,576 rows and a maximum of 1024 columns.
 FEATURES OF C ALC

1. Calc supports 1 million rows in a spreadsheet, making LibreOffice Calc more suitable for heavier
scientific or financial spreadsheets.

2. Calc generates 2D and 3D charts, which can be integrated into other LibreOffice documents.

3. User can also open and work with Microsoft Excel workbooks and save them in Excel format.

4. Calc includes over 300 functions for financial, statistical, and mathematical operations among others.

5. Calc can import and export of spreadsheets in multiple formats, including HTML, CSV, PDF etc.

6. Calc Offers database functions, to arrange,store and filtered.

 HOW TO LAUNCH LIBREOFFICE C ALC

 From the LibreOffice centre, Click on the ‘Create: Calc

Spreadsheet’.

 Click the Quick Starter icon the system tray, right click on the icon
and select ‘Spreadsheet’.

 From the Start menu, click on ‘LibreOffice Calc’.

 LIBREOFFICE CALC USER INTERFACE

 Title bar
 Menu bar
 Toolbars
 Formula bar
 Status bar
 Side bar
 Calc work space layout
 TITLE BAR

 Located at the top.

 Shows the name of the current folder.
 Newly created blank document – Untitled 1
 MENU B AR

 Located below the title bar.

 When the user select an option on the menu bar, a sub menu drops downs to show commands.
 File
 Edit
 View
 Insert
 Format
 Sheet
 Data
 Tools
 Window
 Help
 TOOLBARS

 The default set of icons (sometimes called buttons) on toolbars provides a

wide range of common commands and functions.
 FORMULA B AR

 Located at the top of the sheet in the calc main window.

 If the Formula Bar is not visible, go to View on the Menu bar and select Formula Bar

 Name Box

 Function wizard

 Auto sum

 Function

 Input line
 STATUS BAR

 Provide information about the spreadsheet as well as quick and convenient ways to change some of its
features.

 Insert/overwrite

 Selection mode

 Unsaved/saved changes
 SIDE BAR

 Located on the right side of the window.

 It is a mixture of tool bar and dialog
 C ALC WORK SPACE LAYOUT

 The major components of Calc Workspace layouts are cells, column and row
headers and worksheet tabs.
 ADDING OR RENAMING WORKSHEET

 By default, there will be one sheet, clicking on the ‘+’ sign near to ‘sheet 1’, user can add more
sheets.

 To change the default name for a sheet (sheet 1, sheet 2, and so on), right click on the sheet
tab and select Rename sheet from the context menu.

 To change the color of a sheet tab, right-click on the tab and select Tab color dialog.
 HOW TO CREATE C ALC SPREADSHEETS

a) From the Start Centre

b) From the Quick Starter – Tools> Options > LibreOffice > General

c) From the start menu

d) From Menu bar, Tool bar or Key Board

e) From the Template

 FROM MENU BAR, TOOLBAR AND KEY
BOARD

 Choose File > New > Spreadsheet on the menu bar

 Press the Ctrl + N keys

 Click the New Icon on the standard tool bar

 TEMPLATES

 User can click on the ‘Templates’ tab and select ‘Calc Templates’.

 Press the Ctrl + Shift + N Keys or

 Choose File > Templates > Manage Templates on the Menu bar
or

 Choose File > New > Templates on the Menu bar.

 SAVING A C ALC SPREADSHEET

 Press Ctrl + S or

 Choose File > Save on the Menu bar, or

 Click the Save icon on the Standard toolbar

 If the file has not been saved previously, a save as dialog appears
 HOW TO SAVE A COPY OF CALC
SPREADSHEET

 Save a copy of Calc Spreadsheet – File > Save a copy

 Save all active documents – File > Save all

 Save as command – File > Save as or Use Ctrl + Shift + S

 SAVING A C ALC SPREADSHEET AS
MICROSOFT EXCEL FILE

 Click File > Save As

 On the Save As Dialog, in the File type (or Save as type) drop-down
menu, select the type of Excel format needed.

 Click Save
 HOW TO SET A PASSWORD FOR C ALC

 Two levels of document protection:

1. Read protect (file cannot be viewed without a password)

2. Writer protect (file can be viewed in read-only mode but cannot be changed without a password.)

 Use file > Save As

 On the Save As dialog, select the Save with password and then click Save

 To Set Password dialog opens

 To Read-protect the document, type a password in the two fields.

HOW TO SET PASSWORD
 To write-protect the document, click the Options button
and select Open file read-only in the File Sharing
Password section.

 To write-protect the document but allow selected people

to edit, it select open file read-only and type a
password in the two boxes at the bottom of the dialog

 click Ok to save the file.

 HOW TO REMOVE OR CHANGE A
PASSWORD SET IN A WRITER DOCUMENT

 Choose File > Properties > General and

click the Change Password button
 CELL NAVIGATION
 The user can select or move from cell to cell in any of the following ways:
 Using the mouse
 Place the mouse pointer over the cell and click the left mouse button.
 Using a cell reference
 Highlight or delete the existing cell reference in the Name Box on the Formula Bar.
 Type the new cell reference of the cell you want to move to and press enter.
 Using the Navigator
 Press F5 key to open the Navigator dialog or click the Navigator button in the sidebar.
 Type the cell reference into the Column and Row fields and press the Enter key
 Using enter key
 Pressing Enter moves cell focus down in a column the next row.
 Pressing Shift + Enter moves the focus up in a column to the previous.
 Using the tab key
 Pressing Shift + Tab moves the focus to the left in a row to the previous column
 Using the arrow keys
 Using Home, End, Page Up and Page Down keys
• Home moves the cell focus to the start of a row.
• End moves the cell focus to last cell.
• Page up down and page up
 WORKSHEET NAVIGATION

 Each sheet in a spreadsheet is independent of other sheets.

 Using the Navigator – when the navigator open, double click on any of the listed sheets to
select the sheet.

 Using the keyboard - Ctrl + page down to move one sheet to the right and Ctrl +
Page up to move one sheet to the left.

 Using the mouse – click on one of the sheet tabs at the bottom of the spreadsheet to
select that sheet.
 SELECTING CELLS AND WORK SHEETS

1. Selecting cells
 Selecting range of adjacent cells
 A range of adjacent cells can be selected using the keyboard or the mouse
i. Click in a cell
ii. Press and hold down the left mouse button.
iii. Move the mouse around the screen.
iv. Once the desired block of cells is highlighted, release the left mouse button.
 Selecting a range of cells without dragging the mouse

I. Click in the cell which is to be one corner of the range of cells.

II. Move the mouse to the opposite corner of the range of cells.

III. Hold down the Shift Key and click.

 The user can also directly select a range of cells using the Name box

I. Click into the Name Box on the Formula bar.

II. To select a range of cells, enter the cell reference for the upper left-hand cell,
followed by a colon (:) and then the lower right-hand cell reference.

III. For example, to select the range that would go from A3 to D6, to would enter
A3 : D6
 To select a range of cells by using the key board.

I. Select the cells that will be one of the corners in the range of cells.

II. While holding down the shift key, use the cursor arrows to select the rest of the range.

 The user can also select a contiguous range of cells

I. First click in the selection mode field on the status bar

II. Selecting extending selection before clicking in the opposite corner of the range of cells.
 Selecting range of non-adjacent cells

1. Select the cell or range of cells using one of the methods above.

2. Move the mouse pointer to the start of the next range or single cell.

3. Hold down the Ctrl Key and click or click-and –drag to select another
range of cells to add to the first range.

4. Repeat as necessary.
 SELECTING ROWS AND COLUMNS

 Selecting single column or row

 To select a single column, click on the column header.

 To select a single row, click on the row header.

 SELECTING MULTIPLE COLUMNS OR
ROWS

 To select multiple columns or rows that are contiguous:

1. Click on the first column or row in the group
2. Hold down the shift key
3. Click the last column or row in the group.
 To select multiple columns or rows that are contiguous:

I. Click on the first column or row in the group.

II. Hold down the Ctrl key

III. Click on all of the subsequent columns or rows while down Ctrl
key
 SELECTING ENTIRE SHEET

 Using the combination key Ctrl + A to select the entire sheet.

 Click Edit on the Menu bar and select All.
 SELECT MULTIPLE WORKSHEETS

 Click on the sheet tab for the first desired sheet.

 Move the mouse pointer over the sheet tab for the last desired sheet

 Hold down the shift key and click on the sheet tab.

 All tabs between these two selections will turn white, any actions
performed by user will now affect all highlighted sheets.
 HOW TO INSERT COLUMNS AND ROWS
IN CALC SPREADSHEET

 Using the insert menu

1. Select a cell, column, or row where you want new column or row inserted.
2. Go to insert on the main menu bar and select either Insert > Columns ,
Insert > Rows
 Using the mouse
1. Select a cell, column, or row where you want new column or row inserted.
2. Right click the column header or row header
3. Select Insert Column left or Insert Rows above from the context menu.
 Inserting multiple columns or rows

 Highlight the required number of rows or columns by holding down

the left mouse button on the first one and then dragging across the
number of identifiers.

 Use the insert menu.

HOW TO DELETE COLUMNS AND ROWS IN
CALC SPREADSHEET?

 To delete a single column or row

1. Select the cell or column you want to delete.
2. Go to sheet on the Menu bar and select Delete cells or right click and
select delete from the context menu.
3. Select the option you require from the delete cells dialog and click ok.
 FREEZING ROWS AND COLUMNS IN CALC

 Freezing locks a number of rows at the top of a spreadsheet or a number of columns on the
left of a spreadsheet or both rows and columns.

 Then, when moving around within a sheet, the cells in a frozen rows and columns always
remain in view.

1. Click on the row header below the rows where you want the freeze, or click on the
column header to the right of the columns where you want the freeze.

2. Click on the Freeze rows and columns icon in the main toolbar or go to view on the
menu bar and select Freeze cells > Freeze Rows and Columns.
 How to unfreeze the rows and columns

1. Go to view on the menu bar and select Freeze cells > Freeze
Rows and Columns.

2. Click on the Freeze Rows and Columns icon in the main tool bar.
 MERGING AND SPLITTING CELLS

 Merging cells

1. Select the range of contiguous cells you want merge.

2. Right click on the selected cells and select Merge cells from the context menu, or

 Go to Format > Merge cells > Merge cells or Merge and Center cells on the menu
bar, or

 Click on the Merge and Center cells icon on the formatting toolbar.

 Using Merge and Center Cells will center align any contents in the cells.
 SPLITTING CELLS

1. Select a merged cell

2. Go to format > Merge cells > split cells on the menu bar or

3. Right click and select split cells from the context menu or

4. Click on the merge and center cells icon on the formatting

toolbar.
 DATA ENTRY IN CALC

 Data can be entered in Calc spreadsheet’s cell as text or numbers or a

combination of both.
 User can directly enter the data in the cells by using keyboard.
 Entering text
 Click in a cell and type the text.
 The text is left-aligned by default.
 If you want to use paragraphs, press Ctrl + Enter to create another
paragraph.
 ENTERING NUMBERS

 Click in the cell and type in a number using number keys on either the
main board or numeric keyboard or numeric keypad.
 By default, numbers are right aligned in a cell.
 HOW TO FILL CELL USING TEXT OR NUMBER SERIES?

 The user can select the Fill tool in Calc (available in sheet menu) to duplicate existing
content or create a series in a range of cells in spreadsheet.

1. Select the cells containing the contents you want to copy or start the series.

2. Drag the cursor in any direction or hold down the shift key and click in the last cell you
want to fill.

3. Go to sheet > Fill cells on the main menu bar and select the direction in which you want
to copy or create data (up, down, left right, sheet or series.)
 USING FILL SERIES OPTION
 Select Sheet > Fill cell > Fill series on the menu bar, the Fill series dialog will opens.

 The user can select the type of series needed.

 Direction – determine the direction of series of action.

 Series type

 Linear

 Growth

 Date

 Autofill
 Unit of time
 Start values
 End value
 Increment
 FORMATTING DATA IN CALC

 The cells can be formatted by using ‘ Format cell’ option available.

 Select any cell or cell range and click on Format > Cells or Ctrl + 1.

 The various options for formatting a cell is available in the dialog box.
 FORMATTING NUMBERS

 The numbers entered in cells can be formatted using different options

available in the formatting tool bar and format cells dialog box.

 Formatting icons like format as currency, format as percentage, format as

number, format as date, add decimal, and delete decimal, used to format
numbers entered in cells.

 ‘Numbers’ page of Format cells dialog provides various number

formatting options.
 Use the Numbers page of the Format cells dialog to

 Apply any of the data types in the Category list to the data.

 Control the numbers of decimal places and leading zeros in Options.

 Enter a custom format code.

 The Language setting controls the local settings for the different formats such as the date
format and currency symbol.
 FORMATTING A FONT

Select the cell

1. Click the drop down arrow on the right of the Font name box on the formatting tool
bar and select a font name from the drop down list.

2. Click the drop down arrow on the right of the Font size box on the formatting tool bar
and select a font size from the drop down list.

3. To change the character format, click on the Bold, Italic, or Underline icons.

4. To change the paragraph alignment of the font, click on one of the four alignment icons
(left, centre, right, justified).

5. To change the font colour, click the arrow next to the font colour icon to display colour
palette, then select desired colour.
 FORMATTING CELL BORDERS

 To format the borders of a cell or a group of selected cells, click on

borders icon on the formatting tool bar , and select one of the border
options displayed in the palette.

 To format the line style and line colour for the borders of a cell, click
the small arrows next to Border style and line colour icons on the
formatting tool bar.
 CONDITIONAL FORMATTING

 Conditional formatting is used to highlight data that is outside the specifications set by the
user.

1. Go to format > Conditional Formatting > Condition, Color scale or Data Bar on the menu
bar to open the conditional formatting dialog.

2. Click Add create and define a new condition.

3. Select a style from the styles already defined in the Apply style drop down list.

4. Click ok to save the conditions and close the dialog.

 TYPES OF CONDITIONAL FORMATTING

a. Condition
b. Color scale
c. Data bars
d. Icon sets
 CREATING CHARTS IN C ALC

 Calc uses Chart Wizard to create charts or graphs from the

spreadsheet data.
1. Select the cells containing the data to be included in the chart.
2. Select Insert > Chart on the main menu bar or
3. Click the chart icon on the standard tool bar to open the Chart
Wizard dialog.
4. A sample chart is created using the selected data and is placed in the
spreadsheet as an object.
 From the chart wizard dialog box, the user can change the chart type.

 Click Next > to move onto the next step, selecting the Data Range.

 If the user wants, he can change the rows and columns used as data for
the chart by editing the cell references in the Data range text box.

 Click Next > to move onto the next step, Selecting data series
 FORMULAS AND FUNCTIONS IN CALC

 How to enter formulas in Calc?

 By using the Function wizard

 By typing directly into the cell or into the input line beginning with an = symbol.

 A formula must begin with one of the following symbols: =, + or -.

 With formulas, the equals sign indicates that the cell will be used for a calculation.
 OPERATORS IN FORMULAS

 Arithmetic operations

 The arithmetic operators, additions, subtraction, multiplication and

division operators return numerical operators.

 + (plus ) – addition, -(minus ) – subtraction, * (asterisk) – Multiplication,

/ (slash) – division, % (percent) – percent , ̂(caret) - Exponentiation
 Comparative operators

 Text operators

 Reference operators
 FUNCTIONS IN C ALC

 Calc includes over 350 functions to help you analyse and reference data.

 Each functions has a number of arguments used in the calculations.

 User’s task is to enter the arguments needed to run the function.

 For compatibility, functions and their documents in Calc have almost identical names to their
counter part in Microsoft Excel.
 USING FUNCTIONS

 User can insert the Function Wizard from Insert Menu for executing functions in calc.

 Choose Insert > Function or

 Click the fx button on the Formula bar

 Press Ctrl + F2

 Function list available in the side bar.

 Select a category of functions from the Function Wizard.

 When you select the function its description appears on the right hand side of the dialog.

 The wizard displays an area to the right where you can enter data manually in text boxes or
click the shrink button to shrink the wizard so the user can select cells from the worksheet.

 To select the cells, either click directly upon the cell or hold down the left mouse button and
drag to select the required area.

 When the area has been selected, click the shrink button again to return to wizard.

 Click ok to accept the function and it to the cell and get the result.