Formulas, Operators and Cell References
A formula always begins with an equals sign (=). Excel then calculates and shows the result. A function is a built-in formula such as SUM. Typing a + or - at the start also works, but = is standard.
Operators and order of operations
| Operator | Meaning |
|---|---|
+ - * / | Add, subtract, multiply, divide |
^ | Power |
% | Percent |
& | Joins text (concatenation) |
= <> > < >= <= | Comparison; result is TRUE or FALSE |
: | Range: A1:A10 |
, | Union: A1:A5,C1:C5 |
| space | Intersection: the cells common to two ranges |
Order of calculation (high to low): Brackets → % → ^ → × and ÷ → + and − → & → comparison. Operators of equal rank work left to right.
| Formula | Result | Why |
|---|---|---|
=2+3*4 | 14 | Multiplication first |
=(2+3)*4 | 20 | Brackets first |
=10-2-3 | 5 | Left to right |
=2^3^2 | 64 | (2^3)^2, left to right |
=-2^2 | 4 | Excel applies the minus sign before the power |
=50%*200 | 100 | 50% is 0.5 |
="J"&"K"&1 | JK1 | Text joining |
=5>3 | TRUE | Comparison |
Types of cell reference
| Type | Example | Behaviour when copied |
|---|---|---|
| Relative | A1 | Changes with the new position (default). |
| Absolute | $A$1 | Never changes. Both column and row are locked. |
| Mixed | $A1 or A$1 | Only the column or only the row is locked. |
F4 cycles the reference: A1 → $A$1 → A$1 → $A1 → A1.
Example. Cell B2 holds a price, and F1 holds a tax rate of 18%. In C2 write =B2*$F$1 and copy it down. The price reference moves (B3, B4 ...) but $F$1 stays fixed.
Other kinds of reference
| Reference | Form |
|---|---|
| Another sheet | =Sheet2!A1 |
| Another workbook | =[Book1.xlsx]Sheet1!$A$1 |
| 3-D reference (same cell across sheets) | =SUM(Sheet1:Sheet3!B2) |
| Named range | =SUM(Marks) after naming C2:C9 as Marks (Name Box, or Ctrl + F3 Name Manager, Ctrl + Shift + F3 create from selection) |
| Table (structured) | =SUM(Table1[Marks]) |
Wildcards in criteria
? matches one character, * matches any number of characters, and ~ cancels a wildcard (so ~* means a real star). They work in COUNTIF, SUMIF, MATCH, VLOOKUP and similar functions, not in IF.
The sample sheet used in all the examples
Every example in this unit uses this small table in A1:D9.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | District | Marks | Fee (₹) |
| 2 | Aamir | Srinagar | 78 | 2500 |
| 3 | Bilal | Jammu | 65 | 3000 |
| 4 | Charu | Jammu | 92 | 3000 |
| 5 | Danish | Anantnag | 55 | 2500 |
| 6 | Eram | Srinagar | 88 | 3500 |
| 7 | Farhan | Baramulla | 40 | 2500 |
| 8 | Gurpreet | Jammu | 71 | 3000 |
| 9 | Hina | Anantnag | 83 | 3500 |
Type it into a sheet and test every formula yourself. Total marks = 572, average = 71.5, total fee = 23,500.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.