How do I stop TEXT() from breaking when a referenced cell is empty?

Wrap the reference in `IF(ISBLANK(...), "", TEXT(...))` before concatenating. An empty `TEXT()` call returns "0" or a formatted zero, which produces broken sentences like "Revenue was $0.0M." Better to return an empty string and handle the blank case explicitly.

Can I build dynamic descriptions that reference dates from another tab?

Yes. `TEXT(Assumptions!$B$3, "MMM YYYY")` formats a date serial number from another tab into readable text like "Jun 2026". Combine with concatenation: `="As of "&TEXT(Assumptions!$B$3,"MMM YYYY")&", leverage stands at..."`. Make sure the source cell is actually a date serial - text-formatted dates won't work and will return a garbled number.

What's the character limit for formula-built strings in Google Sheets?

A single cell can hold up to 50,000 characters as of 2026. In practice, formula-generated strings rarely exceed a few hundred characters. The real constraint is formula length: Google Sheets caps formula text at 10,000 characters. If your concatenation formula exceeds that, break it into helper cells and join them.

How do I handle currency formatting for non-USD models in dynamic descriptions?

`TEXT()` uses locale-neutral format codes. For GBP millions, use `"£#,##0.0,,"` as the format string. For EUR, `"€#,##0.0,,"`. The currency symbol is just a literal character inside the format code, so you can substitute any symbol or abbreviation. For multi-currency models, store the currency symbol in an Assumptions cell and concatenate it: `=Assumptions!$B$1&TEXT(value,"#,##0.0,,")&"M"`.

Why does my IFS() description sometimes show the wrong tier?

`IFS()` stops at the first TRUE condition, so order matters. If your conditions overlap or the most restrictive check isn't listed first, lower thresholds will capture values meant for higher ones. Also check that your referenced cells return actual numbers - if a referenced cell contains text (common when importing actuals), comparison operators silently fail and `IFS()` may hit the catch-all `TRUE` case unexpectedly. --- [Try ModelMonkey free for 14 days](/install) - it works in both Google Sheets and Excel.

Google Sheets Dynamic Descriptions That Refresh (2026)

This isn't about auto-fill or data validation dropdowns. It's about building narrative strings directly from live model values using TEXT(), IF(), IFS(), and SWITCH() - so the language in your commentary tab stays synchronized with the P&L without anyone touching it.

How to Refresh Descriptions Automatically: TEXT() + & + IF()

The foundation is string concatenation. You pull numeric values, format them with TEXT(), and stitch the result into a sentence.

A basic variance comment for a quarterly board pack looks like this:

="Revenue was "&TEXT('P&L'!C14,"$#,##0.0,,\"M\""&" vs. budget of "&TEXT(Assumptions!$D$5,"$#,##0.0,,\"M\""")&", a "&TEXT(ABS('P&L'!C14-Assumptions!$D$5)/Assumptions!$D$5,"0.0%")&IF('P&L'!C14>=Assumptions!$D$5," favorable"," unfavorable")&" variance."

Output when revenue hits $4.20M against a $3.88M budget:

"Revenue was $4.2M vs. budget of $3.9M, a 8.2% favorable variance."

Change the P&L number and the sentence rewrites itself. No manual edit, no stale commentary, no explaining to the CFO why the slides say one thing and the model says another.

The key TEXT() format codes for finance work:

Value type	Format string	Output
Dollar millions	`"$#,##0.0,,"`	$4.2M
Dollar thousands	`"$#,##0"`	$4,200
Percentage (1 dp)	`"0.0%"`	8.2%
Multiplier	`"0.0x"`	3.5x
Basis points	`"0"&" bps"`	120 bps

According to Google Sheets' official function documentation, TEXT() applies the same number format codes used in cell formatting - so anything you can set via Format > Number > Custom also works inside a TEXT() formula.

IFS() for Tiered Commentary That Refreshes by Threshold

When the language needs to shift based on magnitude - not just positive/negative - IFS() handles it cleanly. This is useful for covenant compliance language, margin alerts, or budget variance tiers.

A leverage covenant description for a bank syndicate model:

=IFS(
  'Debt Schedule'!F22>4.5, "Leverage of "&TEXT('Debt Schedule'!F22,"0.0x")&" EXCEEDS the 4.5x covenant. Immediate remediation required.",
  'Debt Schedule'!F22>4.0, "Leverage of "&TEXT('Debt Schedule'!F22,"0.0x")&" is within covenant but approaching the 4.5x trigger. Headroom of "&TEXT(('Debt Schedule'!F22-4.5)*-1*'Debt Schedule'!F8,"$#,##0.0,,\"M\""")&".",
  TRUE, "Leverage of "&TEXT('Debt Schedule'!F22,"0.0x")&" is comfortably within covenant. $"&TEXT(('Debt Schedule'!F22-4.5)*-1*'Debt Schedule'!F8/1000000,"0.0")&"M headroom."
)

If the model shows 3.5x leverage against a $150M debt base, this outputs:

"Leverage of 3.5x is comfortably within covenant. $12.5M headroom."

Push the leverage assumption past 4.5x and the cell flips to the breach language automatically. According to Google Sheets documentation, IFS() evaluates conditions in order and returns the first TRUE result - which matters here because the order of thresholds is load-bearing. Flip the first two conditions and the "approaching" bucket never fires.

SWITCH() for Scenario-Labeled Descriptions That Refresh on Scenario Toggle

SWITCH() is cleaner than nested IF() when you're toggling between named scenarios. Most models have a scenario selector somewhere (Base / Upside / Downside); the description should reflect which one is active.

=SWITCH(
  Assumptions!$B$2,
  "Base",    "Base case assumes "  &TEXT(Assumptions!$C$8,"0.0%")&" revenue growth and "&TEXT(Assumptions!$C$12,"0.0%")&" EBITDA margin.",
  "Upside",  "Upside case assumes "&TEXT(Assumptions!$D$8,"0.0%")&" revenue growth and "&TEXT(Assumptions!$D$12,"0.0%")&" EBITDA margin.",
  "Downside","Downside case assumes "&TEXT(Assumptions!$E$8,"0.0%")&" revenue growth and "&TEXT(Assumptions!$E$12,"0.0%")&" EBITDA margin.",
  "Scenario not recognized. Check Assumptions!B2."
)

Change the dropdown in B2 and the description line on the cover page updates instantly. The fallback string at the end catches typos in the scenario selector before they silently produce blank commentary.

Building a Commentary Tab That Refreshes with Your Model

For a quarterly board pack or investor update, the right structure is a dedicated Commentary tab where every row is a formula-built description, not hand-typed text. Here's a layout that works in practice:

Row	Label	Formula output
5	Revenue	"Revenue of $4.2M exceeded budget by $320K (8.2% favorable), driven by..."
6	Gross Margin	"Gross margin of 38.5% was 2.9pp above prior year (35.6%)."
7	EBITDA	"EBITDA of $835K represents a 19.9% margin vs. 17.1% in the prior period."
8	Cash	"Ending cash of $2.1M reflects $340K operating outflow and $450K capex spend."
9	Covenant	"3.5x leverage is within the 4.5x covenant; $12.5M headroom."

Each cell in column C is a formula reaching into 'P&L', 'Balance Sheet', and 'Assumptions'. A model with 10-15 commentary points can run 8-12 formula-driven data points per line - specific enough to be useful, no manual editing required.

The practical constraint: commentary that requires judgment ("driven by strong enterprise wins in the Southeast") still needs a human. Formula descriptions handle the quantitative backbone; the analyst adds the qualitative layer where it matters. That's roughly 45 minutes of manual commentary work versus 3+ hours if every number were typed by hand.

Contribution Margin Commentary by SKU

The same pattern extends to product-level analysis. A contribution margin description by SKU, pulling from a product tab:

="SKU "&Products!A14&" contributed "&TEXT(Products!F14,"$#,##0")&" ("&TEXT(Products!F14/SUMIF(Products!A:A,"<>",Products!F:F),"0.0%")&" of total) at a "&TEXT(Products!G14,"0.0%")&" contribution margin. "&IF(Products!G14<Assumptions!$C$20,"Below the "&TEXT(Assumptions!$C$20,"0.0%")&" threshold.","Within target range.")

For a SKU generating $287K at 34.2% contribution margin against a 30.0% floor, this outputs:

"SKU PRD-042 contributed $287K (6.8% of total) at a 34.2% contribution margin. Within target range."

Cross-tab references like Products!F14 and Assumptions!$C$20 in the same formula are what make this work at model scale - single-tab formulas don't help when your data lives across 8+ sheets.

What Formula-Built Descriptions Don't Cover

A few real limits worth naming:

Period-over-period narrative that requires comparing two non-contiguous periods (e.g., Q3 2025 vs. Q1 2024) gets messy fast. You can do it, but the formulas get long enough to become unmaintainable.

Explanatory context is still manual. The formula can say "revenue missed by $280K." It can't say why. That last step - the actual analysis - belongs to the analyst.

Format length control is rough. TEXT() doesn't truncate, and some cells end up 200+ characters if you pack too much in. Keep descriptions to 1-2 facts per cell; let the tab structure carry the narrative.

If you need descriptions that go beyond formulas - pulling from external data, summarizing patterns across hundreds of rows, or generating the "why" not just the "what" - that's where ModelMonkey earns its spot. It reads your live sheet and generates commentary that's grounded in the actual numbers, not templated language.