How to Add Formulas to Excel After Importing a CSV

To add formulas after importing CSV to Excel, convert the imported range to an Excel Table (Ctrl+T) first, then write formulas with structured references — they auto-fill down new rows and survive the next import. A CSV gives you raw values; formulas turn those values into answers: totals by category, lookups against a price list, flags for overdue invoices, text cleanup. The trick is writing formulas that do not break when the data is replaced next week. (If you have not converted the file yet, the free CSV to Excel converter gets you a workbook in one step.)

Step 0: Make the Import a Table

Select any cell in the imported data and press Ctrl+T (tick "My table has headers"). This single step buys you three things:

1. Auto-expanding formulas. A formula in a Table column fills down automatically to new rows.
2. Structured references. =[@Amount]*[@Qty] is readable; =C2*D2 is not — and structured references keep working when columns move.
3. Refresh-proofing. When Power Query reloads the data, the Table resizes and formulas ride along instead of pointing at stale rows.

Do this before writing a single formula. Everything below assumes a Table.

The Essential Formula Patterns

Lookups: XLOOKUP (or VLOOKUP)

Match imported codes against a reference list — e.g., product names from a SKU table on another sheet:

=XLOOKUP([@SKU], Products[SKU], Products[Name], "Unknown SKU")

The fourth argument ("Unknown SKU") handles codes missing from the reference table instead of showing #N/A. On older Excel without XLOOKUP, VLOOKUP([@SKU], Products!$A:$B, 2, FALSE) does the same job.

Conditional totals: SUMIF / SUMIFS

Total the Amount column for one category:

=SUMIF(Data[Category], "Travel", Data[Amount])
=SUMIFS(Data[Amount], Data[Category], "Travel", Data[Date], ">="&DATE(2026,10,1))

Flags: IF with AND/OR

Flag overdue invoices:

=IF(AND([@Status]="Unpaid", [@DueDate]<TODAY()), "OVERDUE", "OK")

Date math: DATEDIF / EDATE

Days between order and ship dates: =[@ShipDate]-[@OrderDate], formatted as a number. Add 30 days to an invoice date: =EDATE([@InvoiceDate], 1) gives the same day next month.

Text cleanup: TEXTSPLIT / TRIM / UPPER

Split a "First Last" column: =TEXTSPLIT([@Name], " "). Normalize messy text: =TRIM(UPPER([@City])). These are safer than editing values by hand because they recompute on refresh.

Formulas That Survive the Next Import

The failure mode: next month's CSV has 400 rows instead of 250, and your totals reference C2:C250. Defenses:

• Put formulas in Table columns, not beside the data. Table columns grow with the data.
• Reference whole Table columns (Data[Amount]) instead of fixed ranges.
• Keep formulas on a separate sheet from the raw import when the import is a plain range (not a Table): =SUM(Import!C:C) with a header guard like =SUMIF(Import!A:A,"<>",Import!C:C) to skip blanks.
• Never hardcode row counts from this month's file. If a formula says C2:C250, it is a bug waiting for a bigger file.

Common Formula Errors on Imported Data

Numbers stored as text. Imported amounts that will not SUM are text. Convert with =VALUE([@Amount]) in a helper column, or fix the column type at import — the scientific-notation fix guide covers the type problem at its root.

Dates that are text. =[@Date]+30 on a text date gives #VALUE!. Convert with =DATEVALUE([@Date]) (mind the locale — see the date-format guide).

#N/A from lookups. Usually a type mismatch: the CSV key is text "1001", the reference is number 1001. Normalize one side with TEXT() or VALUE().

Trailing spaces. "Travel " does not equal "Travel". Wrap keys in TRIM() in both the lookup and the reference, or clean at import with Power Query.

Dynamic Array Formulas on Imported Data

Modern Excel's dynamic arrays (FILTER, SORT, UNIQUE, TEXTSPLIT) turn imported tables into live reports without pivot tables:

Filtered views. Show only this month's unpaid invoices on a summary sheet:

=FILTER(Data, (Data[Status]="Unpaid")*(Data[Month]=E1), "None found")

The result spills automatically and grows or shrinks with the data — no manual range adjustment when the import grows.

Sorted top-N. Top 10 customers by revenue, no pivot needed:

=TAKE(SORT(Data, 5, -1), 10)

(assuming revenue is the 5th column; -1 sorts descending).

Cross-table summaries. Total revenue per category in one formula:

=GROUPBY(Data[Category], Data[Amount], SUM)

GROUPBY (Excel 365) replaces a whole pivot table for simple aggregations. For older versions, the SUMIFS pattern from the previous section does the same job with more typing.

Chaining. Dynamic arrays compose: =SORT(UNIQUE(Data[Category])) gives a clean sorted category list for a dropdown, which can feed the data-validation lists from the validation guide. One formula becomes the source of truth for the whole workbook's categories.

Readable mega-formulas with LET. When a formula grows tentacles, name its parts:

=LET(inv, Data[InvoiceID], amt, Data[Amount],
     FILTER(inv, amt > 10000, "None"))

LET computes each name once and makes the logic self-documenting. Future-you will thank present-you.

Row numbering that survives sorting. =SEQUENCE(ROWS(Data)) in a helper column numbers every row; unlike dragging 1,2,3 down, it extends automatically when the Table grows. Pair it with SORT for ranked views: =HSTACK(SEQUENCE(ROWS(t)), SORT(t)) builds a numbered, sorted extract in a single spill — a self-updating leaderboard straight from the import.

Caveat: spill ranges need empty space below and to the right. A #SPILL! error means something blocks the output — clear the obstruction, do not rewrite the formula. And keep dynamic-array summaries on their own sheet, away from the raw Table, so refreshes never collide with spills.

FAQ

Why do my formulas break when I import a new CSV?

Because they reference fixed ranges from the old file. Convert the data to a Table (Ctrl+T) and use structured references (Data[Amount]), which resize with the data.

Should formulas live on the same sheet as the imported data?

Helper columns inside the Table are fine and travel with it. Summary formulas (totals, dashboards) are safer on a separate sheet referencing the Table — a re-import never overwrites them.

XLOOKUP or VLOOKUP for imported data?

XLOOKUP: simpler syntax, built-in if-not-found value, works left and right. VLOOKUP only if you share files with Excel 2019 or older.

How do I total only visible (filtered) rows?

Use =SUBTOTAL(109, Data[Amount]) — 109 means SUM ignoring hidden rows. Plain SUM totals everything regardless of filters.

Can formulas update automatically when Power Query refreshes?

Yes. Formulas referencing the Table recalculate on refresh. Keep calculation on Automatic (Formulas > Calculation Options) — Manual mode is the usual reason "formulas don't update."

Final Checklist

• Imported range converted to a Table (Ctrl+T) before any formulas written.
• Formulas use structured references, not fixed A1 ranges with row counts.
• Text-numbers and text-dates converted (VALUE/DATEVALUE) or fixed at import.
• Lookup keys type-matched and trimmed on both sides.
• Summary sheet separated from raw data; calculation set to Automatic.

If all five checks pass, your imported CSV now answers questions on its own — and keeps answering them after every refresh.