2.1 Creation of Computerized Spreadsheet Workbook

Creating a computerized spreadsheet workbook is the first step in using spreadsheet software for data management and analysis. A software developer may use a workbook to log bugs found during testing, track hours spent in each sprint or compare the response times of different builds. Understanding the core concepts and the elements of the spreadsheet window improves productivity and reduces errors during data entry and processing.

2.1.1 Spreadsheet concepts

A spreadsheet is a digital tool for storing, organising and manipulating data in a tabular format. It is made of rows and columns that intersect to form cells, and each cell can hold a label, a number or a formula. Because results are recalculated automatically when the data changes, a spreadsheet is far more flexible than a printed table. Example: a developer who records the number of defects found in each module can see the total update the moment a new figure is typed.

Key spreadsheet concepts

• Range: A group of adjacent cells selected together for operations such as formatting or calculations. For example, the cells A2 to A10 holding module names form one range, written A2:A10.
• Formula: An expression entered into a cell to perform a calculation, which always begins with the equals sign (=). Writing the rules of formulas is covered in the topic on formulas and functions.
• Automatic recalculation: When a value used by a formula is changed, the result is updated immediately without retyping. This keeps reports accurate when figures such as hours worked are corrected.
• Tabular structure: Data is arranged in a grid, with each column normally holding one kind of information (for example, task name) and each row holding one record (for example, one task).
• What-if analysis: Because results update instantly, a user can change an input, such as the estimated hours for a task, and observe the effect on the project total.

Purpose of spreadsheets in software development

• Organising data: Large amounts of project data, such as task lists, test cases and user stories, can be stored systematically in rows and columns.
• Fast, accurate calculation: Totals, averages and percentages are produced in seconds, for example the average number of bugs fixed per week.
• Trend analysis: Patterns such as rising defect counts across releases can be identified to support decisions.
• Reporting: Data can be turned into tables and charts for progress reports and presentations to a project supervisor.
• Sharing and collaboration: A workbook can be shared so that team members update the same task tracker or timesheet.

Examples of spreadsheet software

• Microsoft Excel: A widely used desktop application that provides a full range of formula, formatting and charting tools.
• Google Sheets: A web-based application that allows several users to edit the same workbook at the same time.
• LibreOffice Calc: A free, open-source application that can open and save workbooks in common spreadsheet formats.

2.1.2 Elements of spreadsheet window

The spreadsheet window is the working area on the screen through which a user enters, edits and views data. Knowing where each element is and what it does allows a user to move quickly around a workbook and avoid mistakes. The elements below are described using the layout of Microsoft Excel, and other spreadsheet programs have very similar features.

2.1.2.1 Worksheet

A worksheet is a single page within a workbook, made up of a grid of cells arranged in rows and columns. It is where data is typed, calculated and displayed. Each worksheet has a name shown on its tab at the bottom of the window, such as Sheet1. Example: a developer may use one worksheet for the list of tasks in a sprint and a second worksheet for the test results of the same project, keeping related data together but separate.

2.1.2.2 Workbook

A workbook is the spreadsheet file itself, and it contains one or more worksheets. When a user saves their work, the whole workbook is saved as one file, for example with the extension .xlsx in Excel. Grouping worksheets in one workbook keeps related data together. Example: a workbook named Project_Tracker may hold separate worksheets for January, February and March timesheets.

To create and save a new workbook:

1. Open the spreadsheet program, for example Microsoft Excel, from the Start menu or desktop shortcut.
2. Choose Blank workbook on the start screen. A new workbook opens with an empty worksheet and the cell pointer in cell A1.
3. Type column headings in the first row, such as Task, Developer and Hours, so that each column has a clear purpose.
4. Enter the data for each record in the rows below the headings, pressing Enter to move down or Tab to move to the next cell on the right.
5. Click the File tab and choose Save As (or Save for a new workbook), then select the folder where the file will be stored.
6. Type a meaningful file name in the File name box, for example Sprint_Tasks, and confirm that the file type is Excel Workbook (.xlsx).
7. Click Save. The file name now appears in the title bar, and later changes can be saved quickly with Ctrl + S.

2.1.2.3 Rows

A row is a horizontal line of cells running across the worksheet. Rows are identified by numbers (1, 2, 3 and so on) shown in the row headings at the left of the window. In Microsoft Excel a worksheet has 1,048,576 rows. Example: in a bug-tracking worksheet, row 1 may hold the headings and each row below it may record one bug, so that all the details of that bug are read across a single row.

2.1.2.4 Columns

A column is a vertical line of cells running down the worksheet. Columns are identified by letters (A, B, C ... Z, then AA, AB and so on) shown in the column headings at the top of the window. In Microsoft Excel a worksheet has 16,384 columns, ending at column XFD. Example: in a timesheet, column A may hold the date, column B the task name and column C the hours worked, so that all the values of one kind of information are read down a single column.

2.1.2.5 Cells

A cell is the box formed where a row and a column meet, and it is the basic unit in which data is entered. Each cell has an address made from its column letter followed by its row number, so the cell in column C and row 5 is C5. The cell that is currently selected is called the active cell, and it is outlined by the cell pointer. Example: typing 8 into C5 of a timesheet records eight hours for that entry, and the value also appears in the formula bar while the cell is active.

Main components of the spreadsheet window

• Title Bar: Displays the name of the workbook and the software.
• Menu Bar or File tab: Gives access to commands such as opening, saving and printing a workbook.
• Toolbar/Ribbon: Provides quick access to commonly used tools such as formatting options and formulas, grouped into tabs such as Home, Insert and Formulas.
• Name Box: Shows the address of the active cell and can be used to jump to a cell by typing its address.
• Formula Bar: Shows the contents of the active cell and allows editing of formulas or data.
• Worksheet Tabs: Located at the bottom, enabling switching between worksheets within a workbook.
• Row and Column Headings: Labels that identify rows by numbers and columns by letters.
• Cell Pointer/Active Cell: Highlights the currently selected cell where data entry or editing occurs.
• Scroll Bars: Allow navigation through large worksheets horizontally and vertically.
• Status Bar: Displays the current mode (such as Ready or Edit) and quick summaries, such as the sum and average of selected cells.

Functionality of key elements

• Formula Bar: It allows users to enter or edit data and formulas directly, which is helpful when a long formula does not fit inside a narrow cell.
• Worksheet Tabs: They organise related data across several sheets, for example separating the test results of each module of a system.
• Toolbar/Ribbon: It shows the tools relevant to the task at hand, which speeds up work such as formatting a report.

Customisation options

• Customised toolbars: Users can add frequently used commands, such as Save or Sort, to the Quick Access Toolbar to speed up data entry.
• Freeze panes: This keeps header rows or columns visible while scrolling, which is useful in a test-case worksheet with hundreds of rows.
• Zoom controls: These adjust the view for better readability, depending on screen size or user preference.
• Hiding and showing elements: Gridlines, the formula bar and headings can be switched on or off from the View tab to give more working space or a cleaner display.
• Ribbon display: The ribbon can be collapsed to show more rows on a small laptop screen and expanded again when needed.

Accessibility features

• Keyboard shortcuts: Shortcuts for common actions, such as Ctrl + S to save and Ctrl + arrow keys to move to the edge of data, reduce reliance on the mouse.
• Screen reader compatibility: Spreadsheets can be read aloud by screen readers, which makes them usable by people with visual impairments.
• Zoom and readability: Magnification and larger font sizes help users with low vision to work comfortably.
• Descriptive names: Clear worksheet names and column headings help every user, including those using assistive technology, to understand the layout.

Chapter Summary

This chapter explored the creation of computerized spreadsheet workbooks by first introducing essential spreadsheet concepts and identifying key elements such as worksheets, workbooks, rows, columns, and cells. It then examined various types of cell referencing including relative, absolute, and mixed references, which are crucial for accurate data manipulation and formula application. The chapter detailed spreadsheet editing features like inserting and removing rows or columns, adjusting dimensions, and managing worksheets through actions such as renaming, moving, copying, and deleting. Data entry and the different types of data used in spreadsheets were explained to provide a solid foundation for effective data handling. In addition, the chapter covered formulas and functions, emphasizing syntax and key categories such as arithmetic, logical, and lookup functions. Formatting techniques were also discussed, highlighting font styles, alignment, borders, shading, and the use of headers and footers to enhance worksheet presentation. Finally, the chapter addressed chart generation, including types of charts, how to insert and label them, and concluded with guidance on print setup and printing of spreadsheet workbooks.