← Data Analyst
Day 1 · Excel for Data Analysts: Formulas, Lookups, Conditional Sums, Pivot Tables and Charts
Data Analyst · Crash Course
Day 1

Excel for Data Analysts: Formulas, Lookups, Conditional Sums, Pivot Tables and Charts

⏱ About 90 minutes · 22 pages
By the end of today you will be able to
  • ✔ 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
  1. Excel in a data analyst's day
  2. The rest of the Sales sheet
  3. Formulas and the order of operations

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.

Workbook: sales.xlsx Sheet "Sales" (one tab) cell C5 range A2:H13 Sales Emp Slabs
Data Analyst · Day 12 / 5

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.

✍️ Example 1 — Check the Amount column by hand
  1. Row 7 has Qty 6 and Price 750.

  2. Amount = 6 × 750.

  3. Answer: ₹4,500, which matches H7.

Data Analyst · Day 13 / 5

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)

  1. Brackets ( )
  2. Negation (a minus sign in front, as in -2)
  3. Percent %
  4. Power ^
  5. * and /, left to right
  6. + and -, left to right
  7. & (join)
  8. Comparisons
✍️ Example 2 — Predict `=10+4*2^2`
  1. Power first: 2^2 = 4.

  2. Then multiply: 4 × 4 = 16.

  3. Then add: 10 + 16.

  4. Answer: 26.

✍️ Example 3 — Price with 18% GST for row 4
  1. Price in G4 is 750.

  2. Formula =G4*(1+18%). The bracket gives 1.18.

  3. 750 × 1.18 = 885.

  4. Answer: ₹885.

🙋 Try it now

What does =-3^2+1 return in Excel?

Data Analyst · Day 14 / 5
Free preview ends here

Keep going with the full course

Day 1 has 22 pages. You have read the first 3.

See what's inside →