Download Spreadsheet Skills: Data Tabulation using OpenOffice Calc and more Study notes Information Technology in PDF only on Docsity!
Digital Data Tabulation
Pre-requisite: Unit ITCC-101 Introduction to Information Technology
Unit Code Unit Title (Skills) Hours Total Hour T s h Pr ITDC-104 (^) Data Tabulation Skill using Spreadsheet 6 2 4 30 Basics of spreadsheet 1 4 Modifying cells 1 2 Formatting Spreadsheet 2 4 Types of Cell References 1 8 Using Functions and Obtaining charts 1 6
Ignite Mind
"All which is beautiful and noble is the result of reason and calculation”
Charles Baudelaire
Learning Objectives
After studying this unit the students will be able to: Work on basics of spreadsheets Move Cell Contents, Insert and Delete Cells Format contents of spreadsheet Use formulae for simple calculations in spreadsheet Use functions and obtain charts
Introduction Before the invention of personal computers and spreadsheet programs, accountants and bankers generally wrote their accounts by creating tables of numbers and text. These tables were called spreadsheets as the accountant would spread information like costs, payments, taxes, income, etc on a single, big, oversized sheet of paper to get a complete financial overview. The disadvantage of these manual spreadsheets was that if one number changed, it affected all others down the line. So, a lot of erasing and rewriting was required. Spreadsheet programs on computers solved this problem. Using a Spreadsheet program, you can enter the data that you want on your spreadsheet and key in the types of calculations needed. The program does all the calculations and produces the results. In case there is any change in data, the Spreadsheet program recalculates and displays the new result. These programs have made the tasks of building and modifying spreadsheets much easier. Resource Requirements
Hardware
A Computer system
Software
A Spreadsheet program (Preferably OpenOffice Calc). Suggestions for Effective Teaching Encourage students to use Spreadsheets Program for simplifying their real life day to day tasks like personal budget planning, creating marks sheet, time management etc. Encourage peer learning in Computer Lab Ensure that students follow standard procedures to logon/logoff a computer. Ensure the proper connectivity of I/O devices Encourage students to explore opportunities as IT Professionals Introduce how to integrate IT Tools with traditional practices at various work places Encourage students to follow best practices such as taking backup of files, turning off devices when not in use Educate students to keep storage media virus free Sensitize students on ethical and health related issues Element 1: Basics of Spreadsheets Arranging data systematically in columns and rows is called tabulation. Spreadsheet applications (also referred to as Spreadsheets) are computer programs that let you create and maintain data tabulated in Rows and Columns. Calc is an example of electronic spreadsheet program that is used to create worksheets, charts, etc. It is an Open Source Software.It is a component of a large package called OpenOffice.org, the other components include Writer for Word processing and Base for Database Management System. MS Excel, IBM Lotus Symphony and KSpread are some other examples of spreadsheet programs.
Here is a brief explanation of the Calc window. Parts of Spreadsheet Program Window Title bar The Title bar shows the name of the workbook along with the name of Spreadsheet program. Here the name of the workbook is cafe.ods and the spreadsheet program being used is OpenOffice.org Calc. Fig 1.3 Title bar Main Menu bar The Menu bar displays all the menus that are available in Calc i.e. File,Edit,View,Insert,Format,Tools,Data,Window and Help. The contents of any menu can be displayed by clicking on the menu name. Fig 1.4 Main Menu bar Standard Toolbar The Standard Toolbar contains icons that provide quick access to commands like New, Open, Save, Print, Cut, Copy, Paste, etc Fig 1.5 Standard Toolbar Formatting toolbar Formatting toolbar consists of a set of tools that are specific to cell formatting and calculations e.g., tools to set font , bold, italics, underline, text alignment, number formats, insert border and changing background colors, font color etc. Fig 1.6 Formatting toolbar Navigation buttons and Sheet tabs Horizontal scroll bar^ Zoom tool
Formula bar The Formula Bar contains information that you have entered or that you are currently entering as you type in the current or active cell. The contents of a cell can also be edited in the Formula bar. Fig 1.7 Formula bar When you enter new data into a cell that already contains something, the Sum and Equals buttons change to Cancel and Accept buttons. Fig 1.8 Cancel and Accept buttons Navigation buttons and Sheet tabs At the bottom, left area of the spreadsheet, you will find worksheet tabs. By default, three worksheet tabs appear each time you create a new workbook. Worksheet tabs allow you to move from one worksheet to another in the workbook. The navigation buttons help to display the first, previous, next, or last worksheets in the workbook. Fig 1.9 Navigation buttons and Sheet tabs On the bottom, right area of the spreadsheet you will find the zoom tool and the horizontal scrolling bar. The slider on the Zoom tool may be dragged to the left to zoom out and to the right to zoom in. Fig 1.10 Horizontal Scrollbar Fig 1.11 Zoom tool Terminology(related to Spreadsheets) Name Box Sum Button
Figure:1.13 Range of cells Creating a Workbook To create a new workbook, follow these steps: Step 1 : Choose New > Spreadsheet option from File menu. Fig 1.14 Creating a Workbook The Calc window opens ,by default, on the first of the three worksheets .Until saved with SAVE AS, the file continues to bear the default name Untitled1. The next file in sequence takes the name Untitled2, and so on. You can also use keyboard shortcut Ctrl+N to open a new workbook. Entering Data Mainly, a cell can have three kinds of data: Numbers , Text , and Formulas. Text means words, titles, comments, etc., like ‘school’, ‘9B’. To enter data in a cell, check that the cell is active, if not, click inside it. As you type, you can see the data in the cell and also on the Formula bar. Enter button Range: A3:B
Fig 1.15 Entering data You can complete the entry by clicking the Enter button on the Formula bar, or by pressing Enter key on the keyboard. Entering Formulas A formula can be a combination of values (numbers or cell references) and math operators (+, -, /, *). Formulas perform arithmetic operations and compute a result. All formulas must begin with ‘ = ’ sign. The formula needs to be entered into the cell to make it work. For example, if you want the total of cells A1 and B1 displayed in cell C1, enter =A1+B1 in cell C1 and press Enter. Fig 1.16 Formula shows in Formula bar but result in cell. For example, in the Calc sheet shown above, the formula bar displays the formula =A1+B1 but the cell has what you want—the result of the formula. Arithmetic Operators used in Formulas Calc has several arithmetic operators. The order of mathematical operations done by operators is very important. If you enter a formula that contains several operations, Calc knows to work those operations in a specific order. The order of operations is:
- Operations enclosed in parenthesis
- Exponential calculations (to the power of)
- Multiplication and division, whichever comes first
- Addition and subtraction, whichever comes first Using this order, let us see how the formula 16/(8-4)8-2 is calculated :*
- Operations in parentheses is performed first : 8-4 = 4. Formula becomes 16/4*8-
- As division comes before multiplication, divide 16/4 = 4.Formula becomes 4* 8- 2
- Next, multiplication takes place before subtraction : 4*8 = 32.Formula becomes 32- 32-2 = 30. Result is 30
Fig:1.18 Print dialog box Step 2: Choose the printer's name. Step 3: You can either print all the worksheets or selected sheets by choosing the appropriate options. Step 4: Enter the number of copies to print in Number of copies box. Step 5: Click OK. Closing a Workbook To close a workbook, you can choose Close option from the File menu. If you have made some changes in the workbook after you last saved it, Calc alerts you to save your work by displaying a dialog box. Fig:1.19 Calc Alert dialog box Opening an existing Workbook You can open any workbook that has previously been saved and given a name.To open an existing workbook, Choose File Open from the menu bar. The Open dialog box opens. Locate the file, select it and Click the Open button. Exit Spreadsheet Program You can exit Spreadsheet program by choosing File>Exit
Fig:1.20 Exiting Calc
Summary
Spreadsheet applications (also referred to as Spreadsheets) are computer programs that let us create and maintain data tabulated in Rows and Columns. Lines divide the rows and columns up into boxes called cells. A cell can contain text, a number or a formula. Columns have a letter at the top and rows have a number at the side. Individual cells are identified by their cell reference number which contains a column letter and a row number e.g., A6. The cell that is currently selected for either data entry or editing is called the Active cell.
Self Assessment
Element 2: Modifying Cells Moving Cell Contents There are two ways of moving the contents of one cell to another: a. cutting and pasting b. dragging and dropping a. Cutting and pasting : To move a cell by cutting and pasting, follow steps 1 to 4: Step 1: Select cell(s) that need to be moved. Step 2: Choose Cut option from Edit menu or press Ctrl+X, or click button on the Standard toolbar. Figure: 1.21 Moving cell contents Step 3: Select the cell(s) where the cut cell(s) are to be pasted. Select either the whole range or only the first cell of the range of cells where the selected cells are to be pasted. Step 4: Choose Paste option in Edit menu, or press Ctrl+V, or click the Paste button on the Standard toolbar. b. Dragging and dropping: To move cell contents with drag and drop, follow steps 1 to 4: Step 1: Select the cells to be moved. Step 2: Place the mouse pointer on the border of the selected cells. It turns into an arrow. Step 3: With the mouse pointer in the arrow form, drag it to the new location. Step 4: Release the mouse button. The selected cells moves to the new location. Copying Cell Contents The copy and cut/paste operations in Calc are the same as in Writer. They can be picked up from the Edit menu or from the Standard toolbar. Inserting and Deleting Cells
New cells, rows and columns can be inserted and unwanted ones can be deleted with the help of Insert and Delete commands of Calc. If on a sheet a new cell is added, it has to push the existing ones to create room for itself. Inserting Cells To insert cells follow steps 1 to 4: Step 1: Select a cell or a range of cells. Step 2: Choose Cells option from the Insert menu. Figure:1.22 Select Insert cells The Insert Cells dialog box appears: Figure:1.23 Insert Cells dialog box Step 3: Choose the appropriate option in the above dialog box for Calc to make room for the inserted cells. Step 4: Click OK. Inserting Rows and Columns To insert a row, click on the row before which a row is to be inserted and then choose Rows from the Insert menu. A new row is inserted before the active cell. Figure:1.24 Inserting Rows
Step 3: In the ‘Delete Cells’ dialog box, options are available for shifting cell left or up. Choose the correct option and click OK. Note: To delete an entire column or row, click in any cell in the row/column to be deleted and choose Delete entire row(s) or Delete entire column(s) in the Delete Cells dialog box. Click OK. AutoFilling The AutoFill feature of Calc fills a range of cells with a sequence of data. You may use it when you want to have numbers sequentially or in regular multiples like 2,4,6, and so on in cells but you do not want to enter numbers yourself. Completing a series like: consecutive numbers, numbers that follow a specific pattern, days of week, or dates that follow a specific pattern is a very simple task with AutoFill. Calc only needs a clue which it takes if you start it once. For example, type in Monday, Tuesday in adjacent cells, select these two cells. A small black box at the lower right corner called the Fill handle can be seen.Drag the fill handle to the next cells in the same direction. Figure: 1.28 Days of week entered. Release the mouse button and see that Calc has automatically filled in the other members of the series. Note : You can drag the mouse vertically (up or down) and horizontally (left or right). Dragging down or right counts forward while dragging up or to left counts backwards) Figure: 1.29 Days of week automatically filled Autofill is smart enough to generate a series of numbers, months, days, dates, time and quarters. Some examples are shown below. Figure: 1.30 Months automatically filled Figure: 1.31 Dates automatically filled
Summary
New cells, rows and columns can be inserted with Insert>Cells option. Cells, Rows, Columns can be deleted with Edit>Delete cells option. The Fill handle is a small black box at the lower right corner of the active cell. The AutoFill feature fills a range of cells with a sequence of data.
Self Assessment
S.N
o. I am able to… Yes No 1 Move cell contents from one location to other 2 Copy cell contents to other cells 3 Insert cells 4 Delete cells 5 Insert rows 6 Delete rows 7 Use Autofill feature
_________________________________________ Now I can answer …
- Write steps to copy contents of a range of cells into another part of worksheet.
- Which menu is used to insert a row in a spreadsheet
- Which feature of Spreadsheets would you use if you want to have numbers sequentially or in regular multiples in cells but you do not want to enter numbers yourself?
- What is a fill handle?
Centre align Left align Right align Justify
Step 2: On the toolbar, click the font drop-down arrow to change the font and the font size drop down arrow to change the font size. Figure: 1.34 Changing Font and font size Aligning Text and Numbers in Cells By default, Calc left-aligns text (labels) and right-aligns numbers (values) in a cell to make data easier to read. Figure: 1.35 Default Alignment in a cell Calc allows you to change the default alignment. You may align text and numbers using the left-align, center, right-align, and justify buttons on the Formatting toolbar: Figure 1.36: Alignment options on formatting toolbar Applying Borders to Cells To apply border to cell(s) follow the steps given below. Step 1: Select a cell or range of cells. Step 2: Click on the down arrow that is next to the Borders button. Figure 1.37: Choosing the Border Step 3: Choose a border style from the Borders drop-down menu. The chosen border is applied to the selected cells. Left Aligned Right Aligned
Applying Background color to Cells To apply background colour to cell(s) follow the steps given below. Step 1: Select a cell or range of cells. Step 2: Click the down arrow that is located next to the Background Color button. Figure 1.38: Choosing background colour Step 3: Choose a color from the Background Color drop-down menu. The selected cells display the color. Applying Font colour to cells To apply font colour follow these steps: Step 1: Select a cell or range of cells. Step 2: Click on the down arrow that is located to the right of the font color list box. A drop-down list of available colors appear.
