9 Secret Excel Functions You Don’t Want To Miss
Let’s be real: Excel has been your ride-or-die since day one. It’s seen your late nights, your last-minute board decks, and your desperate Ctrl+Z marathons. You’ve probably got VLOOKUP tattooed on your soul by now. But here’s the plot twist nobody told you, Microsoft Excel has a secret menu.
I’m not talking about the stuff you learned in your corporate training or that one smug coworker who thinks pivot tables make them a wizard. I’m talking about functions that live in the shadows, quietly doing powerful things while most people are busy arguing over INDEX vs MATCH.
Some of these are truly hidden gems (hello, RTD), others are just wildly underused (AGGREGATE deserves more love), and a few are only available in newer versions of Excel. But if you’ve got them, they’ll make you look like a damn magician.
I ’ll walk through what each one does, exactly how to use it (step-by-step, no fluff), and real-life scenarios I’ve seen firsthand. You’ll learn how these functions can automate repetitive tasks, save hours, and make you look like a rockstar in front of the CFO.
Ready to unlock Excel’s secret level? Let’s go hunting for buried treasure.
9 Secret Excel Functions
1. RRI – Rate of Return for Investment
📌 What it Does:
RRI calculates the compound interest rate needed for an investment to grow from one value to another over a given period. Think of it like IRR’s shy, single-period cousin.
💡 Why It’s Secret:
Buried in Excel’s financial functions folder, and most folks default to IRR or CAGR formulas they Googled in 2015. This is pro Excel knowledge.
✅ How to Use It:
Formula:
=RRI(nper, pv, fv)
- nper: Number of periods (e.g. years)
- pv: Present value
- fv: Future value
Example:
If you invested $10,000 and it grew to $15,000 over 5 years:
=RRI(5, 10000, 15000) → 0.08447 (or 8.45% annual return)
📈 Real-Life Case:
I used this in a client review for a PE-backed company comparing portfolio ROI across business units. Instead of awkward annualized formulas and backward math, RRI gave us a clean, apples-to-apples ROI for each BU. We even dropped it into a Power BI card. Magic.
2. RTD – Real-Time Data
📌 What it Does:
RTD pulls live, updating data analytics into Excel from external applications like Bloomberg, ERP systems, or even homemade feeds if you’re a techy type.
💡 Why It’s Secret:
Requires a real-time data (RTD) server running on your machine—something most users aren’t even aware exists. But if your company has one (or you’re using Excel with Bloomberg Terminal), you’re sitting on a goldmine.
✅ How to Use It:
Formula:
=RTD(“progID”, , “topic1”, “topic2”, …)
For Bloomberg:
=RTD(“Bloomberg.Data.1″, ,”IBM US Equity”,”Last_Price”)
📈 Real-Life Case:
A treasury analyst I worked with built a real-time FX exposure tracker using RTD and Excel. He fed in live USD/EUR and USD/JPY rates, married them to AP/AR exposures, and created a rolling dashboard that made the CFO grin (yes, grins do happen in finance).
3. SUBTOTAL – The Filter-Friendly SUM
📌 What it Does:
Performs math on a data range (sum, average, count, etc.) while ignoring filtered-out or hidden rows. Huge win for data management.
💡 Why It’s Secret:
Most people use plain =SUM() and then panic when their filtered reports don’t add up. SUBTOTAL is like SUM, but smarter and more obedient.
✅ How to Use It:
Formula:
=SUBTOTAL(109, A2:A100)
- 109 = SUM while ignoring hidden rows
Other function codes:
- 101 = AVERAGE
- 103 = COUNT
- 104 = MAX
📈 Real-Life Case:
I used SUBTOTAL in a P&L report where departments were toggle-filtered by dropdown. Instead of building separate summaries, the same formulas adjusted instantly. One sheet, no extra tabs, no CFO rage.
4. AGGREGATE – The Bulletproof Calculator
📌 What it Does:
Performs calculations while letting you skip over errors, hidden rows, or both. Think of it as SUBTOTAL with body armor.
💡 Why It’s Secret:
Buried deep in function lists, and frankly, the syntax is a little weird until you get used to it.
✅ How to Use It:
Formula:
=AGGREGATE(9, 7, A2:A100)
- 9 = SUM
- 7 = Ignore hidden rows + errors
Other function codes:
- 1 = AVERAGE
- 14 = LARGE
- 15 = SMALL
📈 Real-Life Case:
Perfect when pulling revenue data from dirty exports where some formulas error out (#REF!, #DIV/0!, etc.). AGGREGATE let me sum only the clean rows—zero drama.
5. XLOOKUP – VLOOKUP’s Much Hotter Replacement
📌 What it Does:
Fetches a value from a range—vertically, horizontally, or backwards. Works left-to-right or right-to-left. Built-in error handling. One function to rule them all.
💡 Why It’s Secret:
Only available in newer versions (Excel 365+). Most folks still cling to the VLOOKUP function like it’s the office coffee machine.
✅ How to Use It:
Formula:
=XLOOKUP(“Product A”, A2:A100, B2:B100, “Not Found”)
- Searches for “Product A” in A2:A100, returns corresponding value from B2:B100.
📈 Real-Life Case:
Monthly close reporting used to mean 5 nested IFERROR INDEX-MATCH functions. XLOOKUP let us simplify the whole thing into one line—and we added defaults for missing data. Cleaner. Faster. CFO-approved.
6. FILTER / UNIQUE / SORT / SEQUENCE – The Dynamic Array Dream Team
📌 What They Do:
These four functions—released in newer versions of Excel (365 and Excel 2021+)—let you build dynamic, self-adjusting formulas that spill into adjacent cells. No more copy-paste. No more duplicate data. No more helper columns. No more pivot table sorcery.
Let’s break it down:
- FILTER() – pulls rows that meet a condition
- UNIQUE() – returns a list of distinct values
- SORT() – sorts data on the fly
- SEQUENCE() – generates a list of numbers or dates dynamically
💡 Why They’re Secret:
Unless you’re running a current Excel version, they don’t exist. And even if you are, they’re hiding in plain sight—Excel doesn’t exactly advertise that it now has drag-and-drop-level automation baked in.
✅ How to Use Them:
FILTER Example:
=FILTER(A2:B100, B2:B100=”West”)
Returns only rows where column B is “West”.
UNIQUE Example:
=UNIQUE(A2:A100)
Returns a list of unique customer names.
SEQUENCE Example:
=SEQUENCE(12,1,1,1)
Returns 1 through 12 down a column—perfect for months, periods, or row IDs.
📈 Real-Life Case:
Built a dynamic customer profitability report that updated automatically as we added new deals. Combined UNIQUE for the customer list, FILTER for their transactions, SEQUENCE for 12-month trend lines, and SORT to rank by revenue. What used to take three hours of manipulation took fifteen seconds. No joke. And when they combined it with conditional formatting? Unstoppable.
7. LET – Variables Inside Your Formulas
📌 What it Does:
LET lets you assign names to values or expressions inside a formula. Think: reusable variables in Excel formulas. Cleaner, faster, and much easier to audit.
💡 Why It’s Secret:
It’s new-ish (Excel 365+ only) and sounds like a developer thing. But once you use it, you’ll never want to go back to 300-character monster formulas.
✅ How to Use It:
Example:
=LET( revenue, A2, cost, B2, profit, revenue – cost, profit / revenue )
Instead of typing A2-B2 over and over, you define it once and reuse it. Like a civilized human.
📈 Real-Life Case:
I helped a client build a contribution margin model that originally had 17 nested IFs and 5 repeated lookup formulas. We restructured it with LET—cut the formula length in half, doubled the speed, and made it readable even for the intern.
8. LAMBDA – Create Your Own Custom Excel Functions
📌 What it Does:
This turns any Excel formula into a reusable, custom-named function without VBA. Yes, you can literally create your own Excel functions now. Finance nerds, rejoice.
💡 Why It’s Secret:
Again, its brand new, and sounds like something only data scientists should touch. But it’s actually pretty simple once you’ve built a couple.
✅ How to Use It:
Step 1: Create the formula
=LAMBDA(x, x*1.2)
Step 2: Define it as a named function
- Go to Formulas > Name Manager
- Add a new name like AddVAT
- Paste in your LAMBDA(x, x*1.2)
Now, you can just call =AddVAT(100) and get 120. Clean, powerful, and reusable.
📈 Real-Life Case:
One of my clients needed to calculate working days left in the month across 40 models. Instead of pasting the same NETWORKDAYS formula, we built a LAMBDA function called DaysLeft() and shared it across teams. Now it’s plug-and-play logic—no manual updates, no mess.
9. Power Query – Your Data Cleanup Dream Machine
📌 What it Does:
Power Query (a.k.a. “Get & Transform Data”) lets you pull, clean, reshape, and automate data workflows in just a few clicks. It’s ETL for Excel.
💡 Why It’s Secret:
It’s not a formula, it lives under the “Data” tab. Most people ignore it, thinking it’s for IT people or analysts with too much time. Wrong. It’s your backstage pass to data automation.
✅ How to Use It:
- Go to Data > Get Data > From Workbook/CSV/Folder
- Use the Power Query Editor to filter, reshape, and clean your data
- Load it to a worksheet or data model
- Refresh with a click—no more copy-paste madness
📈 Real-Life Case:
During a month-end close, I used Power Query to consolidate trial balances from 10 entities stored in 10 different Excel files. Instead of opening each one and doing the copy/paste shuffle, Power Query merged the data tables them into one clean table with a refresh button. Saved 8 hours. Every month.
Case Study: Build A Live Dashboard
Let’s make it real. I’m not just handing you shiny functions and waving goodbye—I’m showing you how to weaponize them. Here’s how I used five of the secret Excel tools we just covered to build a slick, refreshable dashboard that made an exec team stop mid-sentence in a board meeting and go: “Wait… how did you do that?”
The Scenario:
You’re an FP&A lead, and your CFO wants a live executive dashboard that:
- Pulls in actuals from multiple business units
- Compares against budget
- Shows live FX-adjusted revenue in USD
- Highlights top clients and cost centers
- Requires zero manual updates
You’ve got:
✔ Budget in one file
✔ Actuals in separate monthly exports
✔ Exchange rates pulled from Bloomberg
✔ A boss who’s allergic to waiting
Let’s build it step by step:
Step 1: Clean & Combine Data with Power Query
Use Power Query to pull in:
- Actuals from multiple Excel files in a folder (Data > Get Data > From Folder)
- Budget from a separate workbook
💡 Pro Tip: Set all data to refresh on open—your dashboard is now living and breathing.
Step 2: Real-Time FX Rates with RTD
Use RTD to bring in real-time exchange rates (e.g., EUR/USD, JPY/USD).
Example:
=RTD(“Bloomberg.Data.1″,, “EURUSD”, “Last_Price”)
Now convert revenue lines into USD in real time.
Step 3: Calculate Metrics with LET and AGGREGATE
Use LET to streamline calculations.
Instead of this mess:
=(SUMIFS(Actuals[Amount], Actuals[BU], “West”) – SUMIFS(Budget[Amount], Budget[BU], “West”)) / SUMIFS(Budget[Amount], Budget[BU], “West”)
Try this:
=LET( act, SUMIFS(Actuals[Amount], Actuals[BU], “West”), bud, SUMIFS(Budget[Amount], Budget[BU], “West”), (act – bud)/bud )
Use AGGREGATE to calculate top 5 clients without choking on errors from missing or hidden rows:
=AGGREGATE(14, 6, RevenueRange, 5)
Step 4: Build the Dashboard with FILTER, SORT, UNIQUE
Use FILTER to pull dynamic client lists:
=FILTER(ClientTable, ClientTable[Region]=”East”)
Use UNIQUE to auto-generate drop-downs or slicers:
=UNIQUE(ClientTable[Client])
Use SORT to show top-performing products:
=SORT(A2:B100, 2, -1)
Your dashboard now:
- Auto-updates client lists
- Sorts products dynamically
- Slices regions with dropdowns—all without touching a pivot table
Step 5: Tie It Together Visually with Camera Tool
Use the Camera Tool to take snapshots of key cells or charts. These “pictures” auto-update when the source data changes. Place them wherever you want on the dashboard sheet—clean, pretty, and boardroom-ready.
Optional: Wrap Up a Custom Function with LAMBDA
Need to calculate margin across different sheets and reports?
Make it once:
=LAMBDA(rev, cost, (rev – cost) / rev)
Save it as MarginCalc in Name Manager.
Now just do:
=MarginCalc(100000, 70000)
Boom. Done.
Bonus Tricks Worth Knowing
These little gems won’t show up on most Excel cheat sheets, but they’re absolute lifesavers—especially if you’re building dashboards, cleaning up disasters, or documenting a model that six different people have Frankensteined over three fiscal years.
1. Camera Tool – Live Snapshots for Dashboards
Most people don’t even know this thing exists—but it’s chef’s kiss for executive dashboards.
📌 What It Does:
Takes a live, image-style snapshot of any range in your sheet. It looks like a picture, but it updates automatically when the source data changes.
✅ How to Use It:
- Add the Camera Tool to your Quick Access Toolbar (File > Options > Quick Access Toolbar > Add Camera).
- Select your range (like a KPI summary, a chart, or data bars).
- Click the Camera icon, then click anywhere to paste your live image.
💡 When to Use It:
- Build a clean, presentation-ready dashboard on a separate sheet
- Layer visuals without cluttering up cell space
- Create printable reports that still update in real time
2. Go To Special – Spreadsheet Chaos Button
You know that moment when you’re staring at a 5,000-row sheet, trying to figure out what’s formula vs hardcoded vs broken vs blank? This is your one-click fix.
📌 What It Does:
Selects all cells matching specific criteria: blanks, formulas, constants, errors, etc.
✅ How to Use It:
- Select a range (or the whole sheet).
- Hit Ctrl + G, then click “Special”.
- Choose what you want to find: blanks, formulas, constants, errors, etc.
💡 When to Use It:
- Quickly highlight and fill in missing data
- Identify (and eliminate) hardcoded numbers in a model
- Select and fix every broken formula in one shot
3. FORMULATEXT – Instant Documentation
Ever inherit a file with formulas so complicated they look like someone fed Excel a bowl of spaghetti? FORMULATEXT is how you untangle the mess.
📌 What It Does:
Displays the actual formula from another cell as text. Great for auditing, documenting, or building Excel guides.
✅ How to Use It:
=FORMULATEXT(A1)
Shows the full formula from A1, right there in plain English.
💡 When to Use It:
- Create a documentation sheet in your workbook
- Build internal training resources for junior team members
- Show formulas side-by-side with outputs for debugging
4. Data Validation – Keep Users from Ruining Your Sheet
You’ve built the perfect model. Then someone types “Monday” into the “Department” field and breaks three formulas. Enter: Data Validation.
📌 What It Does:
Restricts what someone can enter into a cell—dropdown lists, number ranges, custom formulas.
✅ How to Use It:
- Select a cell or range
- Go to Data > Data Validation
- Choose your rules:
- List: Select from a dropdown
- Whole number: Only allow integers
- Custom: Use a formula like =ISNUMBER(A1)
💡 When to Use It:
- Limit entries to real GL codes, cost centers, or region names
- Prevent typos that wreck pivot tables
- Create dropdown menus tied to dynamic UNIQUE() lists for pro-level UX