20.1 Create a Data Model

2026 Syllabus Objectives

  • Create and edit a spreadsheet model: Insert cell(s), row(s) and column(s), delete cell(s), row(s) and column(s), merge cells
  • Create formulae using cell references
  • Replicate formulae using absolute and relative cell references where appropriate
  • Use arithmetic operators in formulae including add, subtract, multiply, divide, indices
  • Use named cells and named ranges
  • Use functions including sum, average, maximum, minimum, integer, rounding, counting, LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP, IF
  • Use external data sources within functions
  • Use nested functions
  • Formulae and functions: The difference between a formula and a function
  • Order of operations: The order in which mathematical operations are performed including the use of brackets to make sure that formulae work
  • Cell referencing: Characteristics and use of absolute and relative cell referencing

What is a Data Model? 🔑

https://cambridgeassistant.com

A spreadsheet model is used to explore different possible answers through data analysis. These models are often:

  • Financial
  • Mathematical
  • Scientific

This process is sometimes called "what if" modelling or "what if" scenario - it allows you to change data in the spreadsheet to see what will happen to the results.

Spreadsheet Basics

A spreadsheet is a two-dimensional table split into rows and columns, made up of individual cells.

Key terminology:

  • Cell: The intersection of a row and a column in a spreadsheet
  • Cell address: The unique identifier for a cell, consisting of the column letter and row number (e.g., A1\text{A1}, E9\text{E9})
  • Label: Text entered into a cell
  • Formula: An instruction to perform a calculation, which always starts with an == sign
  • Modelling: The process of changing cells to see the results of calculations

Important: Rows go across (horizontal) and columns go down (vertical). Cell C4\text{C4} is at the intersection of column C and row 4.

Creating and Editing a Spreadsheet Model

https://cambridgeassistant.com

Inserting and Deleting Elements

You can alter the structure of a spreadsheet by:

  • Inserting cells, rows, and columns
  • Deleting cells, rows, and columns
  • Merging cells - combines two or more cells into one larger cell (useful for creating headers or titles that span across multiple columns)

Adjusting Column Width

To expand column width:

  1. Move the cursor to the end of the column heading
  2. Double-click
  3. This automatically fits the column to the longest item stored

This is particularly useful when numbers display in scientific notation (e.g., 1.01×1081.01 \times 10^8) because the column is too narrow.

Sign in to view full notes