Chapter 1 Spreadsheet principles

1. Chapter 1 Spreadsheet principles • Objective -Understand the principies, terminology and capacity of one of the tools of productivity aimed toward the processing and manipulation of numbers. • Goals -The student will understand the function of the spreadsheet software. -The student will learn the basic concepts of the spreadsheet. -The student will recognize the elements of a commercial spreadsheet software.

2. 1.1 Software for the Automation of Calculations At present the spreadsheet is the most common example of an application developed for numerical processing. This software allows to automatically calculate and recalculate data on the computer screen.

3. Origins The first spreadsheet program, VisiCal for Apple 11 computers, was created in 1978 by Dan Brickiin, a Harvard student assisted by Bob Frankston, an M.l.T student. With the introduction of the PC by IBM, other programs appeared based on VisiCal that fought to establish íhe spreadsheet standard. In 1983, Mitchell Kapor founded the Lotus company and developed the 1-2-3 software that successfuity dominated MS-DOS settings. At present, Excel from the Microsoft company is one of the leaders in the graphic settings.

4. Impact The apparition of this software changed personal computers compietely because it contributed enormously to their success to such an extent that it is considerated as one of their driving forces. Its influence modified the way people worked with different needs such as execute scientific simulations, elaborate budgets, carry out financial projectíons, or keep a check account among other activities. It was even used in many offices to write tetters and reports when word processors were not widely used.

5. Spreadsheet conceptThe Spreadsheet is the computerized versión ofthe pape' sheets prinied withhorizontal and vertica! iines to be divided ¡n rows and coiutnns. These tools areused manually by accountants and their function is to organize Ítems intoaccountable quantities. These are known as spreadsheetsAt present, the term Spreadsheet describes just the basic aspecí of íneapplication because it has evoived togeíherwiíh persona! compi-iters e>isíing 3great variety of commerciai producís that intégrate tooic- for the creaüon oigraphs, basic drawings, predefined functions, data base macros managemení,among others.The common feature for any software developed as a Spreadsheet is iisstructure through which it can achieve the perfect organization plan íhat makesits operation possible.Next, we.wiH meníion three important basic aspecís to undersíand th'ssoftware,1, The structure of rows and coiumns delimits the workng área inío spacescalled cells. See figure 1.1.Figure 1 1 Spreadshee! structur'?Culumnas --Spreadsheet Concept The Spreadsheet is the computerized version of the paper sheets printed with horizontal and vertical iines to be divided in rows and columns. These tools are used manually by accountants and their function is to organize items into accountable quantities. These are known as spreadsheets.

6. Next, we will mention three importantbasic aspects to understand this Software. 1. The structure of rows and coiumns delimits the workng area into spaces called cells. See the figure. Columns Rows Cell

7. 2. Each element has been properly labeled to make an exact reference of the location of the data. 3. Carry out several operations or calculations through notations alled formulas that involve the content (values) with its reference.

8. 1.2 Worksheet and Workbook • Worksheet A worksheet or spreadsheet is the area where data are manipulated. Due to its great size, the monitor screen displays oniy the spreadsheet in segments. As we nave mentioned before, the working área is organized into rows and columns íhat are identified by labels appearing at the left top corner in the working area of the software, these labels may differ from one manufacturer to another.

9. Workbook The updated versions of the spreadsheet include more than one spreadsheet in the same file. Hence, the sheets are also labeled with the names Sheet l, Sheet 2,...Sheet N, which are assigned by default. These names can be changed by the user into a more descriptive name. This gives the spreadsheets a three-dimensional quality, the file created in this type of software is known as workbook.

10. 1.3 Cell, Reference and Reference Classification • Definition of cell The cell is the fundamental element of the application. This is where data are introduced, edited and manipulated. The cell is the area delimited by a column and a row. Cell is commonly defined as the intersection space between a column and a row. • Definition of cell reference Cell reference, or just reference, refers to the specification of the location or direction of a cell. It indicates the place where the values or the data involved are located.

11. Reference Classification Cell reference can be classified in two ways: • Relative, absolute and mixed reference. This classification specifies how to copy cells containing references to other cells. • Internal, external and remote reference. Classification that specifies the syntax of the reference. If it is going to be used in the same worksheet, in a different worksheet in the same workbook, in a different workbook, and even in another application different from a spreadsheet.

12. Relative Reference This reference established by default modifies the reference of the cells that are involved in a formula when it is copied by adjusting the references involved in the expression to the real position of the copy. • Absolute and Mixed Reference In the absolute or fixed reference, the column nor the row alter, To specify an absolute reference, the symbol for dotlars is placed before the column as well as the file. • Internal Reference The internal reference is the most simple and widely used reference. It relates cells in the same worksheets; for example, A5, Q20.

13. External Reference The external reference makes use of the three-dimensional characteristic of spreadsheets because it relates cells located in other worksheets in the same book, and even in other books. In Excel, the complete syntax specification to interpret this type of reference is: C:\directory\(File.xls)Sheet!ColumnFile Where: C:\Directory\ -indicates the unit and trajectory (directory) where the file is iocated. (File.xls) -specifies the file containing the book. Sheet! -specifies the ñame ofthe spreadsheet. ColumnFile -corresponds to the basic reference of the cell.

14. Remote Reference Remote reference imports to the spreadsheet data from other applications.

15. 1.4 Range concept • Definition of Range A range is a specific group of cells. The cells that form the range is adjacent as in a column or a row, or in a rectangle formed by several rows and columns. But they can also contain non-adjacent cells. Moreover, a range can consist of a single cell. Non-Adjacent Cells Range A2:C2, C5:D7 Horizontal Range A2:C2 Block Range C5:D7 Vertical Range A6: A9 Single Cell Range, D10

16. Some of the actions that can be carried out after the ranges have been identified and selected are: • Entering Data. • Range name assignation. • Copy. • Move. • Fill up data. • Forrnat data. • Erase.

17. 1.5 Data Classification The basic classification of data in the spreadsheet is the following: • Constant Data • Formulas • Constant data Constant data do not alter their value and are automaticaliy entered into the cell. More common constant data: Numerical DataAlphanumerical Data — Numbers — Texts or labels — Date and time

18. Formulas Formulas are data made up of several elements that modify their value. The software identifies them by piacing a sign before them. • Significance of Numbers, Texts and Formulas Numbers are the raw material of spreadsheets. Labels or texts help identify the numbers and results.

19. 1.6 Microsoft Excel Characteristics

20. Screen Description Controls Minimize, Maximize, Reestablish and Close Title of the application, name of the book (maximized) Formula Bar Menu Bar Tool Bar Complete Sheet Seletion Button Active cell Column Headers Row Headers Scroll Bar Working area Worksheet labels (Chosen active sheet: Sheet1) Sheet Displacement Buttons Status Bar

21. Chapter 2Basic Spreadsheet Application Working Operations • Objective -Learn the basic operations to interact with an application developed to elaborate spreadsheets. • Goals -The student will acquire the knowledge and skills to create, save, open, close and edit workbooks. He will learn the options to manipulate spreadsheets.

22. 2.1 Manipulation of Workbooks Due to the undeniable power of the graphic operating system (Windows95 or 98), a great amount of software has been developed to operate in this setting, • Creating a Workbook When we start Excel application, a new blank book is opened by default with three sheets and the name Book1.xls. In order to create a new book, it is necessary only to display the File menu and select the New...option. The name Book2.xls will be assigned by the software and so on.

23. Saving a workbook When you need to save a document, just open the file menu and choose Save As..., there you will put the name you wish to your document, and select the place whee you want to save it. • Opening a workbook The sequence to open a book is: File, Open... Establish the search trajectory in the dialogue window. Select it with a click in the file list and then in the control Open (or double clicking the file name). • Navigating through books Whrn you have opened several books, the Window menu will display at the bottom a numbered list with the open files and the active book marked. To change the active book just select it from the list.

24. Workbook Properties It is important the user dedicates that the user dedicates a few minutes to complete htis record by hand summarizing the following data: • Descriptive title of the book • Matter • Author or authors • Comments In order to configurate the properties of an active book, follow the File, Properties, Summary sequence. Fill in the convenient information i nthe dialogue window.

25. 2.2 Manipulating Worksheets Books are made up of sheets. There are several types of sheets based on their content. For instance: • Spreadsheet • Graphic sheet • Dialogue sheet • Macro sheet • Programming module (Visual Basic) • Others The most common are the spreadsheet and and the graphic sheet. Each spreadsheet contains a large amount of cells, 256 columns X 65,536 rows = 16´777,216 cells.

26. Personalizing the number of Sheets in a New Book The sequence Tool, Menu, Options, General allows to personalize 1 to 255 the number of available sheets at the beggining of a book . • Inserting Sheets -Spreadsheet in the Insert menu. -Slide the mouse device over the label of a sheet. Click once with the right button in the mouse. The contextual menu appears. Select Insert… in the general dialogue window select Spreadsheet and the acceot control. • Eliminating Sheets -Slide the mouse pointer device over the label of the sheet you want to eliminate, click once with the right button of the mouse. The contextaul menu appears, select Eliminate. A warning box is displayed, select Accept. -From the menu, select Eliminate sheet. A warning box is displayed. Select the accept control.

27. The following table shows how to use the keyboard to navigate rapidly through a sheet.