InterviewSam
Log inStart free
Data Analytics
Hands-onBeginner 40 min· Analyst foundations · 2 of 10

Spreadsheets for analysis: formulas, pivots and charts

Analyse real order data in Excel or Google Sheets: write formulas, use SUMIFS and lookups, build a pivot table and make a clear chart, with every result checked.

A spreadsheet is the analyst's notebook. Even people who write SQL all day open a spreadsheet to look at data, test an idea or share a quick result. In this tutorial you will analyse a week-end of Brew & Bean orders with formulas, a pivot table and a chart.

You need Microsoft Excel (desktop or the free web version) or Google Sheets (free with a Google account). The formulas below work in both; where menus differ, both are given. Everything below uses English function names and commas between arguments. If your Excel is set to a regional format that expects semicolons, replace the commas with semicolons.

Load the data

Get the sample data into a sheet

  1. 1Open a new blank workbook (Sheets or Excel).
  2. 2Copy the CSV text from the next block into a plain text editor such as Notepad and save it as orders.csv.
  3. 3In Google Sheets choose File, Import, Upload, then pick the file. In Excel choose Data, From Text/CSV and load it.
  4. 4Check that Date shows as dates and Qty and Unit Price are numbers (right-aligned). If a column is left-aligned, it is text and formulas will fail; see *Cleaning messy data*.
  5. 5Name the sheet orders. Row 1 is the header and the data is in rows 2 to 13.
orders.csv
Order ID,Date,Item,Category,Qty,Unit Price,Status
1001,2026-10-01,Cappuccino,coffee,2,180,picked_up
1002,2026-10-01,Masala Chai,tea,1,90,picked_up
1003,2026-10-01,Butter Croissant,bakery,2,150,picked_up
1004,2026-10-02,Cold Brew,coffee,1,220,picked_up
1005,2026-10-02,Espresso,coffee,3,120,picked_up
1006,2026-10-02,Veg Grilled Sandwich,food,1,160,cancelled
1007,2026-10-03,Cappuccino,coffee,1,180,picked_up
1008,2026-10-03,Banana Bread,bakery,2,130,picked_up
1009,2026-10-03,Masala Chai,tea,4,90,picked_up
1010,2026-10-04,Filter Coffee,coffee,2,80,picked_up
1011,2026-10-04,Cold Brew,coffee,2,220,picked_up
1012,2026-10-04,Butter Croissant,bakery,1,150,cancelled

12 orders across 1 to 4 October 2026, with 2 cancelled. Columns: A Order ID, B Date, C Item, D Category, E Qty, F Unit Price (in rupees), G Status.

Your first formulas

A formula starts with =. It refers to cells like E2 (column E, row 2). Copy a formula down and the references shift with it: this is called a relative reference. Put a $ in front of a part to freeze it ($B$1): an absolute reference.

Add a header Line Total in H1. In H2 type the formula below, then copy it down to H13 (double-click the small square at the cell corner).

H2 (Line Total)
=E2*F2

H2 shows 360 (2 cappuccinos at 180). Copied down, H3 becomes =E3*F3 and so on.

Now summary numbers. Put them in a separate area, for example cells K1 to L6, with a label next to each. Four functions do most of the everyday work: SUM, AVERAGE, COUNT and the conditional versions SUMIFS, COUNTIFS and AVERAGEIFS, which only use rows that meet a condition.

Formulas to type and what they return

QuestionFormulaResult
Total of all lines=SUM(H2:H13)3040
Revenue from picked-up orders=SUMIFS(H2:H13,G2:G13,"picked_up")2730
Coffee revenue, picked up=SUMIFS(H2:H13,D2:D13,"coffee",G2:G13,"picked_up")1720
Number of cancelled orders=COUNTIFS(G2:G13,"cancelled")2
Average picked-up line=AVERAGEIFS(H2:H13,G2:G13,"picked_up")273
Cappuccinos sold=SUMIFS(E2:E13,C2:C13,"Cappuccino")3

In SUMIFS the first argument is what to add up, then pairs of (range to check, condition). All ranges must be the same size. The total of all lines (3040) includes cancelled orders, so it is not revenue. This is why you always decide what counts before summing.

Text, IF and lookups

IF(test, value_if_true, value_if_false) makes decisions. Use it to flag big orders. In I1 write Size and in I2:

I2 (Size)
=IF(H2>=500,"Large","Regular")

Row 6 (3 espressos, 360) is Regular. Row 12 (2 cold brews, 440) is also Regular. No line reaches 500 in this data; change the 500 to 400 to see Large appear.

A lookup fetches a value from another table using a key. Create a second sheet named menu with columns A Item, B Category, C Price and the eight Brew & Bean items. Then, in the orders sheet, find an item's price with VLOOKUP (works in every version) or XLOOKUP (newer Excel and Google Sheets).

Price from the menu sheet
=VLOOKUP(C2,menu!A:C,3,FALSE)
=XLOOKUP(C2,menu!A:A,menu!C:C)

Both return 180 for Cappuccino. In VLOOKUP, 3 is the column number to return and FALSE means exact match. Always use FALSE; the default can return wrong rows silently.

Hard-coded numbers and invisible text

Never type a calculated number by hand into a report; use a formula so it updates. And watch for text that looks like numbers or names with trailing spaces: "Cappuccino " does not match "Cappuccino" in a lookup. TRIM(C2) removes extra spaces.

Pivot tables: summarise in seconds

A pivot table groups rows and summarises them without any formulas. It answers "total of X by Y" questions and is the single most useful spreadsheet feature for analysts.

Build a revenue-by-category pivot

  1. 1Click any cell in your data (A1 to H13).
  2. 2Excel: Insert, PivotTable, OK. Google Sheets: Insert, Pivot table, Create.
  3. 3Rows: add Category.
  4. 4Values: add Line Total and make sure it says Sum (in Sheets, Summarize by SUM).
  5. 5Filters: add Status and choose only picked_up.
  6. 6Read the result: coffee 1720, bakery 560, tea 450. A food row does not appear because its only order was cancelled.
  7. 7Optional: put Date in Columns to see each category by day.
Pivot result
Category    Sum of Line Total
bakery      560
coffee      1720
tea         450
Grand Total 2730

Refreshing pivots

A pivot table does not update when the data changes. In Excel right-click it and choose Refresh. Google Sheets updates automatically. If you add rows, make sure the pivot's source range includes them; converting the data to a Table (Excel: Ctrl+T) makes the range grow by itself.

Charts that tell the truth

Choose the chart for the question. Comparing categories: a bar or column chart. Change over time: a line chart. Parts of a whole with 2 to 4 parts: a pie is acceptable, but a bar is usually clearer. Select the pivot table and insert a chart. Then make it readable: give it a title that states the finding ("Coffee is 63 percent of picked-up revenue"), label the axes, start bar charts at zero, and remove clutter such as heavy gridlines and 3D effects.

Check that headline: coffee is 1720 of 2730, which is 63.0 percent. You can compute it yourself with =1720/2730 formatted as a percentage. Better, use cell references so it updates.

Good habits

Spreadsheet hygiene

  1. Keep raw data on its own sheet and never overwrite it.
  2. One header row, one record per row, no blank rows, no merged cells in the data.
  3. Use formulas instead of typed totals.
  4. Label every summary number so a stranger understands it.
  5. Spot-check one total with a calculator.
  6. Keep a copy of the file before big changes (File, Make a copy).
Prompt · Get help with a formula
I am using [Excel or Google Sheets]. My data is in columns: [A Date, B Item, C Qty, ...] with headers in row 1 and data in rows 2 to [N]. I want [describe the result, e.g. total quantity for each item for October only]. Give me the exact formula, explain each argument in one line, and tell me what the result should be for this small example: [paste 3 or 4 rows].

What to expect: A good answer gives a formula that uses your real column letters. Test it on the example rows you pasted before trusting it on the full sheet.

Try it: Extend the analysis

1. Add a column Day with the formula =TEXT(B2,"ddd") to show Mon, Tue and so on. 2. Build a pivot table with Date in rows and Sum of Line Total in values, filtered to picked_up, then insert a line chart. 3. Which date had the highest revenue, and how much was it? Check your pivot against =SUMIFS(H2:H13,B2:B13,DATE(2026,10,3),G2:G13,"picked_up").

Key takeaways

  1. A formula starts with =; relative references move when copied, $ freezes them.
  2. SUMIFS, COUNTIFS and AVERAGEIFS answer most "total of X where Y" questions.
  3. Use VLOOKUP with FALSE or XLOOKUP to bring in values from another table.
  4. Pivot tables summarise data by group without formulas; refresh them after data changes.
  5. A good chart has a finding in its title, labelled axes and zero-based bars.
  6. Decide what counts (for example exclude cancelled orders) before you total anything.

Check yourself

Finishing the quiz marks the lesson as done.

1. Cancelled orders should not count as revenue. Which formula returns picked-up revenue from Line Total (H) and Status (G)?

2. You copy =E2*F2 from H2 down to H3. What does H3 contain?

3. Why should the last argument of VLOOKUP normally be FALSE?

4. You add new rows to the source data. In Excel the pivot table does not change. What should you do?

5. Which chart best shows revenue for 4 categories at one point in time?

Saved on this device. Create a free account to keep it on every device.

What data analysts doSQL for analysts

Practise this with Sam

A real mock interview, out loud, with an honest scorecard. Free to start.

Start free