Google Sheets D&D Tracker Formulas

Dungeon depth, spell slots, and D20 results for campaign sheets.

Dungeon crawl depth

Put depth 0-10 in A1. Torches lead deeper into the crypt.

=REPT("🕯️",MIN(A1,B1))&REPT("・",MAX(0,B1-A1))&"💀"

🕯️🕯️🕯️・・・・・💀

REPT MAX

D20 roll display

Put roll 1-20 in A1. Natural 20 and fumble get called out.

=IF(A1=20,"🎲 NAT 20!",IF(A1=1,"🎲 Fumble!",A1&" 🎲"))

🎲 NAT 20!

Spell slot counter

Put slots remaining 0-5 in A1. Arcane orbs show spell slots left.

=REPT("🔮",MIN(A1,B1))&REPT("○",MAX(0,B1-A1))&" spell slots"

🔮🔮🔮○○ spell slots

REPT MIN MAX

Dungeon boss approach

Put room progress 0-10 in A1.

=REPT("🚪",MIN(A1,B1))&REPT("・",MAX(0,B1-A1))&"👹"

🚪🚪🚪・・・・・・・👹

REPT MAX

Dungeon key collection

Put keys found 0-5 in A1.

=REPT("🗝️",A1)&REPT("🚪",MAX(0,B1-A1))

🗝️🗝️🗝️🚪🚪

REPT MAX

Dungeon torch supply

Put torches remaining 0-5 in A1.

=REPT("🔥",MIN(A1,B1))&REPT("🕯️",MAX(0,B1-A1))

🔥🔥🔥🕯️🕯️

REPT

Initiative order tracker

Put turn position 1-5 in A1. Shows whose turn is up in combat.

=CHOOSE(A1,"⚔️ 1st","🗡️ 2nd","🛡️ 3rd","🏹 4th","✨ 5th")

⚔️ 1st

CHOOSE

HP bar with temp HP

Put current HP in A1, max HP in B1, temp HP in C1. Orange shows bonus hit points.

=REPT("❤️",MIN(A1,B1))&REPT("🧡",MIN(C1,MAX(0,B1-A1)))&REPT("🖤",MAX(0,B1-A1-MIN(C1,MAX(0,B1-A1))))

❤️❤️❤️🧡🧡🖤🖤🖤

REPT MIN MAX

Long rest countdown

Put hours until long rest 0-8 in A1. Moons count down to a full recovery.

=REPT("🌙",MIN(A1,8))&REPT("○",MAX(0,8-A1))&" hrs to long rest"

🌙🌙🌙○○○○○ hrs to long rest

REPT MIN MAX

Short rest dots

Put short rests used 0-2 in A1. Dots track rests between encounters.

=REPT("●",MIN(A1,2))&REPT("○",MAX(0,2-A1))&" short rests"

●● short rests

REPT MIN MAX

Campaign session number

Put session number in A1. Scrolls mark how far the campaign has run.

="Session "&A1&": "&REPT("📜",MIN(A1,10))

Session 3: 📜📜📜

REPT MIN

XP to next level

Put current XP in A1 and XP needed in B1. Stars fill toward the next level.

=REPT("⭐",MIN(ROUND(A1/B1*5,0),5))&REPT("○",MAX(0,5-ROUND(A1/B1*5,0)))&" "&A1&"/"&B1&" XP"

⭐⭐⭐○○ 600/1000 XP

REPT ROUND MIN MAX

Alignment mood

Put alignment 1-5 in A1. Lawful good through chaotic evil mood labels.

=CHOOSE(A1,"⚖️ Lawful Good","🕊️ Neutral Good","⚔️ True Neutral","🔥 Chaotic Good","💀 Chaotic Evil")

⚔️ True Neutral

CHOOSE

Loot rarity color

Put rarity tier 1-5 in A1. Common through legendary loot colors.

=CHOOSE(A1,"⚪ Common","🟢 Uncommon","🔵 Rare","🟣 Very Rare","🟠 Legendary")

🟣 Very Rare

CHOOSE

Trap disarm progress

Put disarm progress 0-10 in A1. Keys replace warning signs as the trap yields.

=REPT("🗝️",MIN(A1,B1))&REPT("⚠️",MAX(0,B1-A1))&" disarm"

🗝️🗝️🗝️⚠️⚠️⚠️⚠️ disarm

REPT MIN MAX

Perception check meter

Put roll in A1 and DC in B1. Eyes pass; fog means the check failed.

=IF(A1>=B1,REPT("👁️",3)&" spotted!",REPT("🌫️",3)&" missed")

👁️👁️👁️ spotted!

IF REPT

Bardic inspiration dots

Put inspiration dice left 0-5 in A1. Music notes show bardic inspiration remaining.

=REPT("🎵",MIN(A1,B1))&REPT("○",MAX(0,B1-A1))&" inspiration"

🎵🎵🎵○○ inspiration

REPT MIN MAX

Wild magic surge bar

Put surge buildup 0-10 in A1. Sparkles climb until wild magic erupts.

=REPT("✨",MIN(A1,B1))&REPT("💥",IF(A1>=B1,1,0))&REPT("○",MAX(0,B1-A1-IF(A1>=B1,1,0)))&" wild magic"

✨✨✨✨✨✨✨💥○○ wild magic

REPT IF MIN MAX

Party formation march

Put party members gathered 0-5 in A1. Heroes march toward the dungeon gate.

=REPT("🧙",1)&REPT("🗡️",MAX(0,MIN(A1-1,4)))&REPT("・",MAX(0,B1-A1))&"🏰"

🧙🗡️🗡️・・・・・🏰

REPT MIN MAX

Related formula guides

Go deeper with Better Sheets

Members get full courses on CHAR, REPT, and dashboard design, plus hundreds of Google Sheets tutorials. Copy-paste formulas here stay free.

Unlock playground