The structure and purpose of spreadsheets

Spreadsheets are software tools designed to organise, store, and analyse data in a structured format. They allow users to perform tasks such as recording information, searching for specific items, sorting data, carrying out calculations, and generating visual representations like graphs.

Key features of spreadsheets

• Grid layout - Spreadsheets consist of tables divided into rows and columns, creating individual cells where data is stored.
• Cell references - Each cell is identified by a unique coordinate, combining the column letter (e.g., A, B, C) and row number (e.g., 1, 2, 3). For example, the cell at column B and row 3 is referenced as B3.
• Data processing - Spreadsheets can sort data in ascending order (e.g., A to Z or 1 to 10) or descending order (e.g., Z to A or 10 to 1), often by selecting a specific column or row.

Types of data and methods for entering information

Cells in a spreadsheet can hold different types of data, but each cell should contain only one type to ensure accurate processing. Mixing types can prevent calculations from working properly.

Main types of data in cells

• Numerical data - Numbers that can be used in calculations, such as dates, currency values, or measurements (e.g., 100 or $50).
• Text data - Non-numerical information, like names or titles (e.g., "John Smith" or "Favourite Colour").
• Formulas - Instructions starting with an equals sign (=) that perform operations on data from other cells (more on this later).

Avoid combining numerical and text data in one cell. For instance, entering "1000g" treats the entire cell as text, so the number 1000 cannot be used in maths operations.

Ways to enter data

• Data can be added manually or automatically to save time and reduce errors.

• Direct entry - Click on a cell and type the information straight into it, or use the formula bar (a text box at the top of the spreadsheet) to edit contents.

• Auto-fill - Select a cell with data or a pattern (e.g., numbers increasing by 1), then drag the small square handle at the bottom-right corner downwards or across to copy the content or continue the sequence into adjacent cells.

• Adjusting layout - Change column widths or row heights by clicking and dragging the lines between headers. Add headings in the first row or column to describe the data below or beside them, such as "Name" or "Score".

• Gridlines - These are the visible lines around cells, which are usually on by default but can be turned off for a cleaner look.

Formatting options to improve data presentation

Formatting enhances the readability and visual appeal of spreadsheets, helping to highlight important information without changing the data itself.

Common formatting techniques

• Text styles - Apply bold, italics, different fonts, or colours to make headings stand out or emphasise key values.
• Alignment - Adjust how content sits within a cell: horizontally (left, centre, or right) or vertically (top, middle, or bottom).
• Cell appearance - Add fill colours or shading to cells, and draw borders of various thicknesses around them to group related data or create tables.
• Conditional formatting - Automatically changes a cell's format based on its content, such as turning a cell red if a number is negative or below a certain value.
• Merging cells - Combine two or more cells into one larger cell, useful for creating titles that span multiple columns.
• Text wrap - Allows long text to spread over multiple lines within a cell, preventing it from overflowing into neighbouring cells.

Creating charts to visualise data

Charts turn numerical data into visual formats, making patterns and trends easier to spot. Spreadsheet software provides tools to generate them quickly.

Steps to create a chart

1. Organise your data into a single block, ideally in columns with headings (e.g., one column for categories like "Shoe Size" and another for values like "Number of Pupils").
2. Highlight the relevant data, including headings if they help label the chart.
3. Choose a chart type that suits the data (see below for examples).
4. Add a meaningful title that describes the chart's purpose, label any axes (e.g., x-axis for categories, y-axis for values), and include a key (legend) if needed to explain colours or symbols.

Types of charts and their uses

• Bar chart - Uses rectangular bars to compare categories, such as showing the number of pupils with each shoe size. Ideal for discrete data.
• Line graph - Connects data points with lines to show changes over time, like temperature fluctuations throughout a day. Best for continuous data.
• Scatter graph - Plots individual points to reveal relationships between two variables, such as ice cream sales against temperature. Useful for spotting correlations.
• Pie chart - Divides a circle into slices to show proportions of a whole, like the popularity of different fruit flavours. Suitable for percentage-based data.

Choosing the right chart avoids confusion; for example, a pie chart cannot effectively show how values change over time.

Using formulas and functions for calculations

Formulas and functions are what make spreadsheets powerful for automation. They perform calculations on data and update automatically if the input changes.

Basics of formulas

• A formula is a set of instructions that processes data from specific cells, always starting with an equals sign (=).
• Use standard maths operators: + for addition, – for subtraction, * for multiplication, and / for division.
• Reference cells in formulas (e.g., =A2 + B2 adds the values in cells A2 and B2).
• If data in referenced cells changes, the formula recalculates instantly, keeping results up to date.

Built-in functions

Functions are pre-programmed formulas for common tasks. They can be typed directly or selected from a menu.

Here are some useful functions with examples based on a range of exam scores in cells C3 to C5:

The VLOOKUP function

VLOOKUP (vertical lookup) searches a table for a value and returns related information from another column. It's useful for tasks like matching scores to grades.

Formula for VLOOKUP:

=VLOOKUP(lookup_value, table_range, column_number)

Where:

• lookup_value = The value to search for (e.g., a score in cell B1).
• table_range = The area of the table to search (e.g., A5:B8), with the first column sorted in ascending order for approximate matches.
• column_number = The column from which to return the result (e.g., 2 for the second column).

Add "FALSE" at the end for exact matches only (e.g., =VLOOKUP(B1,A5:B8,2,FALSE)), and the table doesn't need sorting.

For example, in a table where scores 0-29 get "None", 30-49 get "Bronze", 50-79 get "Silver", and 80+ get "Gold", entering 65 in B1 would return "Silver" using VLOOKUP. This function automates lookups, saving time in large datasets.