Basic Rules

Topic 01

Basic Rules

Master the foundation of Excel — naming conventions, formula structure, functions, reference types, and order of operations. You'll apply every concept three ways using the same dataset, ending with an understanding of why Table references are the professional standard.

7 concepts 3 methods Practice file: basic.xlsx

Core Concepts

Seven building blocks you need to know before writing a single formula. Click any concept to expand.

Everything in Excel has a name. Understanding the hierarchy helps you communicate precisely and write formulas confidently.

  • Workbook — the Excel file itself (.xlsx). Can contain many sheets.
  • Sheet / Worksheet — a single tab inside the workbook. Each sheet has its own grid.
  • Column — vertical lines, identified by letters: A, B, C … Z, AA, AB …
  • Row — horizontal lines, identified by numbers: 1, 2, 3 …
  • Cell — the intersection of a column and row. Its address = column letter + row number → B3
  • Range — a group of cells. Contiguous: B2:B17 (colon = "to"). Multi-area: B2:B17,D2:D17
💡
Name Box
The box in the top-left corner of the screen shows the current cell address. You can click it and type a cell address or range name to navigate directly to it.

Every formula in Excel starts with =. After the equals sign, you combine cell references with math operators.

+
Addition
−
Subtraction
*
Multiplication
/
Division
^
Power / Exponent

Example — add the first row of sales across all products:

= B2 + C2 + D2 + E2
💡
Click, don't type
Instead of typing cell addresses, type = then click the cells. Excel fills in the references automatically and you avoid typos.

A function is a named, built-in formula. Instead of writing =B2+B3+B4+…+B17, you write:

= SUM(B2:B17)

The pattern is always: = FUNCTION_NAME ( arguments )

  • As you type a function name, Excel shows a dropdown suggestion list. Press Tab to select.
  • After the opening parenthesis, a tooltip appears showing the syntax and a description of each argument.
  • Click the function name in the tooltip to open full help documentation.
📌
Functions used in this topic
SUM · COUNTA · COUNTBLANK · MAX · MIN · AVERAGE

Some functions in Excel 365 return multiple values at once. Instead of requiring you to copy the formula, Excel automatically fills (spills) the results into adjacent cells.

  • A blue border highlights the entire spill range.
  • You only edit the top-left anchor cell. The rest are read-only shadows.
  • If the spill range is blocked by existing data, you get a #SPILL! error — clear the cells in the way.
  • Reference a spill range using the # operator: =SUM(A1#) sums everything the formula in A1 spilled.

When you copy a formula, Excel adjusts the cell references. Whether they move or stay fixed depends on the reference type.

  • Relative B2 — adjusts in both directions when copied. Most common type.
  • Absolute $B$2 — row and column both locked. Never moves.
  • Mixed — locked column $B2 — column B stays fixed, row adjusts.
  • Mixed — locked row B$2 — row 2 stays fixed, column adjusts.
💡
F4 shortcut
Click on a cell reference in the formula bar and press F4 to cycle through all four reference types. Press it multiple times to toggle.

Excel evaluates formulas using the standard mathematical order of operations. The acronym BODMAS helps you remember it:

  • Brackets ( ) — evaluated first, innermost first
  • Orders ^ — exponents / powers
  • Division / and Multiplication * — left to right
  • Addition + and Subtraction - — left to right

Without brackets — multiplication happens before addition:

= 2 + 3 * 4 → result: 14

With brackets — addition happens first:

= (2 + 3) * 4 → result: 20

Comparison operators compare two values and return TRUE or FALSE. They are the logic engine behind functions like IF, COUNTIF, and SUMIF.

=
Equal to
<>
Not equal to
>
Greater than
<
Less than
>=
Greater or equal
<=
Less or equal

Example — check if desktop sales in January exceed 50:

= B2 > 50 → TRUE or FALSE

The Training Dataset

Monthly sales data for 16 months (Jan-2024 to Apr-2025) across four products: Desktop, Laptop, Mouse, and Monitor. Some months have blank values — intentionally, to practice COUNTBLANK.

Download basic.xlsx
Months Desktop Laptop Mouse Monitor
Jan-202475152515
Feb-20241551010
Mar-20243541515
Apr-2024207205
May-202425253510
Jun-20244531455
… 10 more rows · 16 months total (Jan-2024 → Apr-2025)

8 Calculations to Complete

These same eight results will be produced three times — once per method. The answers must match.

# What to calculate Function to use Column
1Number of MonthsCOUNTAMonths
2Total Sale of DesktopSUMDesktop
3Months without Sales of LaptopCOUNTBLANKLaptop
4Highest Sales of MouseMAXMouse
5Lowest Sales of MonitorMINMonitor
6Average Sales of DesktopAVERAGEDesktop
750% of Total Sales of MonitorSUM × 0.5Monitor
8Total Sales (ALL products)SUMAll
Method 1

Cell References — Reference sheet

Open the Reference sheet. Column I shows the calculation labels; enter your formulas in column H. Use direct cell references — B2:B17, C2:C17, etc.

Calculation Formula (cell references)
Number of Months =COUNTA(A2:A17)
Total Sale of Desktop =SUM(B2:B17)
Months without Laptop Sales =COUNTBLANK(C2:C17)
Highest Sales of Mouse =MAX(D2:D17)
Lowest Sales of Monitor =MIN(E2:E17)
Average Sales of Desktop =AVERAGE(B2:B17)
50% of Total Sales of Monitor =SUM(E2:E17)*0.5
Total Sales (ALL) =SUM(B2:E17)
💡
Reading formulas like a human
B2:B17 means "from B2 to B17." When you see a colon, read it as "to." B2:E17 is a rectangle — all cells from B2 in the top-left to E17 in the bottom-right.
Method 2

Named Ranges — naming sheet

Switch to the naming sheet. The data is identical but each column has been given a name (e.g. Desktop, Laptop). Your formulas can use these names instead of cell addresses.

How to name a range

1
Select the range you want to name (e.g. B2:B17).
2
Click the Name Box (top-left, shows the cell address) and type the name — e.g. Desktop.
3
Press Enter to confirm. The name is now saved in the workbook.
4
Alternative: Formulas tab → Define Name for more options (scope, comment, exact range).
Calculation Formula (named ranges)
Number of Months=COUNTA(Months)
Total Sale of Desktop=SUM(Desktop)
Months without Laptop Sales=COUNTBLANK(Laptop)
Highest Sales of Mouse=MAX(Mouse)
Lowest Sales of Monitor=MIN(Monitor)
Average Sales of Desktop=AVERAGE(Desktop)
50% of Total Sales of Monitor=SUM(Monitor)*0.5
Total Sales (ALL)=SUM(Desktop,Laptop,Mouse,Monitor)
📌
Named ranges are workbook-wide
By default, a name is available in all sheets of the workbook. You can also scope a name to a single sheet using the Define Name dialog — useful when two sheets have columns with the same logical name.
Method 3

Table References — Table sheet

Switch to the Table sheet. The data has been converted to an Excel Table. Formulas now use structured references — they read like English and stay correct automatically.

How to convert data to a Table

1
Click anywhere inside your data range.
2
Press Ctrl + T (or Insert → Table).
3
Confirm the range and check "My table has headers" if your top row contains column names.
4
Give the table a meaningful name: Table Design tab → Table Name. In this file the table is named Sales.
Calculation Formula (table references)
Number of Months=COUNTA(Sales[Months])
Total Sale of Desktop=SUM(Sales[Desktop])
Months without Laptop Sales=COUNTBLANK(Sales[Laptop])
Highest Sales of Mouse=MAX(Sales[Mouse])
Lowest Sales of Monitor=MIN(Sales[Monitor])
Average Sales of Desktop=AVERAGE(Sales[Desktop])
50% of Total Sales of Monitor=SUM(Sales[Monitor])*0.5
Total Sales (ALL)=SUM(Sales[Desktop]:Sales[Monitor])
💡
Syntax: TableName[ColumnName]
Square brackets hold the column name. Sales[Desktop] means "the Desktop column in the Sales table." Excel autocompletes column names as you type — press Tab to accept.

Why Table References Win

All three methods produce the same results today. But Tables have structural advantages that matter as your data grows.

Dynamic range Add a new row to the table and every formula that references it automatically includes the new data — no manual range update needed.
Enforced structure New columns inherit the same formatting and formulas. Calculated columns auto-fill for every row without copying.
Readable formulas Sales[Desktop] is self-documenting. Six months later you'll know what it means. B2:B17 tells you nothing about the data.
Avoid Merge & Center Merged cells break sorting, filtering, and formulas. Tables encourage proper column headers instead of visual merging tricks.
✅
Professional standard
In any production spreadsheet — whether for reporting, analysis, or Power BI import — always start with a properly named Table. It costs nothing extra and prevents dozens of future problems.