Swipe left or tap Next ▶ to turn the page.
Select any text to highlight it or add a note. Tap a diagram to enlarge it.
Excel for Data Analysts: Formulas, Lookups, Conditional Sums, Pivot Tables and Charts
- ✔ Write formulas with the right cell references and predict their results
- ✔ Use VLOOKUP, XLOOKUP and INDEX-MATCH and explain when each one fails
- ✔ Answer business questions with IF, COUNTIFS, SUMIFS and AVERAGEIFS
- ✔ Build a pivot table and pick the right chart for the story
Excel in a data analyst's day
Almost every data analyst technical round in India starts with Excel, because it is the one tool every team already uses.
A data analyst takes raw data, cleans it, summarises it and tells people what it means. Even teams that use SQL or Power BI do their quick checks and meeting tables in Excel, so interviewers test whether you work fast and correctly in it.
The building blocks
- A workbook is the whole file (for example
sales.xlsx). - A worksheet (sheet) is one tab inside the workbook.
- A cell is one box, named by its column letter and row number, like
C5. - A range is a block of cells, like
A2:H13(from A2 to H13).
A modern worksheet has 1,048,576 rows and 16,384 columns (the last column is XFD).
Our practice dataset
All through today we will use one small sheet called Sales, a stationery shop's orders for January and February 2025. Learn its shape once and every example will make sense.
| Row | A: Order | C: Region | D: Rep |
|---|---|---|---|
| 2 | 1001 | North | Riya |
| 3 | 1002 | South | Arjun |
| 4 | 1003 | West | Riya |
| 5 | 1004 | North | Kabir |
| 6 | 1005 | East | Meera |
| 7 | 1006 | South | Arjun |
| 8 | 1007 | West | Kabir |
| 9 | 1008 | North | Riya |
| 10 | 1009 | East | Meera |
| 11 | 1010 | South | Kabir |
| 12 | 1011 | West | Meera |
| 13 | 1012 | North | Arjun |
The remaining columns are on the next page. Column B holds the order date.
The rest of the Sales sheet
Every worked example today points back to this table, so keep this page open in your head.
Columns E to H tell us what was sold and for how much. Column H is not typed in; it is a formula, =F2*G2, copied down to row 13.
| Row | E: Product | F: Qty × G: Price | H: Amount (₹) |
|---|---|---|---|
| 2 | Pen | 50 × 20 | 1,000 |
| 3 | Notebook | 30 × 45 | 1,350 |
| 4 | Bag | 4 × 750 | 3,000 |
| 5 | Notebook | 20 × 45 | 900 |
| 6 | Pen | 100 × 20 | 2,000 |
| 7 | Bag | 6 × 750 | 4,500 |
| 8 | Pen | 40 × 20 | 800 |
| 9 | Bag | 3 × 750 | 2,250 |
| 10 | Notebook | 50 × 45 | 2,250 |
| 11 | Pen | 75 × 20 | 1,500 |
| 12 | Bag | 5 × 750 | 3,750 |
| 13 | Notebook | 40 × 45 | 1,800 |
The dates in column B run from 03-Jan-2025 (row 2) to 25-Feb-2025 (row 13). Rows 2 to 7 are January orders and rows 8 to 13 are February orders.
We also use a second sheet, Emp, for lookups:
| A: EmpID | B: Name | C: Dept | E: Salary (₹) |
|---|---|---|---|
| E01 | Riya Sharma | Sales | 42,000 |
| E02 | Arjun Nair | Sales | 38,000 |
| E03 | Kabir Singh | Finance | 51,000 |
| E04 | Meera Iyer | HR | 45,000 |
| E05 | Sahil Gupta | IT | 60,000 |
Column D of Emp holds the city: Delhi, Kochi, Pune, Chennai and Bengaluru, in the same order. The data sits in A2:E6.
Row 7 has Qty 6 and Price 750.
Amount = 6 × 750.
Answer: ₹4,500, which matches H7.
Formulas and the order of operations
If you cannot predict what a formula returns before pressing Enter, you cannot check your own work.
A formula always starts with =. It can use numbers, cell references, operators and functions. Excel calculates it and shows the result, while the formula bar shows the formula.
Operators you must know
| Operator | Meaning | Example → result |
|---|---|---|
+ - * / |
arithmetic | =10+4*2 → 18 |
^ |
power | =2^5 → 32 |
% |
percent (divides by 100) | =200*15% → 30 |
& |
joins text | ="Order "&A2 → Order 1001 |
= <> < > <= >= |
comparison (gives TRUE/FALSE) | =H2>900 → TRUE |
Precedence (highest first)
- Brackets
( ) - Negation (a minus sign in front, as in
-2) - Percent
% - Power
^ *and/, left to right+and-, left to right&(join)- Comparisons
Power first: 2^2 = 4.
Then multiply: 4 × 4 = 16.
Then add: 10 + 16.
Answer: 26.
Price in G4 is 750.
Formula
=G4*(1+18%). The bracket gives 1.18.750 × 1.18 = 885.
Answer: ₹885.
What does =-3^2+1 return in Excel?
Negation comes before power in Excel, so (−3)^2 = 9, and 9 + 1 = 10.
Keep going with the full course
Day 1 has 22 pages. You have read the first 3.
See what's inside →