Spreadsheets for Analysts

By the end of this lesson you will know how analysts use spreadsheets differently from everyone else: tidy tables, a few powerful formulas, PivotTables for summaries, and clear rules about when to move to SQL or Python. You will also see the same summary built in pandas, so you can connect the two worlds.

Almost every manager in India opens Excel before anything else. Monthly MIS reports, targets, budgets and attendance sheets all live in spreadsheets. So even if you love SQL and Python, you must be comfortable sending and receiving spreadsheets. This lesson covers the analyst's core. The Excel course teaches every feature in depth.

Rule 1: keep data in a tidy table

A spreadsheet is like a school register. It works because every row is one student and every column is one piece of information, written the same way every day. Analysts keep data the same way:

1. One row per record (one order, one employee, one day).
2. One column per field, with a single header row.
3. No merged cells, no blank rows, no subtotals inside the data.
4. Each column holds one type: dates in a date column, numbers in a number column.
5. Reports go on a separate sheet from the raw data.

In Excel, select the data and press Ctrl + T to make it an Excel Table. Tables grow automatically when you add rows, and formulas can refer to columns by name. See Excel Tables.

Rule 2: learn a few formulas very well

Analysts use a small set of formulas again and again. Suppose the orders table is called Sales, with columns City, Category, Quantity, UnitPrice and Revenue.

Total revenue for Pune:
=SUMIFS(Sales[Revenue], Sales[City], "Pune")

Total revenue for Pune, Electronics only:
=SUMIFS(Sales[Revenue], Sales[City], "Pune", Sales[Category], "Electronics")

Number of orders with a 20% discount:
=COUNTIFS(Sales[Discount], 20)

Average order value for Kochi:
=AVERAGEIFS(Sales[Revenue], Sales[City], "Kochi")

SUMIFS adds up the first range only for rows where every condition is true. COUNTIFS counts those rows, and AVERAGEIFS averages them. These three formulas answer a huge number of business questions. Learn them in COUNTIF, SUMIF & SUMIFS.

To bring a value from another table, such as the target for each city, use XLOOKUP:

Target for the city in cell A2, looked up in the Targets table:
=XLOOKUP(A2, Targets[City], Targets[Target], "No target")

The last argument is what to show when the city is not found. Older workbooks use VLOOKUP for the same job, but XLOOKUP can look in any direction and does not break when columns are inserted. See VLOOKUP & XLOOKUP.

Rule 3: summarise with a PivotTable

A PivotTable groups and totals a table without any formulas. It is the spreadsheet version of GROUP BY in SQL and groupby in pandas.

1. Click anywhere inside the data table.
2. Choose Insert, then PivotTable, then New Worksheet.
3. Drag City to Rows.
4. Drag Category to Columns.
5. Drag Revenue to Values (it shows "Sum of Revenue").

What you should see: a grid with cities down the side, categories across the top and total revenue in each cell, with grand totals. Here is the same summary in pandas, so you can see what the PivotTable produces:

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
sales = sales[sales["quantity"] > 0]
sales["city"] = sales["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"] / 100)

pivot = sales.pivot_table(index="city", columns="category", values="revenue",
                          aggfunc="sum", margins=True, margins_name="Total")
print((pivot / 1000).round(0).astype(int).to_string())

category   Books  Electronics  Fashion  Groceries  Home & Kitchen  Sports  Total
city
Bengaluru      7           43       73         24              53      10    210
Chennai       11           49       40         14              56       9    179
Delhi          5           70       78         35              88      19    295
Hyderabad      5           49       80         15              42      15    205
Jaipur         2           62       11         10              21       7    114
Kochi          6           22       31         13              16       6     94
Kolkata        5           40       44         14              38       9    149
Lucknow        5           35       22          8              41      15    128
Mumbai         9           99      100         35              46      28    317
Pune          57           34       51         12              48      17    218
Total        112          503      530        178             450     135   1908

What you should see: revenue in thousands of rupees for every city and category, with totals on the right and at the bottom. That is exactly what the PivotTable shows. The margins=True option adds the totals, like the "Grand Total" row in Excel.

Rule 4: check your formulas

Spreadsheet mistakes are easy to make and hard to see. A formula copied one row too far, or a range that misses the last 50 rows, can quietly change a total. Simple checks catch most errors:

Check 1: does the pivot total match the raw total?
=SUM(Sales[Revenue])

Check 2: how many rows have a blank quantity?
=COUNTBLANK(Sales[Quantity])

Check 3: how many orders have a quantity below 1?
=COUNTIFS(Sales[Quantity], "<1")

Here are the same three checks in pandas. When two tools agree, you can trust the number much more.

import pandas as pd

sales = pd.read_csv("sales.csv")
print("Blank quantities:", sales["quantity"].isna().sum())
print("Quantity below 1:", (sales["quantity"] < 1).sum())
print("Rows:", len(sales), "  Distinct order IDs:", sales["order_id"].nunique())

Blank quantities: 6
Quantity below 1: 1
Rows: 1217   Distinct order IDs: 1209

What you should see: six blank quantities, one negative quantity, and fewer distinct order IDs than rows. That last check reveals duplicate orders. In Excel you would find them with Data, then Remove Duplicates, but only after checking that they really are duplicates.

When a spreadsheet is the right tool, and when it is not

Excel worksheets have a limit of 1,048,576 rows. Long before that, large files become slow. Power Query, inside Excel and Power BI, is a good middle step: it records cleaning steps so they can be repeated. See Power Query Basics.

Common mistakes

• Mixing data and report on one sheet. Keep raw data untouched on its own sheet.
• Typing numbers into formulas. =B2*0.18 hides the 18%. Put rates in a labelled cell and refer to it.
• Merged cells in data. They break sorting, filtering and PivotTables.
• Not checking totals. Always compare the pivot total with the raw total.

Key points

• Keep data as a tidy table: one row per record, one header row, no merged cells.
• SUMIFS, COUNTIFS, AVERAGEIFS and XLOOKUP answer most everyday business questions.
• A PivotTable is the spreadsheet version of GROUP BY; pandas pivot_table gives the same result.
• Check totals, blanks and duplicates before you share a spreadsheet.
• Move to SQL, Python or Power Query when data is large, repeated or needs joins.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
sales = sales[sales["quantity"] > 0]
pivot = sales.pivot_table(index="payment_mode", columns="category",
                          values="order_id", aggfunc="count", fill_value=0)
print(pivot)