INDIRECT and OFFSET Equivalents in pandas

INDIRECT and OFFSET are the two Excel functions that build references while the workbook calculates. INDIRECT("'"&A1&"'!B5") reads cell B5 from whichever sheet is named in A1; SUM(OFFSET(B2,0,0,COUNT(B:B),1)) sums a range that grows as rows are added; AVERAGE(OFFSET(B2,COUNT(B:B)-3,0,3,1)) averages the last three values. They are clever, volatile — Excel recalculates them on every change — and hard to read. When such a workbook moves to Python there is no need for an equivalent of the function; what is needed is an equivalent of each job it does, and pandas has a direct, readable tool for every one. This page takes the common INDIRECT and OFFSET patterns, writes them as formulas in a workbook, recalculates them to get Excel-style answers, and reproduces each in pandas. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

indirect_offset.xlsx has a Summary sheet with two settings — a month name and a measure name — three monthly sheets (Jun, Jul, Aug) with sales and returns by region, and a Daily sheet of ten days of units. LibreOffice (soffice) recalculates the Excel formulas in Step 1 so the pandas versions can be checked against them.

Step 1 — The formulas being replaced

Write the typical INDIRECT and OFFSET formulas into the Summary sheet and recalculate, to have the spreadsheet's answers to compare against:

import subprocess
from openpyxl import load_workbook

FORMULAS = {
    "sheet named in B1, cell C3": "=INDIRECT(\"'\"&B1&\"'!C3\")",
    "sum of column named in B2": "=SUM(INDIRECT(\"'\"&B1&\"'!\"&IF(B2=\"Sales\",\"B\",\"C\")&\"2:\"&IF(B2=\"Sales\",\"B\",\"C\")&\"5\"))",
    "sum of growing range": "=SUM(OFFSET(Daily!B2,0,0,COUNT(Daily!B:B),1))",
    "average of last 3": "=AVERAGE(OFFSET(Daily!B2,COUNT(Daily!B:B)-3,0,3,1))",
    "value 2 rows below B4": "=OFFSET(Daily!B4,2,0)",
    "sales across sheets": "=SUMPRODUCT(SUM(INDIRECT(\"'\"&{\"Jun\",\"Jul\",\"Aug\"}&\"'!B2:B5\")))",
}
wb = load_workbook("indirect_offset.xlsx")
ws = wb["Summary"]
for i, (label, f) in enumerate(FORMULAS.items(), start=4):
    ws.cell(i, 1, label)
    ws.cell(i, 2, f)
wb.save("with_formulas.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "with_formulas.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/with_formulas.xlsx", data_only=True)["Summary"]
excel = {calc.cell(i, 1).value: calc.cell(i, 2).value for i in range(4, 4 + len(FORMULAS))}
for k, v in excel.items():
    print(f"{k:28} {v}")

Each formula is a typical use: reading a cell from a sheet whose name is in a settings cell, summing a column chosen by name, summing a range whose length follows the data, averaging the last three values, reading a value a fixed number of rows away, and summing the same range across several sheets. All of these are volatile in Excel. Look at the last result: the three month sheets hold 1,482 in sales between them, but LibreOffice returned 5,928 — four times as much — because INDIRECT with an array of sheet names inside SUMPRODUCT(SUM(...)) is evaluated differently from one spreadsheet program to another (the usual Excel idiom wraps a SUMIF instead). A formula assembled from text can be wrong in ways that are very hard to see, which is one more reason to replace it.

Step 2 — INDIRECT for sheets and columns: just index by name

In pandas, a sheet chosen by name is a dictionary lookup, and a column chosen by name is ordinary column selection:

import pandas as pd

sheets = pd.read_excel("indirect_offset.xlsx", sheet_name=["Jun", "Jul", "Aug"])
settings = pd.read_excel("indirect_offset.xlsx", sheet_name="Summary", header=None, index_col=0, nrows=2)[1]
month, measure = settings["Show month"], settings["Show measure"]

cell_c3 = sheets[month].iloc[1]["Returns"]                    # row 3 of the sheet = second data row
column_sum = sheets[month][measure].sum()
print("C3 of", month, "→", cell_c3, "| Excel:", excel["sheet named in B1, cell C3"])
print(f"sum of {measure} in {month} →", column_sum, "| Excel:", excel["sum of column named in B2"])

pd.read_excel(..., sheet_name=[...]) returns a dictionary of DataFrames keyed by sheet name, so "the sheet named in B1" is sheets[month]. "The column named in B2" is df[measure] — no need to translate a header into a column letter, which is what the IF(B2="Sales","B","C") part of the formula was doing by hand. Cell C3 becomes "the Returns value of the second data row", and here it is better to stop thinking in cell addresses altogether: sheets[month].set_index("Region").loc["South", "Returns"] says what the cell means. Reading every sheet at once is covered in Read All Sheets from an Excel File into DataFrames.

Step 3 — OFFSET for growing ranges and the last N values

OFFSET with COUNT exists to make a range follow the data. A DataFrame already is the data, however many rows it has, so most of these collapse to one method call:

daily = pd.read_excel("indirect_offset.xlsx", sheet_name="Daily")
total = daily["Units"].sum()
last3 = daily["Units"].tail(3).mean()
two_below_b4 = daily["Units"].iloc[2 + 2]                      # B4 is the third data row; two rows further down
print("sum of growing range →", total, "| Excel:", excel["sum of growing range"])
print("average of last 3 →", round(last3, 6), "| Excel:", round(excel["average of last 3"], 6))
print("value 2 rows below B4 →", two_below_b4, "| Excel:", excel["value 2 rows below B4"])

SUM(OFFSET(B2,0,0,COUNT(B:B),1)) is sum(); the range grows with the data automatically. AVERAGE(OFFSET(B2,COUNT(B:B)-3,0,3,1)) — "the last three values" — is tail(3).mean(). A single offset from a fixed cell is positional indexing with iloc, where B4 is the third data row (index 2) and two rows further down is index 4. One subtlety: COUNT counts numeric cells, so in Excel a stray text value in the column silently shortens the range; in pandas, tail(3) counts rows, and a text value would show up as a non-numeric column that refuses to average — a louder and better failure.

Step 4 — OFFSET inside a calculation: shift and rolling

The other common use of OFFSET is relative: the previous row, the next row, or a moving window, copied down a whole column. pandas does these for every row at once:

daily["Prev day"] = daily["Units"].shift(1)                    # =OFFSET(B3,-1,0) copied down
daily["Change"] = daily["Units"] - daily["Prev day"]
daily["3-day avg"] = daily["Units"].rolling(3).mean().round(2) # =AVERAGE(OFFSET(B4,-2,0,3,1)) copied down
print(daily.to_string(index=False))

shift(1) is "the cell one row up" for every row, with an empty value on the first row instead of a #REF! error. rolling(3).mean() is a three-row moving window ending at each row — the job of an AVERAGE(OFFSET(...,-2,0,3,1)) copied down the column — and its first two rows are empty because a full window is not yet available, which is honest rather than an average of fewer values. These are the same tools as Calculate Moving Averages with pandas rolling, seen from the Excel side.

Step 5 — The same range across many sheets

Workbooks with a sheet per month often use INDIRECT with a list of sheet names to sum or look up across them. In pandas, stack the sheets into one table with a column saying which sheet each row came from, and every cross-sheet question becomes a group-by:

long = pd.concat(sheets, names=["Month", "row"]).reset_index(level="Month").reset_index(drop=True)
print("all sales across sheets →", long["Sales"].sum(), "| spreadsheet formula:", excel["sales across sheets"])
print(long.pivot_table(index="Region", columns="Month", values="Sales", aggfunc="sum")[["Jun", "Jul", "Aug"]])
print("July sales only →", long.loc[long["Month"] == "Jul", "Sales"].sum())

pd.concat on the dictionary of sheets uses the sheet names as an extra index level, which becomes a Month column. The total, 1,482, is the correct sum of all three months — not the 5,928 the array formula produced — and the pivot shows each month and region separately, so the figure can be checked by eye. Asking for July alone is a filter rather than a carefully rebuilt text reference. Summing the same cell across sheets has its own page: Sum the Same Cell Across Sheets with 3D References.

Common pitfalls

• Translating INDIRECT literally. Building references from text is unnecessary; index by sheet and column name.
• Recreating cell addresses. Use labels — region, month, measure — instead of C3 and B4.
• COUNT-based ranges with stray text. Excel silently shortens them; check types in pandas.
• Plain shift across groups. Group first when rows belong to different series.
• Keeping a sheet per month. Stack sheets into one long table; cross-sheet formulas become group-bys.

Conclusion

INDIRECT and OFFSET let Excel build references at calculation time, but each of their jobs has a direct pandas tool: a sheet or column chosen by name is a dictionary or column lookup, a range that grows with the data is simply the column, the last N values are tail, a relative row is shift, a moving window is rolling, and a range repeated across sheets becomes one long table and a group-by. Check each translation against the workbook's recalculated formulas once, and the Python version is both easier to read and free of volatile recalculation.

Frequently asked questions

What is the pandas equivalent of Excel's INDIRECT? There is no need for one. Read the sheets into a dictionary with read_excel(sheet_name=None) or a list of names, then select the sheet with sheetsname and the column with dfcolumn_name.

How do I replace OFFSET with a dynamic range in pandas? A DataFrame column already contains all rows, so SUM(OFFSET(B2,0,0,COUNT(B:B),1)) is just df"col".sum(). For the last N values use df"col".tail(N).

What replaces OFFSET for the previous row? df"col".shift(1) gives the value from the row above for every row, with a missing value on the first row.

How do I sum the same range across several sheets in pandas? Concatenate the sheets with pd.concat, keeping the sheet name as a column, then group by or filter on that column.

Related

• Up one level: Excel Formula Equivalents in pandas — spreadsheet functions in Python.
• INDEX and MATCH Equivalent in pandas — the non-volatile lookup.
• Find Out Why an Excel File Is Slow to Open — the cost of volatile formulas.
• Running Totals and Year-over-Year Growth in pandas — more relative calculations.