Computerized spreadsheets are essential tools for ICT Technicians in Kenya, enabling efficient data organization, analysis, and reporting across diverse sectors such as banking, healthcare, education, and public service. Mastery of spreadsheet creation and manipulation enhances productivity and supports decision-making processes in environments like county government offices and retail businesses. This chapter equips ICT Technicians with comprehensive skills to create, edit, and manipulate spreadsheet workbooks, ensuring data accuracy and effective presentation.

2.1 Creation of Computerized Spreadsheet Workbook

Creating a computerized spreadsheet workbook is a fundamental skill for ICT Technicians, enabling them to manage complex data sets systematically. In Kenya, institutions such as SACCOs and hospitals rely on well-structured spreadsheets to track financial records, patient data, and inventory. Understanding spreadsheet concepts and the interface elements allows technicians to design workbooks that are user-friendly and functional.

2.1.1 Spreadsheet concepts

Spreadsheets are digital grids made up of rows and columns used to store, organise, and analyse data. They allow users to perform calculations, create charts, and automate tasks through formulas and functions. In a SACCO, for example, a spreadsheet can hold member names, loan amounts in Ksh and repayment dates, so that interest and balances are calculated automatically, reducing manual errors and accelerating reporting.

Core concepts of a spreadsheet

• Grid structure: Data is stored in a grid of rows and columns, and every item sits in its own cell. This makes data easy to find, sort and compare.
• Formulas and functions: Formulas perform calculations using operators, while functions are predefined operations such as SUM or AVERAGE that simplify data processing.
• Automatic recalculation: When a value that a formula depends on is changed, the result updates immediately. A technician who corrects one loan amount does not need to recalculate the totals by hand.
• Organisation of files: Related data is grouped into worksheets inside one workbook, so that a single file can hold many linked data sets.
• Visual presentation: Data can be turned into charts and formatted tables, which makes results easier to explain to managers and clients.

Purpose of using spreadsheets

• Data Analysis: Enables rapid computation and summarisation of large data sets, critical for financial reporting in banks.
• Automation: Reduces repetitive tasks through formula application and macros, freeing time for strategic work.
• Data Visualisation: Supports creation of charts and graphs, aiding presentations in educational institutions.
• Record Keeping: Maintains historical data efficiently, useful in healthcare for patient monitoring.
• Decision Support: Provides real-time data insights to inform management decisions in retail businesses.

2.1.2 Elements of spreadsheet window

The spreadsheet window is the user interface where interaction with data occurs. Familiarity with its elements is crucial for efficient navigation and operation, especially in environments like universities where data entry speed affects administrative workflows. The window combines tools for commands, the data grid itself, and indicators that show what the program is doing.

Key elements of a spreadsheet window

• Title Bar: Displays the name of the workbook currently open, helping users identify their work among multiple files.
• Menu Bar: Contains drop-down menus or ribbon tabs for accessing commands like file operations, editing, and formatting.
• Toolbar: Provides quick access to commonly used tools such as save, undo, and formatting options.
• Name Box: Shows the address of the active cell, for example B4, and can be used to jump straight to another cell.
• Formula Bar: Shows the content of the active cell, allowing users to enter or edit data and formulas directly.
• Status Bar: Displays information such as the sum of selected cells or the current mode (for example Ready or Edit).
• Scroll Bars: Enable movement across large worksheets both horizontally and vertically.

Importance of interface familiarity

• Efficiency: Quick access to tools minimises time spent on routine tasks in busy offices such as county government units.
• Accuracy: Understanding where and how to input data reduces errors during entry and editing.
• Navigation: Smooth movement across worksheets enhances productivity when handling multiple data sets like inventory and sales.
• Customisation: Users can tailor toolbars and views to match specific tasks, improving workflow in banking or hospital record systems.
• Error Detection: Visual cues within the interface help identify incorrect data or formula errors promptly.

2.1.2.1 Worksheet

A worksheet is a single page within a workbook, made up of cells arranged in rows and columns. Each worksheet handles a specific data set, such as sales figures, attendance records or a monthly loan register. In current versions of Microsoft Excel a worksheet has 1,048,576 rows and 16,384 columns, which is more than enough for most office records.

• Worksheet tabs: Tabs at the bottom of the window carry the names of the sheets and allow switching between them, facilitating organised data management.
• Active worksheet: Only one worksheet is active at a time, and the tab of the active sheet is highlighted.
• Separate purpose: Keeping one type of data per worksheet, for example one sheet for staff and another for equipment, makes a workbook easier to read and maintain.
• Independent grids: Each worksheet has its own grid, so cell B2 on one sheet is a different cell from B2 on another.
• Navigation buttons: Arrows beside the tabs help move to sheets that are hidden from view when a workbook has many sheets.

2.1.2.2 Workbook

A workbook is the file that contains one or more worksheets, enabling organised data management within a single file. It is the object that is saved, opened, emailed or backed up. For instance, a county government office may use one workbook to separate budget sheets by departments, so that all budget data travels together in one file.

• File container: The workbook is saved with a file name and a file extension such as .xlsx, which identifies it as a spreadsheet file.
• Multiple sheets: Related sheets, such as January, February and March sales, can be kept together and linked by formulas.
• Title bar name: The name of the open workbook appears in the title bar, which helps a user who has several workbooks open at the same time.
• Single saving action: Saving the workbook saves every worksheet inside it, so a technician only needs to manage one file.
• Sharing and backup: Because all related data is in one file, it is easier to back up on a flash disk or cloud storage and to share with colleagues.

2.1.2.3 Rows

Rows are the horizontal lines of cells in a worksheet, and each one is identified by a number in the row heading at the left edge of the grid (1, 2, 3 and so on). A row normally holds one complete record, such as all the details of one SACCO member or one stock item. Selecting the row heading selects the whole row, which is useful for formatting, deleting or copying a complete record.

• Row headings: The numbers at the left of the grid identify each row and help in referencing and navigating data.
• Records: In a well-designed table, each row after the heading row stores one record, for example one student's marks.
• Heading row: The first row of a table often holds the column titles, such as Name, Class and Fee Balance (Ksh).
• Row selection: Clicking a row heading highlights every cell in that row so that an action applies to the whole record.
• Row limit: Current versions of Microsoft Excel provide 1,048,576 rows in each worksheet.

2.1.2.4 Columns

Columns are the vertical lines of cells in a worksheet, labelled with letters in the column heading along the top of the grid (A, B, C, and after Z continuing with AA, AB and so on). A column normally holds one type of information, called a field, such as all member names or all loan amounts in Ksh. Keeping one kind of data in each column makes sorting, filtering and calculating much easier.

• Column headings: The letters across the top identify each column and help in referencing and navigating data.
• Fields: Each column stores one field, for example Date of Payment, so that values in the column are consistent.
• Column selection: Clicking a column heading selects every cell in that column, which allows quick formatting of a whole field.
• Calculations down a column: Totals and averages are often calculated over a column, for example the total of all fees paid.
• Column limit: Current versions of Microsoft Excel provide 16,384 columns, ending at column XFD.

2.1.2.5 Cells

A cell is the intersection of a row and a column and is the basic unit where data is entered. Each cell has an address made from its column letter and row number, so the cell in column C and row 5 is C5. Cells can contain text, numbers, formulas, or functions. For example, in a school fees workbook, cell D5 might hold a fee balance in Ksh while cell D20 holds a function that adds all the balances above it.

• Cell address: The column letter followed by the row number uniquely identifies the cell and is displayed in the Name Box.
• Active cell: The cell currently selected is outlined with a thick border, and anything typed goes into it.
• Cell content: The content of the active cell is displayed in the formula bar, where it can be entered or edited.
• Cell range: A group of neighbouring cells, such as B2:B10, can be selected and treated together.
• Displayed value and stored content: A cell with a formula shows the result of the calculation, while the formula bar shows the formula itself.

Chapter Summary

This chapter focused on the manipulation of computerized spreadsheets, beginning with the creation of spreadsheet workbooks and an understanding of fundamental spreadsheet concepts. It detailed the elements of the spreadsheet window, including worksheets, workbooks, rows, columns, and cells, highlighting their roles in organizing data. The chapter then explored various types of cell referencing such as relative, absolute, and mixed, which are essential for accurate data manipulation and formula application. Editing features of spreadsheets were covered extensively, including inserting and removing rows and columns, adjusting dimensions, and managing worksheets through renaming, moving, copying, and deleting. Data entry and the different data types used in spreadsheets were also discussed to enhance effective data handling. The use of formulas and functions was explained, emphasizing their syntax and categories including arithmetic, logical, and lookup functions, alongside worksheet formatting options like font styles, alignment, borders, shading, headers, and footers. Finally, the chapter addressed chart generation, covering types of charts, insertion, labeling, editing, and the procedures for setting up and printing the spreadsheet workbook.