Lesson 1 of 4

Foundations: structure, references and essential formulas

2 min read

Excel (and Google Sheets) is the most widely used business tool in the world. Operations, finance, HR, sales, marketing and analytics roles all test it, often with a practical task in the interview.

Structure data properly

One header row with clear column names; one record per row, one type of data per column.
No blank rows or merged cells inside data; dates as real dates, numbers as numbers (not text).
Convert ranges to Tables (Ctrl + T): they expand automatically, keep formatting, and use readable structured references (=SUM(Sales[Amount])).
Keep raw data, calculations and reports on separate sheets.

Cell references

Relative (A1): changes when copied.
Absolute ($A$1): fixed when copied; use it for rates, totals or lookup ranges. Press F4 to toggle.
Mixed ($A1, A$1): fix only the column or the row (useful in multiplication grids and tax tables).

text

Commission in C2:   =B2*$F$1     (F1 holds the commission rate; copying down keeps F1 fixed)

Essential functions

Purpose	Functions
Totals and averages	`SUM`, `AVERAGE`, `MIN`, `MAX`, `COUNT`, `COUNTA`, `ROUND`
Conditions	`IF`, `IFS`, `AND`, `OR`, `IFERROR`
Conditional totals	`SUMIF(S)`, `COUNTIF(S)`, `AVERAGEIF(S)`
Text	`TRIM`, `PROPER`, `UPPER`, `LEFT`, `RIGHT`, `MID`, `LEN`, `FIND`, `TEXTJOIN`, `CONCAT`, `SUBSTITUTE`
Dates	`TODAY`, `DATEDIF`, `EOMONTH`, `NETWORKDAYS`, `WEEKDAY`, `TEXT(date,"mmm-yy")`

text

=IF(C2>=60,"Pass","Fail")
=IFS(C2>=90,"A",C2>=75,"B",C2>=60,"C",TRUE,"Fail")
=SUMIFS(Sales[Amount], Sales[City], "Pune", Sales[Month], "Oct")
=COUNTIFS(Leads[Status], "Converted", Leads[Source], "Referral")
=NETWORKDAYS(B2, C2, Holidays)      working days between two dates

Check yourself

What's the difference between $A$1 and A$1?
Write a formula that counts orders from Mumbai above ₹5,000.
Why should you avoid merged cells in a data table?