Create a data model Revision Notes

Creating a flexible spreadsheet model begins with managing its structure. You should be able to add or remove cells, rows, and columns to accommodate data changes. For example, if you have a sales table and need to add a new product, inserting a new row allows you to include this data without disrupting existing formulas.

Merging cells is useful for formatting purposes, such as creating a title that spans multiple columns. However, merging should be used sparingly as it can complicate data sorting and referencing. For instance, merging cells A1 to D1 to create a header 'Monthly Sales' improves readability but avoid merging cells that contain data to be calculated.

• Add or delete rows and columns to adjust data size.
• Insert or remove individual cells carefully to maintain formula integrity.
• Merge cells mainly for headings or labels, not for data cells.

Formulas in spreadsheets rely on cell references to perform calculations dynamically. Relative references (e.g., A2) change when copied to another cell, while absolute references (e.g., $A$2) remain fixed. For example, if cell B2 contains =A2*10 and you copy it down, the reference A2 changes to A3, A4, etc. But if you want to multiply all values by a fixed tax rate in cell C1, you use =A2*$C$1 to keep the tax rate constant.

Arithmetic operators include addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). For example, to calculate compound interest, you might use =P*(1+r)^n, where P is principal, r is rate, and n is number of periods.

Using brackets ensures correct order of operations. For example, =(A2+B2)*C2 adds A2 and B2 first, then multiplies by C2, rather than multiplying B2 by C2 first.

• Relative references adjust when copied; absolute references do not.
• Use $ to fix row, column, or both in absolute references.
• Apply +, -, *, /, and ^ operators for calculations.
• Use brackets to control calculation order.

Naming cells or ranges makes formulas easier to read and maintain. Instead of =A2*$C$1, naming cell C1 as 'TaxRate' allows the formula =A2*TaxRate, which is clearer and less error-prone.

To name a range, select the cells and assign a name via the spreadsheet’s name box or menu. For example, naming B2:B10 as 'SalesData' lets you use =SUM(SalesData) instead of =SUM(B2:B10).

Named ranges also help when ranges change size, as you can redefine the range without editing all formulas.

• Assign meaningful names to single cells or ranges.
• Use named ranges in formulas for clarity and ease of updates.
• Update named ranges if data size changes.

Functions perform predefined calculations. Common functions include:

SUM(range): adds all numbers in the range. Example: =SUM(B2:B10) totals sales.

AVERAGE(range): calculates the mean. Example: =AVERAGE(C2:C10) finds average score.

• MAX(range) and MIN(range) find highest and lowest values.
• COUNT(range) counts numeric entries; COUNTA counts non-empty cells.
• IF(condition, value_if_true, value_if_false) performs conditional logic. Example: =IF(A2>100, "Bonus", "No Bonus")
• LOOKUP functions (LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP) search for values in tables. For example, VLOOKUP searches vertically to find a matching value and return related data.
• Functions can be nested, e.g., =IF(SUM(A2:A5)>100, "High", "Low").
• Functions can reference external data sources if linked properly, allowing dynamic updates.

A formula is any calculation entered into a cell, which can include arithmetic operators and cell references, e.g., =A2+B2*2.

A function is a predefined operation with a specific syntax, e.g., =SUM(A2:A10).

Understanding the order of operations (BODMAS/BIDMAS) is essential: Brackets, Orders (exponents), Division and Multiplication (left to right), Addition and Subtraction (left to right). For example, =2+3*4 calculates as 2+(3*4)=14, not (2+3)*4=20, unless brackets are used to change the order: =(2+3)*4=20.

• Formulas combine operators and references; functions are built-in operations.
• Use brackets to override default operation order.
• Check formulas carefully to avoid logic errors.