S R Computer Institute

Spreadsheet

• Introduction to spreadsheet
• Elements of spreadsheets
• Creating a New Worksheet
• Printing of Sheet
• Saving Spreadsheet
• Opening Spreadsheet
• Closing a Spreadsheet
• Formatting data
• Using The fill tool
• sorting and filtering
• filtering
• freezing rows and columns
• creating formulas
• cell referencing in worksheet
• functions
• charts

   Introduction to spreadsheet

A spreadsheet is a large electronic sheet having data and information arranged in rows and columns. MS-Excel and LibreOffice calc are the most widely used spreadsheet applications. Spreadsheets are quite useful in entering, editing, analyzing and storing data. Arithmetic operating with numerical data such as addition, subtraction, multiplication and division can be done using spreadsheet. Spreadsheet is also used for what-if analysis. You can sort numbers and use simple as well as complex financial, mathematical and statistical formulas.

What is calc?

Calc is the spreadsheet component of LibreOffice. You can enter data (usually numerical) in a spreadsheet and then manipulate this data to produce certain results. Alternatively, you can enter data and then use Calc in a “What if…” manner by changing some of the

Data and observing the results without having to retype the entire spreadsheet or sheet.

Other features provided by Calc include:

• Functions, which can be used to create formulas to perform complex calculations on data.
• Database functions, to arrange, store, and filter data.
• Dynamic charts; a wide range of 2D and 3D charts.
• Macros, for recording and executing repetitive tasks; scripting languages supported include LibreOffice Basic, python, Bean Shell, and JavaScript.
• Ability to open, edit, and save Microsoft excel spreadsheets.
• Import and export of spreadsheets in multiple formats, including HTML, CSV, PDF, and postscript.

Elements of Spreadsheet

Calc works with documents called spreadsheets. 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. Cells hold the individual elements- text, numbers, formulas, and so on-that make up data to display and manipulate. Each spreadsheet can have several sheets, and each sheet can have many individual cells. In Calc, each sheet can have a maximum of 1,048,576 rows and a maximum of 1024(AMJ) columns. LibreOffice Calc can hold up to 32,000 sheets. When Calc is started, the main window opens . The parts of this window are described below.

Title bar

The Title bar, located at the top, shows the name of the current spreadsheet. When a spreadsheet is newly created form a template or a blank document, its name is Untitled X, where X is a number. When you save a spreadsheet for the first time, you are prompted to enter a name of your choice.

Menu bar

 When you select an item on the Menu bar, a sub-menu drops down to show commands.

• File: contains commands that apply to the entire document; for example, Open, Save, Wizards, Export as PDF, Print, Digital Signatures, and Templates.
• Edit: contains commands for editing the document; for example, Undo, Copy, Paste, Track Changes, Find and Replace, Compare and Merge Documents.
• View: contains commands for modifying how the Calc user interface looks; for example, Toolbars, Grids, Column & Row Header, Full Screen, Zoom.
• Insert: contains commands for inserting element into spreadsheet; for example, pictures, Media, Objects, Formulas Design, Frames, Special Characters, Charts, Functions, Shapes, Pivot tables, Named Ranges, Comments, Hyperlinks, Headers and footers, Form Controls.
• Format: contains commands for modifying  the layout of a spreadsheet; for example, Cells, Page, Styles, Alignment, Merge Cells,  Print Range, Conditional Formatting,  Spreadsheet Themes, Image, Chart, Object.
• Styles: contains commands to apply standard styles to cell, group of cells or sheets contents.
• Sheet: contains the most often used commands for handling sheets, such as Insert and Delete Cells, Columns, Rows and Sheets , Sheets from file, Cell Reference type, Link to External Data, as well as Comments and fill cells.
• Data: contains  commands for manipulating data in the spreadsheet; for example, Define Database Range, Sort, Filters, Statistics, Pivot Tables, Consolidate, Forms Groups and Outlines.
• Tools: contains various functions to help you check and customize the spreadsheet; for example, Spelling, Share Document, Gallery, Macros, Goal Seek, Solver, Detective, Protect Sheet, XML Filter Settings, Extension Manager.
• Window: contains commands to open a New window or Close an open window.
• Help: contains links to the LibreOffice help system and other Miscellaneous  function; for example, Help, License Information, User Guides, Check for Updates, Send Feedback, and Donate.   

 Toolbars  The default setting when Calc opens is for the Standard and Formatting toolbars to be docked at the top of the workspace

Calc toolbars can be either docked and fixed in place, or floating; you can move a toolbar into a more convenient  position on the workspace. Docked toolbars can be undocked and either moved to different docked position on the workspace, or left as a floating  toolbar. Toolbars that are floating when opened can be docked into a fixed position on the workspace.

You can choose the single-toolbar alternative to the default double toolbar arrangement. It contains the most-used commands. To activate it, enable View> Toolbars>Standard (Single Mode) and disable View > Toolbars > Standard and View> Toolbar > Formatting.

Formula bar

The formula bar (figure 4.2) located at the top of the sheet in the Calc workspace. The Formula bar is permanently docked in this position and cannot be used as a floating toolbar. If the Formula bar is not visible, go to View on the Menu bar and select Formula bar.

From left to right, the Formula bar consists of the following:

• Name box:  gives the current active cell reference using a combination of a letter and number, for example A1 The letter indicates the column and the number indicates the row of the selected cell. If you have selected a range of cells that is also a named range, the name of the range is shown in this box. You can also type a cell reference in the Name Box to jump to referenced cell. If you type the name of a named range and press the enter key, the named range is selected and displayed.
• Function Wizard: opens a dialog from which you can search through a list of available functions. This can be very useful because it also shows how the functions are formatted.
• SUM: clicking on the sum icon totals the numbers in the cells above the selected cell and then places the total in the selected cell. If there are no numbers above the selected cell, then the cells to the left are totaled. When sum icon is used, Calc suggest the range to sum with a colored box, but does not insert the formula. You need confirm the suggested range by typing  Enter ,or reject pressing esc. You can also adjust the range with the mouse by dragging the colored box or its edges to adjust the range. The icons in  the formula bar are equivalent to the Enter and Esc commands.
• Function: clicking on the function icon inserts an equals (=) sign into the selected cell and the input line, allowing a formula to be entered. The name box now displays the most frequently used functions, for a quick access to the function name and syntax.
• Input line: displays the contents of the selected cell (data, formula, or function) and allows you to edit the cell contents. To turn the input line into a multiline input area for very long formulas, click the drop down button on the right.

You can also edit the contents of a cell directly in the cell itself by double-clicking on the cell. When you enter new data into a cell, the sum and Function icons change to cancel and accept icons.

  Status bar

The Calc status bar (figure 4.3) provides information about the spreadsheet as well as quick and convenient ways to change some of its features. Most of the fields are similar to those in other components of LibreOffice.

The status bar has quick way to do some math operations on selected cells in the spreadsheet. You can calculate average and sum, count elements, and more on the selection by right-clicking over the cell information area of the status bar and selecting the operations you want to display in the status bar.

 Sidebar

The Calc Sidebar (View > sidebar) is located on the right side of the window. It is a mixture of toolbar and dialog. It is similar to the sidebar in writer and consists of five decks: Properties, Styles, Gallery, Nevigator, and functions.Each deck has a corresponding icon on  the tab panel to the right of the sidebar, allowing you to switch between them. The decks are described below.

Properties: This deck includes five content panels.

• Styles: Access to the available cell styles, update cell styles, and new cell styles.
• Character: Controls for formatting the text, such as font family, size and color. Some controls, such as superscript, only become active when the text cursor is active in the input line of the formula bar or the cell.
• Number Format: Quickly change the format of numbers including decimals, currency, dates, or numeric text. Numerical and label field controls for forms are also available.
• Alignment: Controls to align the text in various ways, including horizontal and vertical alignment, wrapping, indenting, merging, text orientation, and vertical stacking.
• Cell Appearance: Controls to set the appearance options, including cell background color, cell border formats including line color and style, and grid lines.

Each of these panels has a More Options button, which opens a dialog giving a greater number of options. These dialogs lock the document for editing unit they are closed.

• Styles: This deck contains a single panel, which is the same as that opened by selecting the Styles button (f11) form the text formatting toolbar.
• Gallery: This deck contains a single panel, which is the same as that opened by selecting Gallery from the standard toolbar or Tools > Gallery form the menu bar.
• Navigator: This deck contains a single panel, which is essentially the same as the Navigator window opened by clicking the Navigator button on the Standard toolbar or selecting View > Navigator (f5) from the menu bar. Only the contents button is absent in the Sidebar’s Navigator panel.
• Functions: This deck contains a single panel, which is the same as the window opened by selecting Insert > Function… Form the menu bar.

To the right side of the title bar of each open deck is a Close button (X), which closes the deck to leave only the tab bar open. Clicking on any Tab button reopens the deck. to hide the Sidebar, or reveal it if already hidden, click on the edge Hide/Show button. To adjust the deck width, drag on the left edge of the sidebar.

Spreadsheet layout

Individual cells-The main section of the workspace in Calc displays the cells in the form of a grid. Each cell is formed by the intersection of one column and one row in the spreadsheet. For example, cell D3 is formed by column ‘D’ and row ‘3’

At the top of columns and the left end of the rows are a series of header boxes containing letters and numbers. The column headers use an alpha character starting at A and go on to the right. The row headers use a numerical starting at 1 and go down.

Sheet tabs- In Calc, you can have more than one sheet in a spreadsheet. At the bottom of the grid of cells in a spreadsheet are sheet tabs indicating how many sheets there are in the spreadsheet. Clicking on a tab enables access to each individual sheet and displays that sheet. An active sheet is indicated with a white tab (default Calc setup). You can also select multiple sheets by holding down the Ctlr key while clicking on the sheet tabs.

To change the default name for a sheet (Sheet 1, Sheet2, and so on), right- click on a sheet tab and select  Rename Sheet  from the context menu or double click on the sheet tab to open a dialog to let you type a new name for the sheet. Click Ok when finished to close the dialog.

Creating a New Worksheet

Creating spreadsheets is identical to creating documents to the other modules within LibreOffice. You can create a new worksheet in a number of ways, but selecting File >> New >> Spreadsheet is probably the most convenient way of doing it. Pressing Ctlr+N is also a handy Shortshort for creating a new workbook.

Entering Data in Worksheets

Any type of data- numeric, Alphanumeric , non-numeric or formula can be entered in worksheets by typing. To enter data in a cell, Firstly place the cell pointer over desired cell and then type the data. Three types of data can be entered in Calc worksheet. These three types of data entries are:

• Numbers
• Text
• Formulas

Numbers

These are numeric entries. These entries can be used in calculations. In Calc, a number can contain only the following characters:

0 1 2 3 4 5 6 7 8 9 + – (), $, %. E, e

Calc ignores leading plus signs (+) and treats a single period as a decimal. All other combinations of numbers and non-numeric characters are treated as text.

Text

These are the text entries (neither number not formula) in the worksheet. These entries are not used in calculations. In Calc, text is any combination of numbers, spaces, and non-numeric characters- for example, Calc creats  the following entries as text:

10AA109, 127AXY, 12-976,208, 4675

All text is left-aligned in a cell. To change the alignment, click cells on the format menu, click the Alignment tab, and then select the options you want.

Formulas

Formula is a sequence of values, cell-addresses, names, functions or operators in a cell the produces a new value from existing values). A formula can contain values (entries that can be used for calculations), operators (e.g., +, -, / *) and cell addresses (e.g., C9, B14). A formula can also contain functions. Formulas in Calc start with ‘=’ sign. Therefore, a formula the calculates the difference in the two values of cell B9 And A9, would be written as = B9 –A9

Printing of Sheet

To quickly print a spreadsheet, click on the Print File Directly icon on the Standard toolbar to send the entire spreadsheet to the default printer that is defined for your computer.

For more control over printing, use the print dialog by going to  File > Print on the main menu bar or by using the keyboard shortcut  Ctrl + p. The Print dialog appears, which has four tabs where you can choose a range of options as described in the following sections.

General page

On the General page of the Print dialog , you can:

• Select which printer to use from the pictures available in the Printer section.
•  Select which sheets and pages to print, the number of copies to print, whether to collate multiple copies, and the page order for printing in the Range and Copies section.
• Click Properties to display a printer-specific dialog where you can select paper size, portrait or landscape orientation, duplex printing if available, and which paper tray to use.

  LibreOffice Calc page

 On LibreOffice Calc Page of the print dialog ,you can select whether or not to include the output of empty pages when you print your spreadsheet.

Page Layout Page

On the page Layout page of the print dialog you can select how many pages are printed per sheet of paper, the order in which the pages are printed on a sheet, and whether a border is draw around each page.

Options page

On the Options page of the print dialog you can select whether to print to a file (instead of to a printer), create single print jobs when you want to use collated output (instead of a separate print jib for each copy), or use only the paper size specified in the printer preferences.

Printing multiple pages on a single sheet

To print multiple pages of a spreadsheet on one piece paper:

1. Go to File > Print on the menu bar or use the keyboard shortcut ctrl +p to open the print dialog and then select the page layout page.
2. In the Layout section, select from the Pages per sheet drop-down list the number of pages to print per sheet. The preview panel on the right shows how the pages will look in the printed document.
3. When printing more than two pages per sheet, select the order in which the pages are printed on a sheet from the order drop-down list.
4. To distinguish each page on a sheet, select the Draw a border around each page option.
5.  Click ok.